mysql 在单个查询中的多个插入是原子的吗?
我在一个单个查询中进行多次插入:
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)
现在假设查询中指定了数万个 VALUES(因此是省略号)......
Now suppose that there's over tens of thousands of VALUES specified in the query (hence the ellipsis)....
是否存在这样一种情况，即 VALUES 的某些部分设法在数据库中插入/更新，但其余部分可能由于某种 db 错误/故障/内存耗尽而没有插入/更新等等?
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 查询是 ALL 还是 Nothing?
对于每一个执行的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(原子性，Consistency, Isolation, Durability) 属性用于描述数据库中的此类行为.只有在处理并发修改时，原子性才重要.为了确保一致性，必须达到一定程度的隔离.然而，运行的多个事务越隔离，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 数据库中实现了所有隔离级别，您可以为每个事务选择:https://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
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.
相关文章