mysql存储过程like查询条件中文的异常

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

SQL code

create procedure query_search_apps_info(in keyword varchar(50) character set utf8,in page int)beginset @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这个地方该怎么处理？

[解决办法]
组装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

左右两边的%外侧应该漏了 单引号 吧。
[解决办法]
CONCAT('%',keyword,'%')

改成

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

试试
[解决办法]
6楼的不行？刚在oracle上试的好用，mysql没试。
[解决办法]
没办法，下了个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>

查看更多 下一篇