MySQL如何获取存储过程参数？

AsiaYe

发布于 2022-12-07 09:04:33

3.4K0

发布于 2022-12-07 09:04:33

文章被收录于专栏：DBA随笔

MySQL如何获取存储过程参数？

前两天有个小伙伴问我如何查看MySQL存储过程的参数问题，这个问题还真把我问住了。于是查了查官方文档，把查看的结果分享出来，希望对大家有帮助吧。

MySQL 存储过程的参数

MySQL中的存储过程，可以理解成一段代码，每次调用这段代码，可以帮助你实现对应的功能，例如下面这段代码，就是帮助我们连续插入1000个记录在表t里面：

delimiter ;;
create procedure idata()
begin
declare i int;
set i=;
while(i<=) do
insert into t values(i,i,i);
set i=i+;
end while;
end;;
delimiter ;

可以看到，存储过程本质上是一段代码逻辑。通常情况下，我们更愿意让开发同学将代码逻辑写在代码里面而非数据库中，因为数据库最擅长做的不是处理逻辑，而是进行数据的CRUD。

当然，在现实工作中，由于各种各样的原因，存储过程总是会存在的。

上述存储过程没有参数，我们再来看一个有参数的存储过程，如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc08`(
in empno int ,
out out_ename varchar() )
begin
select ename into out_ename from emp where emp.empno = empno;
end

可以看到，这个procedure里面有2个参数，一个输入的in参数empno，int类型；一个输出的out参数out_name，是varchar类型

现在的问题是，如何通过SQL取出来这两个传入的参数？

获取参数的2种方案

方法一、使用mysql.proc表（仅限MySQL5.7及以下版本）

在MySQL的元信息表中，可以通过mysql.proc表去查看一个存储过程的参数，如下：

mysql> select * from mysql.proc where db='test' and name='proc08'\G
*************************** . row ***************************
                  db: test
                name: proc08
                type: PROCEDURE
       specific_name: proc08
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list: in empno int ,out out_ename varchar()
             returns:
                body: begin
select ename into out_ename from emp where emp.empno = empno;
end
             definer: root@localhost
             created: -- ::
            modified: -- ::
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
        db_collation: latin1_swedish_ci
           body_utf8: begin
select ename into out_ename from emp where emp.empno = empno;
end
 row in set (0.01 sec)

如上图中标红部分，可以通过param_list去获取存储过程的输入和输出值。

但是从结果可以看到，这个输入输出值获取到之后，需要重新做个二次解析，因为输入和输出值是混合在一起的，需要我们使用逗号将结果做一个分割。

注意：在MySQL8.0中，mysql.proc表被去掉了，这种方法也就不适用了。

方法二：直接使用parameter表（MySQL5.7、MySQL8.0都适用）

MySQL的parameter表是专门记录函数和存储过程的输入输出参数的。它的表结构如下：

mysql> show create table information_schema.parameters\G
*************************** 1. row ***************************
       Table: PARAMETERS
Create Table: CREATE TEMPORARY TABLE `PARAMETERS` (
  `SPECIFIC_CATALOG` varchar() NOT NULL DEFAULT '',
  `SPECIFIC_SCHEMA` varchar() NOT NULL DEFAULT '',
  `SPECIFIC_NAME` varchar() NOT NULL DEFAULT '',
  `ORDINAL_POSITION` int() NOT NULL DEFAULT '0',
  `PARAMETER_MODE` varchar() DEFAULT NULL,
  `PARAMETER_NAME` varchar() DEFAULT NULL,
  `DATA_TYPE` varchar() NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` int() DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` int() DEFAULT NULL,
  `NUMERIC_PRECISION` bigint() unsigned DEFAULT NULL,
  `NUMERIC_SCALE` int() DEFAULT NULL,
  `DATETIME_PRECISION` bigint() unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar() DEFAULT NULL,
  `COLLATION_NAME` varchar() DEFAULT NULL,
  `DTD_IDENTIFIER` longtext NOT NULL,
  `ROUTINE_TYPE` varchar() NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 row in set (0.01 sec)

结合我们上面的存储过程，我们看看它的输入输出参数是什么样子：

select * from information_schema.PARAMETERS where specific_schema='test' and specific_name='proc08';
+------------------+-----------------+---------------+------------------+----------------+----------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+----------------+--------------+
| SPECIFIC_CATALOG | SPECIFIC_SCHEMA | SPECIFIC_NAME | ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | DTD_IDENTIFIER | ROUTINE_TYPE |
+------------------+-----------------+---------------+------------------+----------------+----------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+----------------+--------------+
| def              | test            | proc08        |                1 | IN             | empno          | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL              | int()        | PROCEDURE    |
| def              | test            | proc08        |                 | OUT            | out_ename      | varchar   |                        |                     50 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(50)    | PROCEDURE    |
+------------------+-----------------+---------------+------------------+----------------+----------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+----------------+--------------+
2 rows in set (0.01 sec)

可以看到，对于同一个存储过程的多个参数，information_schema.parameters里面，有多条记录，这样其实比较方便的能够取出来我们想要的参数列表，而且对于每个参数，它包含的信息更多，同时它适用的版本更多。

总结：

作为一个开发同学，如何获取MySQL存储过程的输入输出参数，可能会有这方面的需求。

通过对比mysql.proc和infomation_schema.parameters表，我们不难看出来，通过后者，可以更高效的拿到我们想要的信息，而且兼容性更好，所以应当优先选择后者。

作为一名运维同学，可能需要考虑让你的业务方尽可能的将存储过程、函数之类的对象，从MySQL数据库中剥离出来，这样在运维的过程中，可能会更加方便，MySQL的性能会更好。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-08-29，如有侵权请联系 cloudcommunity@tencent.com 删除

存储