各位观众老爷，大家好！今天咱们不聊风花雪月，来点硬核的——MySQL触发器中的动态SQL，保证让你的数据库操作骚气十足，逻辑复杂到飞起！
先别急着关掉页面，我知道触发器听起来就让人头大，动态SQL更是让人想挠墙。但是别怕，今天我会用最通俗易懂的语言，加上各种骚操作，让你彻底搞懂这俩家伙，并且把它们玩出花来。
一、啥是触发器？为啥要用它？
简单来说，触发器就是一个“监听器”，它时刻盯着你的数据库表，一旦发生了你设定的事件（比如插入、更新、删除），它就会自动执行你预先定义好的SQL语句。
想象一下，你是一家电商平台的程序员，用户下订单的时候，你不仅要往订单表里插入数据，还要扣减库存、生成积分、发送短信等等。如果每次都手动写代码来实现这些逻辑，那岂不是要累死？这时候，触发器就派上用场了！
你可以创建一个触发器，监听订单表的INSERT事件，当有新订单插入时，触发器自动执行扣减库存、生成积分等操作。这样一来，你的代码就变得简洁多了，而且保证了数据的一致性。
触发器的主要优点：
- 自动化： 触发器自动执行，无需人工干预。
- 数据一致性： 确保数据操作的原子性，防止数据不一致。
- 简化应用逻辑： 将一些复杂的业务逻辑放在数据库层，简化应用代码。
触发器的类型：
MySQL 提供了多种类型的触发器，可以根据不同的事件触发：
- BEFORE INSERT： 在插入数据之前触发。
- AFTER INSERT： 在插入数据之后触发。
- BEFORE UPDATE： 在更新数据之前触发。
- AFTER UPDATE： 在更新数据之后触发。
- BEFORE DELETE： 在删除数据之前触发。
- AFTER DELETE： 在删除数据之后触发。
二、动态SQL：让你的SQL活起来！
动态SQL，顾名思义，就是可以根据不同的条件，动态生成不同的SQL语句。这玩意儿就像变形金刚，可以根据你的需要，变成各种不同的形态。
举个例子，你想写一个通用的查询函数，可以根据用户传入的参数，查询不同的字段。如果不用动态SQL，你就需要写很多个不同的查询函数，代码冗余不说，维护起来也麻烦。但是有了动态SQL，你只需要写一个函数，根据参数动态生成SQL语句，就可以实现各种不同的查询。
动态SQL的优点：
- 灵活性： 可以根据不同的条件生成不同的SQL语句。
- 代码复用： 可以减少代码冗余，提高代码复用率。
- 可维护性： 简化代码结构，提高代码可维护性。
三、触发器 + 动态SQL：骚操作的开始！
现在，让我们把触发器和动态SQL这两个神器结合起来，看看能擦出什么样的火花！
假设你有一个product表，记录了商品的各种信息，还有一个product_log表，用于记录商品的变更历史。你希望每次修改product表的时候，都能自动往product_log表里插入一条记录，记录这次修改的内容。
如果用静态SQL，你需要写很多个不同的触发器，分别处理不同的字段修改。但是有了动态SQL，你只需要写一个触发器，根据修改的字段，动态生成SQL语句，就可以实现所有的记录功能。
代码示例：
-- 创建 product 表
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
quantity INT NOT NULL,
description TEXT
);
-- 创建 product_log 表
CREATE TABLE product_log (
id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
field_name VARCHAR(255) NOT NULL,
old_value TEXT,
new_value TEXT,
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建触发器
DELIMITER //
CREATE TRIGGER product_update_log
AFTER UPDATE
ON product
FOR EACH ROW
BEGIN
DECLARE field_name VARCHAR(255);
DECLARE old_value TEXT;
DECLARE new_value TEXT;
DECLARE sql_statement TEXT;
-- 检查 name 字段是否被修改
IF OLD.name <> NEW.name THEN
SET field_name = 'name';
SET old_value = OLD.name;
SET new_value = NEW.name;
SET sql_statement = CONCAT('INSERT INTO product_log (product_id, field_name, old_value, new_value) VALUES (', NEW.id, ', '', field_name, '', '', old_value, '', '', new_value, '');');
-- 执行动态SQL
SET @sql = sql_statement;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
-- 检查 price 字段是否被修改
IF OLD.price <> NEW.price THEN
SET field_name = 'price';
SET old_value = OLD.price;
SET new_value = NEW.price;
SET sql_statement = CONCAT('INSERT INTO product_log (product_id, field_name, old_value, new_value) VALUES (', NEW.id, ', '', field_name, '', '', old_value, '', '', new_value, '');');
-- 执行动态SQL
SET @sql = sql_statement;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
-- 检查 quantity 字段是否被修改
IF OLD.quantity <> NEW.quantity THEN
SET field_name = 'quantity';
SET old_value = OLD.quantity;
SET new_value = NEW.quantity;
SET sql_statement = CONCAT('INSERT INTO product_log (product_id, field_name, old_value, new_value) VALUES (', NEW.id, ', '', field_name, '', '', old_value, '', '', new_value, '');');
-- 执行动态SQL
SET @sql = sql_statement;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
-- 检查 description 字段是否被修改
IF OLD.description <> NEW.description THEN
SET field_name = 'description';
SET old_value = OLD.description;
SET new_value = NEW.description;
SET sql_statement = CONCAT('INSERT INTO product_log (product_id, field_name, old_value, new_value) VALUES (', NEW.id, ', '', field_name, '', '', old_value, '', '', new_value, '');');
-- 执行动态SQL
SET @sql = sql_statement;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END //
DELIMITER ;代码解释：
DELIMITER //： 这玩意儿是MySQL的命令分隔符，默认是;。因为我们的触发器代码里有很多个;，所以需要先修改分隔符，防止MySQL把触发器代码提前截断。CREATE TRIGGER product_update_log： 创建一个名为product_update_log的触发器。AFTER UPDATE ON product： 这个触发器在product表更新之后触发。FOR EACH ROW： 表示每一行数据更新都会触发这个触发器。DECLARE： 声明一些变量，用于存储字段名、旧值、新值和SQL语句。IF OLD.name <> NEW.name THEN： 判断name字段是否被修改。OLD表示修改前的值，NEW表示修改后的值。SET field_name = 'name'： 如果name字段被修改，就设置field_name为'name'。SET old_value = OLD.name： 设置old_value为修改前的name值。SET new_value = NEW.name： 设置new_value为修改后的name值。SET sql_statement = CONCAT(...)： 使用CONCAT函数拼接SQL语句，生成一个INSERT语句，用于往product_log表里插入一条记录。SET @sql = sql_statement： 将生成的SQL语句赋值给用户变量@sql。PREPARE stmt FROM @sql： 预处理SQL语句，提高执行效率。EXECUTE stmt： 执行预处理过的SQL语句。DEALLOCATE PREPARE stmt： 释放预处理语句的资源。END IF： 结束IF语句。END //： 结束触发器代码。DELIMITER ;： 将命令分隔符改回默认的;。
测试：
-- 插入一些测试数据
INSERT INTO product (name, price, quantity, description) VALUES ('iPhone 13', 6999.00, 100, 'A new iPhone');
-- 修改商品信息
UPDATE product SET price = 7999.00, quantity = 90 WHERE id = 1;
-- 查询 product_log 表
SELECT * FROM product_log;你会发现，product_log表里已经插入了一条记录，记录了price和quantity字段的修改历史。
四、更骚的操作：利用JSON存储修改前后的值
上面的例子虽然实现了记录商品变更历史的功能，但是有一个缺点：如果商品有很多个字段，你需要写很多个IF语句，代码冗余不说，维护起来也麻烦。
更骚的操作是，我们可以利用JSON来存储修改前后的值，这样只需要写一个IF语句，就可以处理所有的字段修改。
代码示例：
-- 修改 product_log 表，增加 old_data 和 new_data 字段
ALTER TABLE product_log ADD COLUMN old_data JSON;
ALTER TABLE product_log ADD COLUMN new_data JSON;
-- 删除原来的 field_name, old_value, new_value 字段
ALTER TABLE product_log DROP COLUMN field_name;
ALTER TABLE product_log DROP COLUMN old_value;
ALTER TABLE product_log DROP COLUMN new_value;
-- 创建触发器
DELIMITER //
CREATE TRIGGER product_update_log
AFTER UPDATE
ON product
FOR EACH ROW
BEGIN
DECLARE old_data JSON;
DECLARE new_data JSON;
DECLARE sql_statement TEXT;
-- 将修改前的数据转换为JSON
SET old_data = JSON_OBJECT('name', OLD.name, 'price', OLD.price, 'quantity', OLD.quantity, 'description', OLD.description);
-- 将修改后的数据转换为JSON
SET new_data = JSON_OBJECT('name', NEW.name, 'price', NEW.price, 'quantity', NEW.quantity, 'description', NEW.description);
-- 构建动态SQL语句
SET sql_statement = CONCAT('INSERT INTO product_log (product_id, old_data, new_data) VALUES (', NEW.id, ', '', old_data, '', '', new_data, '');');
-- 执行动态SQL
SET @sql = sql_statement;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;代码解释：
ALTER TABLE product_log ADD COLUMN old_data JSON： 往product_log表里增加一个old_data字段，用于存储修改前的数据，类型为JSON。ALTER TABLE product_log ADD COLUMN new_data JSON： 往product_log表里增加一个new_data字段，用于存储修改后的数据，类型为JSON。JSON_OBJECT('name', OLD.name, ...)： 使用JSON_OBJECT函数将修改前的数据转换为JSON格式。JSON_OBJECT('name', NEW.name, ...)： 使用JSON_OBJECT函数将修改后的数据转换为JSON格式。SET sql_statement = CONCAT(...)： 使用CONCAT函数拼接SQL语句，生成一个INSERT语句，用于往product_log表里插入一条记录。
测试：
-- 修改商品信息
UPDATE product SET price = 8999.00, description = 'A super new iPhone' WHERE id = 1;
-- 查询 product_log 表
SELECT * FROM product_log;你会发现，product_log表里已经插入了一条记录，old_data和new_data字段分别存储了修改前后的JSON数据。
五、安全问题：SQL注入！
动态SQL虽然强大，但是也存在安全隐患——SQL注入！
SQL注入是指，攻击者通过构造恶意的SQL语句，绕过应用程序的验证，直接操作数据库。如果你的动态SQL语句没有进行适当的过滤和转义，就很容易受到SQL注入攻击。
如何防止SQL注入？
- 使用参数化查询： 参数化查询可以将用户输入的数据作为参数传递给SQL语句，而不是直接拼接SQL语句，从而防止SQL注入。
- 对用户输入的数据进行过滤和转义： 可以使用MySQL提供的函数，对用户输入的数据进行过滤和转义，防止恶意字符被注入到SQL语句中。
代码示例（使用参数化查询）：
DELIMITER //
CREATE TRIGGER product_update_log
AFTER UPDATE
ON product
FOR EACH ROW
BEGIN
DECLARE old_data JSON;
DECLARE new_data JSON;
-- 将修改前的数据转换为JSON
SET old_data = JSON_OBJECT('name', OLD.name, 'price', OLD.price, 'quantity', OLD.quantity, 'description', OLD.description);
-- 将修改后的数据转换为JSON
SET new_data = JSON_OBJECT('name', NEW.name, 'price', NEW.price, 'quantity', NEW.quantity, 'description', NEW.description);
-- 使用参数化查询
SET @product_id = NEW.id;
SET @old_data_param = old_data;
SET @new_data_param = new_data;
PREPARE stmt FROM 'INSERT INTO product_log (product_id, old_data, new_data) VALUES (?, ?, ?)';
SET @a = @product_id;
SET @b = @old_data_param;
SET @c = @new_data_param;
EXECUTE stmt USING @a, @b, @c;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;代码解释：
PREPARE stmt FROM 'INSERT INTO product_log ... VALUES (?, ?, ?)'： 使用?作为占位符，表示参数。EXECUTE stmt USING @product_id, @old_data, @new_data： 使用USING关键字将参数传递给SQL语句。
这样一来，即使攻击者在用户输入的数据中注入了恶意字符，也不会被当做SQL语句执行，从而防止了SQL注入攻击。
六、总结
今天我们聊了MySQL触发器中的动态SQL，从触发器的基本概念，到动态SQL的骚操作，再到安全问题，希望能够让你对这俩家伙有一个更深入的了解。
记住，触发器和动态SQL都是强大的工具，但是也要小心使用，防止出现意外情况。
最后，希望大家在实际开发中，能够灵活运用触发器和动态SQL，写出更优雅、更高效的代码！
今天的讲座就到这里，谢谢大家！