PHP-MySQL INSERT-找到一个错误后，它会取消其余的查询吗? [英] PHP - MySQL INSERT - Will it cancel the rest of the query after finding one error?

问题描述

我已经制定了一种算法，该算法可以创建具有数千个插入内容的MySQL INSERT QUERY:

I've made an algorithm that creates a MySQL INSERT QUERY with thousands of INSERTS:

它看起来像这样:

-- (id, title, cost, price)
INSERT INTO products VALUES
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
-- ... AND THOUSANDS MORE

如果其中一个插入出现错误会怎样?像:

What happens if one of the inserts has an error; like:

-- (id, title, cost, price)
INSERT INTO products VALUES
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
(null, "product title", INVALID_VALUE_THAT_CANT_BE_INSERTED, 6000),
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
(null, "product title", 3000, 6000),
-- ... AND THOUSANDS MORE

它会在那里停止查询并让其他插入物在空间中漂浮吗?我将不得不截断表并重新开始，而不知道是谁引起了问题:/

Will it stop the Query there and leave the other inserts floating in space? I would have to TRUNCATE the table and start again without knowing who's causing the problem :/

推荐答案

如果其中一个插入出现错误会怎样?像:*

该语句停止执行，正在进行的任何更改都将被撤消. (但是，失败的插入可能会有一些副作用……例如，表上的AUTO_INCREMENT(如果有的话)可能比INSERT语句启动时的情况要高.而且，对于每个触发器都会触发BEFORE INSERT触发器行可能对MyISAM表进行了DML更改，这些更改将不会撤消".

The statement stops executing, and any changes that were underway are undone. (However, there may be some side effects from the failed insert... for example, the AUTO_INCREMENT on the table (if there is one) may be higher than it was when the INSERT statement started. Also, BEFORE INSERT triggers fired for each row may have made DML changes to MyISAM tables, those changes won't be "backed out".

但是就插入到目标表中的行而言，INSERT中失败的所有行都不会在那里.

But as far as rows inserted into the target table, none of the rows from the INSERT that failed will be there.

它将在此处停止查询

是的，INSERT将引发错误，并且该语句所做的任何更改都将被还原.

Yes, the INSERT will raise an error, and any changes made by that statement be reverted.

并使其他插入物在空间中漂浮?

在太空中不会漂浮任何东西.整个语句要么成功，要么失败.将插入所有行，或者不插入任何行.

There won't be anything left floating in space. The entire statement will either succeed, or it will fail. Either all the rows will be inserted, or none of the rows will be inserted.

由您决定处理过程如何进行，是否继续执行下一条INSERT语句或是否停止处理.

It's up to you how the process proceeds, whether you continue with the next INSERT statement, or whether you stop processing.

((我在这里是在插入多个行的单个INSERT语句的上下文中谈论的.不是关于其他语句会发生什么.以前成功的语句仍然是成功的.(在TRANSACTION的上下文中，使用InnoDB(或其他支持事务的存储引擎)，则有可能发出ROLLBACK并撤消"更改.如果您使用的是MyISAM存储引擎，则ROLLBACK不会有任何效果，因为已经提交了成功的语句.

(I'm talking here in the context of a single INSERT statement that's inserting multiple rows. Not about what happens with other statements. Statements that previously succeeded remain successful. (In the context of a TRANSACTION, using InnoDB (or some other storage engine that supports transactions) it would be possible to issue a ROLLBACK and "undo" the changes. If you're using the MyISAM storage engine, a ROLLBACK won't have any effect, a successful statement is already committed.

我将不得不对表进行截断并重新开始，而不知道是谁引起了问题:/

您不一定需要截断表.您可能要使用这种方法来重新启动加载过程，这取决于您.就MySQL而言，将存储所有成功完成的INSERT语句.您只需从失败的INSERT语句重新启动.

You wouldn't necessarily need to truncate the table. That may be an approach you want to use to restart the load process, that's up to you. As far as MySQL is concerned, all the INSERT statements that completed successfully are stored. You'd only need to restart from the INSERT statement that failed.

要考虑的一些事情:

如果您不需要插入无效"行，但是需要插入其他行，则可以使用 INSERT IGNORE 语句.

If you don't need to insert that "invalid" row, but you need the other ones to be inserted, you could use an INSERT IGNORE statement.

IGNORE告诉MySQL在特定行的插入失败时不要引发错误. MySQL继续运行该语句并允许它成功完成，但带有警告而不是错误.

The IGNORE tells MySQL not to raise an error when an insert of a particular row fails. MySQL continues to run the statement and allow it complete successfully, with warnings, rather than errors.

如果您插入一千行，并且其中一行出现错误，则会发出警告，而其他999行将被插入.

If you inserted a thousand rows, and one row had an error, you'd have a warning, and the other 999 rows would be inserted.

对于批量加载巴西人的行，没有什么比LOAD DATA语句更好.

For bulk loading a brazilians of rows, nothing beats the LOAD DATA statement.

如果您走巴西个人插入语句的路线，那将在MySQL服务器上做大量工作，解析语句，生成执行计划，获取锁，返回状态... INSERT相当高效的过程，但是处理每个单独的语句的所有开销加起来了.因此，这不仅会令人痛苦地变慢，而且还将通过无休止且令人费解的逐行处理在MySQL服务器上进行测试.

If you go the route of a brazilian individual inserts statements, that's gonna be a whole lot more work on the MySQL server, parsing statements, generating an execution plan, obtaining locks, returning a status... an INSERT is a fairly efficient process, but all of that overhead for processing each individual statement adds up. So, not only is that going be agonizingly slower, it's also going to be pounding on the MySQL server with the relentless and excruciating row-by-row processing.

这篇关于PHP-MySQL INSERT-找到一个错误后，它会取消其余的查询吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！