Home > Archives > MYSQL JSON

MYSQL JSON

Publish:

在日常开发中,我们不可避免的要使用json数据,如果每次都自己编写生成json文件,总是要花费不少时间,今天我们来学习一下几个常用的程序,希望可以方便今后快速搭建json后台程序。

常用命令

    select REPLACE(UUID(), '-','')

使用Function获取单表json数据

DROP FUNCTION IF EXISTS `F_GETJSON`;

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `F_GETJSON`() RETURNS text CHARSET utf8 COLLATE utf8_bin
BEGIN
  DECLARE jsonStr TEXT DEFAULT "";
SELECT
	CONCAT('{"apiList":[',
	GROUP_CONCAT(
		CONCAT('{"version":"',version,'"'),',',
		CONCAT('"name":"',name,'"'),
		CONCAT(',"desc":"',description),'"',
		CONCAT(',"type":"',type),'"',
		CONCAT(',"typeDesc":"',type_desc),'"',
		CONCAT(',"path":"',path),'"',
		CONCAT(',"requestSample":"',request_sample),'"',
		CONCAT(',"responseSample":"',response_sample),'"}')
,']}')
INTO jsonStr FROM api;
RETURN jsonStr;
END
;;
DELIMITER ;

使用Store Procedure方式来建立较为复杂的Json文件

DROP PROCEDURE IF EXISTS `SP_GETJSON`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `SP_GETJSON`()
BEGIN
DECLARE v_json TEXT DEFAULT "";
DECLARE v_param TEXT DEFAULT "";
DECLARE v_api_id VARCHAR(50) DEFAULT "";
DECLARE v_name VARCHAR(500) DEFAULT "";
DECLARE v_version VARCHAR(500) DEFAULT "";
DECLARE v_desc VARCHAR(500) DEFAULT "";
DECLARE v_type VARCHAR(500) DEFAULT "";
DECLARE v_type_desc VARCHAR(500) DEFAULT "";
DECLARE v_path VARCHAR(500) DEFAULT "";
DECLARE v_request_sample VARCHAR(500) DEFAULT "";
DECLARE v_response_sample VARCHAR(500) DEFAULT "";
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE name_cursor CURSOR FOR 
SELECT api_id, name, version, description, type, type_desc, path, request_sample, response_sample from api;

DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET v_finished = 1;

set v_json = CONCAT(v_json,"{\"apiList\": [");

open name_cursor;

get_name: LOOP
	FETCH name_cursor into v_api_id,v_name,v_version,v_desc,v_type,v_type_desc,v_path,v_request_sample,v_response_sample;
	if v_finished = 1 THEN
	LEAVE get_name;
	END IF;

	set v_json = CONCAT(v_json,
		'{"version":"',IFNULL(v_version,""),'"',',',
		'"name":"',IFNULL(v_name,""),'"',
		',"desc":"',IFNULL(v_desc,""),'"',
		',"type":"',IFNULL(v_type,""),'"',
		',"typeDesc":"',IFNULL(v_type_desc,""),'"',
		',"path":"',IFNULL(v_path,""),'"');

	set v_json = CONCAT(v_json,
		',"requestSample":"',IFNULL(v_request_sample,""),'"',
		',"responseSample":"',IFNULL(v_response_sample,""),'"');

	SELECT
	CONCAT(',"paramList":[',
	GROUP_CONCAT(
		CONCAT('{"name":"',IFNULL(p.name,""),'"'),',',
		CONCAT('"desc":"',IFNULL(p.description,"")),'"}')
	,']')
	into v_param FROM param p where p.api_id = v_api_id;

	set v_json = CONCAT(v_json, IFNULL(v_param,""));
	set v_json = CONCAT(v_json,"},");


END LOOP get_name;

CLOSE name_cursor;

set v_json = CONCAT(v_json,"]}");

select v_json;

END
;;
DELIMITER ;

声明: 本文采用 BY-NC-SA 授权。转载请注明转自: Ding Bao Guo