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

完美！