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

小咪咪 2022-05-22 00:47 351阅读 0赞

**使用命令行工具mysqlimport导入数据**

**Usage:  mysqlimport  \[OPTIONS\]  database  textfile...**

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

/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)

\[root@www 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):

\[root@www tmp\]\# cp classes2.sql /tmp

\[root@www 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)

\[root@www ~\]\# head /tmp/classes2.sql -n 3

1       ShaolinPai        10

2       EmeiPai   7

3       QingChengPai 11

\[root@www ~\]\# 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)

\[root@www tmp\]\# sed 's/\\t/\\|/g'students.sql> students1.sql

\[root@www tmp\]\# head -n 2 students1.sql

1|Shi Zhongyu|22|M|2|3

2|Shi Potian|22|M|1|7

\[root@www tmp\]\# cd

\[root@www ~\]\# 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数据存放路径同名数据库目录下读取文件导入

\[root@www ~\]\# 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)

\[root@www ~\]\# cd /var/lib/mysql/hellodb/

\[root@www hellodb\]\# cp ../tmp/students1.sql.

将数据移到hellodb目录下，成功导入数据

\[root@www 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;

\+-------+-----------+-----+--------+---------+-----------+

\+-------+-----------+-----+--------+---------+-----------+

|    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行数据导入表中**

\[root@www 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：往非空表中导入数据**

\[root@www hellodb\]\# >students1.sql

\[root@www hellodb\]\# vim students1.sql

\[root@www hellodb\]\# mysqlimport -u mhauser-p888888 hellodb students1.sql --fields-terminated="|"

hellodb.students1: Records: 6  Deleted: 0 Skipped: 0  Warnings: 0

\[root@www 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

\[root@www hellodb\]\#

mysql> select \* from students1 limit27,6;

\+-------+------------+-----+--------+---------+-----------+

\+-------+------------+-----+--------+---------+-----------+

|   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)

\[root@test 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

\[root@test 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;

\+-------+------------+-----+--------+---------+-----------+

\+-------+------------+-----+--------+---------+-----------+

|    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)

\[root@test 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;

\+-------+------------+-----+--------+---------+-----------+

\+-------+------------+-----+--------+---------+-----------+

|    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 |