如何查看mysql的元数据
创建了很多的存储过程了，他们也都保存在mysql数据库中，如果我们要查看mysql实际上保存了什么信息，可以有四种方法，两种使用show语句，两种使用select语句，他们的格式如下：
SHOW CREATE PROCEDURE / SHOW CREATE FUNCTION
SHOW PROCEDURE STATUS / SHOW FUNCTION STATUS
SELECT FROM MYSQL.PROC
SELECT FROM INFORMATION_SCHEMA
下面针对以上几种语句举例说明一下。
1、使用show create procedure获得存储过程的信息，和show create table等mysql语法类似，这条语句不返回创建时设定的返回值，而是返回过程的语句信息：
mysql> show create procedure pro1//
+-----------+----------+-------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+-------------------------------------------------------------------------------+
| pro1 | | CREATE DEFINER=`root`@`localhost` PROCEDURE `pro1`()
begin
declare x int;
declare y int;
set x=2;
set y=2;
insert into t1(filed) values(a);
select filed * a from t1 where filed >=b;
end |
+-----------+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create procedure p1//
+-----------+----------+----------------------------------+
| Procedure | sql_mode | Create Procedure |
+-----------+----------+----------------------------------+
| p1 | | CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
select * from db1 |
+-----------+----------+----------------------------------+
1 row in set (0.00 sec)
2、执行show procedure status，这种方法可以返回更多信息的细节：
mysql> show procedure status like 'pro1'//
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| db1 | pro1 | PROCEDURE | root@localhost | 2010-06-26 10:20:45 | 2010-06-26 10:20:45 | DEFINER | |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)
mysql> show procedure status like 'p1'//
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| db1 | p1 | PROCEDURE | root@localhost | 2010-06-24 15:52:53 | 2010-06-24 15:52:53 | DEFINER | |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)
3、使用select可以获得更多的信息
mysql> select * from mysql.proc where name='pro1'//
+-----+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+--------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
| db | name | type | specific_name | language | sql_data_access | is_deterministic | security_type | param_list | returns | body | definer | created | modified | sql_mode | comment |
+-----+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+--------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
| db1 | pro1 | PROCEDURE | pro1 | SQL | CONTAINS_SQL | NO | DEFINER | | | begin
declare x int;
declare y int;
set x=2;
set y=2;
insert into t1(filed) values(a);
select filed * a from t1 where filed >=b;
end | root@localhost | 2010-06-26 10:20:45 | 2010-06-26 10:20:45 | | |
+-----+------+-----------+---------------+----------+-----------------+------------------+---------------+------------+---------+--------------------------------------------------------------+----------------+---------------------+---------------------+----------+---------+
1 row in set (0.01 sec)
4、以上三种方式并不是特别的完善，其实最好的方式是select from information_schema。这种方式是“ANSI/ISO标准”的方式完成工作。这是最好的实现方式，其他的方式可能会出现错误。
在其他的DBMS中，比如SQL server2000，使用information_schema，而只有mysql使用show方式。
访问mysql.proc的特权是不安全的，因为用户访问information_schema视图的特权，每个用户默认对information_schema数据库有select权限。
select功能很多，可以计算表达式，分组，排序，产生可以获取信息的结果集，而这些功能show没有。
所以使用select还是最好的！下面看几个例子，首先使用select information_schema来显示information_schema例程中有哪些列：
mysql> select table_name,column_name,column_type from information_schema.columns where table_name='routines'//
+------------+--------------------+--------------+
| table_name | column_name | column_type |
+------------+--------------------+--------------+
| ROUTINES | SPECIFIC_NAME | varchar(64) |
| ROUTINES | ROUTINE_CATALOG | varchar(512) |
| ROUTINES | ROUTINE_SCHEMA | varchar(64) |
| ROUTINES | ROUTINE_NAME | varchar(64) |
| ROUTINES | ROUTINE_TYPE | varchar(9) |
| ROUTINES | DTD_IDENTIFIER | varchar(64) |
| ROUTINES | ROUTINE_BODY | varchar(8) |
| ROUTINES | ROUTINE_DEFINITION | longtext |
| ROUTINES | EXTERNAL_NAME | varchar(64) |
| ROUTINES | EXTERNAL_LANGUAGE | varchar(64) |
| ROUTINES | PARAMETER_STYLE | varchar(8) |
| ROUTINES | IS_DETERMINISTIC | varchar(3) |
| ROUTINES | SQL_DATA_ACCESS | varchar(64) |
| ROUTINES | SQL_PATH | varchar(64) |
| ROUTINES | SECURITY_TYPE | varchar(7) |
| ROUTINES | CREATED | datetime |
| ROUTINES | LAST_ALTERED | datetime |
| ROUTINES | SQL_MODE | longtext |
| ROUTINES | ROUTINE_COMMENT | varchar(64) |
| ROUTINES | DEFINER | varchar(77) |
+------------+--------------------+--------------+
20 rows in set (0.00 sec)
格式整齐吧，当我们要看information_schema视图时，从information_schema中select信息，就像从tables和
columns获取一样，获取的是元数据的数据元素。再比如看一下数据库db1中已经定义了多少存储过程：
mysql> select count(*) from information_schema.routines where routine_schema='db1'//
+----------+
| count(*) |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)
已经定义了35条。
Access control for the ROUTINE_DEFINITION column ROUTINE_DEFINITION列的访问控制
在INFORMATION_SCHEMA中的ROUTINE_DEFINITION列是由过程或函数组成过程体获得的。这里可能会有一些敏感信息，因此只对过程创建者可见。
CURRENT_USER <> INFORMATION_SCHEMA.ROUTINES.DEFINER：如果对它使用SELECT的用户不是创建它的用户，那么mysql将返回NULL值，而不是ROUTINE_DEFINITION列。
显示过程状态子句中的辅助句子
既然能够显示information_schema.routines中的列，那么现在可以详细解释一下show procedure status中显示的细节，首先看语法：
show procedure status[where condition];语句中的条件判断和select语句一样，如果为真，则在输出中返回行，需要注意，在where子句中必须使用information_schema列的名字，在结果中显示show procedure status字段的名字。例如：
mysql> show procedure status where name='pro1'//
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| db1 | pro1 | PROCEDURE | root@localhost | 2010-06-26 10:20:45 | 2010-06-26 10:20:45 | DEFINER | |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)
mysql> show procedure status where name='p1'//
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
| db1 | p1 | PROCEDURE | root@localhost | 2010-06-24 15:52:53 | 2010-06-24 15:52:53 | DEFINER | |
+-----+------+-----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.01 sec)
mysql> show procedure status where db='db1'// 这条语句显示了我们所有创建的存储过程，内容在这里我就不列出来了！！
