mysql - 如何将 varchar 字符串传递给存储过程 where in 条件？

Question

如何将 varchar 字符串传递给存储过程 where in 条件？在这里，哪里条件不工作如何解决这个问题？

CALL searchStudentByName("%AAP%","'1','2','3'");

MYSQL 存储过程:

DELIMITER $$

USE `studentsdb`$$

DROP PROCEDURE IF EXISTS `searchStudentByName`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `searchStudentByName`(IN `search_key` VARCHAR(40),IN `student_ids` VARCHAR(350))
BEGIN  
    SELECT students.StudentId,students.StudentName FROM students WHERE students.Status='A' AND students.StudentName LIKE search_key AND students.StudentId NOT IN(student_ids) LIMIT 5;   
END$$

DELIMITER ;

Answer 1

您确实需要准备一个语句来使用这样的值列表。试试这个:

CREATE DEFINER=`root`@`localhost` PROCEDURE `searchStudentByName`(IN `search_key` VARCHAR(40),IN `student_ids` VARCHAR(350))
BEGIN  
    SET @sql = CONCAT("SELECT students.StudentId,students.StudentName FROM students WHERE students.Status='A' AND students.StudentName LIKE '", search_key, "' AND students.StudentId NOT IN(", student_ids, ") LIMIT 5");
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$