GreatSQL社区

搜索

李努力

load data的部分使用举例

李努力 已有 511 次阅读2022-12-26 18:23 |个人分类:MySQL|系统分类:运维实战| MySQL

1、参数

首先介绍两个参数:secure_file_priv和local_infile

① secure_file_priv:

此变量用于限制数据导入和导出操作,比如load data和select .. into outfile语句和load_file函数。执行改操作的用户必须具有file权限,并且导出的文件前提不能存在。

有如下几个值:

null:禁用导入和导出操作;

具体的目录名:比如/tmp,表示限制mysql只能在/tmp目录中执行导入导出,其他目录不能执行;

没有值:表示不限制mysql,在任意目录的导入导出。

② local_infile:

默认为off。

该变量控制的是load data语句在服务端的local功能,根据该变量设置的值,服务器拒绝或者允许客户端启用local的客户端加载本地数据。

可以在线修改。

2、select ... into outfile使用:

该语句能把表数据导出到一个文本文件中,只能导出到mysql所在的服务器上,但是这种方法只能导出数据,不包括表结构。

比如:

select * from table into outfile '文件名路径' fields terminated by ',' [OPTIONALLY] enclosed by ' " ' escaped by '&' lines terminated by '\r\n';

解释:

fields子句共有三个亚子句:terminated by、[OPTIONALLY] enclosed by和escaped by,如果指定了fields则这三个当中必须至少指定一个 。

① terminated by:用来指定字段值之间的符号,比如例子中指定了都好作为两个字段值之间的标志。

② enclosed by:用来指定包裹字符值的符号,比如例子中使用 ' " '表示文件中所有字段值分别放在双引号之间,若是加上了optionally则表示仅char和varchar的值放在' " '之间。

③ escaped by:用来指定转义字符,比如escaped by '&' 是将&指定为转义字符。

④ lines子句:lines中使用terminated by来指定一行结束的标志,比如例子中

测试:

表结构和表内容:

0

首先

使用select ... into outfile的用户需要具有file权限,否则会报错,并且file权限是针对*.* 的,否则会报错:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

其次

修改secure_file_priv参数,否则报错(修改该参数需要重启实例):

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

① 测试1:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',';

输出文件内容:

0

可以看到各个字段内容之间使用逗号分隔。

② 测试2:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' enclosed by '"';

0

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' optionally enclosed by '"';

0

③ 测试3:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' optionally enclosed by '"' escaped by '&' lines terminated by '**';

则使用**来结尾:

0

默认使用\r\n换行符。

3、load data infile:

load data也需要file权限以及secure_file_priv参数的配置。

load data的子句和select ... into outfile的子句使用一致。

并且如果使用了load data infile的时候,需要load的文件必须位于mysql服务器上,从数据库服务器的本地文件系统获取文件。该文件必须位于数据库目录中或具有全局读取权限。

创建和test表结构一样的表:create table test1 like test;

① 测试1:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',';

的load data如下:

load data infile '/data0/mysql/test.txt'  into table test1 fields terminated by ',';

② 测试2:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' enclosed by '"';

的load data 如下:

load data infile '/data0/mysql/test.txt'  into table test1 fields terminated by ',' enclosed by '"';

因为在select ... into outfile的时候制定了enclosed为 " ,所以所有字段的值都分别使用 " 包裹,但是第一个字段是int型的,所以在load的时候也需要指定enclosed 。

③ 测试3:

escaped by 是指定转义字符。test表中存在一条数据(16,null)。

a、在不加escaped by 的情况下:

默认将null转译为\N

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' optionally enclosed by '"';

0

load的时候则是直接load就可以:

load data infile '/data0/mysql/test.txt'  into table test1 fields terminated by ',' enclosed by '"';

b、在加escaped by的情况下:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' optionally enclosed by '"' escaped by '&';

为NULL值的\变为了&

0

load的时候如果不指定escaped by '&',则会原模原样的导入,也就是导入表中也是&N。

加入escaped by '&的时候load,则将导入的是NULL:

load data infile '/data0/mysql/test.txt'  into table test1 fields terminated by ',' enclosed by '"' escaped by '&';

④ 测试4:

select * from test into outfile '/data0/mysql/test.txt' fields terminated by ',' optionally enclosed by '"' escaped by '&' lines terminated by '**';

以**结尾。

则导入的时候也需要指定lines terminated by '**'。

结论:

load data和select ... into outfile子句要一致。

4、load data local infile:

load data local infile和load data infile使用方法一样,但是load data local infile需要load的文件必须存在于客户端上,该语句是读取客户端文件并发送到mysql服务器。

使用该语句必须开启local_infile参数。


评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 立即注册

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2024-3-29 21:35 , Processed in 0.022448 second(s), 8 queries , Redis On.
返回顶部