批量插入是原子的吗？
我有带有自动增量主键的表。我想向其中插入一堆数据并获取每个数据的密钥，而无需额外的查询。
START TRANSACTION;
INSERT INTO table (value) VALUES (x),(y),(z);
SELECT LAST_INSERT_ID() AS last_id;
COMMIT;
MySQL 能否保证所有数据都将插入到一个连续的有序流中，以便我可以轻松计算每个元素的 id？
id(z) = last_id;
id(y) = last_id - 1;
id(x) = last_id - 2;
I have table with auto increment primary key. I want insert a bunch of data into it and get keys for each of them without additional queries.
START TRANSACTION;
INSERT INTO table (value) VALUES (x),(y),(z);
SELECT LAST_INSERT_ID() AS last_id;
COMMIT;
Could MySQL guarantee, that all data would be inserted in one continuous ordered flow, so I can easily compute id's for each element?
id(z) = last_id;
id(y) = last_id - 1;
id(x) = last_id - 2;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您开始一个事务，然后将数据插入表中，则整个表将被该事务锁定（除非您开始使用事务隔离级别和/或锁定提示）。
这基本上就是交易的重点。为了防止外部操作改变（无论如何）您正在操作的内容。
这种“表锁”是大多数情况下的默认行为。
在异常情况下，您会发现 RDBMS 设置了某些选项，这意味着“正常”默认（表锁定）行为不是特定安装所发生的情况。如果是这种情况，您应该能够通过指定将表锁作为 INSERT 语句的一部分来覆盖默认值。
编辑：
我在 MS-SQL Server 上有很多经验，在许多其他 RDBMS 上也有不完整的经验。在那段时间里，我没有发现任何保证插入会按特定顺序发生。
原因之一是 INSERT 的 SELECT 部分可以并行计算。这意味着要插入的数据已无序准备好。
同样，在插入特别大量的数据时，RDBMS 可能会识别出新数据将跨越几页内存或磁盘空间。同样，这可能会导致并行操作。
据我所知，MySQL 有一个 row_number() 类型的函数，您可以在 SELECT 查询中指定该函数，并将其结果存储在数据库中。然后，您将能够依赖该字段（由您构建），但不能依赖自动增量 id 字段（由 RDBMS 构建）。
If you begin a transaction and then insert data into a table, that whole table will become locked to that transaction (unless you start playing with transaction isolation levels and/or locking hints).
That is bascially the point of transactions. To prevent external operations altering (in anyway) that which you are operating on.
This kind of "Table Lock" is the default behaviour in most cases.
In unusual circumstances you can find the the RDBMS has had certain options set that mean the 'normal' default (Table Locking) behaviour is not what happens for that particular install. If this is the case, you should be able to override the default by specifying that you want a Table Lock as part of the INSERT statement.
EDIT:
I have a lot of experience on MS-SQL Server and patchy experience on many other RDBMSs. In none of that time have I found any guarantee that an insert will occur in a specific order.
One reason for this is that the SELECT portion of the INSERT may be computed in parallel. That would mean the data to be inserted is ready out-of-order.
Equally where particularly large volumes of data are being inserted, the RDBMS may identify that the new data will span several pages of memory or disk space. Again, this may lead to parallelised operation.
As far as I am aware, MySQL has a row_number() type of function, that you could specify in the SELECT query, the result of which you can store in the database. You would then be able to rely upon That field (constructed by you) but not the auto-increment id field (constructed by the RDBMS).
据我所知，这几乎适用于所有 SQL 引擎。
As far as i know, this will work in pretty much all SQL-engines out there.