存储过程是这样写的
DELIMITER // DROP PROCEDURE IF EXISTS sp_login_info_query// CREATE PROCEDURE sp_login_info_query(IN v_login_id_str VARCHAR(10000)) BEGIN SELECT v_login_id_str; SELECT * FROM `login_info` WHERE login_id IN (v_login_id_str); END // DELIMITER ;
想得到下面的结果
SELECT * FROM `login_info` WHERE login_id IN (23694,23695,23697);
login_id user_id login_type login_ip login_date login_result
-------- ------- ---------- ----------- ------------------- --------------
23694 14829 1 220.231.2.3 2014-11-22 11:12:11 0
23695 14830 1 2014-11-22 11:14:52 0
23697 14831 1 2014-11-22 11:25:13 0
但是发现确实下面这种效果，居然只返回一条
CALL sp_login_info_query('23694,23695,23697');
v_login_id_str ------------------- 23694,23695,23697 login_id user_id login_type login_ip login_date login_result -------- ------- ---------- ----------- ------------------- -------------- 23694 14829 1 220.231.2.3 2014-11-22 11:12:11 0
这时prepare statement强大的功能就该派上用场了
DELIMITER // DROP PROCEDURE IF EXISTS sp_login_info_query// CREATE PROCEDURE sp_login_info_query(IN v_login_id_str VARCHAR(10000)) BEGIN SET @v_sql=CONCAT('SELECT * FROM `login_info` WHERE login_id IN (',v_login_id_str,');'); PREPARE stmt1 FROM @v_sql; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END // DELIMITER ;
测一把
CALL sp_login_info_query('23694,23695,23697')
login_id user_id login_type login_ip login_date login_result -------- ------- ---------- ----------- ------------------- -------------- 23694 14829 1 220.231.2.3 2014-11-22 11:12:11 0 23695 14830 1 2014-11-22 11:14:52 0 23699 14828 1 2014-11-22 11:33:00 1
完美！