使用命令行工具mysqlimport导入数据详解数据库

Usage: mysqlimport [OPTIONS] database textfile …

mysqlimport 程序是一个将以特定格式存放的文本数据(如通过“select * into OUTFILE from …”所生成的数据文件)导入到指定的MySQL Server 中的工具程序,比如将一个标准的csv 文件导入到某指定数据库的指定表中。mysqlimport 工具实际上也只是“load data infile”命令的一个包装实现。

默认从以下路径中文件读取默认参数

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

1、常用选项:

  • –fields-terminated-by=字符串:设置字符串为字段之间的分隔符,可以为单个或多个字符。默认值为制表符“/t”。
  • -L, –local:表示从客户端任意路径读取文件导入表中,未设置该选项时,默认只从datadir下同名数据库目录下读取文件导入
  • –ignore-lines=n:表示可以忽略前n行。
  • -l, –lock-tables:写入时锁定所有表
  • -p, –password[=name]:指定用户密码
  • -u, –user=name:指定登入MySQL用户名
  • -h, –host=name:指定远程连接的服务器
  • -c, –columns=name:往表里导入指定字段,如:–columns=’Name,Age,Gender’
  • -C, –compress:在客户端和服务器之间启用压缩传递所有信息

其它可用选项和默认参数设置可以使用mysqlimport -help查询          

2、用法示例:

例1:基本用法

mysql> create table classes3 like classes; 
 
Query OK, 0 rows affected (0.07 sec) 
 
[[email protected] tmp]# mysqlimport -u root --localhellodb classes3.sql --fields-terminated-by="|" 
 
hellodb.classes3: Records: 10  Deleted: 0 Skipped: 0  Warnings: 0 
 
mysql> select  * from classes3; 
 
+---------+----------------+----------+ 
 
| ClassID | Class          | NumOfStu | 
 
+---------+----------------+----------+ 
 
|      1 | Shaolin Pai    |       10 | 
 
|      2 | Emei Pai       |        7 | 
 
|      3 | QingCheng Pai  |       11 | 
 
|      4 | Wudang Pai     |       12 | 
 
|      5 | Riyue Shenjiao |       31 | 
 
|      6 | Lianshan Pai   |       27 | 
 
|      7 | Ming Jiao      |       27 | 
 
|      8 | Xiaoyao Pai    |       15 | 
 
|      9 | HuaShan Pai    |       32 | 
 
|     10 | Fuwei Biaoju   |       19 | 
 
+---------+----------------+----------+ 
 
10 rows in set (0.00 sec)

例2:指定–local选项,可以从本机任意路径导入数据

mysql> create table classes2 likeclasses; 
 
Query OK, 0 rows affected (0.14 sec): 
 
[[email protected] tmp]# cp classes2.sql /tmp 
 
[[email protected] tmp]# mysqlimport -u root --localhellodb /tmp/classes2.sql 
 
hellodb.classes2: Records: 10  Deleted: 0 Skipped: 0  Warnings: 0 
 
mysql> select  * from classes2; 
 
+---------+----------------+----------+ 
 
| ClassID | Class          | NumOfStu | 
 
+---------+----------------+----------+ 
 
|      1 | Shaolin Pai    |       10 | 
 
|      2 | Emei Pai       |        7 | 
 
|      3 | QingCheng Pai  |       11 | 
 
|      4 | Wudang Pai     |       12 | 
 
|      5 | Riyue Shenjiao |       31 | 
 
|      6 | Lianshan Pai   |       27 | 
 
|      7 | Ming Jiao      |       27 | 
 
|      8 | Xiaoyao Pai    |       15 | 
 
|      9 | HuaShan Pai    |       32 | 
 
|     10 | Fuwei Biaoju   |       19 | 
 
+---------+----------------+----------+ 
 
10 rows in set (0.00 sec)

例3:未指定–local选项,无法从my.cnf中定义的其它路径中往表里导入数据

mysql> delete from classes2; 
 
Query OK, 10 rows affected (0.01 sec) 
 
[[email protected] ~]# head /tmp/classes2.sql -n 3 
 
1       ShaolinPai        10 
 
2       EmeiPai   7 
 
3       QingChengPai 11     
 
[[email protected] ~]# mysqlimport -u root hellodb/tmp/classes2.sql 
 
mysqlimport: Error: 29, File '/tmp/classes2.sql'not found (Errcode: 13), when using table: classes2

 

