sql - How to export a MySQL database to JSON?

Question

I am interested in exporting a subset of values from a MySQL database into a JSON-formatted file on disk.

I found a link that talks about a possible way to do this: http://www.thomasfrank.se/mysql_to_json.html

... but when I use the method from that page, it seems to work but with two problems:

1) It only returns around 15 results, with the last one abruptly cut off (incomplete). My standard query for this returns around 4000 results when I just run it as SELECT name, email FROM students WHERE enrolled IS NULL But when I run it as:

SELECT 
     CONCAT("[",
          GROUP_CONCAT(
               CONCAT("{name:'",name,"'"),
               CONCAT(",email:'",email,"'}")
          )
     ,"]") 
AS json FROM students WHERE enrolled IS NULL;

... as described in the link, it only returns (as I mentioned) 15 results. (fwiw, I checked these results against the 4000 I'm supposed to get, and these 15 are the same as the first 15 of the 4000)

2) There seem to be "escape" characters included in the actual file when I add INTO OUTFILE '/path/to/jsonoutput.txt' FIELDS TERMINATED BY ',' to the end of that query. So commas end up looking like ',' when obviously I would just like to have the commas without the .

Any ideas on how to get proper JSON output from MySQL? (Either using this method, or some other method)?

Thanks!

Answer 1

If you have Ruby, you can install the mysql2xxxx gem (not the mysql2json gem, which is a different gem):

$ gem install mysql2xxxx

and then run the command

$ mysql2json --user=root --password=password --database=database_name --execute "select * from mytable" >mytable.json

The gem also provides mysql2csv and mysql2xml. It's not as fast as mysqldump, but also doesn't suffer from some of mysqldump's weirdnesses (like only being able to dump CSV from the same computer as the MySQL server itself)