mysql - SHOW CREATE PROCEDURE procedure_name 不显示过程内容

Question

你好，我正在努力解决我必须查看存储过程内容的问题，因为我已发出命令以获取过程的名称

mysql> SHOW PROCEDURE STATUS;
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db  | Name                            | Type      | Definer   | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| vmp | ccontrol_table | PROCEDURE | user1@% | 2015-11-10 01:01:51 | 2015-11-10 01:01:51 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----+---------------------------------+-----------+-----------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.27 sec)

它向我显示了名称为 control_table 的过程要查看程序，我使用了SHOW CREATE PROCEDURE control_table，它以这种格式显示，而不是Content

mysql> SHOW CREATE PROCEDURE control_table;
+---------------+----------+------------------+----------------------+----------------------+--------------------+
| Procedure     | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+---------------+----------+------------------+----------------------+----------------------+--------------------+
| control_table |          | NULL             | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------+----------+------------------+----------------------+----------------------+--------------------+
1 row in set (0.28 sec)

我已经使用 MySQL Client 和 mySQL Client Workbench 执行了这个程序，但无法查看程序

Answer 1

过程存储在 information_schema.routines 中。 show create 在这里看到了 control_table 的条目，但是 routine_definition 是空的——这表明有人修改了 information_schema.routines。您可以通过简单的选择检查 information_schema.routines 的内容 -

select routine_name,routine_definition 
from information_schema.ROUTINES
where routine_name = 'control_table'

== 这是输出=====

mysql> select routine_name,routine_definition 
    -> from information_schema.ROUTINES
    -> where routine_name = 'control_table'
    -> ;
+---------------------------------+--------------------+
| routine_name                    | routine_definition |
+---------------------------------+--------------------+
| control_table | NULL               |
+---------------------------------+--------------------+
1 row in set (0.28 sec)