MySQL导入之mysqlimport

1. mysqlimport参数
mysqlimport客户端实际上就是“LOAD DATA”命令的一个包装实现，所以大部分参数选项与LOAD DATA相同。

文件名与导入表相同，例如patient.txt， patient.text和 patient都将被导入到指定表patient中。

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]
1
mysqlimport的具体参数如下：

[root@chengyu ~]# mysqlimport --help
mysqlimport Ver 8.0.20 for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2020, 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.

Loads tables from text files in various formats. The base name of the
text file must be the name of the table that should be used.
If one uses sockets to connect to the MySQL server, the server will open and
read the text file directly. In other cases the client will open the text
file. The SQL command 'LOAD DATA INFILE' is used to import the rows.

Usage: mysqlimport [OPTIONS] database textfile...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql8/etc/my.cnf /usr/local/mysql8/my.cnf ~/.my.cnf
The following groups are read: mysqlimport client
The following options may be given as the first argument:
--print-defaults 打印默认选项.
--no-defaults 除了登录文件，不读取其他选项文件中的任意默认参数.
--defaults-file=# 仅读取指定文件中的默认参数.
--defaults-extra-file=# global选项文件之后读取的指定选项文件.
--defaults-group-suffix=# Also read groups with concat(group, suffix)
--login-path=# 从.mylogin.cnf中读取登录路径选项.
--bind-address= 在具有多个网络接口的计算机上，使用此选项选择用于连接到MySQL服务器的接口.
--character-sets-dir=name 字符集的安装目录.
--default-character-set=name 指定字符集.
-c, --columns=name 指定列名，以逗号分隔.
-C, --compress Use compression in server/client protocol.
-#, --debug[=#] 编写调试日志.
--debug-check 程序退出时打印调试信息.
--debug-info 程序退出时打印调试信息，内存和CPU统计信息.
--default-auth=name Default authentication client-side plugin to use.
-d, --delete 导入前清空表数据.
--enable-cleartext-plugin
Enable/disable the clear text authentication plugin.
--fields-terminated-by=name 字段分隔符
--fields-enclosed-by=name 字段包括符
--fields-optionally-enclosed-by=name 列可选包括符
--fields-escaped-by=name 列终止符
-f, --force 忽略SQL error强制执行
-?, --help 帮助信息展示
-h, --host=name 将数据导入给定主机上的MySQL服务器，默认主机为localhost.
-i, --ignore 数据重复导入时，保存旧的.
--ignore-lines=# 忽略导入文件前n行.
--lines-terminated-by=name 行终止符
-L, --local 读取客户端本地数据文件.
-l, --lock-tables 锁表以写入
--low-priority Use LOW_PRIORITY when updating the table.
-p, --password[=name] 用户密码
--plugin-dir=name 客户端plugins路径.
-P, --port=# 连接端口.
--protocol=name protocol名(tcp, socket, pipe, memory).
-r, --replace 数据重复时，替换旧的.
-s, --silent 静默模式，仅在发生错误时才产生输出.
-S, --socket=name 指定socket.
--server-public-key-path=name 包含RSA公钥的文件的路径名.
--ssl-mode=name 与服务器连接的所需SSL
--ssl-ca=name 包含受信任的SSL证书颁发机构列表的文件
--ssl-capath=name 包含受信任的SSL证书颁发机构证书文件的目录
--ssl-cert=name 包含X.509证书的文件
--ssl-cipher=name 连接加密的允许密码
--ssl-key=name 包含X.509密钥的文件
--ssl-crl=name 包含证书吊销列表的文件
--ssl-crlpath=name 包含证书吊销列表文件的目录
--tls-version=name 允许的TLS协议进行加密连接，允许的值有: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3
--ssl-fips-mode=name 是否在客户端启用FIPS模式，允许的值有: OFF, ON, STRICT
--tls-ciphersuites=name 允许的TLSv1.3密码套件用于加密连接
--use-threads=# 多线程执行.
-u, --user=name 用户名.
-v, --verbose 详细信息
-V, --version 显示版本信息并退出
--compression-algorithms=name 用于服务器连接的允许压缩算法
--zstd-compression-level=# 与使用zstd压缩的服务器的连接的压缩级别，有效区间1-22，默认值3.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
bind-address (No default value)
character-sets-dir (No default value)
default-character-set auto
columns (No default value)
compress FALSE
default-auth (No default value)
delete FALSE
enable-cleartext-plugin FALSE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
force FALSE
host (No default value)
ignore FALSE
ignore-lines 0
lines-terminated-by (No default value)
local FALSE
lock-tables FALSE
low-priority FALSE
plugin-dir (No default value)
port 0
replace FALSE
silent FALSE
socket (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
tls-ciphersuites (No default value)
use-threads 0
user (No default value)
verbose FALSE
compression-algorithms (No default value)
zstd-compression-level 3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
2. mysqlimport导入简单示例
2.1 t1.txt导入到t1表
[root@chengyu ~]# vim /home/t1.txt
chn
sdsa
kjo
hjh
iods
[root@chengyu ~]# mysqlimport -uroot -p dbcy --columns=tname /home/t1.txt
Enter password:
mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: t1
[root@chengyu ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16
Server version: 8.0.20 Source distribution

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.

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | NULL |
+------------------+-------+
1 row in set (0.04 sec)
# secure_file_priv 参数是只读参数，不能使用set global命令修改
mysql> set global secure_file_priv='/home';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
去my.cnf文件修改一下secure_file_priv参数：

[root@chengyu ~]# vim /usr/local/mysql8/my.cnf
secure_file_priv='/home'
[root@chengyu ~]# systemctl restart mysqld8
[root@chengyu ~]# systemctl status mysqld8
● mysqld8.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld8; bad; vendor preset: disabled)
Active: active (running) since Mon 2020-06-29 10:36:11 CST; 8s ago
Docs: man:systemd-sysv-generator(8)
Process: 29479 ExecStop=/etc/rc.d/init.d/mysqld8 stop (code=exited, status=0/SUCCESS)
Process: 29508 ExecStart=/etc/rc.d/init.d/mysqld8 start (code=exited, status=0/SUCCESS)
Tasks: 40
CGroup: /system.slice/mysqld8.service
├─29521 /bin/sh /usr/local/mysql8/bin/mysqld_safe --datadir=/home/mysql8/data --pid-file=...
└─29713 /usr/local/mysql8/bin/mysqld --basedir=/usr/local/mysql8 --datadir=/home/mysql8/d...

Jun 29 10:36:08 chengyu systemd[1]: Starting LSB: start and stop MySQL...
Jun 29 10:36:11 chengyu mysqld8[29508]: Starting MySQL... SUCCESS!
Jun 29 10:36:11 chengyu systemd[1]: Started LSB: start and stop MySQL.
[root@chengyu ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.20 Source distribution

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.

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| secure_file_priv | /home/ |
+------------------+--------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
再次执行导入：

[root@chengyu ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 10
Server version: 8.0.20 Source distribution

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.

mysql> use dbcy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+-----+-----------+
| tid | tname |
+-----+-----------+
| 1 | 钱钟书 |
| 2 | 杨绛 |
| 3 | 路遥 |
| 6 | 鲁迅 |
| 8 | 三毛 |
+-----+-----------+
5 rows in set (0.01 sec)
[root@chengyu ~]# mysqlimport -uroot -p dbcy --columns=tname /home/t1.txt
Enter password:
dbcy.t1: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
[root@chengyu ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 15
Server version: 8.0.20 Source distribution

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.

mysql> use dbcy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+-----+-----------+
| tid | tname |
+-----+-----------+
| 1 | 钱钟书 |
| 2 | 杨绛 |
| 3 | 路遥 |
| 6 | 鲁迅 |
| 8 | 三毛 |
| 9 | chn |
| 10 | sdsa |
| 11 | kjo |
| 12 | hjh |
| 13 | iods |
+-----+-----------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
2.2 mysqlimport导入包含自增序列的数据
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from t1;
+-----+-----------+
| tid | tname |
+-----+-----------+
| 1 | 钱钟书 |
| 2 | 杨绛 |
| 3 | 路遥 |
| 6 | 鲁迅 |
| 8 | 三毛 |
| 9 | chn |
| 10 | sdsa |
| 11 | kjo |
| 12 | hjh |
| 13 | iods |
+-----+-----------+
10 rows in set (0.00 sec)

[root@chengyu ~]# vim /home/t1.txt
14 钟一
15 赵二
16 张三
17 李四
18 王五
[root@chengyu ~]# mysqlimport -uroot -p dbcy --columns=tid,tname /home/t1.txt
Enter password:
mysqlimport: Error: 1265, Data truncated for column 'tid' at row 1, when using table: t1
Bye
[root@chengyu ~]# vim /home/t1.txt
,钟一
,赵二
,张三
,李四
,王五
[root@chengyu ~]# mysqlimport -uroot -p --fields-terminated-by="," dbcy --columns=tid,tname /home/t1.txt
Enter password:
mysqlimport: Error: 1366, Incorrect integer value: ' ' for column 'tid' at row 1, when using table: t1
[root@chengyu ~]# vim /home/t1.txt
0,钟一
0,赵二
0,张三
0,李四
0,王五
[root@chengyu ~]# mysqlimport -uroot -p --fields-terminated-by="," dbcy --columns=tid,tname /home/t1.txt
Enter password:
dbcy.t1: Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t1;
+-----+--------+
| tid | tname |
+-----+--------+
| 16 | 钟一 |
| 17 | 赵二 |
| 18 | 张三 |
| 19 | 李四 |
| 20 | 王五 |
+-----+--------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
注意：这里已有的t1表的数据全部被delete后才导入文本文件的新数据，在STRICT_TRANS_TABLES的SQL_MODE下，指定自增长列为0的作用是生成下一序列号，并不是赋值操作。

再试试空置自增列或赋“null”值自增列的情况：

[root@chengyu ~]# vim /home/t1.txt
null,钟一
null,赵二
null,张三
null,李四
null,王五
[root@chengyu ~]# mysqlimport -uroot -p --fields-terminated-by="," dbcy --columns=tid,tname /home/t1.txt
Enter password:
mysqlimport: Error: 1366, Incorrect integer value: 'null' for column 'tid' at row 1, when using table: t1
[root@chengyu ~]# vim /home/t1.txt
,钟一
,赵二
,张三
,李四
,王五
[root@chengyu ~]# mysqlimport -uroot -p --fields-terminated-by="," dbcy --columns=tid,tname /home/t1.txt
Enter password:
mysqlimport: Error: 1366, Incorrect integer value: '' for column 'tid' at row 1, when using table: t1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
总言之，包含自增列的数据导入请考量SQL_MODE的情况，另外导入之前做好已有数据的备份和测试工作，方法千千万，第一条安全备份第一步，小心谨慎！

其他情况，可以使用–replace或者–delete在导入文本文件数据时处理表已有数据，还有分隔符之类的指定跟LOAD DATA一致，这里就不赘述了，可以看看另一篇https://blog.csdn.net/u010257584/article/details/106895618。

————————————————
版权声明：本文为CSDN博主「山与先生」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u010257584/article/details/106995361/