MySQL 导出数据select into outfile用法

1、select  into  outfield  功能：

导出数据到pc的指定目录下。

2、语法：

SELECT ... INTO OUTFILE 'file_name'        [CHARACTER SET charset_name]        [export_options]export_options:    [{FIELDS | COLUMNS}        [TERMINATED BY 'string']        [[OPTIONALLY] ENCLOSED BY 'char']        [ESCAPED BY 'char']    ]    [LINES        [STARTING BY 'string']        [TERMINATED BY 'string']    ]

语法例子：

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LINES TERMINATED BY '\n'  FROM customers;

3、实际操作例子：Mac环境下

建表：

create table testLoadData( id bigint(20) not null auto_increment, username char(10) not null, age tinyint(3) UNSIGNED not null, description  text not null, primary key(id), unique key(username))engine=myisam default charset=utf8;

导入数据：

LOAD DATA local INFILE '/Users/xxx/Downloads/loaddata.txt' IGNORE INTO TABLE testLoadData FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines (username, age, description);

说明：xxx为本机用户名

查询导入数据：

select * from testLoadData

导出数据：

SELECT  * FROM testLoadData INTO OUTFILE '/Users/xxx/Downloads/loaddatass.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

查看对应目录下：可以发现上述数据已经存入到loaddatass.txt内。

4、遇到问题：

(1)

解决办法：修改对应目录下文件的写权限：

终端下输入：

(2)

出现错误原因：sql脚本中的路径下的文件已经存在。实际上sql脚本下的文件应该为MySQL自己去创建的一个文件，而不是去写一个已经存在的文件，所以重新写一个在该目录下没有的文件名即可。