时间戳

首页 > 解决方案 > 带有游标的Mysql触发器以从一个表中获取所有列并插入到另一个表中

mysql - 带有游标的Mysql触发器以从一个表中获取所有列并插入到另一个表中

问题描述

我有两个表产品和产品日志。当我在产品表中插入、更新或删除记录时，我想调用执行以下操作的触发器

1:- fetch all column name from product table.
2:- arrange product table column name and value in json format 
e.g. log = {"col_first":"value_first", "col_second":"value_second"}
3:- After that insert into product log.

product table 
    create table product(
    id int auto_increment primary key,
    name varchar(100),
    description text,
    created_at datetime 
    );

product_log table
   create table product_log(
    id int auto_increment primary key,
    table_name varchar(100),
    logdata longtext,
    created_at datetime 
    );

这是我的触发代码，不正确请朋友们帮助我。任何建议将不胜感激。谢谢你 

DELIMITER $$
    create trigger after_product_insert
    AFTER INSERT on product for each ROW
    BEGIN 
        DECLARE finished INTEGER DEFAULT 0;
        declare stProduct text;
        DECLARE stRes text;
        declare prodct cursor for SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='product';

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

        OPEN prodct;
            stLoop: LOOP
                FETCH prodct INTO stProduct;
                IF finished = 1 THEN 
                    LEAVE stLoop;
                END IF;

                SET stRes = CONCAT(stProduct,",",stRes);
            END LOOP stLoop;
            insert into product_log(table_name, logdata) values ('product', stRes); 
        CLOSE prodct;

    END$$
DELIMITER ;

查询运行表后如下所示：

Product table
|----------|----------|------------------|---------------------|
|   id     |   name   | description      |   created_at        |
|----------|----------|------------------|---------------------|
|    1     | laptop   | all about laptop | 2020-01-29 00:00:00 |
|----------|----------|------------------|---------------------|

product_log table
|----------|----------------|---------------------------------------------|---------------------|
|   id     |   table_name   |     logdata                                 |   created_at        |
|----------|----------------|---------------------------------------------|---------------------|
|    1     |   product      | {"name":"laptop","description":"all ..",..} | 2020-01-29 00:00:00 |
|----------|----------------|---------------------------------------------|---------------------|

标签： mysql

解决方案

如果只是concat，则不能用作json。

json_set(columnname, key, value[, key, value]);

其次，如果您只想要长文本中的值对，您可以使用

select group_concat( concat(name, ":", description) SEPARATOR ",")...

推荐阅读