MySQL怎样批量导入数据优化
发布时间：2022-01-12 14:45:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章将为大家详细讲解有关MySQL怎么批量导入数据优化，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。 --MyISAM表 mysql show create table testG *************************** 1. row **********
|
这篇文章将为大家详细讲解有关MySQL怎么批量导入数据优化，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。 --MyISAM表 mysql> show create table testG *************************** 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表 导入的数据按照主键的顺序排列； 将unique_checks参数置为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 将autocommit参数设为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 关于MySQL怎么批量导入数据优化就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。 （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