mysql - etl/load data file/select into ... outfile/mysqldump

目录

1.load data infile

2.select into…outfile

3.mysqldump

---

1.load data infile

官方文档:https://dev.mysql.com/doc/refman/5.6/en/load-data.html

中文文档:https://www.docs4dev.com/docs/zh/mysql/5.7/reference/load-data.html#load-data%E8%AF%AD%E6%B3%95

参考文档:https://blog.csdn.net/jichl/article/details/15504113

 

(1)load data infile语法

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}] ...]

 

fields terminated by ','         指定逗号为字段分隔符

optionally enclosed by '"'     指定双引号为字段引用符

escaped by  '\\'                指定反斜杠为转义符

lines terminated by  '\n’      指定\n为每行数据终止符

样例:load data local infile '/home/test.csv' into table test.table character set utf8  fields terminated by ',' optionally enclosed by '\"'  escaped by '\\' lines terminated by '\n’ ;

如果需要跳过表头，则在最后加上 ignore 1 lines

 

(2)load data 案例

load data local infile '/home/GET_20190911.txt'

into table ods.amz_inventory_event CHARACTER SET utf8mb4

FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES

(

@snapshot_date

,@transaction_type

,@fnsku

,@sku

,@product_name

,@fulfillment_center_id

,@quantity

,@disposition

)

set

snapshot_date =@snapshot_date

,transaction_type =@transaction_type

,fnsku =@fnsku

,sku =trim(replace(replace(replace(replace(@sku,'"',''),'&','&'),'­','-'),'"',''))

,product_name =@product_name

,fulfillment_center_id =@fulfillment_center_id

,quantity =if(@quantity='',null,@quantity)

,disposition =@disposition

,start_date='20190911'

,end_date='20190911'

,uds_load_date=curdate()

;

 

2.select into…outfile

(1)select into…outfile语法

select into…outfile把表数据导出到一个文本中，可用load data语句重导入数据，这种方法只能导出或导入数据，不包括表结构。

SELECT ... INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

[export_options]

export_options:

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

FIELDS有三个子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS则三个必指定一个。

(1)TERMINATED BY指定字段之间分隔符号

(2)ENCLOSED BY指定包裹字段值的符号，如“ENCLOSED BY ' " '”表示值放在双引号之间，若加上OPTIONALLY表示所有的值都放在双引号之间。

(3)ESCAPED BY子句用来指定转义字符，例如“ESCAPED BY '*'”将“*”指定为转义字符，取代“\”，如空格将表示为“*N”。

(4)LINES子句：使用TERMINATED BY指定一行结束的标志，如“LINES TERMINATED BY '?'”表示一行以“?”作为结束标志。

 

(2)select into…outfile例子

select * from table into outfile '/var/lib/mysql-files/mws_fee_preview.txt'

fields terminated by ',' enclosed by '"' lines terminated by '\n';

 

select * from test.table into outfile '/var/lib/mysql-files/mws_fee_preview.txt' fields

terminated by '\t' optionally enclosed by '\"' escaped by '\\' lines terminated by '\n';

 

-- 带上表头

select * into outfile 'e:/555.xlsx' fields terminated by '\\t' OPTIONALLY ENCLOSED BY '"'

lines terminated by '\\n' from (select '姓名','性别','年龄' union select username,sex,age from table) ;

 

 

3.mysqldump

(1)mysqldump导出

1)导出表结构及数据

mysqldump -uroot -p --set-gtid-purged=OFF database table1 table2 > mysqldump.sql

2)只导出表结构

mysqldump -uroot -p --set-gtid-purged=OFF -d database table1 table2 > mysqldump.sql

3)只导出数据

mysqldump -uroot -p --set-gtid-purged=OFF -t database table1 table2 > mysqldump.sql

4)导出单张表

[1]导出表结构及数据（有where条件）

mysqldump -uroot -p --set-gtid-purged=OFF database table --where "限制条件" > mysqldump.sql

导出mysq.user表中 user='zejin'的数据和表结构

mysqldump -uroot -p --set-gtid-purged=OFF mysql user --where="user='zejin'" > zejin.sql;

[2]只导出数据（有where条件）

mysqldump -uroot -p --set-gtid-purged=OFF --no-create-info database table --where "限制条件" > mysqldump.sql

参数说明：

d参数：等价于-–no-data，含义是不导出任何数据，只导出数据库表结构；

t参数：等价于—no-create-info，含义是只导出数据，而不添加CREATE TABLE语句；

 

5)导出所有数据库

mysqldump -uroot -p --all-databases > mysqldump.sql

6)导出单个数据库

mysqldump -uroot -p --databases db1 > mysqldump.sql

7)导出多个数据库

mysqldump -uroot -p --databases db1 db2 > mysqldump.sql

 

(2)mysqldump导入

1)导入表

mysql -uroot -p database < mysqldump.sql

2)导入数据库

mysql -uroot -p < mysqldump.sql

 

(3)mysqldump常用参数

1)--all-databases , -A

含义：导出全部数据库。

示例：mysqldump -uroot -p --all-databases

2)--all-tablespaces , -Y

含义：导出全部表空间。

示例：mysqldump -uroot -p --all-databases --all-tablespaces

3)--no-tablespaces , -y

含义：不导出任何表空间信息。

示例：mysqldump -uroot -p --all-databases --no-tablespaces

4)--add-drop-database

含义：每个数据库创建之前添加drop数据库语句。

示例：mysqldump -uroot -p --all-databases --add-drop-database

5)--add-drop-table

含义：每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)

示例：mysqldump -uroot -p --all-databases (默认添加drop语句)

mysqldump -uroot -p --all-databases –skip-add-drop-table (取消drop语句)

