MySQL导入与导出
程序员文章站
2022-04-29 17:13:25
当我们使用mysql数据库时，经常会遇到数据的导入导出，在这里，我简单的介绍几个常用的方法，供大家方便使用。1、利用LOAD DATA INFILE 导入数据1.1 LOAD DATA 语法命令 LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_nme' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (partition_name,...)] [...
当我们使用mysql数据库时，经常会遇到数据的导入导出，在这里，我简单的介绍几个常用的方法，供大家方便使用。
1、利用LOAD DATA INFILE 导入数据
1.1 LOAD DATA 语法命令
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_nme'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTINGG BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
1.2 LOAD DATA 参数说明
LOW_PRIORITY
当没有回话读表时，才会加载数据。
CONCURRENT
现在就加载数据。
LOCAL
从客户主机读文件，如果没有指定，则文件必须放在服务器上。
REPLACE | IGNORE
REPLACE:如果有唯一索引的行，新行替换旧行。
IGNORE:跳过有唯一索引的行，避免数据重复插入
如果不指定，就会报错。除非没有唯一索引，就会重复插入。
FIELDS |COLUMNS
FIELDS:指定文件的分隔的格式。
COLUMNS:指定哪些列。
TERMINATED BY 'string'
每个字段以什么作为分隔符，默认为','。
LINES
指定了每条记录的分隔符默认'\n'即为换行符。
STARTINGG BY 'string'
以什么开头
TERMINATED BY 'string
以什么结尾，换行。
1.3 导入数据
测试环境准备
a) 准备好一个TXT文本文件，保存于/var/lib/mysql/example1.TXT,内容如下:
001,宋蔚然,0,32
002,李赞,1,11
003,李师师,0,26
004,周志国,1,28
005,金婷,0,20
006,张静,0,22
b) 在 my.cnf 文件中加一个参数,需要重启 mysql： secure-file-priv=/var/lib/mysql
c) 在mysql数据库中，创建一张表，字段与文件example1保持一致：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table ld_test1(
-> id int not null,
-> username varchar(8) not null,
-> gender tinyint not null,
-> age smallint not null
-> );
Query OK, 0 rows affected (0.02 sec)
d) 导入数据
mysql> load data infile '/var/lib/mysql/example1.TXT' into table ld_test1 fields terminated by ',';
Query OK, 6 rows affected (0.01 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from id_test1;
+----+-----------+--------+-----+
| id | username | gender | age |
+----+-----------+--------+-----+
| 1 | 宋蔚然 | 0 | 32 |
| 2 | 李赞 | 1 | 11 |
| 3 | 李师师 | 0 | 26 |
| 4 | 周志国 | 1 | 28 |
| 5 | 金婷 | 0 | 20 |
| 6 | 张静 | 0 | 22 |
+----+-----------+--------+-----+
6 rows in set (0.00 sec)
1.4 LOAD DATA 数据加载时增加一个时间列
a) 增加一个时间列
mysql> alter table ld_test1 add update_time timestamp not null;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
b) 清空数据
mysql> truncAte table ld_test1;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from ld_test1;
Empty set (0.00 sec)
mysql> desc ld_test1;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | | NULL | |
| username | varchar(8) | NO | | NULL | |
| gender | tinyint(4) | NO | | NULL | |
| age | smallint(6) | NO | | NULL | |
| update_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> select * from ld_test1;
Empty set (0.00 sec)
c) 重新导入数据
mysql> load data infile '/var/lib/mysql/example1.TXT' ignore into table ld_test1 fields terminated by ',' lines terminated by '\n' set update_time=current_timestamp;
Query OK, 6 rows affected, 6 warnings (0.01 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 6
mysql> select * from ld_test1;
+----+-----------+--------+-----+---------------------+
| id | username | gender | age | update_time |
+----+-----------+--------+-----+---------------------+
| 1 | 宋蔚然 | 0 | 32 | 2020-07-12 15:36:10 |
| 2 | 李赞 | 1 | 11 | 2020-07-12 15:36:10 |
| 3 | 李师师 | 0 | 26 | 2020-07-12 15:36:10 |
| 4 | 周志国 | 1 | 28 | 2020-07-12 15:36:10 |
| 5 | 金婷 | 0 | 20 | 2020-07-12 15:36:10 |
| 6 | 张静 | 0 | 22 | 2020-07-12 15:36:10 |
+----+-----------+--------+-----+---------------------+
6 rows in set (0.00 sec)
2、SQL语句导出数据
2.1 SQL语句导出数据语法命令
select ...
[FROM table_references
WHERE/GROUP BY/ORDER BY/LIMIT
[INTO OUTFILE 'file_name'
[CHARACTER SET chrset_name]
[{FIELDS | COLUMNS }]
[TERMINATED by 'string']
[[OPTIONALLY] enclosed by 'char']
[ESCAPED by 'char']
]
[LINES
[STARTING by 'string']
[TERMINATED by 'string']
]
| INTO DUMPFILE 'file_name'
| INTO var_nmae [, var_nmae]]
2.2 导出数据
mysql> select * into outfile '/var/lib/mysql/example1.TXT' from ld_test1;
Query OK, 6 rows affected (0.01 sec)
2.3 查看文件
[root@ligang scripts]# cat /var/lib/mysql/example1.TXT
1 宋蔚然 0 32 2020-07-12 15:36:10
2 李赞 1 11 2020-07-12 15:36:10
3 李师师 0 26 2020-07-12 15:36:10
4 周志国 1 28 2020-07-12 15:36:10
5 金婷 0 20 2020-07-12 15:36:10
6 张静 0 22 2020-07-12 15:36:10
3、利用CSV存储引擎加载数据
3.1 准备好一个TXT文本文件，保存于/var/lib/mysql/example1.TXT,内容如下:
001,宋蔚然,0,32
002,李赞,1,11
003,李师师,0,26
004,周志国,1,28
005,金婷,0,20
006,张静,0,22
3.2 在mysql数据库中，创建一张表，存储引擎为CSV,字段与文件example1.TXT保持一致：
mysql> create table ld_csv1(
-> id int not null,
-> username varchar(8) not null,
-> gender tinyint not null,
-> age smallint not null
-> )engine=csv default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
3.3 进入到ld_csv1对应的文件夹,即可找到对应的ld_csv1.CSV数据文件。
[root@test test]# cd /mysql/data/3306/data/test
[root@test test]# ll
total 128
-rw-rw----. 1 mysql mysql 61 Jul 12 15:00 db.opt
-rw-rw----. 1 mysql mysql 8700 Jul 12 15:28 id_test1.frm
-rw-rw----. 1 mysql mysql 98304 Jul 12 15:36 id_test1.ibd
-rw-rw----. 1 mysql mysql 35 Jul 12 16:37 ld_csv1.CSM
-rw-rw----. 1 mysql mysql 0 Jul 12 16:37 ld_csv1.CSV
-rw-rw----. 1 mysql mysql 8656 Jul 12 16:37 ld_csv1.frm
此时，ld_csv1.CSV文件是空的。
3.4 粘贴数据到ld_csv1.CSV数据文件。
[root@test test]# cat /var/lib/mysql/example1.TXT > /mysql/data/3306/data/test/ld_csv1.CSV
3.5 查询数据。
mysql> select * from ld_csv1;
+----+-----------+--------+-----+
| id | username | gender | age |
+----+-----------+--------+-----+
| 1 | 宋蔚然 | 0 | 32 |
| 2 | 李赞 | 1 | 11 |
| 3 | 李师师 | 0 | 26 |
| 4 | 周志国 | 1 | 28 |
| 5 | 金婷 | 0 | 20 |
| 6 | 张静 | 0 | 22 |
+----+-----------+--------+-----+
6 rows in set (0.00 sec)
本文地址：https://blog.csdn.net/xiaolichaoren/article/details/107301748