Export mysql database / mysql tables in json format through command line OR PHP

Question

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.

chmac · Accepted Answer · 2021-07-15 14:44:38Z

2

mysql cannot output directly in json format

so you have two options:

export in XML the use a tool to convert from XML to JSON (a tool that can deal with large tables of course)
write a small script (for example in PHP) that fetch the data from the DB and writes it to file in 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.

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.

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 SORT the table by any field -ex. id- and use the LIMIT clause)
Convert each record with json_econde, write the string to file, write a comma , unless you have written the last record.
Back to 3 until you reach the last record.
Write a closing square bracket to file ] (closing the array).
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 (last updated 2016).

edited Jul 15, 2021 at 14:44

chmac

12.9k3 gold badges37 silver badges37 bronze badges

answered Oct 18, 2016 at 8:08

Paolo

15.9k28 gold badges73 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Naga Over a year ago

I can write PHP script. But the amount of data is too large. So the script unable to complete the process.

Naga Over a year ago

Thank you very much for your detailed answer. I will go with your answer if I didn't find any other options.

Paolo Over a year ago

@Naga see my update for a ready to use script that fetch the data from the DB and writes it to file in JSON format

VIET THANG VU · Accepted Answer · 2019-08-10 12:18:09Z

2

You can use Mysql Shell to directly out put to JSON.

echo "[Your SQL query]" | mysqlsh --sql --result-format=json --uri=[username]@localhost/[schema_name]

answered Aug 10, 2019 at 12:18

VIET THANG VU

1581 silver badge4 bronze badges

Collectives™ on Stack Overflow

Export mysql database / mysql tables in json format through command line OR PHP

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related