6)--add-locks

含义：在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)

示例：mysqldump -uroot -p --all-databases (默认添加LOCK语句)

mysqldump -uroot -p --all-databases –skip-add-locks (取消LOCK语句)

7)--create-options, -a

含义：在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

示例：mysqldump -uroot -p --all-databases

8)--databases, -B

含义：导出几个数据库。参数后面所有名字参量都被看作数据库名。

示例：mysqldump -uroot -p --databases test mysql

9)--default-character-set

含义：设置默认字符集，默认值为utf8

示例：mysqldump -uroot -p --all-databases --default-character-set=utf8

10)--events, -E

含义：导出事件。

示例：mysqldump -uroot -p --all-databases --events

11)--flush-logs

含义：开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。

示例：mysqldump -uroot -p --all-databases --flush-logs

12)--flush-privileges

含义：在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。

示例：mysqldump -uroot -p --all-databases --flush-privileges

13)--help

含义：显示帮助信息并退出。

示例：mysqldump --help

14)--host, -h

含义：需要导出的主机信息

示例：mysqldump -uroot -p --host=localhost --all-databases

15)--ignore-table

含义：不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……

示例：mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user

16)--lock-all-tables, -x

含义：提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。

示例：mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables

17)--no-create-db, -n

含义：只导出数据，而不添加CREATE DATABASE 语句。

示例：mysqldump -uroot -p --host=localhost --all-databases --no-create-db

18)--no-create-info, -t

含义：只导出数据，而不添加CREATE TABLE 语句。

示例：mysqldump -uroot -p --host=localhost --all-databases --no-create-info

19)--no-data, -d

含义：不导出任何数据，只导出数据库表结构。

示例：mysqldump -uroot -p --host=localhost --all-databases --no-data

20)--password, -p

含义：连接数据库密码

21)--port, -P

含义：连接数据库端口号

22)--routines, -R

含义：导出存储过程以及自定义函数。

示例：mysqldump -uroot -p --host=localhost --all-databases --routines

23)--tables

含义：覆盖--databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。

示例：mysqldump -uroot -p --host=localhost --databases test --tables test

24)--triggers

含义：导出触发器。该选项默认启用，用--skip-triggers禁用它。

示例：mysqldump -uroot -p --host=localhost --all-databases --triggers

25)--user, -u

含义：指定连接的用户名。

26)--version, -V

含义：输出mysqldump版本信息并退出

27)--where, -w

含义：只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。

示例：mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”

28)--xml, -X

含义：导出XML格式.

示例：mysqldump -uroot -p --host=localhost --all-databases --xml

 

(4)mysqldump案例

--ignore-table 忽略表不到出 举例 ----ignore-table=dataname.table1 --ignore-table=dataname.table2

-d 结构(--no-data:不导出任何数据，只导出数据库表结构)

-t 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)

-n (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）

-R (--routines:导出存储过程以及自定义函数)

-E (--events:导出事件)

--triggers (默认导出触发器，使用--skip-triggers屏蔽导出)

-B (--databases:导出数据库列表，单个库时可省略）

--tables 表列表（单个表时可省略）

 

(1)常用导出

1.存储过程&函数操作

只导出存储过程和函数(不导出结构和数据，要同时导出结构的话，需要同时使用-d)

mysqldump -R -ndt dbname1 -u root -p > xxx.sql

 

2.事件操作

只导出事件.

mysqldump -E -ndt dbname1 -u root -p > xxx.sql

 

3.触发器操作

不导出触发器（触发器是默认导出的–triggers，使用–skip-triggers屏蔽导出触发器）

mysqldump --skip-triggers dbname1 -u root -p > xxx.sql

 

4.导出一个表结构

mysqldump -d dbname1 tablename1 -u root -p > xxx.sql

导出多个表结构

mysqldump -d -B dbname1 --tables tablename1 tablename2 -u root -p > xxx.sql

 

5.导出一个表数据

mysqldump -t dbname1 tablename1 -u root -p > xxx.sql

导出多个表数据

mysqldump -t -B dbname1 --tables tablename1 tablename2 -u root -p > xxx.sql

 

6.导出一个表结构以及数据

mysqldump dbname1 tablename1 -u root -p > xxx.sql

导出多个表结构以及数据

mysqldump -B dbname1 --tables tablename1 tablename2 -u root -p > xxx.sql

 

7.导出一个库结构

mysqldump -d dbname -u root -p > xxx.sql

导出多个库结构

mysqldump -d -B dbname1 dbname2 -u root -p > xxx.sql

 

8.导出一个库数据

mysqldump -t dbname -u root -p > xxx.sql

导出多个库数据

mysqldump -t -B dbname1 dbname2 -u root -p > xxx.sql

 

9.导出一个库结构以及数据

mysqldump dbname1 -u root -p > xxx.sql

导出多个库结构以及数据

mysqldump -B dbname1 dbname2 -u root -p > xxx.sql

 

(2)导入导出压缩文件

1)语法

压缩并导出数据库命令：

mysqldump -h主机IP -u数据库用户名 -p数据库密码 数据库名称 | gzip > /导出目录/test.sql.gz

mysql导入压缩文件:

gzip -d < /导出目录/test.sql.gz | mysql -u用户名 -p密码 要导入的库名

 

2)例子

[1]导出至zip

mysqldump -h192.168.0.3 -P3306 -uroot -ppwd123 --single-transaction --default-character-set=utf8 -R -E zabbix --log-error=zabbix2018030709.log |gzip > zabbix2018030709.sql.gz

 

[2]导入zip

gunzip < zabbix2018030709.sql.gz |mysql -uroot -ppwd123 zabbix