使用命令行工具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.sqlnot 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//|/gstudents.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, Cant 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/4725.html

mysql