mysqlimport学习总结
发表于：2024-10-24 作者：千家信息网编辑
千家信息网最后更新 2024年10月24日，原文链接： https://www.modb.pro/db/23208?xy摘要：mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。1.mysqlimport概述mysql
千家信息网最后更新 2024年10月24日mysqlimport学习总结
原文链接： https://www.modb.pro/db/23208?xy
摘要：mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。1.mysqlimport概述
mysqlimport是MySQL数据库提供的一个命令行程序，可用于数据导入。从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。其语法格式如下：
shell>mysqlimport [options] db_name textfilel [textfile2 …]
和LOAD DATA INFILE不同的是，mysqlimport命令可以用来导入多张表。并且通过-use-threads=参数并发地导入不同的文件。这里的并发是指并发导入多个文件，而不是指mysqlimport可以并发地导入一个文件，这是有明显区别的。此外，通常来说并发地对同一张表进行导入，其效果一般都不会比串行的方式好。
参数说明：
-use-threads=# Load files in parallel. The argument is the number of threads to use for loading data.
2.演示
2.1导出数据
cd /usr/local/mysql/bin
./mysqldump -uroot -poracle --tab=/data/backup test
使用mysqldump工具导出test库下面所有的表。添加-tab参数表名，导出的每张表的定义输出到一个文件（xxxTAB.sql），每张表的数据输出到另外一个文件（xxxTAB.txt）。
[root@source backup]# cd /usr/local/mysql/bin[root@source bin]# ./mysqlpump --versionmysqlpump Ver 1.0.0 Distrib 5.7.20, for linux-glibc2.12 (x86_64)[root@source bin]# [root@source bin]# ./mysqldump -uroot -poracle --tab=/data/backup testmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@source bin]# [root@source mysql]# cd /data/backup/[root@source backup]# lltotal 28-rw-r--r-- 1 root root 1408 Mar 20 17:37 BONUS.sql-rw-rw-rw- 1 mysql mysql 0 Mar 20 17:37 BONUS.txt-rw-r--r-- 1 root root 1400 Mar 20 17:37 DEPT.sql-rw-rw-rw- 1 mysql mysql 80 Mar 20 17:37 DEPT.txt-rw-r--r-- 1 root root 1662 Mar 20 17:37 EMP.sql-rw-rw-rw- 1 mysql mysql 767 Mar 20 17:37 EMP.txt-rw-r--r-- 1 root root 1383 Mar 20 17:37 SALGRADE.sql-rw-rw-rw- 1 mysql mysql 59 Mar 20 17:37 SALGRADE.txt[root@source backup]# [root@source backup]# more /data/backup/DEPT.sql-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86_64)---- Host: localhost Database: test-- -------------------------------------------------------- Server version 5.7.20-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `DEPT`--DROP TABLE IF EXISTS `DEPT`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `DEPT` ( `DEPTNO` int(10) NOT NULL, `DNAME` varchar(14) DEFAULT NULL, `LOC` varchar(13) DEFAULT NULL, PRIMARY KEY (`DEPTNO`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2020-03-20 17:37:49[root@source backup]# [root@source backup]# more DEPT.txt10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON[root@source backup]#
2.2新建数据库test1，将数据导入到test1库
[root@source backup]# mysql -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.7.20-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@db 17:41: [(none)]> root@db 17:41: [(none)]> create database test1;Query OK, 1 row affected (0.11 sec)root@db 17:41: [(none)]> root@db 17:41: [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test || test1 |+--------------------+6 rows in set (0.00 sec)root@db 17:41: [(none)]> root@db 17:41: [(none)]> root@db 17:41: [(none)]> root@db 17:41: [(none)]> exitBye[root@source backup]#
2.3导入数据
2.3.1导入方法1
使用mysql导入定义，使用mysqlimport方法导入数据
create database test1;
mysql -uroot -poracle test1 mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txt
mysqlimport参数说明：
-L, --local Read all files through the client.
[root@source backup]# mysql -uroot -poracle test1 root@db 17:43: [(none)]> USE test1;Database changedroot@db 17:43: [test1]> root@db 17:43: [test1]> show tables;+-----------------+| Tables_in_test1 |+-----------------+| DEPT |+-----------------+1 row in set (0.00 sec)root@db 17:43: [test1]> root@db 17:43: [test1]> select * from DEPT;Empty set (0.00 sec)root@db 17:43: [test1]> root@db 17:44: [test1]> exitBye[root@source backup]#[root@source backup]# mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txttest1.DEPT: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0[root@source backup]# [root@source backup]# mysql -p test1Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 21Server version: 5.7.20-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@db 17:46: [test1]> root@db 17:46: [test1]> show tables;+-----------------+| Tables_in_test1 |+-----------------+| DEPT |+-----------------+1 row in set (0.00 sec)root@db 17:46: [test1]> root@db 17:46: [test1]> select * from DEPT;+--------+------------+----------+| DEPTNO | DNAME | LOC |+--------+------------+----------+| 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH | DALLAS || 30 | SALES | CHICAGO || 40 | OPERATIONS | BOSTON |+--------+------------+----------+4 rows in set (0.00 sec)root@db 17:46: [test1]>
2.3.2导入方法2
在mysql命令行执行脚本创建命令，再使用load data local infile … into …加载数据
mysql -p test1
source /data/backup/DEPT.sql
load data local infile '/data/backup/DEPT.txt' into table DEPT;
[root@source backup]# mysql -p test1Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.7.20-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.root@db 17:47: [test1]> root@db 17:47: [test1]> DROP TABLE DEPT;Query OK, 0 rows affected (0.06 sec)root@db 17:47: [test1]> source /data/backup/DEPT.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.03 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)root@db 17:47: [test1]> root@db 17:47: [test1]> root@db 17:47: [test1]> show tables;+-----------------+| Tables_in_test1 |+-----------------+| DEPT |+-----------------+1 row in set (0.00 sec)root@db 17:47: [test1]> root@db 17:47: [test1]> select * from DEPT;Empty set (0.00 sec)root@db 17:47: [test1]> root@db 17:47: [test1]> root@db 17:49: [test1]> load data local infile '/data/backup/DEPT.txt' into table DEPT;Query OK, 4 rows affected (0.01 sec)Records: 4 Deleted: 0 Skipped: 0 Warnings: 0root@db 17:49: [test1]> root@db 17:49: [test1]> root@db 17:49: [test1]> select * from DEPT;+--------+------------+----------+| DEPTNO | DNAME | LOC |+--------+------------+----------+| 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH | DALLAS || 30 | SALES | CHICAGO || 40 | OPERATIONS | BOSTON |+--------+------------+----------+4 rows in set (0.00 sec)root@db 17:49: [test1]>
2.4并行与串行演示
2.4.1环境准备
root@db 11:28: [(none)]> use test1Database changedroot@db 11:28: [test1]> root@db 11:28: [test1]> show tables;+-----------------+| Tables_in_test1 |+-----------------+| DEPT |+-----------------+1 rows in set (0.00 sec)root@db 11:28: [test1]> root@db 11:31: [test1]> create table sbtest1(id int(10) unsigned primary key,k int(10) unsigned,c char(120),pad char(60));Query OK, 0 rows affected (0.05 sec)root@db 11:32: [test1]> desc sbtest1;+-------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id | int(10) unsigned | NO | PRI | NULL | || k | int(10) unsigned | YES | | NULL | || c | char(120) | YES | | NULL | || pad | char(60) | YES | | NULL | |+-------+------------------+------+-----+---------+-------+4 rows in set (0.00 sec)root@db 11:32: [test1]> root@db 11:33: [test1]> create table sbtest2(id int(10) unsigned primary key,k int(10) unsigned,c char(120),pad char(60));Query OK, 0 rows affected (0.02 sec)root@db 11:33: [test1]> root@db 11:33: [test1]> desc sbtest2;+-------+------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id | int(10) unsigned | NO | PRI | NULL | || k | int(10) unsigned | YES | | NULL | || c | char(120) | YES | | NULL | || pad | char(60) | YES | | NULL | |+-------+------------------+------+-----+---------+-------+4 rows in set (0.00 sec)root@db 11:33: [test1]> root@db 11:33: [test1]> show tables;+-----------------+| Tables_in_test1 |+-----------------+| DEPT || sbtest1 || sbtest2 |+-----------------+3 rows in set (0.00 sec)root@db 11:33: [test1]> root@db 11:33: [test1]> exitBye[root@source ~]#[root@source ~]# cd /data/[root@source data]# [root@source data]# lltotal 18372drwxr-xr-x 2 mysql mysql 4096 Mar 21 11:35 backupdrwxr-xr-x 7 mysql mysql 4096 Mar 21 11:19 mysql-rw-r--r-- 1 root root 6264322 Mar 21 11:36 sbtest1.txt-rw-r--r-- 1 root root 6264322 Mar 21 11:36 sbtest2.txt[root@source data]# [root@source data]# more sbtest1.txt 1 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt2 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt3 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt4 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt5 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt6 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt7 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt8 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt9 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt10 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt11 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt12 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt13 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt14 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt15 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt16 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt17 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt18 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt19 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt20 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt21 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt22 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt23 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt24 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt25 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt26 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt27 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt28 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt29 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt30 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt31 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt32 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt33 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt34 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt35 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt36 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt37 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt38 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt39 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt40 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt41 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt42 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt43 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt44 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt。。。。。。[root@source data]# more sbtest2.txt 1 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt2 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt3 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt4 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt5 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt6 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt7 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt8 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt9 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt10 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt11 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt12 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt13 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt14 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt15 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt16 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt17 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt18 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt19 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt20 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt21 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt22 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt23 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt24 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt25 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt26 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt27 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt28 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt29 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt30 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt31 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt32 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt33 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt34 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt35 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt36 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt37 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt38 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt39 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt40 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt41 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt42 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt43 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt44 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt。。。。。。
2.4.2串行导入
下面演示串行导入2张表数据：
mysqlimport -uroot -poracle test1 /data/sbtest1.txt /data/sbtest2.txt
show full processlist;
窗口1：
[root@source data]# mysqlimport -uroot -poracle test1 /data/sbtest1.txt /data/sbtest2.txttest1.sbtest1: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0test1.sbtest2: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0[root@source data]#
窗口2：
如果在上述命令的运行过程中，查看MySQL的数据库线程列表，应该可以看到类似如下内容：
root@db 11:38: [(none)]> show full processlist;+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+| 9 | root | localhost | NULL | Query | 0 | starting | show full processlist || 10 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest1.txt' INTO TABLE `sbtest1` IGNORE 0 LINES |+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+2 rows in set (0.00 sec)root@db 11:38: [(none)]> root@db 11:38: [(none)]> show full processlist;+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+| 9 | root | localhost | NULL | Query | 0 | starting | show full processlist || 10 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest2.txt' INTO TABLE `sbtest2` IGNORE 0 LINES |+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+2 rows in set (0.00 sec)root@db 11:38: [(none)]>
可以看到，mysqlimport每次只有一个线程在导入数据，不加-use-threads=2参数，是串行地导人数据。
2.4.3并发导入
下面通过mysqlimport并发地导入2张表：
mysqlimport -uroot -poracle --use-threads=2 test1 /data/sbtest1.txt /data/sbtest2.txt
show full processlist;
窗口1：
[root@source data]# mysqlimport -uroot -poracle --use-threads=2 test1 /data/sbtest1.txt /data/sbtest2.txttest1.sbtest1: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0test1.sbtest2: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0
窗口2：
如果在上述命令的运行过程中，查看MySQL的数据库线程列表，应该可以看到类似如下内容：
root@db 11:45: [(none)]> show full processlist;+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+| 9 | root | localhost | NULL | Query | 0 | starting | show full processlist || 11 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest1.txt' INTO TABLE `sbtest1` IGNORE 0 LINES || 12 | root | localhost | test1 | Query | 1 | executing | LOAD DATA INFILE '/data/sbtest2.txt' INTO TABLE `sbtest2` IGNORE 0 LINES |+----+------+-----------+-------+---------+------+-----------+----------------------------------------------------------------------------+3 rows in set (0.00 sec)root@db 11:45: [(none)]>
可以看到，加-use-threads=2参数后，mysqlimport实际上是同时执行了两句LOAD DTA INFILE并发地导人数据。
数据
命令
参数
数据库
文件
方法
线程
演示
不同
内容
程序
语法
过程
输出
运行
明显
相同
原文
参数表
只有
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
redis同步到数据库
vps 架设代理服务器
计算机系统数据库设计
推动涉农信用信息数据库
大风起兮服务器
数据库备份一致性
网站的服务器数据库什么意思
邮件接收服务器密码
商丘精益管理软件开发
怎么更改c 更改数据库
网络安全从业人员毕业院校
方舟服务器加载mod闪退
正规网络技术开发操作
专业财务代账软件开发
奇安信成为世界第一网络安全
光网网络技术有限公司
3级网络技术注意事项
河南软件开发哪里有提供
逆水寒直播服务器
海思软件开发
vpn拨号服务器
一般软件系统连接不同数据库系统
网络安全技术系统组成
服务器存储作用
胯地域服务器
计算机三级数据库应用与设计
台州黄岩区高端软件开发
阳江市江城区中正网络技术服务部
软件开发出来干啥的
途观车载与网络技术
