【MySQL】老板：让你将数据导入mysql就这么费劲？

---

前言

今天，程序员小李被老板训了一顿，还被扣了1k的工资，原因就是因为有一个项目已经上线，客户这边要求将提供的历史数据批量导入到mysql中，但是小李由于对这么方面有点生疏，都是现百度现实现，导致工作效率低，引发了老板的不满。 小李悲痛欲绝，下决心要搞明白mysql的数据批量导入，经过查阅各种资料学习， 小李对此了解的八九不离十了，从而在老板面前硬了起来……

---

一、mysql 命令导入

老板：我发了一个sql文档，把这个sql文档导入mysql数据库中，10分钟内搞定，搞不定你可以不用来上班了

小李会心一笑，对通过sql文档导入数据到mysq中的命令早已滚瓜烂熟了，于是熟练的操作了起来：

因为数据安全考虑，只能通过跳板机ssh到mysql服务器，才能操作mysql

先将老板发的sql文档上传的跳板机上，然后通过scp将跳板机文档上传到mysql服务器中

再来熟悉下scp命令
语法：

scp [-12346BCpqrv] [-c cipher] [-F ssh_config] [-i identity_file]
[-l limit] [-o ssh_option] [-P port] [-S program]
[[user@]host1:]file1 ... [[user@]host2:]file2

简易写法:

scp [可选参数] file_source file_target 

参数说明：

• -1： 强制scp命令使用协议ssh1
• -2： 强制scp命令使用协议ssh2
• -4： 强制scp命令只使用IPv4寻址
• -6： 强制scp命令只使用IPv6寻址
• -B： 使用批处理模式（传输过程中不询问传输口令或短语）
• -C： 允许压缩。（将-C标志传递给ssh，从而打开压缩功能）
• -p： 保留原文件的修改时间，访问时间和访问权限。
• -q： 不显示传输进度条。
• -r： 递归复制整个目录。
• -v： 详细方式显示输出。scp和ssh(1)会显示出整个过程的调试信息。这些信息用于调试连接，验证和配置问题。
• -c cipher： 以cipher将数据传输进行加密，这个选项将直接传递给ssh。
• -F ssh_config： 指定一个替代的ssh配置文件，此参数直接传递给ssh。
• -i identity_file： 从指定文件中读取传输时使用的密钥文件，此参数直接传递给ssh。
• -l limit： 限定用户所能使用的带宽，以Kbit/s为单位。
• -o ssh_option： 如果习惯于使用ssh_config(5)中的参数传递方式，
• -P port： 注意是大写的P, port是指定数据传输用到的端口号
• -S program： 指定加密传输时所使用的程序。此程序必须能够理解ssh(1)的选项。

在跳板机上执行：

[fuyun@web01 ~]$ scp temp/test_tb.sql fuyun@192.168.99.19:/fuyun/temp
Warning: Permanently added '192.168.99.19' (ECDSA) to the list of known hosts.
fuyun@192.168.99.19's password: 
test_tb.sql                    100%  613   239.2KB/s   00:00  

在mysql服务器对应目录查看是否已经上传成功

[fuyun@192-168-99-19 ~]# ls temp/
test_tb.sql

使用 mysql 命令导入语法格式为：

mysql -u用户名 -p密码 < 要导入的数据库数据(test_tb.sql)

test_tb.sql代码内容：

drop table if exists test.test_tb;
create table if not exists test.test_tb (
user_id   varchar(11) comment '用户id',
user_name varchar(200) comment '用户名称'
) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci;

insert into test.test_tb values ('u00001', '1号用户'),
	                            ('u00002', '2号用户'),
	                            ('u00003', '3号用户'),
	                            ('u00004', '4号用户'),
	                            ('u00005', '5号用户'),
	                            ('u00006', '6号用户')
;

实例：

[fuyun@192-168-99-19 ~]# mysql -u root -h 192.168.99.19 -pfuyunpassword < temp/test_tb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

登录mysql客户端，查看test.test_tb是否存在及是否有数据

mysql> select * from test.test_tb;
+---------+------------+
| user_id | user_name  |
+---------+------------+
| u00001  | 1号用户    |
| u00002  | 2号用户    |
| u00003  | 3号用户    |
| u00004  | 4号用户    |
| u00005  | 5号用户    |
| u00006  | 6号用户    |
+---------+------------+
6 rows in set (0.00 sec)

没问题，搞定，小李想在老板面前秀下自己的技术，再通过另一种方法上实现。于是删了刚导入的表：drop table test.test_tb;，继续一顿操作起来……

二、source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> source /fuyun/temp/test_tb.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

再校验下表数据

mysql> select * from test.test_tb;
+---------+------------+
| user_id | user_name  |
+---------+------------+
| u00001  | 1号用户    |
| u00002  | 2号用户    |
| u00003  | 3号用户    |
| u00004  | 4号用户    |
| u00005  | 5号用户    |
| u00006  | 6号用户    |
+---------+------------+
6 rows in set (0.00 sec)

👌，大功告成。

三、使用 load data 导入数据

导入完数据后……
老板：小李，比以前还是有进步了哈。我刚又发了个test_tb.txt文件，这个文件也是test_tb的历史数据，再把这个test_tb.txt数据追加导入test_tb表中。

小李又熟练的操作起来，先将本地文件导入跳板机服务器上，再通过scp命令将跳板机文件上传到mysql服务器上。

先查看文件情况：

[fuyun@192-168-99-19 temp]# cat test_tb.txt 
"7号用户"|"u00007"
"8号用户"|"\N"
"9号用户"|"u00009"

