查看数据库metadata的方法

创建了很多的存储过程了，他们也都保存在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'// 这条语句显示了我们所有创建的存储过程，内容在这里我就不列出来了！！