如何将 MySQL 数据库导出为 JSON？

Question

我有兴趣将 MySQL 数据库中的值子集导出到磁盘上的 JSON 格式文件中。

我找到了一个链接，讨论了一种可能的方法： http ://www.thomasfrank.se/mysql_to_json.html

…但是当我使用该页面中的方法时，它似乎可以工作，但有两个问题：

1）它只返回大约 15 个结果，最后一个被突然切断（不完整）。当我将它作为 SELECT name, email FROM students WHERE enrolled IS NULL 运行时，我的标准查询返回大约 4000 个结果，但是当我运行它时：

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

…如链接中所述，它仅返回（如我所述）15 个结果。 （fwiw，我根据我应该得到的 4000 检查了这些结果，这 15 个与 4000 的前 15 个相同）

2）当我在查询末尾添加 INTO OUTFILE '/path/to/jsonoutput.txt' FIELDS TERMINATED BY ',' 时，实际文件中似乎包含“转义”字符。所以逗号最终看起来像’\，’，显然我只想让逗号没有\。

关于如何从 MySQL 获得正确的 JSON 输出的任何想法？ （使用这种方法，或其他方法）？

谢谢！

原文由 mindthief 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

期望 MySQL 直接从查询中生成格式良好的 json 可能对 MySQL 提出了太多要求。相反，请考虑生成更方便的内容，例如 CSV（使用您已经知道的 INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ',' 代码段），然后将结果转换为内置支持它的语言中的 json，例如 python 或 php。

使用精细的 SQLAlchemy 编辑 python 示例：

 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)

原文由 SingleNegationElimination 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

很多这些答案似乎都被过度设计了。只有一个 shell 和 mysql 您可以从命令行执行此操作。

假设一个名为 mydb 的数据库很简单

for t in $(mysql -Bse "select table_name from information_schema.tables where table_schema = 'mydb'")
  do
    mysql -Bse "select concat('select json_object(', group_concat('''', column_name, ''',', column_name), ') from ', table_name, ';') from information_schema.columns where table_schema='mydb' and table_name='$t'" mydb \
      | mysql -Bs mydb > $t.json
done

运行它，然后你将在每个表中拥有一个 json 文件，不需要任何编程语言。

答案的核心是来自 information_schema 的以下查询：

 select concat(
    'select json_object(',
    group_concat(
        '''',
        column_name,
        ''',',
        column_name
    ),
    ') from ',
    table_name,
    ';')
from information_schema.columns
where table_schema = 'mydb'
    and table_name = ?

原文由 dland 发布，翻译遵循 CC BY-SA 4.0 许可协议