MySQL - BEFORE INSERT 触发器

MySQL：掌握 BEFORE INSERT 触发器

BEFORE INSERT 触发器是 MySQL 触发器的一种特定类型，它在将新行写入表之前执行其逻辑。这提供了一个关键的机会来检查、验证或修改传入的数据，确保其在永久存储之前符合应用程序的业务规则。它充当数据完整性的最终守门人。

A key feature of a BEFORE trigger is its ability to change the values that will be inserted. This is done by assigning new values to the NEW alias (e.g., SET NEW.column_name = 'new_value';).

核心概念和用例

BEFORE INSERT 触发器的主要目的是强制执行对于标准列约束而言过于复杂的规则。常见用例包括：

• 复杂验证： 根据同一行中其他列的值检查某个值是否符合条件。
• 数据清理： 清除空格、转换大小写或保持数据格式一致。
• 默认值生成： 根据其他传入数据为列计算默认值。
• 阻止插入： 如果数据无效，发出错误信号以中止 INSERT 操作。

语法

该语法是通用 CREATE TRIGGER 语句的一种专用形式：

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
    -- 使用 `NEW` 别名访问传入值的触发器逻辑。
    -- 示例：IF NEW.some_column IS NULL THEN ...
END;

实战示例：自动生成用户 Slug

我们考虑一个 users 表，在创建用户时，我们希望根据用户的 username 自动生成一个简洁、对 URL 友好的 slug。这正是 BEFORE INSERT 触发器的理想用途。

首先，users 表的定义如下：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(60) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

现在，创建触发器。它将获取 username，将其转换为小写，并将空格替换为连字符以创建 slug。

DELIMITER //

CREATE TRIGGER before_user_insert_generate_slug
BEFORE INSERT ON users FOR EACH ROW
BEGIN
    -- 根据新的用户名设置 slug。
    -- 当调用 INSERT 时，NEW.slug 最初为空或 NULL。
    SET NEW.slug = LOWER(REPLACE(NEW.username, ' ', '-'));
END//

DELIMITER ;

当我们插入新用户时，只需提供 username 和 email。slug 将由触发器处理。

INSERT INTO users (username, email) VALUES
    ('John Doe', 'john.doe@example.com'),
    ('Jane Smith', 'jane.smith@example.com');

验证

查询表以验证 slug 是否已自动正确生成。

SELECT username, email, slug FROM users;

结果展示了触发器的作用：

username	email	slug
John Doe	john.doe@example.com	john-doe
Jane Smith	jane.smith@example.com	jane-smith

使用客户端程序实现和测试

以下介绍如何使用现代代码模式，以编程方式创建触发器、插入数据并验证结果。这些示例提供了一个完整的测试周期。

NodeJS
Python
Java
PHP



使用 `mysql2/promise` 演示完整的创建-插入-验证周期。

const mysql = require('mysql2/promise');
require('dotenv').config();

async function testBeforeInsertTrigger() {
    const connection = await mysql.createConnection(process.env.DATABASE_URL);
    try {
        console.log('1. 正在设置表和触发器...');
        await connection.execute('DROP TABLE IF EXISTS users');
        await connection.execute(`CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL, slug VARCHAR(60) NOT NULL UNIQUE)`);
        await connection.execute('DROP TRIGGER IF EXISTS before_user_insert_generate_slug');
        await connection.execute(`CREATE TRIGGER before_user_insert_generate_slug BEFORE INSERT ON users FOR EACH ROW SET NEW.slug = LOWER(REPLACE(NEW.username, ' ', '-'))`);

        console.log('2. 正在插入测试数据...');
        await connection.execute("INSERT INTO users (username) VALUES ('My Test User')");

        console.log('3. 正在验证结果...');
        const [rows] = await connection.execute("SELECT username, slug FROM users WHERE username = 'My Test User'");

        if (rows.length > 0 && rows[0].slug === 'my-test-user') {
            console.log('成功：触发器工作正常！');
            console.log(rows[0]);
        } else {
            console.error('失败：触发器未按预期工作。');
        }
    } finally {
        await connection.end();
    }
}

testBeforeInsertTrigger().catch(console.error);



