mysql是否在Single查询中多次插入原子？

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)....

现在假设在查询中指定了超过数万个VALUES（因此省略号）....

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?

有没有一个案例，其中VALUES的某些部分设法在数据库中插入/更新，但其余部分可能由于某种db错误/故障/内存耗尽等而未插入/更新？

Are mysql queries ALL or Nothing?

mysql查询ALL还是Nothing？

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?

对于每个执行的mysql查询，是否可以顺利插入/更新查询中指定的所有值，或者不会插入/更新任何值？

4 个解决方案

#1

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.

ACID（原子性，一致性，隔离性，持久性）属性用于描述数据库中的此类行为。只有当我们处理并发修改时，原子性才是重要的。为确保一致性，必须达到一定程度的隔离。但是，运行的隔离多个事务越多，DBMS通常具有的性能就越低。因此，存在所谓的“隔离级别”，其表明在DBMS中可能发生的错误，哪些不可能发生。

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

现在，MySQL实现INNODB数据库中的所有隔离级别，您可以选择每个事务：https：//dev.mysql.com/doc/refman/5.1/en/set-transaction.html

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.

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

#2

"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?"

“有没有一个案例，其中VALUES的某些部分设法在数据库中插入/更新，但其余部分可能由于某种db错误/故障/内存耗尽等而未插入/更新？ “

Late answer, but perhaps interesting: [ON DUPLICATE KEY] UPDATE is not strictly atomic for single rows (neither for MyISAM, nor for InnoDB), but it will be atomic in regards to errors.

迟到的回答，但也许很有意思：[ON DUPLICATE KEY] UPDATE对于单行来说不是严格原子的（既不是MyISAM也不是InnoDB），但它在错误方面是原子的。

What's the difference? Well, this illustrates the potential problem in assuming strict atomicity:

有什么不同？那么，这说明了假定严格原子性的潜在问题：

CREATE TABLE `updateTest` (
  `bar` INT(11) NOT NULL,
  `foo` INT(11) NOT NULL,
  `baz` INT(11) NOT NULL,
  `boom` INT(11) NOT NULL,
  PRIMARY KEY (`bar`)
)
COMMENT='Testing'
ENGINE=MyISAM;

INSERT INTO `updateTest` (`bar`, `foo`, `baz`, `boom`) VALUES (47, 1, 450, 2);

INSERT
    `updateTest`
    (`bar`, `foo`, `baz`, `boom`)
VALUES
    (47, 0, 400, 5)
ON DUPLICATE KEY UPDATE
    `foo` = IF(`foo` = 1, VALUES(`foo`), `foo`),
    `baz` = IF(`foo` = 1, VALUES(`baz`), `baz`),
    `boom` = IF(`foo` = 1, VALUES(`boom`), `boom`);

(47, 1, 450, 2) will have turned into (47, 0, 450, 2), and not into (47, 0, 400, 5). If you assume strict atomicity (which is not to say you should; you might prefer this behaviour), that shouldn't happen - foo should certainly not change before the other columns' values are even evaluated. foo should change together with the other columns - all or nothing.

（47,1,450,2）将变为（47,0,450,2），而不是（47,0,400,5）。如果你假设严格的原子性（这不是你应该;你可能更喜欢这种行为），那不应该发生 - 在其他列的值被评估之前，foo肯定不会改变。 foo应该与其他列一起更改 - 全部或全部。

If I say atomic in regards to errors, I mean that if you remove the IF() condition in the above example that's highlighting the stricter situation, like this...

如果我说关于错误的原子，我的意思是如果你删除上面例子中的IF（）条件突出更严格的情况，像这样...

INSERT INTO `updateTest` (`bar`, `foo`, `baz`, `boom`) VALUES (48, 1, 450, 2);

INSERT
    `updateTest`
    (`bar`, `foo`, `baz`, `boom`)
VALUES
    (48, 0, 400, 5)
ON DUPLICATE KEY UPDATE
    `foo` = VALUES(`foo`),
    `baz` = VALUES(`baz`),
    `boom` = VALUES(`boom`);

...you will always either end up with (48, 1, 450, 2) or (48, 0, 400, 5) after your statement has finished/crashed, and not some in-between state like (48, 0, 450, 2).

......在你的陈述完成/崩溃之后，你将总是以（48,1,450,2）或（48,0,400,5）结束，而不是像（48,0， 450,2）。

The same is true for the behaviour of UPDATE, but there's even less of a reason to juggle IF() statements there, since you can just put your conditionals into your WHERE clause there.

对于UPDATE的行为也是如此，但是在那里处理IF（）语句的理由更少，因为您可以将条件放入WHERE子句中。

In conclusion: Outside of edge-cases, you do have atomicity for single-row statements, even using MyISAM. See Johannes H.'s answer for further information.

总结：在边缘情况之外，即使使用MyISAM，您也确实具有单行语句的原子性。有关详细信息，请参阅Johannes H.的答案。

#3

It the table storage engine is InnoDB, yes, the operation is definitely atomic and a partial insert is not possible. I believe this is not true with MyISAM, the default engine, since it is not ACID-compliant and doesn't support transactions.

表存储引擎是InnoDB，是的，操作肯定是原子的，不可能部分插入。我认为默认引擎MyISAM不是这样，因为它不符合ACID并且不支持事务。

#4

Single statements such as this are. If you need multiple calls to act in an atomic manner you may use transactions in most relational databases.

像这样的单个陈述是。如果您需要多次调用以原子方式执行操作，则可以在大多数关系数据库中使用事务。

#1