首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > JAVA > J2EE开发 >

关于mysql存储过程like查询的有关问题

2012-08-10 
关于mysql存储过程like查询的问题SQL codecreate procedure query_search_apps_info(in keyword varchar(5

关于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

热点排行