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

I don't know if you have any understanding of the differences and usage of the two native data import methods similar to MySQL. Today, I'm here to talk about it briefly. If you are interested, let's take a look at the body. I believe you will gain something after reading the differences and usage of the two native data import methods of MySQL.

There are two native data import methods in MySQL, load and source. First look at the process and characteristics of the two ways.

For demonstration convenience, use the test table tb1, which is structured as follows:

Mysql > SHOW CREATE TABLE tb1\ G

* 1. Row**

Table:tb1

Create Table: CREATE TABLE `tb1` (

`id`bigint (20) unsigned NOT NULL AUTO_INCREMENT

`username`varchar (20) NOT NULL DEFAULT''

`age`tinyint (3) unsigned NOT NULL DEFAULT'0'

PRIMARY KEY (`id`)

UNIQUE KEY`uniq _ username` (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

There are several test data in the table:

Mysql > SELECT * FROM tb1

+-- +

| | id | username | age | |

+-- +

| | 1 | aa | 22 | |

| | 2 | bb | 20 | |

| | 3 | cc | 24 | |

| | 4 | dd | 20 | |

+-- +

4 rows in set (0.00 sec)

Export the data in the tb1 table to a file tb1.csv in CSV format:

Mysql > SELECT * FROM tb1 INTO OUTFILE "/ tmp/tb1.csv" FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "' LINES TERMINATED BY'\ n'

Query OK, 4 rows affected (0.00 sec)

The content of tb1.csv is:

1, "aa", 22

2, "bb", 20

3, "cc", 24

4, "dd", 20

After TRUNCATE the tb1 table, load imports:

Mysql > TRUNCATE TABLE tb1

Query OK, 0 rows affected (0.02 sec)

Mysql-- login-path=mytest test-- execute= "LOADDATA INFILE'/ tmp/tb1.csv' INTO TABLE tb1 FIELDS TERMINATED BY', 'OPTIONALLYENCLOSED BY'\" 'LINES TERMINATED BY'\ n' "

To test the error, modify the tb1.csv file as follows (lines 1 and 4):

9, "ff", 22

2, "bb", 20

3, "cc", 24

14, "gg", 25

When an error occurs in the load import, the import process is terminated, indicating the location and reason of the error, but this location line does not directly correspond to the original file (should be at line 2):

Mysql-- login-path=mytest test-- execute= "LOADDATA INFILE'/ tmp/tb1.csv' INTO TABLE tb1 FIELDS TERMINATED BY', 'OPTIONALLYENCLOSED BY'\" 'LINES TERMINATED BY'\ n' "

ERROR 1062 (23000) at line 1: Duplicate entry '2'for key' PRIMARY'

Look at the data in the tb1 table and there is no change:

Mysql > SELECT * FROM tb1

+-- +

| | id | username | age | |

+-- +

| | 1 | aa | 22 | |

| | 2 | bb | 20 | |

| | 3 | cc | 24 | |

| | 4 | dd | 20 | |

+-- +

4 rows in set (0.00 sec)

It can be seen here that when load imports data, it will terminate immediately when it encounters an error, indicating the location and cause of the error, and the data before the error will not be imported.

How to control the import speed, there is no way; another point, load import data, to specify self-increasing primary key value, which in the data table already has data, may encounter trouble.

Next, take a look at the performance of source, dump the data in the tb1 table into a SQL file tb1.sql (here you only need the INSERT statement):

Mysqldump-login-path=mytest-add-drop-database--skip-add-drop-table-add-drop-trigger-complete-insert--default-character-set=utf8mb4-events-skip-extended-insert-force--hex-blob-no-create-db-no-create-info-quick result-file=tb1.sql--routines single-transaction single-transaction triggers test tb1

The content of tb1.sql is:

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES (2)

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES (4)

After TRUNCATE the tb1 table, source imports:

Mysql > TRUNCATE TABLE tb1

Query OK, 0 rows affected (0.02 sec)

Mysql-login-path=mytest test-execute= "source/tmp/tb1.sql"

To test the error, modify the tb1.sql file as follows (lines 1 and 4):

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES (9)

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES (2)

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES (14)

When an error occurs in the source import, the import process is terminated, indicating the location and reason of the error:

Mysql-login-path=mytest test-execute= "source/tmp/tb1.sql"

ERROR 1062 (23000) at line 2 in file:'/tmp/tb1.sql': Duplicate entry'2' for key 'PRIMARY'

Looking at the data in the tb1 table, it is found that the data before the error was imported:

Mysql > SELECT * FROM tb1

+-- +

| | id | username | age | |

+-- +

| | 1 | aa | 22 | |

| | 2 | bb | 20 | |

| | 3 | cc | 24 | |

| | 4 | dd | 20 | |

| | 9 | ff | 22 | |

+-- +

5 rows in set (0.00 sec)

It can be seen here that when source imports data, it will terminate immediately when it encounters an error, indicating the location and cause of the error, and the data before the error will be imported.

Let's take a look at whether source solves the other two problems of load:

How to control the speed of data import, SELECT SLEEP (N) can be added to the SQL file to suspend the import, which can alleviate the delay.

There is also a problem of self-increasing primary keys. You can either remove the primary key field or set its value to NULL by processing the INSERT statement in the data file as follows:

INSERT INTO `tb1` (`username`, `age`) VALUES ('ff',22)

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES (NULL,'ff',22)

By comparison, using source gives you more control over the data import process (in addition, for redirecting imports using MySQL command-line tools, such as mysql < filename.sql, this is actually the same as source).

After selecting source, you will still encounter problems such as delay, if you want to further control the import process, you can only use the Bash script to add the detection logic, such as in the import of the next file, check first if there is a delay, then the sleep N in the script pauses the import, and if there is an error, you can notify it by email and define the way to handle it in various cases in the script. I will also sort out the best practices of Bash programming later. If you are interested, you can follow Subscription account's Database Best practices (DBBestPractice).

Write at the end, before testing load, using SELECT. INTO OUTFILE exports data to CSV format. It is very convenient to export a small amount of data in this way, but if the data contains Chinese, use Excel to open it. If you encounter garbled codes, you can specify the character set when you try to export:

SELECT * FROM tb1 INTO OUTFILE "/ tmp/tb1.csv" CHARACTER SET GBK FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'LINES TERMINATED BY'\ n'

After reading the difference and usage of the two native data import methods of MySQL, what do you think of this article? If you want to know more about it, you can continue to follow our industry information section.

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.