笔记-mysql 导出查询结果

语法：

The SELECT ... INTO OUTFILE 'file_name' [options] form of SELECT writes the selected rows to a file.

示例：

select * from students
    into outfile 'test/users.csv' 
    fields terminated by ',' optionally enclosed by '"' -- fields separated by commas and enclosed within double quotation marks
    lines terminated by '\r\n'

注意：

1、该语法只能在MYSQL服务器上执行；

2、数据文件的保存位置与file_name指定的值相关。

其中，文件夹需要手动创建，文件不能为已存在的文件

原因： /不懂哇~

file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. 

 

额外示例说明：

当前库：

select DATABASE(); -- mydemo 

数据文件保存位置：

select @@datadir;  -- D:\Programs\MySQL\Data\
-- show variables like 'datadir'

 

示例：

1、”fname” - %datadir%/<db_name>

file_name='users.csv'

file_path= D:\Programs\MySQL\data\mydemo\

 

2、”/fname” – rootd_%datadir%/

file_name='/users.csv'

file_path=D:\

 

3、”folder_name/fname” – %datadir%/folder_name

file_name='test/users.csv'

file_path= D:\Programs\MySQL\data\test\

 

其中，此处报错：Can't create/write to file 'D:\Programs\MySQL\Data\test\users.csv' (Errcode: 2)

根据错误编号Errcode，定位错误原因：需要手动创建文件夹 test

> perror 2
OS error code   2:  No such file or directory 

4、”/folder_name /fname” – rootd_%datadir%/folder_name

file_name='/test/users.csv'

file_path= D:\test\

 

导入外部文件

load data infile 'test/users.csv' REPLACE
        into table students
fields terminated by ',' optionally enclosed by '"'
lines terminated by '\r\n'

 

参考：

https://dev.mysql.com/doc/refman/5.7/en/select-into.html

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

http://www.mysqltutorial.org/import-csv-file-mysql-table/

http://www.cnblogs.com/zeroone/archive/2013/01/12/2857388.html

http://www.cnblogs.com/ahudyan-forever/p/5817847.html