Shulou(Shulou.com)06/01 Report--

There are two main ways to import data in MySQL: LOAD and SOURCE. Let's take a look at the characteristics of both.

The binary log format and the table structure used during the test are as follows:

(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)

Import data using 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

Analyzing the binary log generated by it, we can find that LOAD regards this process as a thing.

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

# @ 1room1

# @ 2roomaa'

# @ 3room2

# INSERT INTO `stage`.`st1`

# SET

# @ 1room2

# @ 2roombb'

# @ 334

# INSERT INTO `stage`.`st1`

# SET

# @ 1y3

# @ 2thanks cc'

# @ 3x6

# INSERT INTO `stage`.`st1`

# SET

# @ 1mm 4

# @ 2roomdd'

# @ 3x8

# at 397

# 170904 15:36:07 server id 1683316 end_log_pos 428 CRC32 0x67fed44c Xid = 29

Com _ MIT _ blank /

The above process is actually equivalent to the following statement.

START TRANSACTION

INSERT INTO st1 VALUES (…)

INSERT INTO st1 VALUES (…)

...

COMMIT

If LOAD encounters an error, such as data type mismatch or data column mismatch, the whole process will be rolled back. Here is an error encountered in the actual data import:

(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

If you report an error, when you import the 499999 record, you encounter an error and the whole thing is rolled back. such a big thing is very inefficient, and even if the final submission is successful, it is very likely to cause delay in the master-slave replication environment.

It is suggested that when LOAD imports data, the data file can be divided into several small files with the command split, and then imported many times, or the file can be divided with the help of PT tool pt-fifo-split, and its specific use can be found in the instructions document.

In fact, the above split import method is exactly the idea of the command SOURCE, which generally imports INSERT statements in a format such as INSERT INTO st1 VALUES (…) , () , () ...

The following is the log extracted from the actual data import:

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

It can be seen that SOURCE commits every 8000 rows or so, and it can also be verified in the binary log. If a record goes wrong, the group it belongs to will be rolled back. As for why it is 8000, it is understood as the internal mechanism for the moment.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.