将 MySQL 表导出为 csv 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22783202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting MySQL table into a csv file
提问by user3304713
I have a MySQL table which has to be taken out as a csv file, The query I used is
我有一个必须作为 csv 文件取出的 MySQL 表，我使用的查询是
SELECT "ID","NAME","SALARY","SAL1","SAL2","SAL3","SAL4","SAL5","SAL6","SAL7","SAL8","SAL9","SAL10","SAL11","SAL12","SAL13","SAL14","SAL15","SAL16","SAL17","SAL18","SAL19","SAL20","SAL21","SAL22","SAL23","SAL24","SAL25","SAL26"
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\JOSE DATA\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
This query works, what if I have 200 column names? Is there any way to do it without manually typing it?
这个查询有效，如果我有 200 个列名怎么办？有没有办法不用手动输入呢？
回答by Rems
This command almost gives you what you want, and it even works with a remote server. The only caveat is that it generates a TSV files (fields are separated by a tab).
这个命令几乎可以给你你想要的东西，它甚至可以与远程服务器一起使用。唯一需要注意的是它会生成一个 TSV 文件（字段由制表符分隔）。
mysql mydb -e "select * from mytable" -B > mytable.tsv
But you could convert it to CSV using sed, as suggested in this answer
但是您可以使用 sed 将其转换为 CSV，如本答案中所建议
mysql mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv
回答by user2102266
DESCRIBE addstock25;
Strip the first column and first 3 entry of that column (depends on your usage). You will get the list of fields in addstock25.
剥离该列的第一列和前 3 个条目（取决于您的使用情况）。您将获得 addstock25 中的字段列表。
This will bring only field names using virtual tables derived in core... called information schema.
这将只带来使用核心派生的虚拟表的字段名称......称为信息模式。
SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_NAME`='foo';
Lets say name of this query would be sq_fieldnamelist
假设这个查询的名称是 sq_fieldnamelist
So, above table have one column and it has the field names of th "foo" table.
因此，上表只有一列，并且具有“foo”表的字段名称。
If directly write like this
如果直接这样写
SELECT (sq_fieldnamelist)
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\JOSE DATA\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n
Mysql will give an error. "subquery returns multiple row"
Mysql 会报错。“子查询返回多行”
We must edit sq_fieldnamelist to concat all entries back to back separated with commas.
我们必须编辑 sq_fieldnamelist 以将所有条目背靠背连接起来，以逗号分隔。
Select GROUP_CONCAT(COLUMN_NAME)
FROM
(SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_NAME`='ffd_companies'
LIMIT 3,100
) AS fafa
GROUP BY 'COLUMN_NAME' // this group by is just to make group concat work
Lets say this is sq_fieldnamelist2
假设这是 sq_fieldnamelist2
If we edit sq_fieldnamelist like this. It will return only one value which is all field names seperated with commas. So now we can put this subquery to your select statement to acquire needed fields.
如果我们像这样编辑 sq_fieldnamelist。它将仅返回一个值，即所有字段名称以逗号分隔。所以现在我们可以把这个子查询放到你的 select 语句中来获取需要的字段。
SELECT (sq_fieldnamelist2)
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\JOSE DATA\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n
You need to edit LIMIT 3,100 in sq_fieldnamelist2 for you own purpose.
您需要根据自己的目的在 sq_fieldnamelist2 中编辑 LIMIT 3,100。
lets say your table is like fil1,fil2...filN,sal1,sal2,sal3....,salI to see the only salary fields you should use LIMIT N,x>I+N. if you want to see all use LIMIT 0,x>N+I
假设您的表格类似于 fil1,fil2...filN,sal1,sal2,sal3....,salI 以查看您应该使用的唯一工资字段LIMIT N,x>I+N。如果你想看到所有的使用LIMIT 0,x>N+I
回答by pacifist
I'm not seeing why you can't do
我不明白你为什么不能做
SELECT *
FROM addstock25
INTO OUTFILE "E:\JOSE DATA\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
回答by Oscar Rovira
I think you are looking for something like this.
我想你正在寻找这样的东西。
SET @sql = NULL;
SELECT GROUP_CONCAT("'",COLUMN_NAME,"'")
FROM
(SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'yourdatabasename'
and `TABLE_NAME`='ffd_companies'
) AS colnames
GROUP BY 'COLUMN_NAME'
into @sql;
SET @sql = concat ("SELECT", @sql, " from dual
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE 'E:\JOSE DATA\addstock7.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '", '"',"'
)"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Hope this helps you.
希望这对你有帮助。
Note that I added the WHEREclause TABLE_SCHEMA= 'yourdatabasename'
请注意，我添加了WHERE子句 TABLE_SCHEMA= 'yourdatabasename'
回答by Vineet Bihari Gupta
I have integrated the script for simple UI based code. Here you can enter query and simply can get the csv file containing all rows including column names.
我已经为简单的基于 UI 的代码集成了脚本。在这里您可以输入查询，并且可以简单地获取包含所有行（包括列名）的 csv 文件。
This script works for both windows and linux systems.
此脚本适用于 windows 和 linux 系统。
https://github.com/Bihari12/databasetocsv
https://github.com/Bihari12/databasetocsv
This would save the result in csv file in the same folder in which the code exists.
这会将结果保存在代码所在文件夹中的 csv 文件中。
回答by Alva Valencia
This answer works well!
这个答案效果很好！
mysql mydb -e "select * from mytable" -B > mytable.tsv &&
mysql mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv
