python - 什么时候引发MySQL主键错误？

Question

如果我有一个批处理插入语句，例如：

INSERT INTO TABLE VALUES (x,y,z),(x2,y2,z2),(x3,y3,z3);

并且 x2违反了主键，在处理 x3之前还是之后引发了错误？

具体来说，我在使用Python和PyMySQL的try-catch块中有一堆批处理插入，例如：

conn = myDB.cursor() 
try:
     conn.execute("INSERT INTO TABLE VALUES (x,y,z),(x2,y2,z2),(x3,y3,z3);")
except pymysql.Error as  msg:
     print("MYSQL ERROR!:{0}".format(msg)) #print error

我想确保，如果批处理插入中的一个元组失败，从而打印错误，那么同一批处理中的其余元组仍将被处理。

我的动机是我要在两台服务器之间传输很多数据。在服务器1中，数据存储在日志文件中，并将其插入服务器2上的MySQL中。某些数据已经在服务器2上的MySQL中，因此存在许多故障。但是，如果我不使用批处理插入，并且我对每（几百万个）记录都有单独的 INSERT INTO，则运行似乎会慢得多。因此，我无论哪种方式都遇到麻烦：使用批处理插入，重复失败会破坏整个语句，而如果没有批处理插入，则过程将花费更长的时间。

Answer 1

MySQL处理多个插入（或更新）语句的方式因表引擎和服务器SQL模式而异。

尽管只有表引擎对于您在此处要问到的关键约束确实很重要，但了解大局很重要，因此我将花时间添加一些额外的细节。如果您很着急，请随时阅读下面的第一部分和最后一部分。

表格引擎

对于像MyISAM这样的非事务处理表引擎，由于每次插入或更新都是按顺序执行的，并且在遇到不良行并中止该语句时无法回滚，因此您很容易最终执行部分更新。

但是，如果您使用InnoDB之类的事务表引擎，则在插入或更新语句期间发生的任何约束冲突都会触发回滚到该点为止所做的任何更改，除了中止该语句。

SQL模式

当您不违反键约束时，server SQL mode变得很重要，但是您要插入或更新的数据不适合要放入的列的定义。例如：


插入一行而不为每个NOT NULL列提供值
将'123'插入由数字类型（而不是123）定义的列中
更新CHAR(3)列以保存值'four'


在这些情况下，如果严格模式生效，MySQL将抛出错误。但是，如果严格模式无效，它通常会“修复”您的错误，这可能导致各种潜在的有害行为（仅举两个示例，请参见MySQL 'Truncated incorrect INTEGER value'和mysql string conversion return 0）。

危险，威尔·罗宾逊！

使用非事务表和严格模式存在一些潜在的“陷阱”。您尚未告诉我们您使用的是哪种表引擎，但是当前编写的this answer显然是在使用非事务表，因此了解如何影响结果很重要。

例如，考虑以下语句集：

SET sql_mode = '';  # This will make sure strict mode is not in effect

CREATE TABLE tbl (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  val INT
) ENGINE=MyISAM;  # A nontransactional table engine (this used to be the default)

INSERT INTO tbl (val) VALUES (1), ('two'), (3);

INSERT INTO tbl (val) VALUES ('four'), (5), (6);

INSERT INTO tbl (val) VALUES ('7'), (8), (9);

由于严格模式无效，因此将所有9个值都插入并且将无效字符串强制转换为整数也就不足为奇了。服务器足够聪明，可以将 '7'识别为数字，但不能识别 'two'或 'four'，因此它们会转换为 default value for numeric types in MySQL：

mysql> SELECT val FROM tbl;
+------+
| val  |
+------+
|    1 |
|    0 |
|    3 |
|    0 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

现在，尝试使用 sql_mode = 'STRICT_ALL_TABLES'再次执行此操作。长话短说，第一个 INSERT语句将导致部分插入，第二个将完全失败，第三个将默默地将 '7'强制为 7（如果您认为这不是很“严格”）问我，但这是 documented behavior，不是那么不合理）。

