Usage of mysqlloaddatainfile (importing 40 W data to mysql in 3-5 seconds) _ MySQL

Usage of mysqlloaddatainfile (importing 40 W data to mysql in 3-5 seconds) bitsCN.com

If you want to import Chinese data, the utf8 character 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.,

When using load data to MySQL, there are two cases:

(1) on the remote client (you need to add options:-- Local-infile = 1) To import the remote client text to MySQL, you must specify the LOCAL (ignore by default). If you add the ignore option, the data will be discarded, and the data will be updated with the replace option. no uniqueness constraints will occur.

(2) import the local server text to MySQL on the local server without specifying the LOACL. if a uniqueness constraint conflict occurs, rollback will fail and data cannot be imported, in this case, you need to add ignore or replace to import data.

The test is as follows:

(1) import local server text from the local server

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'

# Rollback will fail if a uniqueness constraint conflict occurs.

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: 2 Deleted: 0 Skipped: 1 Warnings: 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 terminated ',';

Query OK, 3 rows affected (0.00 sec)

Records: 2 Deleted: 1 Skipped: 0 Warnings: 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

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

# An error is reported because the database server does not have the corresponding text file.

Mysql>

Mysql> load data localinfile '/tmp/2.txt' into table tmp_loaddata fields terminated ',';

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

# Add the mysql remote client-- 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: 3 Deleted: 0 Skipped: 1 Warnings: 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: 3 Deleted: 0 Skipped: 3 Warnings: 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: 3 Deleted: 1 Skipped: 0 Warnings: 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 --

BitsCN.com