mysql 存储过程 单引号_MySQL：将参数传递给存储过程的单引号问题

发送prmMenusList作为整数列表而不是字符串：

UPDATE RolesMenus SET Enabled=0 WHERE MenuID IN(1,7,19,20,21,26) AND RoleID = 74;如果您的存储过程只进行了更新，我认为您可以编写整个函数，如：

Public function enableMenus($selectedMenus, $roleID){

$this->db->where_in('MenuID', $selectedMenus);

$this->db->where('RoleID', $roleID);

$result = $this->db->update('RolesMenus', array( 'Enabled' => 0 ) );

$tempResult = $result;

$result->next_result();

return $tempResult->result();

}据我所知，直到现在，没有直接的方法将数组传递给存储过程。

您可以创建另一个存储过程来解析包含id的字符串，创建一个存储数组值的临时表，然后将该临时表作为参数发送到实际的存储过程。

但是，在我看来，这更令人头痛：

DELIMITER $$

DROP PROCEDURE IF EXISTS `YourDB`.`sp_parseIntList` $$

CREATE DEFINER=`hotstuff`@`%` PROCEDURE `sp_parseIntList`(

_intlist TEXT )

BEGIN

DECLARE comma INT DEFAULT 0;

DECLARE mylist TEXT DEFAULT _intlist;

DECLARE temp TEXT DEFAULT '';

DECLARE strlen int DEFAULT LENGTH(_intlist);

CREATE TEMPORARY TABLE TempTable (num int) TYPE=INNODB;

SET comma = LOCATE(',',mylist);

WHILE strlen > 0 DO

IF

comma = 0

THEN

SET temp = TRIM(mylist);

SET mylist = '';

SET strlen = 0;

ELSE

SET temp = TRIM(SUBSTRING(mylist,1,comma));

SET mylist = TRIM(SUBSTRING(mylist FROM comma+1));

SET strlen = LENGTH(mylist);

END IF;

IF CAST(temp as UNSIGNED) != 0

THEN

INSERT INTO TempTable VALUES(CAST(temp as UNSIGNED));

END IF;

SET comma = LOCATE(',',mylist);

END WHILE;

SELECT * FROM TempTable;

DROP TEMPORARY TABLE IF EXISTS TempTable;

END $$

DELIMITER ;