再说说LOAD和SOURCE
MySQL中导入数据的方法主要有两种: LOAD和SOURCE, 下面看看两者的特点.
测试过程中二进制日志格式, 和用到的表结构如下:
(root@localhost) [(none)]> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
(root@localhost) [stage]> SHOW CREATE TABLE st1\G
*************************** 1. row***************************
Table: st1
Create Table: CREATE TABLE `st1` (
`a`int(10) unsigned NOT NULL DEFAULT '0',
`b`varchar(4) NOT NULL DEFAULT '',
`c`int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
使用LOAD导入数据.
(root@localhost) [stage]> LOAD DATA INFILE '/tmp/st1.txt' INTO TABLE st1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
分析其产生的二进制日志, 可以发现LOAD将该过程作为一个事物了.
BEGIN
/*!*/;
# at 193
# at 263
#170904 15:36:07 server id 1683316 end_log_pos 314 CRC32 0xffbd6789 Table_map: `stage`.`st1` mapped to number76
# at 314
#170904 15:36:07 server id 1683316 end_log_pos 397 CRC32 0xb3c288aa Write_rows: table id 76 flags: STMT_END_F
### INSERT INTO `stage`.`st1`
### SET
### @1=1
### @2='aa'
### @3=2
### INSERT INTO `stage`.`st1`
### SET
### @1=2
### @2='bb'
### @3=4
### INSERT INTO `stage`.`st1`
### SET
### @1=3
### @2='cc'
### @3=6
### INSERT INTO `stage`.`st1`
### SET
### @1=4
### @2='dd'
### @3=8
# at 397
#170904 15:36:07 server id 1683316 end_log_pos 428 CRC32 0x67fed44c Xid = 29
COMMIT/*!*/;
上面的过程, 其实和下面的语句是等价的.
START TRANSACTION;
INSERT INTO st1 VALUES(…);
INSERT INTO st1 VALUES(…);
…
COMMIT;
若LOAD遇到错误, 如数据类型不对, 或数据列不匹配等, 整个过程就会回滚. 下面是实际数据导入中遇到的一个报错:
(root@localhost) [product]> LOAD DATA INFILE '/tmp/pro1.txt' INTO TABLE pro1;
ERROR 1261 (01000): Row 4999999 doesn'tcontain data for all columns
(root@localhost) [product]>system perror 1261;
MySQL error code 1261(ER_WARN_TOO_FEW_RECORDS): Row %ld doesn't contain data for all columns
如上报错, 在导入第499999条记录时, 遇到错误, 整个事物进行了回滚, 这样的大事物运行效率很低, 即使最后提交成功, 在主从复制环境下, 也极有可能造成延时.
建议LOAD导入数据时, 可先用命令split将数据文件分成若干小文件, 然后多次导入; 也可借助PT工具pt-fifo-split分割文件, 其具体使用见说明文档.
其实上面拆分导入的方式, 正是命令SOURCE的思路, 其一般导入INSERT语句, 格式如INSERT INTO st1 VALUES(…), (…), (…) …
下面是实际数据导入中摘出来的日志:
Query OK, 8690 rows affected (0.19 sec)
Records: 8690 Duplicates: 0 Warnings: 0
Query OK, 8800 rows affected (0.24 sec)
Records: 8800 Duplicates: 0 Warnings: 0
可见SOURCE每8000行记录左右提交一次, 也可在二进制日志中得到印证. 若某条记录出错, 其所在分组会进行回滚. 至于为什么是8000, 暂且理解为内部机制了.