例4:未指定–local选项,默认只从mysql数据存放路径同名数据库目录下读取文件导入表中,必须指定绝对路径。

mysql> delete from students1; 
 
Query OK, 27 rows affected (2.60 sec) 
 
[[email protected] tmp]# sed 's//t//|/g'students.sql> students1.sql 
 
[[email protected] tmp]# head -n 2 students1.sql 
 
1|Shi Zhongyu|22|M|2|3 
 
2|Shi Potian|22|M|1|7 
 
[[email protected] tmp]# cd 
 
[[email protected] ~]# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated="|" 
 
mysqlimport: Error: 13, Can't get stat of'/var/lib/mysql/hellodb/students1.sql' (Errcode: 2), when using table:students1

未设置–local选项时,默认只从mysql数据存放路径同名数据库目录下读取文件导入

[[email protected] ~]# mysqlimport -u mhauser-p888888 hellodb /var/lib/mysql/tmp/students1.sql--fields-terminated="|" 
 
hellodb.students1: Records: 27  Deleted: 0 Skipped: 0  Warnings: 0

 

例5:数据库存放表目录下同名文件导入表中,只需指定文件名

mysql> delete from students1; 
 
Query OK, 27 rows affected (0.47 sec) 
 
[[email protected] ~]# cd /var/lib/mysql/hellodb/ 
 
[[email protected] hellodb]# cp ../tmp/students1.sql. 
 
将数据移到hellodb目录下,成功导入数据 
 
[[email protected] hellodb]# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated="|" 
 
hellodb.students1: Records: 27  Deleted: 0 Skipped: 0  Warnings: 0 
 
--fields-terminated="|":指定字段分隔符 
 
mysql> select * from students1 limit5,3; 
 
+-------+-----------+-----+--------+---------+-----------+ 
 
| StuID | Name      | Age | Gender | ClassID | TeacherID | 
 
+-------+-----------+-----+--------+---------+-----------+ 
 
|    6 | Shi Qing  |  46 | M     |       5 |      NULL | 
 
|    7 | Xi Ren    |  19 | F     |       3 |      NULL | 
 
|    8 | Lin Daiyu |  17 | F      |      7 |      NULL | 
 
+-------+-----------+-----+--------+---------+-----------+ 
 
3 rows in set (0.00 sec)

 

例6:忽略前5行数据导入表中

[[email protected] tmp]# mysqlimport -u root --localhellodb classes2.sql --ignore-lines=5 
 
hellodb.classes2: Records: 5  Deleted: 0 Skipped: 0  Warnings: 0 
 
--ignore-lines=n:指定忽略前n行 
 
mysql> select * from classes2; 
 
+---------+--------------+----------+ 
 
| ClassID | Class        | NumOfStu | 
 
+---------+--------------+----------+ 
 
|      6 | Lianshan Pai |       27 | 
 
|      7 | Ming Jiao    |       27| 
 
|      8 | Xiaoyao Pai  |       15 | 
 
|      9 | HuaShan Pai  |       32 | 
 
|     10 | Fuwei Biaoju |       19 | 
 
+---------+--------------+----------+ 
 
5 rows in set (0.00 sec)

 

例7:往非空表中导入数据

[[email protected] hellodb]# >students1.sql 
 
[[email protected] hellodb]# vim students1.sql 
 
[[email protected] hellodb]# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated="|" 
 
hellodb.students1: Records: 6  Deleted: 0 Skipped: 0  Warnings: 0 
 
[[email protected] hellodb]# more  students1.sql 
 
|Meng Qi D|17|M|2|3 
 
|SuoLong|22|M|1|7 
 
|Xiang Kesi|43|M|2|16 
 
|KaiDuo|52|M|4|4 
 
|JoBa|12|M|3|1 
 
|Nami|18|F|4|1 
 
[[email protected] hellodb]# 
 
mysql> select * from students1 limit27,6; 
 
+-------+------------+-----+--------+---------+-----------+ 
 
| StuID | Name       | Age | Gender | ClassID | TeacherID | 
 
+-------+------------+-----+--------+---------+-----------+ 
 
|   28 | Meng Qi D  |  17 | M     |       2 |         3 | 
 
|   29 | SuoLong    |  22 | M     |       1 |         7 | 
 
|   30 | Xiang Kesi |  43 | M      |      2 |        16 | 
 
|   31 | KaiDuo     |  52 | M     |       4 |         4 | 
 
