Naga Naga - 1 month ago 10
JSON Question

Export mysql database / mysql table in json format through command line

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


I tried something like the below for json format.
mysql -u root -p --json -e "SELECT * FROM db_name.tbl_name" > d:\export.json


I got the error message
unknown option '--json'


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.

Answer

mysql cannot output directly in json format

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)

2) write a small script (for example in PHP) that fetch the data from the DB and writes it to file in JSON

Edit:

If you choose option nr. (2) you may (try first giving php a lot of memory in the php.ini configuration...) 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 data.

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.

  1. Open a connection to the db

  2. Start writing the output file and open the array by writing an open square bracket [

  3. 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)

  4. Convert each record with json_econde, write the string to file, write a comma , unless you have written the last record.

  5. Back to 3 until you reach the last record.

  6. Write a closing square bracket to file ] (closing the array).

  7. Close the file and the db connection

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

Comments