如何将MySQL数据库导出到JSON?

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 NULL But when I run it as:

1)它只返回15个结果，最后一个结果突然中断(不完整)。我的标准查询返回了大约4000个结果当我以SELECT name的形式运行时，来自注册学生的邮件是空的但是当我以:

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)当我添加到OUTFILE '/path/to/jsonoutput时，实际文件中似乎包含了“转义”字符。txt' FIELDS以'，'，'结尾的查询。所以逗号最后看起来像'\ '，而显然我只是想有逗号而不带\。

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

关于如何从MySQL获得适当的JSON输出的任何想法?(用这个方法还是用别的方法)?

Thanks!

谢谢!

13 个解决方案

#1

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'字段以'、'代码片段结尾)，然后使用内置支持的语言将结果转换为json，如python或php。

Edit python example, using the fine SQLAlchemy:

编辑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)

#2

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

如果您有Ruby，您可以安装mysql2xxxx gem(不是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 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)

gem还提供了mysql2csv和mysql2xml。它不像mysqldump那样快，但也不受mysqldump怪异特性的影响(比如只能从与MySQL服务器相同的计算机上转储CSV)

#3

Another possibility is using the MySQL Workbench.

另一种可能是使用MySQL工作台。

There is an 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.

更多关于MySQL文档的信息:数据导出和导入。

#4

THis is somthing that should be done in the application layer.

这是应用层中必须做的事情。

For example, in php it is a s simple as

例如，在php中，它是一个简单的as

Edit Added the db connection stuff. No external anything needed.

编辑添加的db连接内容。不需要任何外部。

$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));

#5

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安装activerecord

# 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- mysql-database-to-json- csv-xml -with-ruby-and-the-activerecord-gem

#6

HeidiSQL allows 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:

在data选项卡或查询结果集中突出显示任何数据…然后右击并选择Export Grid Rows选项。此选项允许您将任何数据导出为JSON，直接导出到剪贴板中或直接导出到文件中:

#7

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 here You need to have root access to your server and be comfortable installing plugins (it's simple).

这里有一个用于MYSQL的JSON库，您需要具有对服务器的根访问权限，并且可以轻松安装插件(这很简单)。

1) upload the lib_mysqludf_json.so into the plugins directory of your mysql installation

1)上传lib_mysqludf_json。在mysql安装的插件目录中

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": "someone@somewhere.net"
    }, 
   { 
     "name": "someone",
     "email": "something@someplace.com"
    }

]

#8

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文档…

#9

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.

同样，如果在应用程序层中导出，不要忘记限制结果。例如，如果您有10M行，您应该部分地获得结果。

#10

You can export any SQL query into JSON directly from PHPMyAdmin

您可以直接从PHPMyAdmin将任何SQL查询导出到JSON中

#11

Use the following ruby code

使用下面的ruby代码。

require 'mysql2'

client = Mysql2::Client.new(
  :host => 'your_host', `enter code here`
  :database => 'your_database',
  :username => 'your_username', 
  :password => 'your_password')
table_sql = "show tables"
tables = client.query(table_sql, :as => :array)

open('_output.json', 'a') { |f|       
    tables.each do |table|
        sql = "select * from `#{table.first}`"
        res = client.query(sql, :as => :json)
        f.puts res.to_a.join(",") + "\n"
    end
}

#12

For anyone that wants to do this using Python, and be able to export all tables without predefinining field names etc, I wrote a short script for this the other day, hope someone finds it useful:

对于任何想使用Python来实现这一点的人，并且能够在没有预先定义字段名的情况下导出所有表，我在前几天写了一个简短的脚本，希望有人发现它有用:

from contextlib import closing
from datetime import datetime
import json
import MySQLdb
DB_NAME = 'x'
DB_USER = 'y'
DB_PASS = 'z'

def get_tables(cursor):
    cursor.execute('SHOW tables')
    return [r[0] for r in cursor.fetchall()] 

def get_rows_as_dicts(cursor, table):
    cursor.execute('select * from {}'.format(table))
    columns = [d[0] for d in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]

def dump_date(thing):
    if isinstance(thing, datetime):
        return thing.isoformat()
    return str(thing)


with closing(MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_NAME)) as conn, closing(conn.cursor()) as cursor:
    dump = {}
    for table in get_tables(cursor):
        dump[table] = get_rows_as_dicts(cursor, table)
    print(json.dumps(dump, default=dump_date, indent=2))

#13

This might be a more niche answer but if you are on windows and MYSQL Workbench you can just select the table you want and click Export/Import in the Result grid. This will give you multiple format options including .json

这可能是一个更合适的答案，但是如果您在windows和MYSQL Workbench中，您可以选择您想要的表，并在结果网格中单击Export/Import。这将为您提供多种格式选项，包括.json

#1