Python+MySQL - 批量插入

Question

我正在用Python的MySQLdb模块和数据库打交道。我遇到一个问题，就是我有一个非常大的列表（有几万个元素），需要把这些元素作为行插入到一个表里。

我现在的解决办法是生成一个很大的INSERT语句，把它当字符串执行。

有没有更聪明的方法呢？

Answer 1

只要你是一次性插入数据，而不是分开插入成千上万个，那这样做确实是最好的方法。不过要注意不要超过MySQL的最大数据包大小，如果需要的话要进行调整。比如，这段代码是把服务器的数据包最大值设置为32MB。你在客户端也需要做同样的设置。

mysqld --max_allowed_packet=32M

Answer 2

如果你需要插入大量数据，为什么要试图把所有数据都放在一个单独的insert语句里呢？这样会让你的内存负担加重，因为你要生成一个很大的insert字符串，而且在执行的时候也会很吃力。如果你要插入的数据量特别大，这种做法并不是一个好主意。

为什么不每次用一个insert命令插入一行数据呢？你可以用一个for...loop循环把所有行都插入，然后最后一次性提交所有的更改。

con = mysqldb.connect(
                        host="localhost",
                        user="user",
                        passwd="**",
                        db="db name"
                     )
cur = con.cursor()

for data in your_data_list:
    cur.execute("data you want to insert: %s" %data)

con.commit()
con.close()

相信我，这样做真的很快。不过如果你发现速度变慢，那可能是因为你的autocommit设置为True。按照msw的建议，把它设置为False。

Answer 3

其实有更聪明的方法。

批量插入数据的问题在于，默认情况下，自动提交是开启的，这就导致每次执行一个insert语句时，系统会先把这条数据保存到数据库里，然后才能进行下一条插入。

手册上提到：

默认情况下，MySQL是开启自动提交模式的。这意味着一旦你执行了一个更新（修改）表的语句，MySQL就会立即把这个更新保存到硬盘上，让它变得永久有效。如果你想关闭自动提交模式，可以使用以下语句：

SET autocommit=0; 

在把自动提交模式设置为零后，对事务安全的表（比如InnoDB、BDB或NDBCLUSTER）所做的更改不会立即变得永久。你必须使用COMMIT来把更改保存到硬盘上，或者使用ROLLBACK来忽略这些更改。

这是关系型数据库管理系统（RDBMS）中一个很常见的特性，因为它们认为数据库的完整性是最重要的。这也导致批量插入的时间变得比较长，每条插入大约需要1秒，而不是1毫秒。另一种方法是使用一个非常大的插入语句，试图一次性提交所有数据，但这样可能会让SQL解析器负担过重。