转义不行?可能转义只适合在“”(双引号里)试试看 like ''',CONCAT('%',keyword,'%'), '''或者 like ',"'",CONCAT('%',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 ',"'",CONCAT('%',keyword,'%'), "'",' order by app_priority asc,app_regtime desc limit ',page,',20');
木有写过,不过思路就是这样了,关键是怎么在字符串中输入单引号的问题 帮你查了一下,可以用QUOTE()函数(感觉其实就是把首尾的单引号换成双引号的意思)set @sql=concat(quote(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,'%'), quote(' order by app_priority asc,app_regtime desc limit ),page,',20');
有这个函数吗,我怎么没查到! set @sql=concat(quote('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,'%'), quote(' order by app_priority asc,app_regtime desc limit' ),page,',20');是这样吗?可是还是不行!
查了一下帮助文档,转义是可以用的,但是你又说不行,难搞了 http://dev.mysql.com/doc/refman/5.1/en/string-literals.htmlThere 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)试试吧
木有办法,下了个mysql帮你测试了一下 以下是测试过程,用转义没问题mysql> delimiter // 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.02 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.03 sec)mysql>
mysql> delimiter // 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//就这一段对吧?为什么要定义一个//呢
奇怪,我就复制那段SQL,保存后,/自动去掉了!查询照样是错! 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 '%',keyword,'%' order by app_priority asc,app_regtime desc limit ',page,',20');
这保存就出问题啊
换成like '\'',CONCAT('%',keyword,'%'), '\',也是一样的,保存出问题!
like ''',CONCAT('%',keyword,'%'), '''或者
like ',"'",CONCAT('%',keyword,'%'), "'",'...
帮你查了一下,可以用QUOTE()函数(感觉其实就是把首尾的单引号换成双引号的意思)set @sql=concat(quote(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,'%'), quote(' order by app_priority asc,app_regtime desc limit ),page,',20');
set @sql=concat(quote('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,'%'), quote(' order by app_priority asc,app_regtime desc limit' ),page,',20');是这样吗?可是还是不行!
http://dev.mysql.com/doc/refman/5.1/en/string-literals.htmlThere 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)试试吧
以下是测试过程,用转义没问题mysql> delimiter //
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.02 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.03 sec)mysql>
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//就这一段对吧?为什么要定义一个//呢
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 '%',keyword,'%' order by app_priority asc,app_regtime desc limit ',page,',20');
我传的参数也是以,号分隔!那这怎么办啊
不用in就用or
a in (1,2,3) 可以改成
a = 1 or a = 2 or a = 3