通过命令行或PHP以json格式导出mysql数据库/mysql表 [英] Export mysql database / mysql tables in json format through command line OR PHP
问题描述
我的信息表很大.我想将其导出为json格式以用于其他目的.我可以使用以下命令以xml格式导出.
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.
mysql -u root -p --xml -e "SELECT * FROM db_name.tbl_name" > d:\export.xml
我为json格式尝试了以下内容.
mysql -u root -p --json -e "SELECT * FROM db_name.tbl_name" > d:\export.json
I tried something like the below for json format.
mysql -u root -p --json -e "SELECT * FROM db_name.tbl_name" > d:\export.json
我收到错误消息unknown option '--json'
PS:由于表太大，我无法使用任何第三方应用程序，例如PHPMyadmin/workbench/SQLyog.
PS: I could not able to use any third party application like PHPMyadmin / workbench / SQLyog due to large size of table.
如果您能帮助我，将不胜感激.
It would be greatly appreciated if you help me on this.
推荐答案
mysql无法直接以json格式输出
所以您有两个选择:
1)使用工具将XML导出为XML(当然可以处理大型表)
1) export in XML the use a tool to convert from XML to JSON (a tool that can deal with large tables of course)
2)编写一个小脚本(例如，在PHP中)，该脚本从数据库中获取数据并将其写入JSON文件中
2) write a small script (for example in PHP) that fetch the data from the DB and writes it to file in JSON
重要说明:
如果选择选项nr. (2)如果您有很多记录，则可能很难在单个原子"步骤中加载整个表数据，转换为JSON并保存到文件.
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.
但是您可以将任务分解为几个步骤.
However you may break the task into steps.
基本上，变成JSON的表格是一个对象数组，每个对象代表一个记录.
Basically a table turned into a JSON is an array of objects, each object representing a single record.
-
打开与数据库的连接
Open a connection to the db
开始编写输出文件，并通过写一个方括号[
Start writing the output file and open the array by writing an open square bracket [
执行查询，一次获取 n (1< n< 1000)条记录. (为此，您必须通过-ex.id-任何字段来SORT表并使用LIMIT子句)
Execute your query fetching n ( 1 < n < 1000 ) record at time. (In order to do that you have to SORT the table by any field -ex. id- and use the LIMIT clause)
用json_econde转换每条记录，将字符串写入文件，并用逗号,书写，除非您已写入最后一条记录.
Convert each record with json_econde, write the string to file, write a comma , unless you have written the last record.
返回到 3 ，直到获得最后一条记录.
Back to 3 until you reach the last record.
在文件]中写一个方括号(关闭数组).
Write a closing square bracket to file ] (closing the array).
关闭文件和数据库连接
这需要更多的编码，但这不是火箭科学.
This would require a little more coding but it's not rocket science.
...也许您在网上已经找到了可以做到的事情.
...and maybe you find something online that already does that.
更新:
可以在GitHub上找到一个脚本，该脚本可以从DB中获取数据并将其写入JSON文件中:转储至JSON
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
这篇关于通过命令行或PHP以json格式导出mysql数据库/mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