但是，等等，还有更多！用 sql_mode = 'STRICT_TRANS_TABLES'尝试一下。现在，您会发现第一条语句引发警告而不是错误-但是第二条语句仍然失败！如果您对一堆文件使用 LOAD DATA，而有些文件却失败了而另一些文件却没有失败（请参阅 this closed bug report），则这尤其令人沮丧。

该怎么办

特别是在键冲突的情况下，重要的只是表引擎是否为事务性的（例如：InnoDB）或事务性的（例如：MyISAM）。如果您正在处理事务表，则问题中的Python代码将导致MySQL服务器按以下顺序执行操作：


解析 INSERT语句并开始事务。*
插入第一个元组。
插入第二个元组（违反键约束）。
回滚事务。
将错误消息发送到 pymysql。


*在开始事务之前先对语句进行解析是很有意义的，但是我不知道确切的实现方式，因此我将它们汇总为一个步骤。

在这种情况下，当您的脚本收到来自服务器的错误消息并进入 except块时，错误元组之前的所有更改都将被撤消。

但是，如果您正在使用非事务处理表，则服务器将跳过步骤4（以及步骤1的相关部分），因为表引擎不支持 transaction statements。在这种情况下，当脚本进入 except块时，已插入第一个元组，第二个元组被炸毁，由于 the function that normally does that返回-您可能无法轻易确定成功插入了多少行-如果最后一个insert或update语句引发错误，则为1。

应严格避免部分更新；它们比仅仅确保您的语句完全成功或完全失败要难得多。在这种情况下， the documentation suggests：


为了避免[部分更新]，请使用单行语句，该语句可以在不更改表的情况下中止。


我认为，这正是您应该做的。用Python编写循环几乎很困难，只要您是 inserting values properly as parameters而不是对它们进行硬编码，就不必重复代码-您已经在这样做了，对吗？对？？？ > :(

替代方案

如果您有时希望违反约束并且想要在尝试插入的行已经存在时采取其他措施，那么您可能会对 `INSERT ... ON DUPLICATE KEY UPDATE'感兴趣。这使您可以在计算体操方面完成惊人的壮举，包括数数事物：

mysql> create table counting_is_fun (
    -> stuff int primary key,
    -> ct int unsigned not null default 1
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> insert into counting_is_fun (stuff)
    -> values (1), (2), (5), (3), (3)
    -> on duplicate key update count = count + 1;
Query OK, 6 rows affected (0.04 sec)
Records: 5  Duplicates: 1  Warnings: 0

mysql> select * from counting_is_fun;
+-------+-------+
| stuff | count |
+-------+-------+
|     1 |     1 |
|     2 |     1 |
|     3 |     2 |
|     5 |     1 |
+-------+-------+
4 rows in set (0.00 sec)

（注意：将您插入的元组的数量与查询所影响的“行数”以及之后表中的行数进行比较。这不算有趣吗？）

或者，如果您认为现在要插入的数据至少与表中当前的数据一样好，则可以查看 REPLACE INTO-但这是SQL标准的MySQL特定扩展，通常 it has its quirks，尤其是与外键引用关联的 AUTO_INCREMENT字段和 ON DELETE动作。

人们喜欢建议的另一种方法是 INSERT IGNORE。这将忽略该错误，并且只会继续滚动。太好了吧？谁仍然需要错误？我不喜欢将其作为解决方案的原因是：


INSERT IGNORE会导致语句期间发生的任何错误都将被忽略，而不仅仅是您认为自己不关心的任何错误。
文档指出， "Ignored errors may generate warnings instead, although duplicate-key errors do not."因此，您甚至不必知道使用该关键字时会出现哪些警告！
对我来说，使用 INSERT IGNORE表示：“我不知道如何以正确的方式进行操作，因此我只会以错误的方式进行操作。”


有时我确实使用 INSERT IGNORE，但是当文档整理告诉您做某事的“正确方法”时，请不要自欺欺人。首先尝试这种方式；如果您仍然有很好的理由以错误的方式进行操作，并冒着破坏数据完整性并永久毁坏一切的风险，至少您已经做出了明智的决定。