MySQL[批量]插入数据

=Start=

搜索关键字：

mysql insert many data
mysql 批量插入数据
mysql insert data speed
mysql insert ignore

参考解答：

先说解决方案：

INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

INSERT IGNORE INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

load data infile '/path/to/file.csv'
    into table tbl_name
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (field1, field2, field3,..., fieldN);

load data local infile '/path/to/file.csv'
    into table tbl_name
    fields terminated by ',' optionally enclosed by '"'
    lines terminated by '\n'
    ignore 1 lines
    (field1, field2, field3,..., fieldN);

再说在实际过程中碰到的问题：

0.影响MySQL插入语句的速度的因素「Speed of INSERT Statements」

为了优化插入速度，可以将多个小的插入操作合并成一个大的插入操作来达到目的。理想情况是：在一个连接建立后，一次性发送所有的数据、延迟所有的索引更新操作直到批量插入完成。

下面是MySQL中插入一行数据的各阶段操作的所需时间占比情况：

Connecting——建立连接: (3)
Sending query to server——向服务器发送请求: (2)
Parsing query——服务器解析请求: (2)
Inserting row——插入数据: (1 × size of row)
Inserting indexes——插入索引: (1 × number of indexes)
Closing——关闭连接: (1)

说明：以上内容并没有把打开表操作的时间算进去，因为那在每个并发执行的查询操作中只会发生一次。当使用B-tree结构作索引时，插入索引操作的速度与表的大小成反比（比值约为log N）。

1.insert vs. bulk insert

常规的每个insert语句插入一条记录效率太低，若将多个记录放在一个insert语句中进行插入，效率会有较大提高（参考上面的说明）；当记录可能存在重复时使用ignore关键字进行忽略（是否使用需要视具体情况而定）；

2.bulk_insert_buffer_size

在一个insert语句中进行大量记录的插入时可能需要关注一下「bulk_insert_buffer_size」变量的大小；

3.load data

个人感觉用「load data」的方式速度是最快的，但是坑也比较多（除非用于加载的文件内容格式全都准确无误，一般情况下这个比较难以保证，比如文件中多了个空行什么的）；

4.用「load data」的方式时最常碰到的权限问题

ERROR 1045 (28000): Access denied for user ‘xxx’@’xxx’ (using password: YES)

原因：这个一般是因为非root用户没有FILE Privilege，可以通过show grants查看当前登陆用户的权限，也可以通过select mysql.user查看某用户的权限，一般情况下，normal user是无FILE权限的。

三种解决办法：

1）给「load data」命令加local参数，用「load data local infile ‘filename’ into table xxx」来导数据（推荐）

2）给normal user开通FILE Privilege，注意：FILE权限与SELECE/DELETE/UPDATE等不同，后者是可以具体指定到某个db的某个表的，而FILE则是全局的，即只能通过：

GRANT FILE ON *.* TO user@xxx;
/* 或 */
UPDATE mysql.user SET File_priv = 'Y' WHERE Host = 'xxx' AND User = 'user';

FLUSH PRIVILEGES;

才能使FILE权限对所有db的所有tables生效。通过：

GRANT ALL ON dbName.* TO 'someuser'@'somehost';

不能使指定的user在指定的db上具有FILE权限。根据最小权限的原则，这个方法并不安全，故不推荐使用。

3）修改 ~/.my.cnf 中的配置，如下：

[mysql]
local-infile

参考链接：

5.「load data」的语法

将指定column字段的部分放在最后，参考：

=EOF=

20 12 月, 2015

admin

Database, KnowledgeBase, Programing

insert, load data, mysql