Notes for load data infile

Http://blog.chinaunix.net/u/29134/showart_1069838.html

My articles are generally easy to understand and won't be so in-depth that it is hard for everyone to understand. (In fact, I am not at the same level)

Load data infile has always been considered a powerful Mysql Data Import tool because it is very fast.
But pay attention to a few issues.
1. encoding.
2. Flexible import and export.

Here are two examples.
I. Encoding
Our example text file:
"I love you", "20", "common looks, often playing tricks! Haha"
"Li Kui", "21", "common looks, often playing tricks! Haha"
"Wang ermi", "20", "common looks, often playing hooligans! Haha"
"Laosan", "24", "strong"
"La 4", "34", "XXXXX"
"Old Five", "52", "***** % ** ￥ * ￥"
"Kitten", "45", "omitted in the middle... "
"Puppy", "12", "will be called"
"Xiaomei", "21", "PP is very"
"Little bad guy", "52", "Table different"
"Lord God", "96", "very handsome"
"Mm is coming", "10 ","... "
"Praise the party", "20", "Good socialism"
"Good People", "20", "indeed"
"Lao GAO", "10", "learning well"
"Oblique 3", "60", "oblique eyes"
"Son of China", "100", "Mighty"
"Rice", "63", "I love"
"Apple", "15", "delicious"
Our example table structure:

+ ------- + Begin certificate --------- +
| Table | create table |
+ ------- + Begin certificate --------- +
| T0 | create table 't0 '(
'Id' bigint (20) unsigned not null auto_increment,
'Name' char (20) not null,
'Age' tinyint (3) unsigned not null,
'Description' text not null,
Primary Key ('id '),
Unique key 'idx _ name' ('name ')
) Engine = MyISAM default charset = utf8 |
+ ------- + Begin certificate --------- +
1 row in SET (0.00 Sec)

Copy the text file from windows to Linux.

Mysql> load data infile '/tmp/t0.txt' ignore into Table t0 Character Set GBK fields terminated ', 'enabledby' "'Lines terminated by '/N' ('name', 'age', 'description ');
Query OK, 19 rows affected (0.01 Sec)
Records: 19 deleted: 0 skipped: 0 Warnings: 0

Mysql> select * from t0;
+ ---- + ---------- + ----- + ---------------------------- +
| ID | Name | age | description |
+ ---- + ---------- + ----- + ---------------------------- +
| 1 | I love you | 20 | common looks, often playing tricks! Haha |
| 2 | Li Kui | 21 | regular appearance, often playing hooligans! Haha |
| 3 | Wang ermi | 20 | common looks, often playing hooligans! Haha |
| 4 | laosan | 24 | strong |
| 5 | last4 | 34 | XXXXX |
| 6 | old five | 52 | ***** % ** ￥ * ￥ |
| 7 | kitten | 45 | omitted in the middle... |
| 8 | puppy | 12 | it will be called |
| 9 | Xiaomei | 21 | PP is very good |
| 10 | bad guy | 52 | different tables |
| 11 | Lord God | 96 | very handsome |
| 12 | mm is coming | 10 |... |
| 13 | praise the party | 20 | socialism is good |
| 14 | good people | 20 | indeed, good people |
| 15 | old master | 10 | excellent learning |
| 16 | oblique 3 | 60 | oblique eyes |
| 17 | son of China | 100 | wei wu's Failure |
| 18 | rice | 63 | I love it. |
| 19 | Apple | 15 | delicious |
+ ---- + ---------- + ----- + ---------------------------- +
19 rows in SET (0.00 Sec)

Let me describe the relevant parameters.
For more information about my import statements, see other reference manuals.
Character Set GBK;

This character set must be written. Otherwise, it may be garbled or only some data is imported.

Ignore into table

Because the name column adds a unique index, this is used to avoid duplicate data insertion errors.

When we run this import statement again, we will find that
Query OK, 0 rows affected (0.00 Sec)
Records: 19 deleted: 0 skipped: 19 Warnings: 0
No value is imported because the same value already exists.

Replace into table can also be used here
MySQL will first kill the same and then insert a new value.
Mysql> load data infile '/tmp/t0.txt' replace into Table t0 Character Set GBK fields terminated ', 'enabledby' "'Lines terminated by '/N' ('name', 'age', 'description ');
Query OK, 38 rows affected (0.00 Sec)
Records: 19 deleted: 19 skipped: 0 Warnings: 0

Mysql> select * from t0;
+ ---- + ---------- + ----- + ---------------------------- +
| ID | Name | age | description |
+ ---- + ---------- + ----- + ---------------------------- +
| 20 | I love you | 20 | common looks, often playing tricks! Haha |
| 21 | Li Kui | 21 | regular appearance, often playing hooligans! Haha |
| 22 | Wang ermi | 20 | common looks, often playing hooligans! Haha |
| 23 | laosan | 24 | strong |
| 24 | last4 | 34 | XXXXX |
| 25 | old five | 52 | ***** % ** ￥ * ￥ |
| 26 | kitten | 45 | omitted in the middle... |
| 27 | puppy | 12 | it will be called |
| 28 | Xiaomei | 21 | PP is very good |
| 29 | bad guy | 52 | different tables |
| 30 | Lord God | 96 | very handsome |
| 31 | mm is coming | 10 |... |
| 32 | praise the party | 20 | socialism is good |
| 33 | good people | 20 | indeed, good people |
| 34 | old master | 10 | excellent learning |
| 35 | oblique 3 | 60 | oblique eyes |
| 36 | son of China | 100 | wei wu's Failure |
| 37 | rice | 63 | I love it. |
| 38 | Apple | 15 | delicious |
+ ---- + ---------- + ----- + ---------------------------- +
19 rows in SET (0.00 Sec)

