mysql 导入 文章_MySQL导入与导出

当我们使用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)