Usage of mysql load data infile (importing 40 W data to mysql in 3-5 seconds)

If you want to import Chinese data, the utf8 Character Set set in mysql will save the UTF-8 character set for the xxx.txt file you want to import. The command load data infile "d: /Websites/Sxxxx/test1.txt "ignore into table 'names' fields terminated by ', 'enabledby '"';

I don't know if the keyword replace is used, it will be garbled .. Different, and other experts answer.

For more information, see the mysql manual.,

In useLOAD DATAToMySQLWhen there are2Case:

(1) On the remote client (you must add the following options:-- Local-infile = 1) Import the remote client textMySQL, You must specifyLOCAL(Default isIgnore),AddIgnoreOption will discard data, addReplaceOption updates the data, and no uniqueness constraints occur.

(2) Import local server textMySQL, Not specifiedLOACLIf a uniqueness constraint conflict exists, rollback will fail. If data cannot be imported, you need to addIgnoreOrReplaceTo import data.

The test is as follows:

(1) The local server imports local server text.

Mysql> show create table tmp_loaddata \ G;

* *************************** 1. row ***************************

Table: tmp_loaddata

Create Table:Create table 'tmp _ loaddata '(

'Id' int (11) not null,

'Name' varchar (10) 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 table tmp_loaddata fields terminated ',';

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

#If a uniqueness constraint conflict occurs, rollback will fail.

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 terminated ',';

Query OK,1 row affected(0.00 sec)

Records: 2Deleted: 0Skipped: 1Warnings: 0

#UseIGNOREDiscard 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 terminated ',';

Query OK,3 rows affected(0.00 sec)

Records: 2Deleted: 1Skipped: 0Warnings: 0

#UseREPLACEUpdate conflicting data.

Mysql>Select * from tmp_loaddata;

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

| Id | name|

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

|1 | new update |

|2 | new update |

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

2 rows in set (0.00 sec)

(2) Import remote client text

[Zhu Xu @ 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) 2000,201 0, 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 license

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 table tmp_loaddata fields terminated ',';

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

#The database server does not have the corresponding text file, so an error is reported.

Mysql>

Mysql>LOAD DATALOCALINFILE '/tmp/2.txt' into table tmp_loaddata fields terminated ',';

ERROR 1148 (42000): The used command is not allowed with this MySQL version

#Go inMysqlRemote client, you also need to add-- Local-infile = 1Parameter.

Mysql> exit

Bye

[Zhu Xu @ xentest9-vm1 tmp] $Mysql-uzhuxu-pzhuxu test-h10.254.5.151-- Local-infile = 1-- Show-warnings-v \

>-E "load data local infile '/tmp/2.txt' into table tmp_loaddata fields terminated ','";

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

Load data local infile '/tmp/2.txt' into table tmp_loaddata fields terminated ','

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

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)

#

[Zhu Xu @ xentest9-vm1 tmp] $Mysql-uzhuxu-pzhuxu test-h10.254.5.151-- Local-infile = 1-- Show-warnings-v \

>-E "load data local infile '/tmp/2.txt'IGNOREInto table tmp_loaddata fields terminated ','";

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

Load data local infile '/tmp/2.txt' ignore into table tmp_loaddata fields terminated ','

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

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)

#

[Zhu Xu @ xentest9-vm1 tmp] $Mysql-uzhuxu-pzhuxu test-h10.254.5.151-- Local-infile = 1-- Show-warnings-v \

>-E "load data local infile '/tmp/2.txt'REPLACEInto table tmp_loaddata fields terminated ','";

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

Load data local infile '/tmp/2.txt' replace into table tmp_loaddata fields terminated ','

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

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 --