mysql into outfile语法_MySQL 导出数据select into outfile用法

功能：

sql">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']

]

语法例子：

sql">SELECT customer_id,firstname,surname INTO OUTFILE '/exportdata/customers.txt'

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

FROM customers;

sql">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;

sql">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为本机用户名

查询导入数据：

sql">select * from testLoadData

sql">SELECT * FROM testLoadData

INTO OUTFILE '/Users/xxx/Downloads/loaddatass.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'查看对应目录下：可以发现上述数据已经存入到loaddatass.txt内。

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

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

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢交流学习经验，点击链接加入交流1群：1065694478(已满)交流2群：163560250