将MySql表转换为JSON格式 [英] MySql table into JSON format

问题描述

我想将100个表从MySql转换为MongoDb
因此，我认为最简单的方法是将数据导入为JSON
是否可以在不使用PHP/ruby​​/Python的情况下将MySql数据转换为JSON格式的任何通用查询?

I would like to convert like 100 tables from MySql to MongoDb
So I think that the easiest way will be to import the data as JSON
Any generic query to convert MySql data to JSON format without using PHP /ruby/Python?

SELECT CONCAT(CONCAT('{"CompanyId":', company_id),"}")  AS JSON FROM company;

推荐答案

尝试一下:

SET @schema = 'test_db';
SET @table = 'test';

SELECT CONCAT(
  'SELECT CONCAT(TRIM(TRAILING ', QUOTE(','), ' FROM CONCAT(', QUOTE('{'), ',', 
  GROUP_CONCAT(QUOTE('"'), ',', QUOTE(COLUMN_NAME), ',', 
  QUOTE('"'), ',', QUOTE(':'), ',', QUOTE('"'), ',', COLUMN_NAME, ',', 
  QUOTE('"'),',', QUOTE(',')),
  ')), ''}'') FROM ', @table
)
INTO @qry FROM 
  (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c
  WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table) t;

SELECT @qry;
PREPARE stmt FROM @qry;
EXECUTE stmt;

以上查询以JSON格式生成表数据.使用该查询，您可以准备一个存储过程，以数据库和表名作为输入参数，并填充数据.如果要排除表中的某些列，只需修改查询即可从INFORMATION_SCHEMA数据库中选择数据，例如SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND COLUMN_NAME NOT IN (**columns to exclude**).

Above query generates table data in JSON format. Using the query you can prepare a stored procedure taking database and table names as input parameters and populate your data. If you want to exclude some of the columns in a table, just modify the query which selects data from INFORMATION_SCHEMA database like SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND COLUMN_NAME NOT IN (**columns to exclude**).

这篇关于将MySql表转换为JSON格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！