如何将 MySQL 数据库导出为 JSON

它对MySQL的要求可能太高了，以至于无法直接从查询中生成格式良好的json.相反，考虑生成更方便的东西，比如CSV(使用你已经知道的INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ','个片段)，然后将结果转化为JSON，支持内置的语言，如Python或PHP.

Edit python示例，使用精细的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 "<Student(%r, %r)>" % (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)