英文:
How to use 'terminated by' without using 'INTO OUTFILE' (using a client command)
问题
为了将我的表导出到文本文件中，我执行以下操作：
mysql -e 'select * from my_table;' > /my_file.txt
但我需要将表导出到一个文本文件中，其中字段由逗号分隔。所以我执行了以下操作：
mysql -e 'select * from my_table fields terminated by ",";' > /my_file.txt
但是它给我返回了以下错误：
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'fields terminated by ',';'
MySQL文档在这里建议在使用'INTO OUTFILE'时使用'terminated by'，但由于一些权限问题，我无法使用'INTO OUTFILE'。是否有一种方法可以在使用以下命令输出结果时以逗号终止字段：
mysql -e 'mySql query' > text_file.txt
英文:
To dump my table into a text file, I do:
mysql -e 'select * from my_table;' > /my_file.txt
but I need to dump the table into a text file where fields are terminated by a comma. so I did:
mysql -e 'select * from my_table fields terminated by ",";' > /my_file.txt
but it gives me this error:
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'fields terminated by ','
MySql documentation here suggests using 'terminated by' with 'INTO OUTFILE', but I can't use INTO OUTFILE because of some permission issues.
Is there any way to terminate the fields by "," when outputting the results using
mysql -e 'mySql query' > text_file.txt
?
答案1
得分: 1
你可以使用 SELECT ... INTO OUTFILE。语法不是你猜的那样。我不会重复语法，你可以阅读参考文档。
但我猜你如果没有使用 LOAD DATA INFILE 的权限，可能也无法执行这个操作。
另一种选择是将结果输出为制表符分隔的格式，这正是MySQL客户端在批处理模式下输出的格式，然后将制表符转换为逗号。
% mysql -BNe "select 123, 456"
123 456
这是数字之间的制表符。你可以使用你喜欢的shell筛选工具来将制表符转换为逗号。例如，tr 可以做到：
% mysql -BNe "select 123, 456" | tr '\t' ','
123,456
英文:
You could use SELECT ... INTO OUTFILE. The syntax isn't what you guessed. I won't repeat the syntax, you can read the reference documentation.
But I suppose you don't have privilege to do that if you don't have privilege to use LOAD DATA INFILE.
The other alternative is to output to tab-delimited format which is what the MySQL client outputs in batch mode, then transform the tabs to commas.
% mysql -BNe "select 123, 456"
123 456
That's a tab character between the numbers. Use your favorite shell filter tool to transform the tabs to commas. For example tr can do it:
% mysql -BNe "select 123, 456" | tr '\t' ','
123,456
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论