MySQL file import to database load data infile into table use example

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.