如何查看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'// 这条语句显示了我们所有创建的存储过程，内容在这里我就不列出来了！！