如何根据Type_ID限制data表插入值及触发器语法报错解决

问题背景 #

现有两张表结构：

data_type    data
=========    ====================
id | Type    Type_ID | data_count
---------    --------------------
 1 | a             1 | 50
 2 | b             2 | 100
 3 | c             3 | 30

需求：向data表插入数据时，根据Type_ID限制data_count的取值范围：

• Type_ID=1（类型a）：仅允许50-100的整数
• Type_ID=2（类型b）：允许0-100的整数
• Type_ID=3（类型c）：允许10-30的整数

原代码执行时触发语法错误：

"err": "ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$\n CREATE TRIGGER checkValueTBL \n BEFORE INSERT ON TBL FOR EACH ROW' at line 1"

错误原因分析 #

1. DELIMITER命令不被数据库服务器支持：DELIMITER是MySQL客户端专属命令，并非标准SQL语句，通过数据库驱动（如mysql2）执行时，服务器无法识别该命令，直接触发语法错误。
2. 表名、字段名不匹配：触发器中写的表名是TBL，字段是data_type、datas，和实际的data表、Type_ID、data_count字段完全不符。
3. 数值范围与需求脱节：原触发器中的取值区间和需求完全不一致，错误提示文本也混淆了类型对应关系。
4. 重复创建触发器：每次调用函数都执行CREATE TRIGGER，即使第一次执行成功，后续调用也会触发"触发器已存在"的错误。
5. a_id取值错误：原代码用固定值1937190关联a表，无法正确匹配刚插入的记录。

解决方案 #

1. 正确创建触发器（仅执行一次） #

触发器是数据库级别的持久化对象，需提前在数据库初始化阶段执行以下语句，无需每次插入都重复创建：

CREATE TRIGGER check_data_count_before_insert
BEFORE INSERT ON data
FOR EACH ROW
BEGIN
    IF NEW.Type_ID = 1 THEN
        IF NEW.data_count < 50 OR NEW.data_count > 100 THEN
            SIGNAL SQLSTATE '45000'
            SET message_text = 'Type a: data_count必须在50-100之间';
        END IF;
    ELSEIF NEW.Type_ID = 2 THEN
        IF NEW.data_count < 0 OR NEW.data_count > 100 THEN
            SIGNAL SQLSTATE '45000'
            SET message_text = 'Type b: data_count必须在0-100之间';
        END IF;
    ELSEIF NEW.Type_ID = 3 THEN
        IF NEW.data_count < 10 OR NEW.data_count > 30 THEN
            SIGNAL SQLSTATE '45000'
            SET message_text = 'Type c: data_count必须在10-30之间';
        END IF;
    END IF;
END;

2. 修正后的DAO函数代码 #

移除触发器创建逻辑，动态获取正确的a_id关联记录：

const createRecordData = async (userId, a1, a2, typeId, count) => {
    // 插入a表并获取自增主键id
    const [insertAResult] = await myDataSource.query(
        `INSERT INTO a (user_id, a1, a2) VALUES (?, ?, ?)`,
        [userId, a1, a2]
    );
    const aId = insertAResult.insertId;

    // 插入data表，触发器自动校验data_count
    const datas = await myDataSource.query(
        `INSERT INTO data (a_id, Type_ID, data_count) VALUES (?, ?, ?)`,
        [aId, typeId, count]
    );
    return datas;
};

关键修正点说明 #

• 移除DELIMITER：数据库驱动提交SQL时无需客户端分隔符命令，直接提交完整的触发器定义即可被服务器正确解析。
• 匹配表结构：触发器作用于data表，字段对应Type_ID和data_count，完全贴合实际表结构。
• 修正取值范围：严格按照需求设置各类型的data_count取值区间，错误提示文本对应正确类型，避免混淆。
• 触发器仅创建一次：将触发器创建放在数据库初始化流程中，避免重复创建导致的错误。
• 动态获取a_id：通过insertId获取刚插入a表的主键，保证数据关联的正确性，替代固定值的硬编码。

内容的提问来源于stack exchange，提问作者ggyoE