在MySQL中，如何在插入时在另一列中使用自动增量值?

问题描述：

我希望在插入过程中将自动设置的自动增量包含在另一列中.例如，在ID为自动递增且Name为另一列的表中，我想做类似的事情

I am looking to have the automagically set autoincrement included in another column during the insert. For example in a table where ID is the autoincrement and Name is the other column, I'd like to do something like

`INSERT INTO Names (Name) VALUES (CONCAT("I am number ",ID));`

当前，我在不使用Name的情况下执行INSERT，然后必须立即使用$ mysqli-> insert_id进行UPDATE.

Currently, I do the INSERT without Name, then I have to immediately after do an UPDATE using $mysqli->insert_id.

我不想提前查询该表，因为在获取下一个自动增量值和插入之间可能会花很短的时间插入另一条记录.扳机可以工作，但似乎过大了.我只想知道是否可以在插入内容中引用自动增量.

I don't want to query the table in advance because, as small a time as it may be, another record could get inserted between getting the next autoincrement value and the insertion. A trigger could work, but it seems like overkill. I just want to know if I can reference the autoincrement within the insertion.

非常感谢！

答

问题并不像看起来那样容易.在BEFORE INSERT触发器中，尚未生成自动增量值(NEW.autoinc_column为0)，在AFTER INSERT触发器中，无法再更改要插入的值.

The problem is not as easy as it seems. In a BEFORE INSERT trigger, the autoincrement value hasn't been generated yet (NEW.autoinc_column is 0), and in an AFTER INSERT trigger, it is not possible anymore to change the values to be inserted.

对于MyISAM表，您可以检查表定义中的下一个AUTO_INCREMENT值:

With MyISAM tables, you could check the table definition for the next AUTO_INCREMENT value:

DELIMITER //    

    CREATE TRIGGER inserName BEFORE INSERT ON name FOR EACH ROW
    BEGIN
        DECLARE next_ai INT;
        SELECT auto_increment INTO next_ai
          FROM information_schema.tables
          WHERE table_schema = DATABASE() AND table_name = 'name';
        SET NEW.name = CONCAT("I am number ", next_ai);
    END //

DELIMITER ;

我相信，如果innodb_autoinc_lock_mode = 0(默认情况下不是这种情况)，这也适用于InnoDB表，但是我不确定(因为可能存在并发问题).

I believe this could work too with InnoDB tables if innodb_autoinc_lock_mode = 0 (not the case by default), but I am not sure (because of possible concurrency issues).

但是，如果concat的值始终相同，则最好使用如下视图:

But if the value to concat is always the same, you probably had better using a view such as:

CREATE VIEW names_v AS SELECT id, CONCAT("I am number ", id) AS name FROM names;

在MySQL中，如何在插入时在另一列中使用自动增量值?

相关推荐