SQL插入时如何将未指定的anchorId设为新分配的自增ID

嘿，这个需求我之前也碰到过，其实在不同数据库里都有对应的实现方式，我给你梳理几种常用的方案，你可以根据自己用的数据库来选：

首先得确认你的id字段是自增主键哈——比如MySQL的AUTO_INCREMENT、PostgreSQL的SERIAL/IDENTITY或者SQL Server的IDENTITY，这是实现的前提，然后看下面的方案：

1. 数据库层面：用触发器/默认值自动处理（对应用透明） #

MySQL/MariaDB #

你可以创建一个AFTER INSERT触发器，插入后自动检查如果anchorId没传值（为NULL），就把它更新成刚生成的id：

DELIMITER //
CREATE TRIGGER set_default_anchorId
AFTER INSERT ON your_table_name
FOR EACH ROW
BEGIN
  IF NEW.anchorId IS NULL THEN
    UPDATE your_table_name 
    SET anchorId = NEW.id 
    WHERE id = NEW.id;
  END IF;
END //
DELIMITER ;

之后你插入数据时，只要不指定anchorId，触发器就会自动帮你把它设成新的id，比如插入Billy的时候，直接写INSERT INTO your_table_name (name) VALUES ('Billy');就可以了。

PostgreSQL #

PostgreSQL可以用BEFORE INSERT触发器，在插入之前就把anchorId设为即将生成的id，这样不用额外更新：

CREATE OR REPLACE FUNCTION set_anchorId_default()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.anchorId IS NULL THEN
    NEW.anchorId := NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_anchorId_default
BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE FUNCTION set_anchorId_default();

或者你也可以先给anchorId设置默认值为nextval('your_table_id_seq')（这里的序列名要和你id字段的自增序列一致），插入时直接用DEFAULT就行：

INSERT INTO your_table_name (name, anchorId)
VALUES ('Billy', DEFAULT)
RETURNING id, anchorId;

SQL Server #

SQL Server同样可以用AFTER INSERT触发器来处理：

CREATE TRIGGER trg_SetDefaultAnchorId
ON your_table_name
AFTER INSERT
AS
BEGIN
  UPDATE t
  SET t.anchorId = t.id
  FROM your_table_name t
  JOIN inserted i ON t.id = i.id
  WHERE i.anchorId IS NULL;
END;

2. 应用程序层面：手动处理（更灵活） #

如果不想用数据库触发器，也可以在代码里分两步操作：先插入记录（anchorId设为NULL），拿到新生成的id后再更新anchorId。

比如用Python的SQLAlchemy举个例子：

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData

engine = create_engine('mysql+pymysql://user:password@host/db_name')
metadata = MetaData()
users_table = Table(
    'users', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('name', String(50)),
    Column('anchorId', Integer)
)

with engine.connect() as conn:
    # 第一步：插入Billy，anchorId设为None
    insert_result = conn.execute(users_table.insert().values(name='Billy', anchorId=None))
    new_id = insert_result.inserted_primary_key[0]
    # 第二步：更新anchorId为新生成的id
    conn.execute(users_table.update().where(users_table.c.id == new_id).values(anchorId=new_id))
    conn.commit()

小提醒 #

• 如果你的id不是自增主键（比如用UUID），逻辑也类似：插入前先生成UUID，同时赋值给id和anchorId就行
• 触发器方式是数据库自动处理，应用代码不用改，但高并发场景下最好测试下性能
• 应用层面处理更灵活，比如可以加一些额外的判断逻辑，但需要多一次数据库操作

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