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_et_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这个地方该怎么处理?
from joye_et_app
where app_name like %qq%
order by app_priority asc,app_regtime desc limit 1, 20左右两边的%外侧应该漏了 单引号 吧。
\'%',keyword,'%\'
这样的时候,当保存后,这二个/就会没有了!故变成了 '%',keyword,'%'
app_name like \'\%',keyword,'\%\' order
app_name like \'\%',keyword,'\%\' order
app_name like ''\%',keyword,'\%'' order
app_name like ', char(39),\%',keyword,'\%',char(39),' order
like '\%',keyword,'\%'
set @sql=concat('select app_id,app_name,app_packname,app_vsname,app_vscode,app_score,app_icon,app_apk,app_size from joye_et_app where app_name like char(39),\%',keyword,'\%',char(39),' order by app_priority asc,app_regtime desc limit ',page,',20');错误也是一样的,没有变化!
另一个帖子用转义测试的,没问题,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_et_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_et_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_et_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>