|   32 | JoBa       |  12 | M     |       3 |         1 | 
 
|   33 | Nami       |  18 | F     |       4 |         1 | 
 
+-------+------------+-----+--------+---------+-----------+ 
 
6 rows in set (0.17 sec) 
 
mysql> select count(*) from students1 ; 
 
+----------+ 
 
| count(*) | 
 
+----------+ 
 
|      33 | 
 
+----------+ 
 
1 row in set (0.03 sec)

数据会追加在表后

例8、远程连接MySQL服务器导入特定字段

mysql> drop table students1; 
 
Query OK, 0 rows affected (2.89 sec) 
 
mysql> create table students1 likestudents; 
 
Query OK, 0 rows affected (1.57 sec) 
 
[[email protected] mysql]# more /tmp/students1.sql 
 
Meng Qi D|17|M 
 
SuoLong|22|M 
 
Xiang Kesi|43|M 
 
KaiDuo|52|M 
 
JoBa|12|M| 
 
Nami|18|F| 
 
Luo Bing|25|F 
 
Wu Suopu|20|M 
 
[[email protected] mysql]# mysqlimport -h192.168.88.131 -u mhauser -p888888 hellodb --local --fields-terminated-by='|' '/tmp/students1.sql'--columns='Name,Age,Gender' 
 
hellodb.students1: Records: 8  Deleted: 0 Skipped: 0  Warnings: 0

–columns=’Name,Age,Gender’:指定导入那些字段

-h 192.168.88.131:指定远程登录主机名

mysql> select * from students1; 
 
+-------+------------+-----+--------+---------+-----------+ 
 
| StuID | Name       | Age | Gender | ClassID | TeacherID | 
 
+-------+------------+-----+--------+---------+-----------+ 
 
|    1 | Meng Qi D  |  17 | M     |    NULL |      NULL | 
 
|    2 | SuoLong    |  22 | M     |    NULL |     NULL | 
 
|    3 | Xiang Kesi |  43 | M      |   NULL |      NULL | 
 
|    4 | KaiDuo     |  52 | M     |    NULL |      NULL | 
 
|    5 | JoBa       |  12 | M     |    NULL |      NULL | 
 
|    6 | Nami       |  18 | F     |    NULL |      NULL | 
 
|    7 | Luo Bing   |  25 | F     |    NULL |      NULL | 
 
|    8 | Wu Suopu   |  20 | M     |    NULL |      NULL | 
 
+-------+------------+-----+--------+---------+-----------+ 
 
8 rows in set (0.01 sec)

 

例9、远程连接MySQL服务器导入特定字段,采用压缩传递数据的形式

mysql> drop table students1; 
 
Query OK, 0 rows affected (0.75 sec) 
 
mysql> create table students1 likestudents; 
 
Query OK, 0 rows affected (0.66 sec) 
 
[[email protected] mysql]# mysqlimport -h192.168.88.131 -u mhauser -p888888 hellodb --local -C --fields-terminated-by='|' '/tmp/students1.sql'--columns='Name,ClassID,Gender' 
 
hellodb.students1: Records: 8  Deleted: 0 Skipped: 0  Warnings: 0

 

-C:指定压缩方式传递数据

mysql> select * from students1; 
 
+-------+------------+-----+--------+---------+-----------+ 
 
| StuID | Name       | Age | Gender | ClassID | TeacherID | 
 
+-------+------------+-----+--------+---------+-----------+ 
 
|    1 | Meng Qi D  |   0 | M     |      17 |      NULL | 
 
|    2 | SuoLong    |   0 | M     |      22 |      NULL | 
 
|    3 | Xiang Kesi |   0 | M      |     43 |      NULL | 
 
|    4 | KaiDuo     |   0 | M     |      52 |      NULL | 
 
|    5 | JoBa       |   0 | M     |      12 |      NULL | 
 
|    6 | Nami       |   0 | F     |      18 |      NULL | 
 
|    7 | Luo Bing   |   0 | F     |      25 |      NULL | 
 
|    8 | Wu Suopu   |   0 | M     |      20 |      NULL | 
 
+-------+------------+-----+--------+---------+-----------+ 
 
8 rows in set (0.00 sec)

 

原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/tech/database/4725.html

(0)
上一篇 2021年7月16日 23:13
下一篇 2021年7月16日 23:14

相关推荐

发表回复

登录后才能评论