呵呵，文件分隔符竟然是|，字段顺序竟然也不对，字段值还被"包围。小李又会心一笑，心里暗自说着：就这也能难住我？

mysql官方手册

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

说明：

• LOW_PRIORITY：使用LOW_PRIORITY修饰符，LOAD DATA语句的执行会被延迟，直到没有其他客户端从表中读取数据。这只影响仅使用表级锁的存储引擎(如MyISAM、MEMORY和MERGE)
• CONCURRENT：使用CONCURRENT修饰符和满足并发插入条件的MyISAM表(也就是说，它中间不包含空闲块)，其他线程可以在执行LOAD data时从表中检索数据。即使没有其他线程同时使用这个表，这个修饰符也会略微影响LOAD DATA的性能
• LOCAL：如果 load data 使用时指定了 local 关键字，则表示文件放在客户端主机上，从客户端读取文本文件；如果没指定，则表示从服务器主机读取文本文件
• REPLACE：如果指定 replace ，与唯一键重复的行将被覆盖更新。对于任意记录覆盖更新时，如果唯一键外的各个字段其实都没有变化，那么执行操作时受影响行数为1；如果除唯一键外的任意字段有变化，那么执行操作时受影响行数为2
• IGNORE：如果指定 ignore ，与唯一键重复的行将被忽略，默认指定 ignore
• PARTITION：将数据插入指定分区
• CHARACTER SET：若不指定字符集，MySQL默认使用character_set_database变量指定的字符集去读取文件，若文件字符集不同，则应指定该关键字
• FIELDS TERMINATED BY：字段值的分隔符，若不指定则默认为 ‘\t’
• OPTIONALLY ENCLOSED BY：字段值的包含字符，若不指定则默认为 ‘’
• ESCAPED BY：字段值的转义字符，若不指定则默认为’\’
• LINES TERMINATED BY：指定行分隔符，若不指定则默认为为系统的默认行分隔符（‘\r\n‘ on windows，’\n’ on linux）
• LINES STARTING BY：若指定该值为xxx，则MySQL会自动去掉xxx及其前面的字符，若某行不包含xxx，则改行将被忽略，若不指定默认为’’，比如执行以下语句：

mysql> load data local infile '/fuyun/temp/test.txt' into table test  
   -> fields  terminated  by ','  lines starting by 'xxx';  
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

对应的文件内容：

[fuyun@192-168-99-19 temp]# cat test.txt
  xxx"abc",1  
  something xxx"def",2  
  "ghi",3 

查看mysql表中数据

mysql> select * from test;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| "abc"   | 1         |
| "def"   | 2         |
+---------+-----------+
2 rows in set (0.00 sec)

• IGNORE LINES | ROWS：忽略文件开头的指定行，比如指定为2，那么MySQL只会解析并插入第三行及后面的数据

事例：

mysql> load data local infile '/fuyun/temp/test_tb.txt' into table test.test_tb 
    -> character set utf8 
    -> fields terminated by '|' 
    -> optionally enclosed by '"' 
    -> escaped by '\\' 
    -> lines terminated by '\n' 
    -> (user_name, user_id)
    -> ;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

查看数据是否正确

mysql> select * from test_tb;
+---------+------------+
| user_id | user_name  |
+---------+------------+
| u00001  | 1号用户    |
| u00002  | 2号用户    |
| u00003  | 3号用户    |
| u00004  | 4号用户    |
| u00005  | 5号用户    |
| u00006  | 6号用户    |
| u00007  | 7号用户    |
| NULL    | 8号用户    |
| u00009  | 9号用户    |
+---------+------------+
9 rows in set (0.00 sec)

四、使用 mysqlimport 导入数据

小李觉得只通过一种方法导入显示不出技术的熟练度，于是又开始使用mysqlimport方法导入文件数据到mysql表中

先删除表重新加载sql文件

source /fuyun/temp/test_tb.sql;

再执行mysqlimport命令

[fuyun@192-168-99-19 temp]# mysqlimport -u root -pfuyunpassword --local --columns=user_name,user_id \
>    --fields-terminated-by="|" \
>    --fields-optionally-enclosed-by='"' \
>    --fields-escaped-by='\\' \
>    --lines-terminated-by="\n"  test /fuyun/temp/test_tb.txt
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
test.test_tb: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

⚠️注意：mysqlimport命令只能指定database，表名是根据文件名获取，所以文件名必须和表名一致。

[fuyun@192-168-99-19 temp]# mysqlimport -u root -pfuyunpassword --local --columns=user_name,user_id \
> --fields-terminated-by="|" \
> --fields-optionally-enclosed-by='"' \
> --fields-escaped-by='\\' \
> --lines-terminated-by="\n"  test /fuyun/temp/tb.txt
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysqlimport: Error: 1146, Table 'test.tb' doesn't exist, when using table: tb

检查数据是否正确

mysql> select * from test_tb;
+---------+------------+
| user_id | user_name  |
+---------+------------+
| u00001  | 1号用户    |
| u00002  | 2号用户    |
| u00003  | 3号用户    |
| u00004  | 4号用户    |
| u00005  | 5号用户    |
| u00006  | 6号用户    |
| u00007  | 7号用户    |
| NULL    | 8号用户    |
| u00009  | 9号用户    |
+---------+------------+
9 rows in set (0.00 sec)

老板：看来你对mysql数据导入的熟练度还是可以的，今天就先到这吧。

小李：幸亏提前掌握了mysql的数据导入，不然又得加班了。