MySQL-触发器中不允许使用动态SQL [英] Mysql - dynamic SQL not allowed in trigger

问题描述

我想用动态SQL创建一个触发器，在该触发器中我从一个变量中获取一个列名.这是我的简化mysql模式:

I want to create a trigger with dynamic SQL where I get a column name from a variable. Here is my simplified mysql schema:

CREATE TABLE products (id int);
INSERT INTO products VALUES (1),(2);

CREATE TABLE attribute_values 
(product_id int, `key` varchar(100), value varchar(100));
INSERT INTO attribute_values VALUES
( 1, 'title', 'Orange'),
( 1, 'code', 'O125'),      
( 2, 'title', 'Pizza');


CREATE TABLE product_attributes 
SELECT  products.id,
        MAX(CASE WHEN attribute_values.key = 'title' THEN attribute_values.value END) title,
        MAX(CASE WHEN attribute_values.key = 'code' THEN attribute_values.value END) code
FROM    products JOIN attribute_values ON products.id = attribute_values.product_id
GROUP   BY products.id;


# trigger
DELIMITER //
CREATE PROCEDURE attribute_values_after_insert(IN product_id INT, IN column_name VARCHAR(100), IN val VARCHAR(100))
BEGIN
    SET @sql = NULL;
    SELECT concat('UPDATE product_attributes SET product_attributes.', column_name, '=', val, ' WHERE id=', product_id) INTO @sql;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @sql = NULL;
END//
DELIMITER ;

DELIMITER //
CREATE TRIGGER attribute_values_insert_trigger AFTER INSERT ON attribute_values FOR EACH ROW
BEGIN
    CALL attribute_values_after_insert(NEW.product_id, NEW.key, NEW.value);
END
DELIMITER ;

http://sqlfiddle.com/#!9/674fd/1

即:

餐桌产品:

+-------------+
| id          |
+-------------+
| 1           |
| 2           |
+-------------+

表attribute_values:

table attribute_values:

+-------------+----------+-----------+
| product_id  | key      | value     |
+-------------+----------+-----------+
| 1           | title    | Orange    |
| 1           | code     | O125      |
| 2           | title    | Pizza     |
+-------------+----------+-----------+

表product_attributes:

table product_attributes:

+-------------+----------+-----------+
| id          | title    | code      |
+-------------+----------+-----------+
| 1           | Orange   | 0125      |
| 2           | Pizza    |           |
+-------------+----------+-----------+

在触发器中，我想在插入表attribute_values之后更新表product_attributes.

In the trigger I want to update table product_attributes after inserting to table attribute_values.

当我执行查询时:

INSERT INTO attribute_values VALUES (2, 'code', '0126');

我收到错误消息:

MySQL的查询面板中不允许使用

DDL和DML语句.仅允许使用SELECT语句.将DDL和DML放在架构中面板.

DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.

在mysql中是否有解决方案?

Is there a solution for this in mysql?

感谢您的付出:)

推荐答案

MYSQL的功能和触发功能不支持动态插入但是该程序可以支持动态插入.

MYSQL IN FUNCTION AND TRIGGER NOT SUPPORT DYNAMIC INSERT BUT PROCEDURE CAN SUPPORT DYNAMIC INSERT.

因此您可以更改程序并在attribute_values表插入查询中进行操作.

SO YOU CAN MAKE CHANGES IN PROCEDURE AND MAKE PROCEDURE IN attribute_values TABLE INSERT QUERY.

插入属性值VALUES(product_id，column_name，val);

然后您将只需拨打电话程序

AND AFTER YOU WILL BE JUST CALL PROCEDURE

呼叫attribute_values_after_insert(2，'code'，132);

DELIMITER //
drop procedure if exists attribute_values_after_insert //
CREATE PROCEDURE attribute_values_after_insert(IN product_id INT, IN 
column_name VARCHAR(100), IN val VARCHAR(100))
BEGIN
SET @sql = NULL;

INSERT INTO attribute_values VALUES(product_id,column_name,val);

SELECT concat('UPDATE product_attributes SET product_attributes.', column_name, '=', val, ' WHERE id=', product_id) INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = NULL;
END//
DELIMITER ;

DELIMITER //

这篇关于MySQL-触发器中不允许使用动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！