mysql json格式输出_如何将MySQL数据库导出为JSON？

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!

解决方案

It may be asking too much of MySQL to expect it to produce well formed json directly from a query. Instead, consider producing something more convenient, like CSV (using the INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ',' snippet you already know) and then transforming the results into json in a language with built in support for it, like python or php.

Edit python example, using the fine SQLAlchemy:

class Student(object):

'''The model, a plain, ol python class'''

def __init__(self, name, email, enrolled):

self.name = name

self.email = email

self.enrolled = enrolled

def __repr__(self):

return "" % (self.name, self.email)

def make_dict(self):

return {'name': self.name, 'email': self.email}

import sqlalchemy

metadata = sqlalchemy.MetaData()

students_table = sqlalchemy.Table('students', metadata,

sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),

sqlalchemy.Column('name', sqlalchemy.String(100)),

sqlalchemy.Column('email', sqlalchemy.String(100)),

sqlalchemy.Column('enrolled', sqlalchemy.Date)

)

# connect the database. substitute the needed values.

engine = sqlalchemy.create_engine('mysql://user:pass@host/database')

# if needed, create the table:

metadata.create_all(engine)

# map the model to the table

import sqlalchemy.orm

sqlalchemy.orm.mapper(Student, students_table)

# now you can issue queries against the database using the mapping:

non_students = engine.query(Student).filter_by(enrolled=None)

# and lets make some json out of it:

import json

non_students_dicts = ( student.make_dict() for student in non_students)

students_json = json.dumps(non_students_dicts)