mysql 虚拟表 json,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;

解决方案

Try this:

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;

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**).