mysql存储过程like查询条件中文的错误

[Spring89]

create procedure query_search_apps_info(in keyword varchar(50) character set utf8,in page int)
begin
set @sql=concat('select app_id,app_name,app_packname,app_vsname,app_vscode,app_score,app_icon,app_apk,app_size from joye_market_app where app_name like ',CONCAT('%',keyword,'%'), ' order by app_priority asc,app_regtime desc limit ',page,',20');
PREPARE sqlstr from @sql;
execute sqlstr;
end

执行以上的存储过程输入参数：qq,2
结果会出错，错误是：1054 - unknown column 'qq' in field list
把keyword处写死替换成：'qq'，故没错！请问这个keyword这个地方该怎么处理？

[Spring89]

这些方法我都试过了，可还是不行！
set @sql=concat('select app_id,app_name,app_packname,app_vsname,app_vscode,app_score,app_icon,app_apk,app_size from joye_market_app where app_name like char(39),\%',keyword,'\%',char(39),' order by app_priority asc,app_regtime desc limit ',page,',20');

错误也是一样的，没有变化！

[brightyq]

6楼的不行？刚在oracle上试的好用，mysql没试。

[Spring89]

嗯，你有MySQL吗？反正我都试了就是不行！挺郁闷的！

[qybao]

没办法，下了个mysql帮你测试了一下，没问题
另一个帖子用转义测试的，没问题，LZ自己去看看吧，这个帖子用''连续的单引号测试，也没问题

以下是测试结果

mysql> drop procedure query_search_apps_info//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure query_search_apps_info(in keyword varchar(50) character
set utf8,in page int)
-> begin
-> set @sql=concat('select app_id,app_name,app_packname,app_vsname,app_vscod
e,app_score,app_icon,app_apk,app_size from joye_market_app where app_name like '
'%',keyword,'%'' order by app_priority asc,app_regtime desc limit ',page,',20');

-> select @sql;
-> end//
Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure query_search_apps_info//
+------------------------+----------+-------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------+----------------------+----------------------+--------------
------+
| Procedure | sql_mode | Create Procedure




| character_set_client | collation_connection | Database Coll
ation |
+------------------------+----------+-------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------+----------------------+----------------------+--------------
------+
| query_search_apps_info | | CREATE DEFINER=`root`@`localhost` PROCEDUR
E `query_search_apps_info`(in keyword varchar(50) character set utf8,in page int
)
begin
set @sql=concat('select app_id,app_name,app_packname,app_vsname,app_vscode,app_s
core,app_icon,app_apk,app_size from joye_market_app where app_name like ''%',key
word,'%'' order by app_priority asc,app_regtime desc limit ',page,',20');
select @sql;
end | cp932 | cp932_japanese_ci | latin1_swedish_ci |
+------------------------+----------+-------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------+----------------------+----------------------+--------------
------+
1 row in set (0.00 sec)

mysql> call query_search_apps_info('qq', 1)//
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+
| @sql

|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+
| select app_id,app_name,app_packname,app_vsname,app_vscode,app_score,app_icon,a
pp_apk,app_size from joye_market_app where app_name like '%qq%' order by app_pri
ority asc,app_regtime desc limit 1,20 |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

[qybao]

这里的测试只是打印sql看看是否正确，因为没有建表，所以也就不执行sql了

[Spring89]

哦，我是在navicat上试的， 不可以啊！你用navicat试下看！

[MiceRice]

组装SQL需要谨慎，按照你的语句，假设传入的keyword是：qq，page是1，那么组装后的SQL是：

select app_id,app_name,app_packname,app_vsname,app_vscode,app_score,app_icon,app_apk,app_size
from joye_market_app
where app_name like %qq%
order by app_priority asc,app_regtime desc limit 1, 20

左右两边的%外侧应该漏了 单引号 吧。

[Spring89]

嗯，那我应该怎样加上这个单引号呢？我试了好多种都不行！

[MiceRice]

用转义字符应该是可以的：

set @sql=concat('select app_id,app_name,app_packname,app_vsname,app_vscode,app_score,app_icon,app_apk,app_size from joye_market_app where app_name like \'%',keyword,'%\' order by app_priority asc,app_regtime desc limit ',page,',20');

[Spring89]

这样也是不行！
\'%',keyword,'%\'
这样的时候，当保存后，这二个/就会没有了！故变成了 '%',keyword,'%'

[MiceRice]

哦，MySQL对%有特殊含义么？

那么用：
app_name like \'\%',keyword,'\%\' order

[brightyq]

CONCAT('%',keyword,'%')

改成

CONCAT('''%''',keyword,'''%''')

试试

[qybao]

oh，LZ又问了同样的问题

转义或者''或者char(39)都试试吧
app_name like \'\%',keyword,'\%\' order
app_name like ''\%',keyword,'\%'' order
app_name like ', char(39),\%',keyword,'\%',char(39),' order

[Spring89]

[Quote=引用 5 楼 的回复:]
哦，MySQL对%有特殊含义么？

那么用：
app_name like \'\%',keyword,'\%\' order
[/Quote]
保存之后变成了：
like '\%',keyword,'\%'