关于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这个地方该怎么处理？

[解决办法]
like '%xxx%' --本意应该是这样，但是LZ的结果是
like %xxx% --看到了吗？ LZ的like后面的字符串少了单引号

试试看
like \'',CONCAT('%',keyword,'%'), '\'... --把单引号补上
[解决办法]
查了一下帮助文档，转义是可以用的，但是你又说不行，难搞了
http://dev.mysql.com/doc/refman/5.1/en/string-literals.html

There are several ways to include quote characters within a string: 
?A “'” inside a string quoted with “'” may be written as “''”. 
?A “"” inside a string quoted with “"” may be written as “""”. 
?Precede the quote character by an escape character (“\”). 
?A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment. 
The following SELECT statements demonstrate how quoting and escaping work: 
mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

可以用''''来表示一个单引号（前后两个是表示是字符串的单引号，中间两个单引号连在一起包含在单引号中表示一个单引号字符串） 
双引号和单引号差不多
还有一种就是转义 \' 

所以之前都给你建议了
要么用转义
'select ... like \'',CONCAT('%',keyword,'%'), '\' order by...'
要么用两个连着的单引号''
'select ... like ''',CONCAT('%',keyword,'%'), ''' order by...'
要么把单引号包含在双引号中"'"

结果LZ都说不行，我也8知道该怎么做了，实在不行，直接用char(39)试试吧


[解决办法]
会不会是%也要转义一下，LZ自己试试看吧

[解决办法]
木有用过navicat
不用in就用or
a in (1,2,3) 可以改成
a = 1 or a = 2 or a = 3

查看更多 下一篇