前言
今天,程序员小李被老板训了一顿,还被扣了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"
呵呵,文件分隔符竟然是|
,字段顺序竟然也不对,字段值还被"
包围。小李又会心一笑,心里暗自说着:就这也能难住我?
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的数据导入,不然又得加班了。