将列名作为参数传递给mySQL中的存储过程 [英] Passing a column name as parameter to a stored procedure in mySQL
问题描述
我正在创建一些存储过程来管理我的数据库.特别是,我想创建一个存储过程来编辑特定行的一列,但是我想通过将列名作为参数传递来做到这一点.
I'm creating some stored procedures to manage my DB. In particular, i want to create a stored procedore to edit a column, of a specific row, but i want to do it dinamically, passing the column name as an argument.
这就是我想做的
CREATE PROCEDURE myDB.edit_myTable(
IN key CHAR(16),
IN col VARCHAR(100),
new_value VARCHAR(200)
)
UPDATE myDB.myTable SET col = new_value
使用参数 key
在 myTable
中找到我要编辑的特定行,并且我想使用参数 col
仅编辑我想要的列.
Using the parameter key
i find the specific row in myTable
that i want to edit, and i want to use the parameter col
to edit just the column that i want.
我已经尝试过使用 CONCATE()
或定义局部变量,就像我在其他主题上阅读的一样,但是我没有找到解决方案.
I've already tried using CONCATE()
or defining local variables, as i read on other topic, but i haven't find a solution.
有帮助吗?
推荐答案
您将需要使用动态SQL :
DELIMITER //
CREATE PROCEDURE myDB.edit_myTable(
IN key CHAR(16),
IN col VARCHAR(100),
new_value VARCHAR(200)
)
BEGIN
SET @s = CONCAT(
'UPDATE myDB.myTable SET `',
col, '` = ', QUOTE(new_value),
' WHERE key = ', QUOTE(key)
);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//
DELIMITER;
请注意,正如 Paul Spiegel 所述,对列名称使用变量会产生SQL风险.注射.一种提高安全性的解决方案是使用MySQL信息模式确保输入的 col
确实存在于目标表中:
Please note that, as commented by Paul Spiegel, using a variable for column name creates a risk of SQL injection. One solution for improve security would be to make sure that the input col
does exists in the target table, using MySQL information schema :
DELIMITER //
CREATE PROCEDURE myDB.edit_myTable(
IN key CHAR(16),
IN col VARCHAR(100),
new_value VARCHAR(200)
)
BEGIN
DECLARE col_exists INT;
SELECT COUNT(*) INTO col_exists
FROM information_schema.COLUMNS
WHERE TABLENAME = 'mytable' AND COLUMN_NAME = col;
IF (col_exists != 1) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = CONCAT('Column ', col, ' does not exist in table mytable');
END IF;
SET @s = CONCAT(
'UPDATE myDB.myTable SET `',
col, '` = ', QUOTE(new_value),
' WHERE key = ', QUOTE(key)
);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//
DELIMITER;
这篇关于将列名作为参数传递给mySQL中的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!