将MySQL表导出为CSV文件

epfja78i  于 2023-01-12  发布在  Mysql
关注(0)|答案(5)|浏览(183)

我有一个MySQL表,必须将其作为CSV文件取出。

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';

这个查询是有效的,但是如果我有200个列名怎么办?有没有不需要手动输入的方法?

ruyhziif

ruyhziif1#

这个命令几乎给了你想要的东西,它甚至可以与远程服务器一起工作。唯一需要注意的是,它生成了一个TSV文件(字段由制表符分隔)。

mysql mydb -e "select * from mytable" -B > mytable.tsv

但是您可以使用sed将其转换为CSV,如以下答案所示:

mysql mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv
62o28rlo

62o28rlo2#

DESCRIBE addstock25;

去掉第一列和该列的前三个条目(这取决于您的用法),您将得到addstock25中的字段列表。
这将只带来使用核心中派生的虚拟表的字段名...称为信息模式。

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='foo';

假设这个查询的名称是sq_fieldnamelist。
因此,上面的表有一列,其中包含“foo”表的字段名。
如果直接写

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将给予错误。“子查询返回多行”
我们必须编辑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 concatenation work

假设这是sq_fieldnamelist2。
如果我们像这样编辑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

您需要根据自己的需要编辑sq_fieldnamelist2中的LIMIT 3,100。
假设您的表为fil1、fil2...filN、sal1、sal2、sal3....、salI。要查看仅有的薪金字段,应使用LIMIT N, x > I+N。如果要查看所有字段,则使用LIMIT 0, x > N+I

stszievb

stszievb3#

我不明白你为什么不能

SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
fxnxkyjh

fxnxkyjh4#

我觉得你在找这样的东西。

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;

注意,我添加了WHERE子句TABLE_SCHEMA = 'yourdatabasename'。

brgchamk

brgchamk5#

可以使用“表格数据导出向导”,并从那里复制选择的SQL;

相关问题