如何防止SQL表主键重复_通过触发器实现逻辑ID分配-SQL-PHP中文网

主键重复时触发器无法阻止报错，因主键约束在校验阶段早于触发器执行；逻辑id应存入独立字段，避免干扰自增主键；postgresql触发器必须显式return new，mysql则无需；并发下逻辑id生成需防重复，推荐应用层id生成+数据库落库。

如何防止sql表主键重复_通过触发器实现逻辑id分配

主键重复时触发器根本拦不住 `INSERT` 报错

触发器无法阻止主键冲突报错，因为 PRIMARY KEY 约束在触发器执行前就已校验。哪怕你在 BEFORE INSERT 里改了 id，只要原始语句带了冲突值（比如显式插入 id = 100），MySQL/PostgreSQL 会在触发器前直接抛出 ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'。

实操建议：

所有 INSERT 必须省略主键字段（让数据库自动生成），或统一走存储过程封装
触发器只用于「生成逻辑ID」场景（如业务编号 ORD202405200001），不能替代主键约束本身
若必须显式指定主键，得先 SELECT ... FOR UPDATE 查重+加锁，再 INSERT，触发器此时已无意义

用 `BEFORE INSERT` 触发器生成逻辑ID要避开自增冲突

常见错误是：表设了 AUTO_INCREMENT 主键，又在触发器里手动赋值 NEW.id，结果下次 INSERT 用默认值时，自增值跳变、甚至报错。

使用场景：需要同时保留数字主键（用于关联、排序）和可读逻辑ID（存入 logic_id 字段）。

实操建议：

主键仍用 INT AUTO_INCREMENT，另加一个 VARCHAR 字段存逻辑ID（如 order_no）
触发器只写 NEW.order_no := CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(@next_seq := @next_seq + 1, 4, '0'));
别碰 NEW.id —— 让自增机制自己管
注意：MySQL 中 @next_seq 是会话级变量，高并发下需用 SELECT MAX(...) + 1 FROM ... FOR UPDATE 替代

PostgreSQL 的 `BEFORE INSERT` 触发器必须返回 `NEW`

现象：触发器函数写了但逻辑ID没生效，查表发现还是 NULL 或默认值。原因是函数末尾漏了 RETURN NEW;。

参数差异：

MySQL 触发器隐式返回修改后的行，无需显式 RETURN
PostgreSQL 触发器函数必须声明为 RETURNS TRIGGER，且最后一行必须是 RETURN NEW;（修改行）或 RETURN NULL;（丢弃本次插入）

示例（PostgreSQL）：

CREATE OR REPLACE FUNCTION gen_order_no()
RETURNS TRIGGER AS $$
BEGIN
  NEW.order_no := 'ORD' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || 
                   LPAD((SELECT COALESCE(MAX(SUBSTRING(order_no FROM 11)::INT), 0) + 1 
                         FROM orders 
                         WHERE order_no LIKE 'ORD' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || '%')::TEXT, 4, '0');
  RETURN NEW; -- 这行不能少
END;
$$ LANGUAGE plpgsql;