代码如下,问题在注释中。DROP TABLE IF EXISTS alarm;
CREATE TABLE alarm ( id int(11) NOT NULL auto_increment, name varchar(255) default NULL,PRIMARY KEY (id));INSERT INTO alarm (name) values('aa');
INSERT INTO alarm (name) values('bb');DROP procedure if exists statalarm;
delimiter //
create procedure statalarm()
begin
SET @id = '1,2';
/*
下面的语句本意是组成 select * from alarm where id IN (1,2);
但调用结果只能查到id为1的记录
*/
SET @sql = 'select * from alarm where id IN (?)';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
/*
下面的语句调用是正确的。但为什么上面使用using @id 语句的结果不对呢?
*/
SET @sql = concat('select * from alarm where id IN (',@id,')');
PREPARE stmt FROM @sql;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;end;//
delimiter ;
PREPARE EXECUTE
CREATE TABLE alarm ( id int(11) NOT NULL auto_increment, name varchar(255) default NULL,PRIMARY KEY (id));INSERT INTO alarm (name) values('aa');
INSERT INTO alarm (name) values('bb');DROP procedure if exists statalarm;
delimiter //
create procedure statalarm()
begin
SET @id = '1,2';
/*
下面的语句本意是组成 select * from alarm where id IN (1,2);
但调用结果只能查到id为1的记录
*/
SET @sql = 'select * from alarm where id IN (?)';
PREPARE stmt FROM @sql;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;
/*
下面的语句调用是正确的。但为什么上面使用using @id 语句的结果不对呢?
*/
SET @sql = concat('select * from alarm where id IN (',@id,')');
PREPARE stmt FROM @sql;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;end;//
delimiter ;
PREPARE EXECUTE
SET @sql = 'select * from alarm where id IN (?)';
select @sql;SET @sql = concat('select * from alarm where id IN (',@id,')');
select @sql;
看一下拼接后的语句就明白了。
第一种最后拼成的sql是 :select * from alarm where id IN ('1,2')
第二种最后拼成的sql是:select * from alarm where id IN (1,2)
但还有两个问题不明白:
1.下面的select后只能看到sql是:select * from alarm where id IN (?)。如果我想看到EXECUTE stmt USING @id; 语句执行的真正的sql语句,应该怎么做呢?SET @id = '1,2';
SET @sql = 'select * from alarm where id IN (?)';
select @sql;2. 第一种方法拼出的字符串为什么会把字符串的引号也拼进去了呢?有没有办法只拼字符串的值进去,向c++的sprintf一样?
select @sql;
你的结果是什么,应该没有字符串的引号,只是字符串的值
SET @sql = 'select * from alarm where find_in_set(id ,?)';
select @sql;
SET @sql = 'select * from alarm where id IN (?)';
PREPARE stmt FROM @sql;
/*
实际执行的是:select * from alarm where id IN ('1,2');
并非是预想的:select * from alarm where id IN (1,2);
*/
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;解决办法就是使用concat拼字符串,或者 使用 where find_in_set(id ?)谢谢各位