Load data infile "C:/users/administrator/desktop/1.txt"
into table to have an existing table name
Fields are separated by tabs by default
File
I love you. 20 looks normal
Li Kui 21 appearance 1 usual
Wang Yimi 210 appearance 3 usual
Old 324 is strong.
Old Four 34XXXXX
Commonly used as follows:
Load Data InFile ' c:/data.txt ' into Table ' tabletest ' Lines Terminated by ' \ r \ n ';
In this statement, the fields are separated by tabs by default, and each record is separated by a newline character, and the newline character is "\ r \ n" under Windows.
The contents of the C:/data.txt file are two lines:
1 A
1 A
There is a tab between "1" and "a"
This leads to the entry of two records.
Custom syntax
Load Data InFile ' c:/data.txt ' into Table ' tabletest ' fields Terminated by ', ' enclosed by ' "' escaped by '" ' Lines termin Ated by ' \ r \ n ';
Fields Terminated by ', ' enclosed by ' "' escaped by '" '
Indicates that each field is separated by commas and the contents are enclosed in double quotation marks
Lines Terminated by ' \ r \ n ';
Indicates that each piece of data is separated by a newline character.
Contrary to Load Data InFile,
Select * from ' tabletest ' to OutFile ' c:/data_outfile.txt ';
Export the table's data
Our sample text file:
"I Love You", "20", "common appearance, often bullying!" haha
"Li Kui", "21", "ordinary appearance, often bullying!" haha
"Wang two Meters", "20", "common appearance, often bullying!" haha
"Old Three", "24", "very strong"
"Old Four", "XXXXX", "the"
"Old Five", "52", "***%*￥*￥*￥*￥"
"Kitten", "45", "middle omitted ... "
"Puppy", "12", "will be called"
"Little Sister", "I", "PP's very"
"Little Wretch", "52", "duplicity"
"God the Lord", "96", "very handsome"
"MM Come", "10", "... "
"Celebrating the party", "20", "Socialist good"
"Good People", "20", "Really good"
"Old High", "10", "Good study"
"Diagonal Three", "60", "Eye oblique"
"Son of China", "100", "Mighty"
"Rice", "63", "I love to eat"
"Apple", "15", "Yummy"
Our sample Table structure:
CREATE TABLE t0 (id bigint () unsigned not NULL auto_increment,name char (3) Null,age tinyint Scription Text not null,primary key (ID), UNIQUE key Idx_name (name)) Engine=myisam DEFAULT charset=utf8 mysql> load da Ta infile '/tmp/t0.txt ' ignore into table t0 character set GBK fields terminated by ', ' enclosed by ' "' Lines terminated b Y ' \ n ' (name,age,description);Abbreviated Methodmysql> Load Data infile '/tmp/t0.txt 'into table nameQuery OK, 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 | Ordinary appearance, often bullying! haha |
| 2 | Li Kui | 21 | Ordinary appearance, often bullying! haha |
| 3 | Wang Two M | 20 | Ordinary appearance, often bullying! haha |
| 4 | Old Three | 24 | Very strong |
| 5 | Old Four | 34 | XXXXX |
| 6 | Old Five | 52 | %*￥*￥*￥*￥|
| 7 | Kitten | 45 | Middle omitted ... |
| 8 | Puppy | 12 | It will be called |
| 9 | Sister | 21 | PP's Very |
| 10 | Little Villain | 52 | Duplicity |
| 11 | God he | 96 | Very handsome |
| 12 | MM Come on | 10 | ... |
| 13 | Praise Party | 20 | Socialism Good |
| 14 | People good | 20 | It is indeed good |
| 15 | Old High | 10 | Learning is Good |
| 16 | Diagonal Three | 60 | The Eye is tilted |
| 17 | Son of China | 100 | A Mighty Death |
| 18 | Rice | 63 | I love to eat |
| 19 | Apple | 15 | Delicious |
+----+----------+-----+----------------------------+
Rows in Set (0.00 sec)
Let me explain the relevant parameters.
With regard to my import statement, I will now speak straight to two other reference manuals.
Character Set GBK;
This character set must be written, otherwise it will be garbled or only part of the data import.
Ignore into table
Because the Name column adds a unique index, this is to avoid duplicate data insertion errors.
Join us to run this import statement again and you will find
Query OK, 0 rows Affected (0.00 sec)
records:19 deleted:0 skipped:19 warnings:0
There are no values to import because the same values are already in the box.
You can also use replace into table here
MySQL will kill the same first and then insert the new value.
mysql> load Data infile '/tmp/t0.txt ' replace into table t0 character set GBK fields terminated by ', ' enclosed by ' ' Lines terminated by ' \ n ' (name,age,description);
Query OK, Affected Rows (0.00 sec)
records:19 deleted:19 skipped:0 warnings:0
Mysql> select * from t0;
+----+----------+-----+----------------------------+
| ID | name | Age | Description |
+----+----------+-----+----------------------------+
| 20 | I love You | 20 | Ordinary appearance, often bullying! haha |
| 21 | Li Kui | 21 | Ordinary appearance, often bullying! haha |
| 22 | Wang Two M | 20 | Ordinary appearance, often bullying! haha |
| 23 | Old Three | 24 | Very strong |
| 24 | Old Four | 34 | XXXXX |
| 25 | Old Five | 52 | %*￥*￥*￥*￥|
| 26 | Kitten | 45 | Middle omitted ... |
| 27 | Puppy | 12 | It will be called |
| 28 | Sister | 21 | PP's Very |
| 29 | Little Villain | 52 | Duplicity |
| 30 | God he | 96 | Very handsome |
| 31 | MM Come on | 10 | ... |
| 32 | Praise Party | 20 | Socialism Good |
| 33 | People good | 20 | It is indeed good |
| 34 | Old High | 10 | Learning is Good |
| 35 | Diagonal Three | 60 | The Eye is tilted |
| 36 | Son of China | 100 | A Mighty Death |
| 37 | Rice | 63 | I love to eat |
| 38 | Apple | 15 | Delicious |
+----+----------+-----+----------------------------+
Rows in Set (0.00 sec)
(name,age,description);
These are the specific table properties, which indicate that you can import the data you want.
MySQL file import to database load data infile into table use example
