php 通过命令行或PHP以json格式导出mysql数据库/mysql表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40102747/
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
Export mysql database / mysql tables in json format through command line OR PHP
提问by Naga
I have big information table. I want to export it in json format for some other purpose. I could export in xml format using the below command.
我有大信息表。我想将它以 json 格式导出以用于其他目的。我可以使用以下命令以 xml 格式导出。
mysql -u root -p --xml -e "SELECT * FROM db_name.tbl_name" > d:\export.xml
mysql -u root -p --xml -e "SELECT * FROM db_name.tbl_name" > d:\export.xml
I tried something like the below for json format.
mysql -u root -p --json -e "SELECT * FROM db_name.tbl_name" > d:\export.json
我尝试了类似下面的 json 格式。
mysql -u root -p --json -e "SELECT * FROM db_name.tbl_name" > d:\export.json
I got the error message unknown option '--json'
我收到错误信息 unknown option '--json'
PS:I could not able to use any third party application like PHPMyadmin / workbench / SQLyog due to large size of table.
PS：由于表很大，我无法使用任何第三方应用程序，如 PHPMyadmin / workbench / SQLyog。
It would be greatly appreciated if you help me on this.
如果您能在这方面帮助我，我将不胜感激。
采纳答案by Paolo
mysql cannot output directly in json format
mysql无法直接以json格式输出
so you have two options:
所以你有两个选择：
1) export in XML the use a tool to convert from XML to JSON (a tool that can deal with large tables of course)
1) 导出为 XML 使用工具将 XML 转换为 JSON（当然是可以处理大表的工具）
2) write a small script (for example in PHP) that fetch the data from the DB and writes it to file in JSON
2）编写一个小脚本（例如在PHP中）从数据库中获取数据并将其写入JSON文件
Important note:
重要的提示：
If you choose option nr. (2) you may have trouble loading the whole table data, converting to JSON and saving to file in a single "atomic" step if you have a lot of records.
如果您选择选项 nr。(2)如果您有很多记录，您可能无法在单个“原子”步骤中加载整个表数据、转换为 JSON 并保存到文件。
However you may break the task into steps.
但是，您可以将任务分解为多个步骤。
Basically a table turned into a JSON is an array of objects, each object representing a single record.
基本上，转换为 JSON 的表是一个对象数组，每个对象代表一条记录。
Open a connection to the db
Start writing the output file and open the array by writing an open square bracket
[Execute your query fetching n( 1 < n < 1000 ) record at time. (In order to do that you have to
SORTthe table by any field -ex.id- and use theLIMITclause)Convert each record with
json_econde, write the string to file, write a comma,unless you have written the last record.Back to 3until you reach the last record.
Write a closing square bracket to file
](closing the array).Close the file and the db connection
打开与数据库的连接
开始写输出文件，写开方括号打开数组
[执行查询以获取n( 1 < n < 1000 ) 条记录。（为了做到这一点，你必须
SORT通过任何字段 -ex.id- 并使用该LIMIT子句到表中）用 转换每条记录
json_econde，将字符串写入文件，写一个逗号，,除非你写了最后一条记录。回到3直到到达最后一个记录。
将右方括号写入文件
]（关闭数组）。关闭文件和数据库连接
This would require a little more coding but it's not rocket science.
这需要更多的编码，但这不是火箭科学。
...and maybe you find something online that already does that.
...也许你在网上找到了一些已经做到了的东西。
Update:
更新：
A script that fetch the data from the DB and writes it to file in JSON can be found here on GitHub: Dump-table-to-JSON
可以在 GitHub 上找到从数据库获取数据并将其写入 JSON 文件的脚本：Dump-table-to-JSON
回答by VIET THANG VU
You can use Mysql Shell to directly out put to JSON.
您可以使用 Mysql Shell 直接输出到 JSON。
echo "[Your SQL query]" | mysqlsh --sql --result-format=json --uri=[username]@localhost/[schema_name]
