如何将 MySQL 数据库导出为 JSON？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5036605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export a MySQL database to JSON?
提问by mindthief
I am interested in exporting a subset of values from a MySQL database into a JSON-formatted file on disk.
我有兴趣将值的子集从 MySQL 数据库导出到磁盘上的 JSON 格式的文件中。
I found a link that talks about a possible way to do this: http://www.thomasfrank.se/mysql_to_json.html
我找到了一个讨论可能的方法的链接：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 NULLBut when I run it as:
1）它只返回大约 15 个结果，最后一个突然中断（不完整）。当我运行它时，我对此的标准查询返回大约 4000 个结果SELECT name, email FROM students WHERE enrolled IS NULL但是当我运行它时：
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)
...如链接中所述，它只返回（正如我提到的）15 个结果。（fwiw，我将这些结果与我应该得到的 4000 进行了核对，这 15 个与 4000 中的前 15 个相同）
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 \.
2）当我添加INTO OUTFILE '/path/to/jsonoutput.txt' FIELDS TERMINATED BY ','到该查询的末尾时，实际文件中似乎包含“转义”字符。所以逗号最终看起来像 '\,' 显然我只想有没有 \ 的逗号。
Any ideas on how to get proper JSON output from MySQL? (Either using this method, or some other method)?
关于如何从 MySQL 获得正确的 JSON 输出的任何想法？（要么使用这种方法，要么使用其他方法）？
Thanks!
谢谢！
采纳答案by SingleNegationElimination
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.
它可能对 MySQL 要求太多，期望它直接从查询中生成格式良好的 json。相反，请考虑生成更方便的内容，例如 CSV（使用INTO OUTFILE '/path/to/output.csv' FIELDS TERMINATED BY ','您已经知道的代码段），然后将结果转换为具有内置支持的语言的 json，例如 python 或 php。
Editpython example, using the fine SQLAlchemy:
使用优秀的 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)
回答by Seamus Abshere
If you have Ruby, you can install the mysql2xxxxgem (not the mysql2json gem, which is a different gem):
如果你有 Ruby，你可以安装mysql2xxxxgem（不是 mysql2json gem，它是一个不同的 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 mysql2csvand 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)
gem 还提供mysql2csv和mysql2xml。它不如 mysqldump 快，但也没有 mysqldump 的一些奇怪之处（比如只能从与 MySQL 服务器本身相同的计算机上转储 CSV）
回答by Wtower
Another possibility is using the MySQL Workbench.
另一种可能性是使用 MySQL Workbench。
There is a JSON export option at the object browser context menu and at the result grid menu.
在对象浏览器上下文菜单和结果网格菜单中有一个 JSON 导出选项。
More information on MySQL documentation: Data export and import.
回答by Byron Whitlock
THis is somthing that should be done in the application layer.
这是应该在应用层完成的事情。
For example, in php it is a s simple as
例如，在 php 中它很简单
EditAdded the db connection stuff. No external anything needed.
编辑添加了数据库连接的东西。不需要外部任何东西。
$sql = "select ...";
$db = new PDO ( "mysql:$dbname", $user, $password) ;
$stmt = $db->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
file_put_contents("output.txt", json_encode($result));
回答by jjj
HeidiSQLallows you to do this as well.
HeidiSQL 也允许您这样做。
Highlight any data in the DATA tab, or in the query result set... then right click and select Export Grid Rows option. This option then allows you can export any of your data as JSON, straight into clipboard or directly to file:
突出显示“数据”选项卡或查询结果集中的任何数据...然后右键单击并选择“导出网格行”选项。然后，此选项允许您将任何数据导出为 JSON，直接导入剪贴板或直接导出到文件：
回答by seanbehan
Another solution, if you are using Ruby, is to write a connection script to the database with ActiveRecord. You will need to install it first
如果您使用 Ruby，另一种解决方案是使用 ActiveRecord 编写到数据库的连接脚本。您需要先安装它
gem install activerecord
gem 安装活动记录
# ruby ./export-mysql.rb
require 'rubygems'
require 'active_record'
ActiveRecord::Base.establish_connection(
:adapter => "mysql",
:database => "database_name",
:username => "root",
:password => "",
:host => "localhost"
)
class Event < ActiveRecord::Base; end
class Person < ActiveRecord::Base; end
File.open("events.json", "w") { |f| f.write Event.all.to_json }
File.open("people.json", "w") { |f| f.write Person.all.to_json }
You can also add methods to the ActiveRecord classes if you want to manipulate data first or include or exclude certain columns.
如果您想首先操作数据或包含或排除某些列，您还可以向 ActiveRecord 类添加方法。
Person.all.to_json(:only => [ :id, :name ])
With ActiveRecord you are not limited to JSON. You can just as easily export as XML or YAML
使用 ActiveRecord，您不仅限于 JSON。您可以像 XML 或 YAML 一样轻松导出
Person.all.to_xml
Person.all.to_yaml
You are not limited to MySQL. Any database supported by ActiveRecord (Postgres, SQLite3, Oracle... etc).
您不仅限于 MySQL。ActiveRecord 支持的任何数据库（Postgres、SQLite3、Oracle 等）。
And it's worth mentioning you could open another handle to a database
值得一提的是，您可以打开另一个数据库句柄
require 'active_record'
ActiveRecord::Base.configurations["mysql"] = {
:adapter => 'mysql',
:database => 'database_name',
:username => 'root',
:password => '',
:host => 'localhost'
}
ActiveRecord::Base.configurations["sqlite3"] = {
:adapter => 'sqlite3',
:database => 'db/development.sqlite3'
}
class PersonMySQL < ActiveRecord::Base
establish_connection "mysql"
end
class PersonSQLite < ActiveRecord::Base
establish_connection "sqlite3"
end
PersonMySQL.all.each do |person|
PersonSQLite.create(person.attributes.except("id"))
end
Here is a quick little blog post about it http://www.seanbehan.com/how-to-export-a-mysql-database-to-json-csv-and-xml-with-ruby-and-the-activerecord-gem
这是一篇关于它的快速小博客文章http://www.seanbehan.com/how-to-export-a-mysql-database-to-json-csv-and-xml-with-ruby-and-the-activerecord -宝石
回答by Patrick Pease
I know this is old, but for the sake of somebody looking for an answer...
我知道这是旧的，但为了有人寻找答案......
There's a JSON library for MYSQL that can be found hereYou need to have root access to your server and be comfortable installing plugins (it's simple).
有一个用于 MYSQL 的 JSON 库，可以在此处找到。您需要对服务器具有 root 访问权限，并且可以轻松安装插件（这很简单）。
1) upload the lib_mysqludf_json.so into the plugins directory of your mysql installation
1) 将 lib_mysqludf_json.so 上传到你的 mysql 安装的 plugins 目录中
2) run the lib_mysqludf_json.sql file (it pretty much does all of the work for you. If you run into trouble just delete anything that starts with 'DROP FUNCTION...')
2) 运行 lib_mysqludf_json.sql 文件（它几乎为您完成所有工作。如果遇到问题，只需删除以“DROP FUNCTION...”开头的任何内容）
3) encode your query in something like this:
3）将您的查询编码为这样的：
SELECT json_array(
group_concat(json_object( name, email))
FROM ....
WHERE ...
and it will return something like
它会返回类似的东西
[
{
"name": "something",
"email": "[email protected]"
},
{
"name": "someone",
"email": "[email protected]"
}
]
回答by user2014202
You can export any SQL query into JSON directly from PHPMyAdmin
您可以直接从 PHPMyAdmin 将任何 SQL 查询导出为 JSON
回答by robot_man
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)
如链接中所述，它只返回（正如我提到的）15 个结果。（fwiw，我将这些结果与我应该得到的 4000 进行了核对，这 15 个与 4000 中的前 15 个相同）
That's because mysql restricts the length of the data returned by group concat to the value set in @@group_concat_max_len as soon as it gets to the that amount it truncates and returns what it's gotten so far.
那是因为 mysql 将 group concat 返回的数据长度限制为 @@group_concat_max_len 中设置的值，一旦它达到它截断的数量并返回它到目前为止获得的数据。
You can set @@group_concat_max_len in a few different ways. reference The mysql documentation...
您可以通过几种不同的方式设置 @@group_concat_max_len。参考mysql 文档...
回答by Hasan Tayyar BESIK
Also, If you are exporting in application layer don't forget to limit results. For example if you've 10M rows, you should get results part by part.
此外，如果您在应用程序层导出，请不要忘记限制结果。例如，如果您有 1000 万行，则应该逐个获取结果。
