MySQL - MySQL 8.0(二)基本操作：文件

此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖

查询并导出到文件

1. 配置secure-file-priv路径

  默认情况下，MySQL不允许写入文件，应该在配置文件中设置secure-file-priv并重新启动MySQL，意味着所有文件都将被保存在该目录中

• 编辑/etc/mysql/mysql.conf.d/mysqld.cnf

# nangy @ nangy-vm in ~ [19:09:44] 
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 

• 填写如下内容

secure-file-priv = /var/lib/mysql-files

• 重启MySQL服务器

# nangy @ nangy-vm in ~ [19:07:36] 
$ sudo service mysql restart

2. 授权

要将输出结果保存到文件中，你需要拥有file权限。

# 普通用户
# 先查一下初始权限
mysql> show grants for emp_read;
+-------------------------------------------------+
| Grants for emp_read@%                           |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `emp_read`@`%`            |
| GRANT SELECT ON `employees`.* TO `emp_read`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

# 无file权限时执行报错
mysql> select * into outfile 'employees.csv' from employees.employees limit 2;
ERROR 1227 (42000): Access denied; you need (at least one of) the FILE privilege(s) for this operation

  file是一个全局特权，这意味着不能将其限制为针对特定数据库的权限。但是，可以限制授权只读用户

# 管理员用户
# 限制库表授权会报错
mysql> grant file on employees.* to 'emp_read'@'%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

# 只能授权 *.* 给只读用户
mysql> grant file on *.* to 'emp_read'@'%';
Query OK, 0 rows affected (0.00 sec)

# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 普通用户
# 验证权限
mysql> show grants for emp_read;
+-------------------------------------------------+
| Grants for emp_read@%                           |
+-------------------------------------------------+
| GRANT FILE ON *.* TO `emp_read`@`%`             |
| GRANT SELECT ON `employees`.* TO `emp_read`@`%` |
+-------------------------------------------------+
2 rows in set (0.00 sec)

ok，授权完成，别急，已经登陆的普通用户要重新登陆才行，否则依然会报错

ERROR 1227 (42000): Access denied; you need (at least one of) the FILE privilege(s) for this operation

3. 导出（INTO OUTFILE）

重新登陆普通用户

• 查看一下导出路径

mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
2 rows in set (0.00 sec)

• 执行导出
  报错1：未指定secure-file-priv路径
  报错2：目标文件已存在

select col1, col2,…… into outfile '/路径/文件名.csv' from {schema}.{table} where {filter};

# 没有指定secure-file-priv路径，执行报错
mysql> select first_name, last_name
    -> into outfile 'employees.csv'
    -> fields terminated by ','  -- 字段分隔符
    -> optionally enclosed by '"'  -- 引号字符
    -> lines terminated by '\n'  -- 行分隔符
    -> from employees.employees 
    -> limit 100;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

# 指定secure-file-priv路径后，执行成功
mysql> select first_name, last_name
    -> into outfile '/var/lib/mysql-files/employees.csv'
    -> fields terminated by ','  -- 字段分隔符
    -> optionally enclosed by '"'  -- 引号字符
    -> lines terminated by '\n'  -- 行分隔符
    -> from employees.employees 
    -> limit 100;
Query OK, 100 rows affected (0.01 sec)

• 验证一下数据

# root @ nangy-vm in /var/lib/mysql-files [9:33:14] 
$ ll
总用量 4.0K
-rw-rw-rw- 1 mysql mysql 2.0K 7月  23 09:28 employees.csv

# root @ nangy-vm in /var/lib/mysql-files [9:33:41] 
$ tail employees.csv
"Amabile","Gomatam"
"Valdiodio","Niizuma"
"Sailaja","Desikan"
"Arumugam","Ossenbruggen"
"Hilari","Morton"
"Jayson","Mandell"
"Remzi","Waschkowski"
"Sreekrishna","Servieres"
"Valter","Sullins"
"Hironobu","Haraldson"

！疑惑

  之前把secure-file-priv路径设置成/var/lib/mysql，此路径是Linux下MySQL默认的数据文档存储目录，和datadir路径一致，导出时候没有指定路径也能导出到此路径下。

• 疑惑1：难道和datadir一致就行？
• 疑惑2：还是只有/var/lib/mysql才行？

没验证过，回头有空了试试，或者查查资料，看看大神的解释

加载本地数据到表中

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

# 等会要进行操作的普通用户
mysql> show grants for emp_read_write using app_read,app_write;
+-------------------------------------------------------------------------------+
| Grants for emp_read_write@%                                                   |
+-------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO `emp_read_write`@`%`                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `emp_read_write`@`%` |
| GRANT `app_read`@`%`,`app_write`@`%` TO `emp_read_write`@`%`                  |
+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

1. 创建一个表

# 管理员用户先创建一个表，准备好
mysql> create table if not exists employees.t_employees_loadfile( 
    ->  first_name varchar(14) not null, 
    ->  last_name varchar(16) not null 
    -> ) Engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

文件就用上面例子导出的/var/lib/mysql-files/employees.csv文件

2. 导入（LOAD DATA INFILE）

load data infile '/路径/文件名.csv' into table {schema}.{table};

mysql> load data infile '/var/lib/mysql-files/employees.csv'
    -> into table employees.t_employees_loadfile
    -> fields terminated by ','  -- 字段分隔符
    -> optionally enclosed by '"'  -- 引号字符
    -> lines terminated by '\n';  -- 行分隔符
Query OK, 100 rows affected (0.00 sec)
Records: 100  Dele表ted: 0  Skipped: 0  Warnings: 0

2.1. 特殊操作

• 如果文件开头包含一些你想忽略的行，可以用IGNORE n Lines指定

load data infile '/路径/文件名.csv' into table {schema}.{table} ignore n lines;

• 可以用REPLACE或者IGNORE来处理重复的行

load data infile '/路径/文件名.csv' replace into table {schema}.{table};

load data infile '/路径/文件名.csv' ignore into table {schema}.{table};

• MySQL假定你要加载的文件在服务器上是可用的。如果你要从客户端远程连接到服务器，则可以指定LOCAL以装载位于客户端上的文件。本地文件将从客户端被复制到服务器上。该文件保存在服务器的常规临时路径中，在Linux系统中是/tmp

load data local infile '/客户端路径/文件名.csv' [replace/ignore] into table {schema}.{table};

3. 验证一下表数据

# 数据内容格式
mysql> select * from employees.t_employees_loadfile limit 2;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Georgi     | Facello   |
| Bezalel    | Simmel    |
+------------+-----------+
2 rows in set (0.00 sec)

# 数据量
mysql> select count(*) from employees.t_employees_loadfile;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)