MySQL导出CSV步骤

 

MySQL中导出CSV格式数据的SQL语句样本如下:

Sql代码   收藏代码
  1. select * from test_info   
  2. into outfile '/tmp/test.csv'   
  3. fields terminated by ',' optionally enclosed by '"' escaped by '"'   
  4. lines terminated by '\r\n';   
  1. select * from test_info   
  2. into outfile '/tmp/test.csv'   
  3. fields terminated by ',' optionally enclosed by '"' escaped by '"'   
  4. lines terminated by '\r\n';   

MySQL中导入CSV格式数据的SQL语句样本如下:

Sql代码   收藏代码
  1. load data infile '/tmp/test.csv'   
  2. into table test_info    
  3. fields terminated by ','  optionally enclosed by '"' escaped by '"'   
  4. lines terminated by '\r\n';   
  1. load data infile '/tmp/test.csv'   
  2. into table test_info    
  3. fields terminated by ','  optionally enclosed by '"' escaped by '"'   
  4. lines terminated by '\r\n';   

里面最关键的部分就是格式参数

Sql代码   收藏代码
  1. fields terminated by ',' optionally enclosed by '"' escaped by '"'   
  2. lines terminated by '\r\n'   
  1. fields terminated by ',' optionally enclosed by '"' escaped by '"'   
  2. lines terminated by '\r\n'   

这个参数是根据RFC4180文档设置的,该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files,其中详细描述了CSV格式,其要点包括:

(1)字段之间以逗号分隔,数据行之间以\r\n分隔;

(2)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。

 

文件:test_csv.sql

Sql代码   收藏代码
  1. use test;  
  2.   
  3. create table test_info (  
  4.     id  integer not null,  
  5.     content varchar(64) not null,  
  6.     primary key (id)  
  7. );  
  8.   
  9. delete from test_info;  
  10.   
  11. insert into test_info values (2010, 'hello, line  
  12. suped  
  13. seped  
  14. "  
  15. end'  
  16. );  
  17.   
  18. select * from test_info;  
  19.   
  20. select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
  21.   
  22. delete from test_info;  
  23.   
  24. load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
  25.   
  26. select * from test_info;  
  27.   
  28.    
  1. use test;  
  2.   
  3. create table test_info (  
  4.     id  integer not null,  
  5.     content varchar(64) not null,  
  6.     primary key (id)  
  7. );  
  8.   
  9. delete from test_info;  
  10.   
  11. insert into test_info values (2010, 'hello, line  
  12. suped  
  13. seped  
  14. "  
  15. end'  
  16. );  
  17.   
  18. select * from test_info;  
  19.   
  20. select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
  21.   
  22. delete from test_info;  
  23.   
  24. load data infile '/tmp/test.csv' into table test_info  fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
  25.   
  26. select * from test_info;  
  27.   
  28.    

 

文件:test.csv

Text代码   收藏代码
  1. 2010,"hello, line  
  2. suped  
  3. seped  
  4. ""  
  5. end"  
  1. 2010,"hello, line  
  2. suped  
  3. seped  
  4. ""  
  5. end"  

 

在Linux下如果经常要进行这样的导入导出操作,当然最好与Shell脚本结合起来,为了避免每次都要写格式参数,可以把这个串保存在变量中,如下所示:(文件mysql.sh)

Bash代码   收藏代码
  1. #!/bin/sh  
  2.   
  3.   
  4. # Copyright (c) 2010 codingstandards. All rights reserved.  
  5. # file: mysql.sh  
  6. # description: Bash中操作MySQL数据库  
  7. # license: LGPL  
  8. # author: codingstandards  
  9. # email: codingstandards@gmail.com  
  10. # version: 1.0  
  11. # date: 2010.02.28  
  12.   
  13.   
  14. # MySQL中导入导出数据时,使用CSV格式时的命令行参数  
  15. # 在导出数据时使用:select ... from ... [where ...] into outfile '/tmp/data.csv' $MYSQL_CSV_FORMAT;  
  16. # 在导入数据时使用:load data infile '/tmp/data.csv' into table ... $MYSQL_CSV_FORMAT;  
  17. # CSV标准文档:RFC 4180  
  18. MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"  
 

使用示例如下:(文件test_mysql_csv.sh)

