MySQL 查询中的类型检查

发布于 2024-12-11 18:58:20 字数 326 浏览 12 评论 0原文

因此，我使用运行以下查询将表转储到 CSV 文件：

SELECT * INTO OUTFILE 'testout.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM dbname.tblname;

这工作正常，但所有字段都用双引号引起来。我想得到它，以便在输出中只有 varchar （即字符串）字段被引号包围。有什么简单的方法可以做到这一点吗？它可能会以某种方式涉及查询中的类型检查字段，但我对如何处理这个问题没有丝毫线索。任何帮助将不胜感激。

原文

So I'm using to run the following query to dump my table to a CSV file:

SELECT * INTO OUTFILE 'testout.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM dbname.tblname;

This works fine, but all of the fields are being surrounded by double quotes. I'd like to get it so that only the varchar (i.e. string) fields are surrounded by quotes in the output. Is there any easy way to do this? It would probably involve type-checking fields in the query somehow, but I don't have the slightest clue for how to approach this. Any help would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

野の 2024-12-18 18:58:20

当您有 OPTIONALLY ENCLOSED BY 时，它应该已经按照您的意愿进行操作：

如果指定 OPTIONALLY，则 ENCLOSED BY 字符仅用于包含字符串数据类型（例如 CHAR、BINARY、TEXT 或 ENUM）的列中的值：
1,"一个字符串",100.20
2,"包含 , 逗号的字符串",102.20
3、“包含\”引号的字符串”，102.20
4,"包含 \"、引号和逗号的字符串",102.20

您确定不是每列都有 varchar 列吗？

When you have OPTIONALLY ENCLOSED BY it should already do as you want:

If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM):
1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

Are you sure that you don't have varchar columns for every column?

回复收藏 0 原文

~没有更多了~