【MySQL】数据导出导入成CSV格式
2024-10-02 03:52:06
一、自动输出中文字符集
select * from db into outfile 'test.csv' CHARACTER SET gbk fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';
二、导出后转换字符集
转换成CSV文件,如果乱码将CSV已记事本打开另存为UTF8
select * from db into outfile 'test.csv' CHARACTER SET utf8 fields terminated by ',' optionally enclosed by '' lines terminated by '\n';
三、导入
导入文件扩展名不敏感,只要直接可读就可以按照对应分隔符导入
tips:导入表特定字段可以先建个对应字段的临时表,然后导入到临时表,再使用insert into table(column_a,column_b) select column_a,column_b from table_tmp 导入进去
http://dev.mysql.com/doc/refman/5.5/en/load-data.html
##以,为间隔符
load data infile 'test.csv' into table test_table fields terminated by ',' lines terminated by '\r\n'; ##以tab为间隔符
load data local infile "test.csv" into table test_table fields terminated by '\t' lines terminated by '\r\n';
##导入特定字段
load data infile 'test.csv' into table test_table (column1);
fields terminated by 表示字段分隔符
lines terminated by 表示行分隔符
optionally enclosed by '"' escaped by '"' 字符串需要用引号区分区域
##导入含字符串的文件
load data infile 'test.txt'
into table test_table
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n';
参考:http://blog.csdn.net/sara_yhl/article/details/6850107
最新文章
- 2015 CTSC &; APIO滚粗记
- leetcode-【简单题】Two Sum
- (转)2G到C-RAN网络架构的演进
- c# 常用正则
- 二模 (7) day2
- TCP/IP之大明王朝邮差
- VISA资源名称控件
- 《Python 学习手册4th》 第十一章 赋值、表达式和打印
- Measuring Signal Similarities
- 文件I/O操作(2)
- Struts2 interceptor使用经验小结
- NET下三种缓存机制(Winform里面的缓存使用 )
- math方法集合
- effective c++ 条款06 不想自动生成函数,就明确拒绝
- 第52周四ApplicationContext
- python之数据库操作(sqlite)
- 资讯类产品-创业邦APP产品原型模板公开分享
- hystrix实战
- SharePoint “File not found” 错误
- Appium+Python3+ Android入门