Bash代码   收藏代码
  1. #!/bin/sh  
  2.   
  3. . /opt/shtools/commons/mysql.sh  
  4.   
  5. # MYSQL_CSV_FORMAT="fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n'"  
  6. echo "MYSQL_CSV_FORMAT=$MYSQL_CSV_FORMAT"  
  7.   
  8. rm /tmp/test.csv  
  9.   
  10. mysql -p --default-character-set=gbk -t --verbose test <<EOF  
  11.   
  12. use test;  
  13.   
  14. create table if not exists test_info (  
  15.     id  integer not null,  
  16.     content varchar(64) not null,  
  17.     primary key (id)  
  18. );  
  19.   
  20. delete from test_info;  
  21.   
  22. insert into test_info values (2010, 'hello, line  
  23. suped  
  24. seped  
  25. "  
  26. end'  
  27. );  
  28.   
  29. select * from test_info;  
  30.   
  31. -- select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
  32. select * from test_info into outfile '/tmp/test.csv' $MYSQL_CSV_FORMAT;  
  33.   
  34. delete from test_info;  
  35.   
  36. -- load data infile '/tmp/test.csv' into table test_info fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';  
  37. load data infile '/tmp/test.csv' into table test_info $MYSQL_CSV_FORMAT;  
  38.   
  39. select * from test_info;  
  40.   
  41.   
  42. EOF  
  43.   
  44. echo "===== content in /tmp/test.csv ====="  
  45. cat /tmp/test.csv  
 
<think>好的,我现在需要解决用户在Mac OS下使用MySQL导出CSV失败的问题。首先,我得回忆一下MySQL导出CSV的常用方法,通常是使用SELECT ... INTO OUTFILE语句。不过,这个语句对文件路径和权限有特定要求,可能用户在这里遇到了问题。 首先,用户可能在执行类似以下的命令时出错: ```sql SELECT * INTO OUTFILE '/tmp/output.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM my_table; ``` 这时候常见的错误包括文件路径不可写,或者MySQL没有权限写入指定目录。Mac OS的文件系统权限比较严格,特别是新版本可能对某些目录如/tmp有更多限制。用户可能没有正确设置目录权限,或者试图写入没有权限的路径,比如用户主目录以外的位置。 另外,安全模式也是一个可能性。MySQL的secure_file_priv参数限制了导出文件的路径。如果用户指定的路径不在secure_file_priv允许的范围内,导出就会失败。需要检查该参数的设置,可以通过执行SHOW VARIABLES LIKE 'secure_file_priv';来查看当前配置。如果该值设置为某个特定目录,比如/var/mysql-files/,那么用户必须将输出文件指定到这个目录下。 还有,文件路径中的目录必须存在,否则MySQL无法创建文件。用户可能没有事先创建所需的目录,或者路径拼写错误。例如,用户可能输入了错误的路径,比如把/tmp写成了/tmpp,导致目录不存在。 此外,文件权限问题也不能忽视。即使用户指定了正确的路径,MySQL进程(通常是_mysql用户)也需要有写入该目录的权限。在Mac OS上,可能需要使用chmod或chown命令修改目录权限,确保MySQL用户有写入权限。 还有可能遇到的是字段分隔符或换行符的问题,特别是在跨平台时。比如,在Windows中常用的是\r\n作为换行符,而Mac OS和Linux使用\n。不过,这可能不会导致导出失败,而是文件格式的问题,但用户的问题是关于导出失败,所以这可能不是主要原因,但需要提及可能的兼容性问题。 此外,用户可能需要检查是否有足够的磁盘空间,或者文件名是否已经存在导致冲突。如果输出文件已经存在,并且MySQL没有覆盖权限,也会导致失败。这时候需要确保文件不存在,或者MySQL有权限覆盖它。 可能的解决方案步骤包括: 1. 确认使用的文件路径在secure_file_priv允许的范围内。 2. 检查目录是否存在,并确保MySQL有写入权限。 3. 使用绝对路径而不是相对路径。 4. 检查文件是否已经存在,并尝试更换文件名。 5. 可能需要修改MySQL配置文件,调整secure_file_priv的设置,然后重启MySQL服务。 另外,用户可能没有使用sudo或者足够的权限来修改系统目录,这时候建议使用用户有权限的目录,比如用户主目录下的某个文件夹,并确保该目录权限正确。 例如,用户可以将导出路径改为用户主目录下的一个子目录: ```sql SELECT * INTO OUTFILE '/Users/username/data/output.csv' ... ``` 然后,需要确保该目录存在,并且MySQL进程有权限写入。 还有,Mac OS的MySQL安装方式可能影响配置文件的路径,比如通过Homebrew安装的MySQL,配置文件可能在/usr/local/etc/my.cnf,而系统自带的可能在/etc/my.cnf。用户
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值