('Name', 'age', 'description ');

These are the specific table attributes. You can specify this to import the desired data.
2. Flexibility is actually a record function.
What if I want to record the import time during import?
Let's take a look.
Add a time attribute to record the import time.
Mysql> alter table t0 add update_time timestamp not null;
Query OK, 19 rows affected (0.00 Sec)
Records: 19 duplicates: 0 Warnings: 0
Unique index Removal
Mysql> alter table t0 drop index idx_name;
Query OK, 19 rows affected (0.00 Sec)
Records: 19 duplicates: 0 Warnings: 0

Mysql> load data infile '/tmp/t0.txt' into Table t0 Character Set GBK fields terminated by ', 'enabledby' "'Lines terminated by '/N' ('name ', 'age', 'description') set update_time = current_timestamp;
Query OK, 19 rows affected (0.00 Sec)
Records: 19 deleted: 0 skipped: 0 Warnings: 0

Mysql> select * from t0;
+ ---- + ---------- + ----- + ---------------------------- + --------------------- +
| ID | Name | age | description | update_time |
+ ---- + ---------- + ----- + ---------------------------- + --------------------- +
| 20 | I love you | 20 | common looks, often playing tricks! Haha | 0000-00-00 00:00:00 |
............
| 24 | la 4 | 34 | XXXXX | 0000-00-00 00:00:00 |
| 25 | old five | 52 | ***** % ** ￥ * ￥ | 0000-00-00 00:00:00 |
............
| 35 | oblique 3 | 60 | oblique eyes | 0000-00-00 00:00:00 |
| 36 | son of China | 100 | wei wu's Failure | 0000-00-00 00:00:00 |
............
| 60 | Wang ermi | 20 | common looks, often playing hooligans! Haha | 14:58:37 |
............
| 68 | lord of God | 96 | very handsome | 14:58:37 |
| 69 | mm is coming | 10 |... | 14:58:37 |
............
| 75 | rice | 63 | I love it | 14:58:37 |
| 76 | Apple | 15 | delicious | 14:58:37 |
+ ---- + ---------- + ----- + ---------------------------- + --------------------- +
38 rows in SET (0.00 Sec)

The 19 new records have been recorded.
Only the previous database does not have records, but the duplicate data is not required now.

Just take him out.

Mysql> alter table t0 order by id desc;
Query OK, 38 rows affected (0.01 Sec)
Records: 38 duplicates: 0 Warnings: 0

Mysql> alter ignore table t0 add unique index idx_name ('name ');
Query OK, 38 rows affected (0.00 Sec)
Records: 38 duplicates: 19 Warnings: 0

Mysql> alter table t0 order by id asc;
Query OK, 19 rows affected (0.01 Sec)
Records: 19 duplicates: 0 Warnings: 0

Mysql> select * from t0;
+ ---- + ---------- + ----- + ---------------------------- + --------------------- +
| ID | Name | age | description | update_time |
+ ---- + ---------- + ----- + ---------------------------- + --------------------- +
| 58 | I love you | 20 | common looks, often playing tricks! Haha | 14:58:37 |
| 59 | Li Kui | 21 | regular appearance, often playing hooligans! Haha | 14:58:37 |
| 60 | Wang ermi | 20 | common looks, often playing hooligans! Haha | 14:58:37 |
| 61 | laosan | 24 | strong | 14:58:37 |
| 62 | laosan | 34 | XXXXX | 14:58:37 |
| 63 | old five | 52 | ***** % ** ￥ * ￥ | 14:58:37 |
| 64 | kitten | 45 | omitted in the middle... | 14:58:37 |
| 65 | puppy | 12 | it will be called | 14:58:37 |
| 66 | Xiaomei | 21 | pp very much | 14:58:37 |
| 67 | bad guy | 52 | different tables | 14:58:37 |
| 68 | lord of God | 96 | very handsome | 14:58:37 |
| 69 | mm is coming | 10 |... | 14:58:37 |
| 70 | praise the party | 20 | socialism is good | 14:58:37 |
| 71 | good people | 20 | Yes | 14:58:37 |
| 72 | old master | 10 | excellent learning | 14:58:37 |
| 73 | oblique 3 | 60 | oblique eyes | 14:58:37 |
| 74 | son of China | 100 | wei wu's Failure | 14:58:37 |
| 75 | rice | 63 | I love it | 14:58:37 |
| 76 | Apple | 15 | delicious | 14:58:37 |
+ ---- + ---------- + ----- + ---------------------------- + --------------------- +
19 rows in SET (0.00 Sec)

Now we have achieved our goal. Why should we get rid of the unique index in the middle? Because the set syntax will be ignored when there is ignore.