Shulou(Shulou.com)05/31 Report--

This article will explain in detail how MySQL imports data optimization in batches, and the content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

-- MyISAM table

Mysql > show create table test\ G

* * 1. Row *

Table: test

Create Table: CREATE TABLE `test` (

`id`int (11) NOT NULL

`last_ name` char (30) NOT NULL

`first_ name` char (30) NOT NULL

PRIMARY KEY (`id`)

KEY `name` (`last_ name`, `first_ name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

Mysql > show keys from test

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | test | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE |

| | test | 1 | name | 1 | last_name | A | NULL | NULL | NULL | | BTREE |

| | test | 1 | name | 2 | first_name | A | NULL | NULL | NULL | | BTREE |

+- -+

3 rows in set (0.00 sec)

Mysql > alter table test disable keys

Query OK, 0 rows affected (0.00 sec)

Mysql > load data infile'/ var/lib/mysql-files/test_out.txt' into table test charset gbk fields terminated by', 'enclosed by''

Query OK, 5 rows affected (0.02 sec)

Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

Mysql > alter table test enable keys

Query OK, 0 rows affected (0.00 sec)

-- InnoDB table

The imported data is arranged in the order of primary keys.

Set the unique_checks parameter to 0

Mysql > show variables like'% unique%'

+-+ +

| | Variable_name | Value |

+-+ +

| | unique_checks | ON |

+-+ +

1 row in set (0.01 sec)

Mysql > set unique_checks = 0

Query OK, 0 rows affected (0.10 sec)

Mysql > show variables like'% unique%'

+-+ +

| | Variable_name | Value |

+-+ +

| | unique_checks | OFF |

+-+ +

1 row in set (0.00 sec)

Mysql > load data infile'/ var/lib/mysql-files/test_out.txt' into table test charset gbk fields terminated by', 'enclosed by''

Query OK, 5 rows affected (0.00 sec)

Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

Set the autocommit parameter to 0

Mysql > show variables like 'autocommit'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'autocommit'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

+-+ +

1 row in set (0.00 sec)

Mysql > load data infile'/ var/lib/mysql-files/test_out.txt' into table test charset gbk fields terminated by', 'enclosed by''

Query OK, 5 rows affected (0.00 sec)

Records: 5 Deleted: 0 Skipped: 0 Warnings: 0

About how MySQL batch import data optimization is shared here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.