MySQL触发器:插入时将auto_increment值复制到另一列 [英] MySQL Trigger: copy auto_increment value to another column upon insert

问题描述

插入时，我需要将auto_increment ID值复制到另一列中. 我认为我需要一个插入后"触发器，因为否则不知道新的ID.

我尝试过:

IF NEW.content IS NULL THEN
    SET NEW.content = NEW.id;
END IF

但它抱怨:

在触发器之后不允许更新NEW行

解决方案

在此代码上

  CREATE TRIGGER insert_example
  BEFORE INSERT ON notes
  FOR EACH ROW 
  SET NEW.content = (
        SELECT AUTO_INCREMENT 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'notes'
  );

我做了这样的事

SET NEW.content = (SELECT CONCAT('ID',LPAD(AUTO_INCREMENT, number,'0')) 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'notes');

如果您在ID字段上使用UNSIGNED_ZEROFILL ...，并且您可能需要自定义类型的公开" ID ...

I need to copy the auto_increment ID-value into another column upon insert. I think I need an "after insert" trigger, because otherwise the new ID is not known..?

I tried this:

IF NEW.content IS NULL THEN
    SET NEW.content = NEW.id;
END IF

But it complains:

Updating of NEW row is not allowed in after trigger

解决方案

On this code

  CREATE TRIGGER insert_example
  BEFORE INSERT ON notes
  FOR EACH ROW 
  SET NEW.content = (
        SELECT AUTO_INCREMENT 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'notes'
  );

i did something like this

SET NEW.content = (SELECT CONCAT('ID',LPAD(AUTO_INCREMENT, number,'0')) 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'notes');

in case you use UNSIGNED_ZEROFILL on the id field... and you may need a custom type of "public" id...

这篇关于MySQL触发器:插入时将auto_increment值复制到另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！