SELECT ... INTO OUTFILE和LOAD DATA INFILE

语法

SELECT … INTO OUTFILE

这个语句用于将查询结果导出到服务器的文件系统中。它将 SELECT 语句的结果集直接写入到一个文本文件中。

1
2
3
4
5
6
7
SELECT
column1, column2, ...
INTO OUTFILE 'file_path'
[character set charset_name]
[fields [enclosed by 'char'] [escaped by 'char']]
[lines [terminated by 'char']]
[ignore lines number];
  • **file_path**:指定导出文件的路径和文件名。需要注意的是,该路径必须是 MySQL 服务器有权限访问的路径。
  • **character set charset_name**:指定导出文件的字符集。
  • **fields**:定义字段的格式,如字段是否由特定字符包围。
  • **lines**:定义行的格式,如行终止符。
  • **ignore lines number**:忽略文件开头的若干行。

LOAD DATA INFILE

这个语句用于将文件中的数据导入到数据库表中。它直接从文件读取数据,并将数据插入到指定的表中。

1
2
3
4
5
6
7
8
LOAD DATA INFILE 'file_path'
INTO TABLE table_name
[character set charset_name]
[FIELDS [TERMINATED BY 'char'] [ENCLOSED BY 'char'] [ESCAPED BY 'char']]
[LINES [STARTING BY 'char'] [TERMINATED BY 'char']]
[IGNORE number LINES]
(列名列表)
[SET column_name value, ...];
  • **file_path**:指定要导入数据的文件路径。
  • **INTO TABLE table_name**:指定目标表的名称。
  • **character set charset_name:指定文件的字符集。
  • **FIELDS**:定义字段的格式,如字段分隔符、字段包围符等。
  • **LINES**:定义行的格式,如行起始符和行终止符。
  • **IGNORE number LINES**:忽略文件开头的若干行。
  • **(列名列表)**:指定表中列的顺序。
  • **SET column_name value**:为某些列指定默认值。

示例

创建表

1
2
3
4
5
6
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
);

插入数据

1
2
3
4
INSERT INTO employees (name, department, salary) VALUES
('John Doe', 'Finance', 50000),
('Jane Smith', 'IT', 60000),
('Bob Johnson', 'HR', 55000);

导出文件

1
2
3
4
SELECT * FROM employees INTO OUTFILE '/var/lib/mysql-files/employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY ' '
LINES TERMINATED BY '\n';

创建employees_1表准备导入数据

1
create table employees_1 like employees;

导入数据

1
2
3
4
5
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees_1
FIELDS TERMINATED BY ','
ENCLOSED BY ' '
LINES TERMINATED BY '\n';

注意事项

  • 权限:使用这两个语句需要相应的权限。SELECT ... INTO OUTFILE 需要 FILE 权限,而 LOAD DATA INFILE 需要 LOAD DATA 权限。
  • 安全性:在生产环境中使用这些语句时,需要注意安全性,避免导出敏感数据或导入恶意数据。
  • 字符集:确保文件的字符集与数据库的字符集匹配,以避免数据乱码。

通过使用 SELECT ... INTO OUTFILE 和 LOAD DATA INFILE,你可以方便地在 MySQL 数据库和文件系统之间导入和导出数据。