mysqlimport學(xué)習(xí)總結(jié)
原文鏈接： https://www.modb.pro/db/23208?xy
站在用戶(hù)的角度思考問(wèn)題，與客戶(hù)深入溝通，找到偏關(guān)網(wǎng)站設(shè)計(jì)與偏關(guān)網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶(hù)體驗(yàn)好的作品，建站類(lèi)型包括：網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、主機(jī)域名、虛擬空間、企業(yè)郵箱。業(yè)務(wù)覆蓋偏關(guān)地區(qū)。
摘要：MySQLimport是MySQL數(shù)據(jù)庫(kù)提供的一個(gè)命令行程序，可用于數(shù)據(jù)導(dǎo)入。
1.mysqlimport概述
mysqlimport是MySQL數(shù)據(jù)庫(kù)提供的一個(gè)命令行程序，可用于數(shù)據(jù)導(dǎo)入。從本質(zhì)上來(lái)說(shuō)，是LOAD DATA INFILE的命令接口，而且大多數(shù)的選項(xiàng)都和LOAD DATA INFILE語(yǔ)法相同。其語(yǔ)法格式如下：
shell>mysqlimport [options] db_name textfilel [textfile2 …]
和LOAD DATA INFILE不同的是，mysqlimport命令可以用來(lái)導(dǎo)入多張表。并且通過(guò)–use-threads=參數(shù)并發(fā)地導(dǎo)入不同的文件。這里的并發(fā)是指并發(fā)導(dǎo)入多個(gè)文件，而不是指mysqlimport可以并發(fā)地導(dǎo)入一個(gè)文件，這是有明顯區(qū)別的。此外，通常來(lái)說(shuō)并發(fā)地對(duì)同一張表進(jìn)行導(dǎo)入，其效果一般都不會(huì)比串行的方式好。
參數(shù)說(shuō)明：
–use-threads=# Load files in parallel. The argument is the number of threads to use for loading data.
2.演示
2.1導(dǎo)出數(shù)據(jù)
cd /usr/local/mysql/bin
./mysqldump -uroot -poracle --tab=/data/backup test
使用mysqldump工具導(dǎo)出test庫(kù)下面所有的表。添加–tab參數(shù)表名，導(dǎo)出的每張表的定義輸出到一個(gè)文件（xxxTAB.sql），每張表的數(shù)據(jù)輸出到另外一個(gè)文件（xxxTAB.txt）。
[root@source backup]# cd /usr/local/mysql/bin [root@source bin]# ./mysqlpump --version mysqlpump 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 test mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@source bin]# [root@source mysql]# cd /data/backup/ [root@source backup]# ll total 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.txt 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON [root@source backup]#
2.2新建數(shù)據(jù)庫(kù)test1，將數(shù)據(jù)導(dǎo)入到test1庫(kù)
[root@source backup]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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)]> exit Bye [root@source backup]#
2.3導(dǎo)入數(shù)據(jù)
2.3.1導(dǎo)入方法1
使用mysql導(dǎo)入定義，使用mysqlimport方法導(dǎo)入數(shù)據(jù)
create database test1;
mysql -uroot -poracle test1 </data/backup/DEPT.sql
mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txt
mysqlimport參數(shù)說(shuō)明：
-L, --local Read all files through the client.
[root@source backup]# mysql -uroot -poracle test1 </data/backup/DEPT.sql [root@source backup]# [root@source backup]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server 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 its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@db 17:43: [(none)]> root@db 17:43: [(none)]> USE test1; Database changed root@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]> exit Bye [root@source backup]# [root@source backup]# mysqlimport -uroot -poracle --local test1 /data/backup/DEPT.txt test1.DEPT: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 [root@source backup]# [root@source backup]# mysql -p test1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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導(dǎo)入方法2
在mysql命令行執(zhí)行腳本創(chuàng)建命令，再使用load data local infile … into …加載數(shù)據(jù)
mysql -p test1
source /data/backup/DEPT.sql
load data local infile ‘/data/backup/DEPT.txt’ into table DEPT;
[root@source backup]# mysql -p test1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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.sql 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 (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: 0 root@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環(huán)境準(zhǔn)備
root@db 11:28: [(none)]> use test1 Database changed root@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]> exit Bye [root@source ~]# [root@source ~]# cd /data/ [root@source data]# [root@source data]# ll total 18372 drwxr-xr-x 2 mysql mysql 4096 Mar 21 11:35 backup drwxr-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 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 2 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 3 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 4 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 5 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 6 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 7 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 8 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 9 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 10 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 11 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 12 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 13 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 14 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 15 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 16 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 17 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 18 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 19 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 20 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 21 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 22 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 23 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 24 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 25 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 26 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 27 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 28 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 29 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 30 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 31 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 32 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 33 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 34 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 35 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 36 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 37 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 38 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 39 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 40 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 41 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 42 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 43 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 44 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 。。。。。。 [root@source data]# more sbtest2.txt 1 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 2 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 3 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 4 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 5 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 6 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 7 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 8 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 9 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 10 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 11 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 12 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 13 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 14 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 15 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 16 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 17 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 18 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 19 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 20 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 21 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 22 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 23 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 24 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 25 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 26 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 27 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 28 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 29 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 30 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 31 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 32 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 33 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 34 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 35 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 36 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 37 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 38 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 39 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 40 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 41 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 42 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 43 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 44 0 qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt 。。。。。。
2.4.2串行導(dǎo)入
下面演示串行導(dǎo)入2張表數(shù)據(jù)：
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.txt test1.sbtest1: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0 test1.sbtest2: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0 [root@source data]#
窗口2：
如果在上述命令的運(yùn)行過(guò)程中，查看MySQL的數(shù)據(jù)庫(kù)線程列表，應(yīng)該可以看到類(lèi)似如下內(nèi)容：
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每次只有一個(gè)線程在導(dǎo)入數(shù)據(jù)，不加–use-threads=2參數(shù)，是串行地導(dǎo)人數(shù)據(jù)。
2.4.3并發(fā)導(dǎo)入
下面通過(guò)mysqlimport并發(fā)地導(dǎo)入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.txt test1.sbtest1: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0 test1.sbtest2: Records: 100011 Deleted: 0 Skipped: 0 Warnings: 0
窗口2：
如果在上述命令的運(yùn)行過(guò)程中，查看MySQL的數(shù)據(jù)庫(kù)線程列表，應(yīng)該可以看到類(lèi)似如下內(nèi)容：
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參數(shù)后，mysqlimport實(shí)際上是同時(shí)執(zhí)行了兩句LOAD DTA INFILE并發(fā)地導(dǎo)人數(shù)據(jù)。
新聞標(biāo)題：mysqlimport學(xué)習(xí)總結(jié)
URL網(wǎng)址：http://www.rwnh.cn/article24/gdgdje.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁(yè)設(shè)計(jì)公司、服務(wù)器托管、外貿(mào)建站、企業(yè)建站、搜索引擎優(yōu)化、網(wǎng)站導(dǎo)航
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 成都手機(jī)網(wǎng)站建設(shè)要滿(mǎn)足哪些條件？ 2017-01-03
- 手機(jī)網(wǎng)站建設(shè)找創(chuàng)新互聯(lián)，值得您信賴(lài) 2022-06-10
- 如何提升章丘手機(jī)網(wǎng)站建設(shè)的體驗(yàn)度 2020-12-16
- 手機(jī)網(wǎng)站建設(shè)多樣化的瀏覽方式 2021-12-02
- 手機(jī)網(wǎng)站和傳統(tǒng)計(jì)算機(jī)網(wǎng)站之間的差異 2016-06-02
- 企業(yè)想開(kāi)發(fā)手機(jī)APP軟件，首先要弄清楚以下10點(diǎn) 2016-08-12
- 響應(yīng)式網(wǎng)站與單獨(dú)設(shè)計(jì)手機(jī)網(wǎng)站有什么不同? 2015-12-12
- 企業(yè)建站選擇Pc網(wǎng)站建設(shè)還是手機(jī)端網(wǎng)站制作？它們有什么區(qū)別？ 2016-09-21
- 手機(jī)網(wǎng)站建設(shè)要哪些小技巧？ 2021-03-09
- 企業(yè)手機(jī)網(wǎng)站建設(shè)需要注意的要點(diǎn) 2015-07-18
- 如何設(shè)計(jì)手機(jī)網(wǎng)站頁(yè)面才能瀏覽速度快 2016-11-11
- 從哪些方面可以對(duì)手機(jī)網(wǎng)站進(jìn)行優(yōu)化 2013-07-12