MySQL多行插入是否可以获取顺序的自动增量ID?

本文介绍了MySQL多行插入是否可以获取顺序的自动增量ID?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我认为这是对的，但是我没有在网上找到任何可以确认的东西.我可以使用last_insert_id()获取为autoincrement字段生成的第一个ID，但是我可以假定下一条记录将具有顺序ID吗?还是另一个用户可以获取一个ID，以使生成的ID不连续?

I think this is true, but I haven't found anything on the web to confirm it. I can get the first id generated for the autoincrement field using last_insert_id(), but can I assume that the next records will have sequential IDs? Or could another user grab an id so that the resulting IDs are not sequential?

示例:将值(1,2)，(3,4)，(5,6)，...，(10000,10001)插入mytable(asdf，qwer);

Example: insert into mytable (asdf, qwer) values (1,2), (3,4), (5,6), ... , (10000,10001);

如果mytable有一个autoincrement列，并且如果两个用户同时运行此语句，一个用户将获取10000个顺序ID，而另一个用户将获取下一个10000个顺序ID?数百名用户呢?

If mytable has an autoincrement column, and if two users run this statement at the same time, will one user grab 10000 sequential IDs, and the other user the next 10000 sequential IDs? How about for hundreds of users?

谢谢.

推荐答案

这取决于所使用的存储引擎.

It depends on the storage engine used.

• MyISAM 保证插入的行具有顺序ID，因为在插入过程中会保持表级锁定.
• InnoDB :除非innodb_autoinc_lock_mode设置为 interleaved(2)，否则插入的行将具有顺序ID.默认情况下，自从5.1和traditional模式开始以来，InnoDB便以consecutive模式运行.

• MyISAM guarantees the inserted rows to have sequential ID's, since a table-level lock is held during the insert.
• InnoDB: unless innodb_autoinc_lock_mode is set to interleaved (2), the inserted rows will have sequential ID's. By default InnoDB runs in consecutive mode since 5.1 and traditional mode prior.

有关innodb_autoinc_lock_mode选项的更多信息，请参见 13.6.4.3. InnoDB中的AUTO_INCREMENT处理

For more on the innodb_autoinc_lock_mode option, see 13.6.4.3. AUTO_INCREMENT Handling in InnoDB