此 Python 示例使用 `mysql-connector-python` 演示完整的测试周期。

import mysql.connector
import os

config = {
    'user': os.getenv('DB_USER', 'root'),
    'password': os.getenv('DB_PASSWORD', 'password'),
    'host': os.getenv('DB_HOST', '127.0.0.1'),
    'database': os.getenv('DB_NAME', 'test_db'),
}

try:
    with mysql.connector.connect(**config) as conn:
        with conn.cursor() as cursor:
            print('1. 正在设置...')
            cursor.execute('DROP TABLE IF EXISTS users')
            cursor.execute('CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), slug VARCHAR(60) UNIQUE)')
            cursor.execute('DROP TRIGGER IF EXISTS before_user_insert_generate_slug')
            cursor.execute("CREATE TRIGGER before_user_insert_generate_slug BEFORE INSERT ON users FOR EACH ROW SET NEW.slug = LOWER(REPLACE(NEW.username, ' ', '-'))")

            print('2. 正在插入数据...')
            cursor.execute("INSERT INTO users (username) VALUES (%s)", ('My Python User',))
            conn.commit()

            print('3. 正在验证...')
            cursor.execute("SELECT slug FROM users WHERE username = %s", ('My Python User',))
            result = cursor.fetchone()
            if result and result[0] == 'my-python-user':
                print(f"成功：Slug 已生成: {result[0]}")
            else:
                print("失败：Slug 不正确。")
except mysql.connector.Error as err:
    print(f"错误: {err}")



此 Java 示例使用 try-with-resources 并演示了完整的工作流程。

import java.sql.*;

public class BeforeInsertTriggerTest {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test_db";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            System.out.println("1. 正在设置...");
            stmt.execute("DROP TABLE IF EXISTS users");
            stmt.execute("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), slug VARCHAR(60) UNIQUE)");
            stmt.execute("DROP TRIGGER IF EXISTS before_user_insert_generate_slug");
            stmt.execute("CREATE TRIGGER before_user_insert_generate_slug BEFORE INSERT ON users FOR EACH ROW SET NEW.slug = LOWER(REPLACE(NEW.username, ' ', '-'))");

            System.out.println("2. 正在插入数据...");
            String sqlInsert = "INSERT INTO users (username) VALUES ('My Java User')";
            stmt.executeUpdate(sqlInsert);

            System.out.println("3. 正在验证...");
            String sqlSelect = "SELECT slug FROM users WHERE username = 'My Java User'";
            try (ResultSet rs = stmt.executeQuery(sqlSelect)) {
                if (rs.next()) {
                    String slug = rs.getString("slug");
                    if ("my-java-user".equals(slug)) {
                        System.out.println("成功：Slug 已生成: " + slug);
                    } else {
                        System.out.println("失败：Slug 不正确: " + slug);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



此 PHP 示例使用 `mysqli` 扩展来执行测试周期。

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'root', 'password', 'test_db');

try {
    echo "1. 正在设置...\n";
    $mysqli->query("DROP TABLE IF EXISTS users");
    $mysqli->query("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), slug VARCHAR(60) UNIQUE)");
    $mysqli->query("DROP TRIGGER IF EXISTS before_user_insert_generate_slug");
    $mysqli->query("CREATE TRIGGER before_user_insert_generate_slug BEFORE INSERT ON users FOR EACH ROW SET NEW.slug = LOWER(REPLACE(NEW.username, ' ', '-'))");

    echo "2. 正在插入数据...\n";
    $stmt = $mysqli->prepare("INSERT INTO users (username) VALUES (?)");
    $username = 'My PHP User';
    $stmt->bind_param('s', $username);
    $stmt->execute();
    $stmt->close();

    echo "3. 正在验证...\n";
    $result = $mysqli->query("SELECT slug FROM users WHERE username = 'My PHP User'");
    $row = $result->fetch_assoc();
    if ($row && $row['slug'] === 'my-php-user') {
        echo "成功：Slug 已生成: {$row['slug']}\n";
    } else {
        echo "失败：Slug 不正确。\n";
    }
} catch (mysqli_sql_exception $e) {
    echo "错误: " . $e->getMessage() . "\n";
} finally {
    $mysqli->close();
}