mysql是否在单个查询原子内多次插入? [英] Are mysql multiple inserts within a Single query atomic?

问题描述

我在单个查询中进行多次插入:

I'm doing multiple inserts in a single query:

INSERT INTO table (c1, c2) VALUES (1,2),
                                  (2,3),
                                  (3,4),......
         ON DUPLICATE KEY UPDATE c2 = VALUES(c2)

现在假设在查询中指定了成千上万的值(因此省略号)....

Now suppose that there's over tens of thousands of VALUES specified in the query (hence the ellipsis)....

在某些情况下，是否可能会某些值设法在数据库中插入/更新，而其余部分却未插入/更新，这可能是由于某种数据库错误/故障/内存耗尽而引起的等等?

Can there ever be a case in which some parts of the VALUES managed to get inserted/updated in the database but the rest did not get inserted/updated possibly due to some sort of db error/failure/memory-running-out etc?

mysql是全部查询还是全部查询?

对于每个执行的mysql查询，是否可以平滑地插入/更新查询中指定的所有值，或者不插入/更新所有值?

Is it true that for every mysql query executed, either all values specified in the query will be inserted/updated smoothly, or none of the values will be inserted/updated?

推荐答案

ACID (原子性，一致性，隔离性，耐久性)属性用于描述数据库中的此类行为.仅当我们正在处理并发修改时，原子性才重要.为了确保一致性，必须达到一定程度的隔离.但是，多个事务运行的隔离度越高，DBMS通常具有的性能就越差.因此，存在所谓的"隔离级别"，其中指出了哪些错误可能发生在DBMS中，而不能发生.

ACID (Atomicity, Consistency, Isolation, Durability) properties are used to describe such behaviour in databases. Atomicity is only important if we're dealing with concurrent modifications. To ensure Consistency, a certain level of Isolation must be reached. The more isolated multiple transactions run, however, the less performance the DBMS usually has. So there is the so called "isolation level", which states what errors can possibly occur in a DBMS and which cannot.

现在，MySQL在INNODB数据库中实现了所有隔离级别，您可以为每个事务选择:

Now, MySQL implements all isolation levels in INNODB databases, and you can choose for each transaction: https://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

MyIsam数据库不支持事务，但是单个操作应该自动运行. (来源: https://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html ).但是请注意，这不能保证在一个操作中读取和写入之间的数据不会更改-DBMS术语中的原子性仅表示该操作已完全完成或已完全跳过.它不保证隔离，一致性或持久性.

MyIsam databases don't support transactions, single operations should however run atomically. (Source: https://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html). Note however, that this does NOT guarantee data isn't changed between the reads and writes in one operation - atomicity in DBMS terms only means that the operation is either completely done or completely skipped. It does NOT guarantee isolation, consistency or durability.

这篇关于mysql是否在单个查询原子内多次插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！