mysql -- 保存查询结果和加载数据

_泡泡 / 2023-05-03 / 原文

可以使用select into outfile语句将输出保存到文件中。可以指定列和行分割符,然后可以将数据导入其他数据平台。

保存查询结果

可以将输出目标另存为文件或表。

1.另存为文件

要将输出结果保存到文件中，需要拥有file权限。 FILE是一个全局特权，这意味着你不能将其限制为针对特定数据库的权限。但是，你可以限制用户查询的内容:

> create user if not exists 'mary'@'%' identified with mysql_native_password by 'Com.123456';
> grant select on employees.* to 'mary'@'%';
> grant file on *.* to 'mary'@'%';

在Ubuntu系统中，默认情况下，MySQL不允许写入文件。应该在配置文件中设置secure_file_priv 并重新启动MySQL;在CentOS. Red Hat系统中，secure_ file_ priv被设置为/var/lib/mysql-files,意味着所有文件都将被保存在该目录中。

> show variables like '%secure%';

# 如需修改配置文件，可以像这样设置,需重启MySQL服务器
$ secure_file_priv = /var/lib/mysql

在CentOS上可以执行以下语句保存结果,保存文件为.csv格式

# SELECT INTO…OUTFILE语法：
# select * from Table into outfile '/路径/文件名'
# fields terminated by ','
# enclosed by '"'
# lines terminated by '\r\n'

# ●  在FIELDS子句中有三个亚子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。如果指定了FIELDS子句，则这三个亚子句中至少要指定一个。

# （1）TERMINATED BY用来指定字段值之间的符号，例如，“TERMINATED BY ','”指定了逗号作为两个字段值之间的标志。

# （2）ENCLOSED BY子句用来指定包裹文件中字符值的符号，例如，“ENCLOSED BY ' " '”表示文件中字符值放在双引号之间，若加上关键字OPTIONALLY表示所有的值都放在双引号之间。

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

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

#/var/lib/mysql-files/result.csv路径为secure_file_priv的值
> select first_name,last_name into outfile '/var/lib/mysql-files/result.csv' \
->  fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' \
-> from employees.employees where hire_date < '1986-01-01' limit 10;

# 查看保存结果
$ cat /var/lib/mysql/employees/result.csv
"Bezalel","Simmel"
"Sumant","Peac"
"Eberhardt","Terkki"
"Otmar","Herbst"
"Florian","Syrotiuk"
"Tse","Herber"
"Udi","Jansch"
"Reuven","Garigliano"
"Erez","Ritzmann"
"Premal","Baek"

2.另存为表

也可以将select语句的结果保存到表中。即使表不存在，也可以使用CREATE和SELECT来创建表并加载数据。如果表已经存在，则可以使用INSERT和SELECT加载数据。

# 查看titles表中的数据 distinct [dɪˈstɪŋkt] 不同的
>  select distinct title from employees.titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+


# 可以将上面的结果保存为一个新的表titles_only中
> create table employees.titles_only as select distinct title from employees.titles;

# 如果表已经存在，则可以使用INSERT INTO SELECT语句：
> insert into employees.titles_only select distinct title from employees.titles;

为了避免重复，可以使用INSERT IGNORE [ɪɡˈnɔːr] 。本例中，titles_only表中没有primary key，因此IGNORE子句不会造成任何影响。

加载数据到表中

不仅可以将表数据保存到文件中，反过来操作也可以，即将文件中的数据加载到表中，这种方式广泛用于加载批量数据的情况，并且是将数据加载到表中的超快速方式。可以指定列分隔符将数据加载到相应的列中。需要拥有表的FILE权限和INSERT权限。

# 创建一个新表，如果表已经存在，则可以直接加载。
> create table employee_names (first_name varchar(14) not null, last_name varchar(16) not null);
# 查看表结构的详细信息
> desc employee_names;

# 使用LOAD DATA INFILE语句加载数据：
> load data infile '/var/lib/mysql-files/result.csv' into table employee_names \
	-> fields terminated by ',' \
	-> optionally enclosed by '"' \
	-> lines terminated by '\n';

> select * from employee_names;
+------------+------------+
| first_name | last_name  |
+------------+------------+
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
   mysql
+------------+------------+

# 如果文件开头包含一些你想忽略的行，可以使用IGNORE n Lines指定：
> load data infile '/result.csv' into table employee_names \
	-> fields terminated by ',' \
	-> optionally enclosed by '"' \
	-> lines terminated by '\n' \
	-> ignore 1 lines;

# 可以使用REPLACE [rɪˈpleɪs]或者IGNORE来处理重复的行：
> load data infile 'result.csv' REPLACE into table employee_names \
	-> fields terminated by ',' \
	->  optionally enclosed by '"' \
	-> lines terminated by '\n';
    
# 或者
> load data infile 'result.csv' IGNORE into table employee_names \
    -> fields terminated by ',' \
    -> optionally enclosed by '"' \
    -> lines terminated by '\n';