儲存過程WHERE條件不生效

業務上有個刪除操作需要涉及到幾張表，一條一條操作很麻煩，所以想寫個儲存過程來封裝下，原始語句如下：

DELETE FROM AUTH_AUTHORITY WHERE `ID` in ('f_view_access');
DELETE FROM AUTH_ROLE_AUTHORITY WHERE AUTHORITY_ID in ('f_view_access');

儲存過程如下：

DROP PROCEDURE IF EXISTS delete_authority;
DELIMITER $$
CREATE PROCEDURE delete_authority(IN authority_id CHAR(30)) 
BEGIN
	DELETE FROM AUTH_AUTHORITY WHERE `ID` = authority_id;
	DELETE FROM AUTH_ROLE_AUTHORITY WHERE `AUTHORITY_ID` = authority_id;	
END $$
DELIMITER ;
CALL delete_authority('f_view_access');
DROP PROCEDURE IF EXISTS delete_authority;

執行後發現AUTH_ROLE_AUTHORITY表的資料全部被刪除了，即WHERE條件不生效。

經過排查確認是變數（authority_id）命名的問題，總結起來就是：儲存過程DELETE語句中where條件後的變數名不能和欄位名相同，不區分大小寫的！其他SELECT、UPDATE、INSERT同理，坑爹。。。

修改變數名後的儲存過程如下，執行符合預期：

DROP PROCEDURE IF EXISTS delete_authority;
DELIMITER $$
CREATE PROCEDURE delete_authority(IN authority CHAR(30)) 
BEGIN
	DELETE FROM AUTH_AUTHORITY WHERE `ID` = authority;
	DELETE FROM AUTH_ROLE_AUTHORITY WHERE `AUTHORITY_ID` = authority;	
END $$
DELIMITER ;
CALL delete_authority('f_view_access');
DROP PROCEDURE IF EXISTS delete_authority;