Usage of MySQL load data infile (40w 3-5 seconds to lead MySQL) _mysql

If you are importing the Chinese data, my MySQL set UTF8 character set, so you want to import the Xxx.txt file to save the Utf-8 character set, command load Data infile "D:/websites/sxxxx/test1.txt" Ignore into table ' names ' fields terminated by ', ' enclosed by ' ';

Do not know with replace this keyword words, still will garbled. Different, and so the master answer.

in the detailed introduction, recommend everyone to see the MySQL manual to go to it, inside the introduction of very detailed ,

When you use LOAD data to MySQL, there are 2 different scenarios:

(1) in remote client (need add option:--local-infile=1) Import remote client text to MySQL, need to specify local (default is ignore), plus The Ignore option discards the data, and the replace option updates the data without any uniqueness constraint problems.

(2) in the local server import local server text to MySQL, do not specify Loacl, a unique constraint conflict, will fail rollback, data import does not go in, this time need to add ignore or replace to import data.

The test is as follows:

(1) Local server import local server text

Mysql> Show CREATE TABLE tmp_loaddata\g;

1. Row ***************************

Table:tmp_loaddata

CREATE TABLE:CREATE TABLE ' Tmp_loaddata ' (

' id ' int (one) not NULL,

' Name ' varchar (a) DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT charset=latin1

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>SELECT * from Tmp_loaddata;

+----+------+

| ID | name |

+----+------+

| 1 | test |

+----+------+

1 row in Set (0.00 sec)

Mysql>

Mysql>System Cat/home/zhuxu/1.txt

1,new Update

2,new Update

Mysql>

mysql>LOAD DATA INFILE '/home/zhuxu/1.txt ' into the TABLE tmp_loaddata FIELDS terminated by ', ';

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

# A Uniqueness constraint violation occurs and a rollback is unsuccessful

Mysql>SELECT * from Tmp_loaddata;

+----+------+

| ID | name |

+----+------+

| 1 | test |

+----+------+

1 row in Set (0.00 sec)

mysql>LOAD DATA INFILE '/home/zhuxu/1.txt ' ignoreinto TABLE tmp_loaddata FIELDS by ', ';

Query OK,1 row affected(0.00 sec)

Records:2deleted:0skipped:1warnings:0

# Use Ignore to discard conflicting data.

Mysql>SELECT * from Tmp_loaddata;

+----+------------+

| ID | Name|

+----+------------+

| 1 | Test|

| 2 | new Update |

+----+------------+

2 rows in Set (0.00 sec)

mysql>LOAD DATA INFILE '/home/zhuxu/1.txt ' replaceinto TABLE tmp_loaddata FIELDS by ', ';

Query OK,3 rows affected(0.00 sec)

Records:2deleted:1skipped:0warnings:0

# use replace to update conflicting data.

Mysql>SELECT * from Tmp_loaddata;

+----+------------+

| ID | Name|

+----+------------+

| 1 | new update |

| 2 | new Update |

+----+------------+

2 rows in Set (0.00 sec)

(2) Remote client import remote client text

[Zhuxu@xentest9-vm1 tmp]$Mysql-uzhuxu-pzhuxu test-h10.254.5.151

Welcome to the MySQL Monitor. Commands end with; or \g.

Your MySQL Connection ID is 15

Server Version:5.1.47-log Source Distribution

Copyright (c), the Oracle and/or its affiliates. All rights reserved.

This software comes with absolutely NO WARRANTY. This is free software,

And you are welcome to modify and redistribute it under the GPL v2

Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>SELECT * from Tmp_loaddata;

+----+------+

| ID | name |

+----+------+

| 1 | test |

+----+------+

1 row in Set (0.00 sec)

Mysql>System Cat/tmp/2.txt

1,new Update

2,new Update

3,new Update

Mysql>

mysql>LOAD DATA INFILE '/tmp/2.txt ' into the TABLE tmp_loaddata FIELDS terminated by ', ';

ERROR (HY000): Can ' t get stat of '/tmp/2.txt ' (errcode:2)

# because the database server does not have a corresponding text file, an error is given.

Mysql>

mysql>LOAD DATA localinfile '/tmp/2.txt ' into the TABLE tmp_loaddata FIELDS terminated by ', ';

ERROR 1148 (42000): The used command isn't allowed with this MySQL version

# go in mysql remote client, also need to add --local-infile=1 parameter specified.

Mysql> exit

Bye

[Zhuxu@xentest9-vm1 tmp]$mysql-uzhuxu-pzhuxu test-h10.254.5.151 --local-infile=1--show-warnings-v -V-V \

> E "LOAD DATA local INFILE '/tmp/2.txt ' to TABLE tmp_loaddata FIELDS terminated by '";

--------------

LOAD DATA local INFILE '/tmp/2.txt ' to TABLE tmp_loaddata FIELDS terminated by ', '

--------------

Query OK,2 rows affected(0.00 sec)

Records:3deleted:0skipped:1warnings:0

Bye

Mysql>SELECT * from Tmp_loaddata;

+----+------------+

| ID | Name|

+----+------------+

| 1 | Test|

| 2 | new Update |

| 3 | new Update |

+----+------------+

3 Rows in Set (0.00 sec)

#

[Zhuxu@xentest9-vm1 tmp]$mysql-uzhuxu-pzhuxu test-h10.254.5.151 --local-infile=1--show-warnings-v -V-V \

> E "LOAD DATA local INFILE '/tmp/2.txt ' ignoreinto TABLE tmp_loaddata FIELDS by ', '";

--------------

LOAD DATA local INFILE '/tmp/2.txt ' IGNORE to TABLE tmp_loaddata FIELDS terminated by ', '

--------------

Query OK,0 rows affected(0.00 sec)

Records:3deleted:0skipped:3warnings:0

Bye

Mysql>SELECT * from Tmp_loaddata;

+----+------------+

| ID | Name|

+----+------------+

| 1 | Test|

| 2 | new Update |

| 3 | new Update |

+----+------------+

3 Rows in Set (0.00 sec)

#

[Zhuxu@xentest9-vm1 tmp]$mysql-uzhuxu-pzhuxu test-h10.254.5.151 --local-infile=1--show-warnings-v-V- V \

> E "LOAD DATA local INFILE '/tmp/2.txt ' replaceinto TABLE tmp_loaddata FIELDS by ', '";

--------------

LOAD DATA local INFILE '/tmp/2.txt ' REPLACE in TABLE tmp_loaddata FIELDS terminated by ', '

--------------

Query OK,4 rows affected(0.00 sec)

Records:3deleted:1skipped:0warnings:0

Bye

Mysql> select * from Tmp_loaddata;

+----+------------+

| ID | Name|

+----+------------+

| 1 | new update |

| 2 | new Update |

| 3 | new Update |

+----+------------+

3 Rows in Set (0.00 sec)

--eof--