MySQL 利用JSON可以创建表值函数

1、表值函数

很久以前用了几年SQL Server，SQL_Server的用户自定函数支持返回一个结果集，被称为表值函数，很遗憾，MySQL没有表值函数一说，函数也不能返回结果集。

2、利用Josn及json函数让MySQL也可以返回表值函数

代表如下：

--  构造数据
drop table if exists json_t;
create table json_t
(
  a int,
  b int,
  c varchar(10)
);

insert into json_t
values(1,1,'a'),(2,2,'b'),(3,3,'c');

-- 创建函数
drop function if exists test_j;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `test_j`() RETURNS json
    READS SQL DATA
begin
    set @j=JSON_ARRAY();
    select json_arrayagg(json_object('a',a,'b',b,'c',c)) into @j from json_t ;
    return @j;
end$$
DELIMITER ;

-- 返回结果
select jst.* from (select test_j() as t) as t,  json_table( t.t,"$[*]"
COLUMNS( rowid for ordinality,
  a int  PATH "$.a" DEFAULT '0' ON EMPTY DEFAULT '0' ON ERROR,
  b int  PATH "$.b" DEFAULT '0' ON EMPTY DEFAULT '0' ON ERROR,
  c varchar(10)  PATH "$.c"  null ON EMPTY  null ON ERROR
)) as jst
复制

上面查询能得到下面结果

利用json与json函数让自定义函数能返回结果集，达到表值函数的效果

方法是让自定义函数返回json数组，再利用json_table解析json数组返回我们关系型数据库熟悉的多行结果集。