如何在不调用mysqldump实用程序的情况下使用SQL语法从表创建大容量插入语句?
请记住,SELECT ... INTO OUTFILE 'file_name'
对我不起作用,因为它生成的是CSV文件,而不是下面中BULK INSERT SQL格式的输出
INSERT INTO mytable VALUES (1,2), (5,5), ...;
运行
SELECT * FROM mytable INTO OUTFILE 'dump.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n';
然后用sed/perl/python/shell/whateverelse对输出的CSV进行后处理,得到SQL脚本。
例如在python中:
import csv
with open('dump.csv',"rU") as inf:
reader = csv.reader(inf)
with open("dump.sql","wb") as outf:
for row in reader:
outf.write('insert into mytable values ("'+ '","'.join(row) +'");n')
该脚本不适用于Null
和列值中的引号字符。