mysql元数据如何存储的_MySQL存储过程之函数及元数据

1.创建函数

1 CREATE FUNCTION factorial (n DECIMAL(3,0))2 RETURNS DECIMAL(20,0)3 DETERMINISTIC4 BEGIN5     DECLARE factorial DECIMAL(20,0) DEFAULT 1;6     DECLARE counter DECIMAL(3,0);7     SET counter =n;8 factorial_loop: REPEAT9        SET factorial = factorial *counter;10         SET counter = counter - 1;11       UNTIL counter = 1

12     ENDREPEAT;13     RETURNfactorial;14 END //

函数跟过程很相似，唯一需要指出的语法上的不同:创建函数后必须有RETURN语句指定返回值类型并返回相应类型的值.下面是函数使用的例子:

1 INSERT INTO t VALUES (factorial(pi)) //

2 SELECT s1, factorial (s1) FROM t //

3 UPDATE t SET s1 = factorial(s1) WHERE factorial(s1) < 5 //

注意在函数中访问表,这使得函数不如存储过程强大,以下是不能出现在函数中的限制:

ALTER 'CACHE INDEX'CALL COMMIT CREATE DELETE

DROP'FLUSH PRIVILEGES'GRANT INSERT KILL

LOCK OPTIMIZE REPAIR REPLACE REVOKE

ROLLBACK SAVEPOINT'SELECT FROM table'

'SET system variable' 'SET TRANSACTION'SHOW'START TRANSACTION' TRUNCATE UPDATE

以下指令是可以出现在函数中的:

'BEGIN END'DECLARE

IF

ITERATE

LOOP

REPEAT

RETURN'SET declared variable'WHILE

2. Metadata元数据

我们创建的过程或函数,都会保存在MySQL数据库中.如果要查看MySQL实际上保存了什么信息,有以下四种方法,分别是两个SHOW和两个SELECT语句:

1) Show: mysql> show create procedure p6//

+-------------+------------+--------------------------------------------------------------------+

| Procedure | sql_mode |            Create Procedure                                              |

+-------------+------------+--------------------------------------------------------------------+

|      p6      |                | CREATE PROCEDURE`db5`.`p6`(out p| int) set p = -5|

+-------------+------------+--------------------------------------------------------------------+

这同SHOW CREATE TABLE及其他类似MySQL语句一样.它并不返回你创建过程时设定的返回值,但大部分情况下已经够用了.

2) Show: mysql> SHOW PROCEDURE STATUS LIKE 'p6'// 第二种获得无数据信息的方法是执行SHOW PROCEDURE STATUS,这种方法可以返回更多信息的细节.

3) SELECT from mysql.proc: SELECT * FROM mysql.proc WHERE name = 'p6'// 这种方法提供的信息是最多的.

4) SELECT from information_schema:

1 mysql > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROMINFORMATION_SCHEMA.COLUMNS2 -> WHERE TABLE_NAME = 'ROUTINES';//

这是被推荐的方式,因为其他方式可能会出现错误:

1) 其他DBMS如SQL Server 2000,使用information_schema,只有MySQL才有SHOW方式.

2) 我们访问mysql.proc的权限是没有保证的,但有访问information_schema视图的权限,每个用户都有隐式的对当局者迷information_schema数据库的SELECT权限.

3) SELECT功能很多,可以计算表达式,分组,排序,产生可以获取信息的结果集.而这些功能SHOW没有.

下面是使用该方法的例子,首先使用SELECT information_schema来显示information_schema例程中有哪些列:

1 mysql > SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROMINFORMATION_SCHEMA.COLUMNS2 -> WHERE TABLE_NAME = 'ROUTINES';//

执行以上指令后会输出结果如下:

TABLE_NAME

COLUMN_NAME

COLUMN_TYPE

ROUTINES

SPECIFIC_NAME

varchar(64)

ROUTINES

ROUTINE_CATALOG

longtext

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

varbinary(19)

ROUTINES

LAST_ALTERED

varbinary(19)

ROUTINES

SQL_MODE

longtext

ROUTINES

ROUTINE_COMMENT

varchar(64)

ROUTINES

DEFINER

varchar(77)

知道了上面的表结构,就可以查询我们想要得到的信息,如下代码可查看数据库db6中定义的存储过程:

1 mysql > SELECT COUNT(*) FROMINFORMATION_SCHEMA.ROUTINES2 -> WHERE ROUTINE_SCHEMA = 'db6';//

ROUTINE_DEFINITION列的访问控制

在INFORMATION_SCHEMA中的ROUTINE_DEFINITION列是由过程或函数组成过程体获得的,因可能存在敏感信息而只对过程创建者可见.

CURRENT_USER<>INFORMATIN_SCHEMA.ROUTINES.DEFINER:如果对它使用SELECT的用户不是创建它的用户,则MySQL将返回null值,而不是ROUTINE_DEFINITION列.

SHOW PROCEDURE STATUS中的辅助子句

既然已列出INFORMATION_SCHEMA.ROUTINES中的列,就可以回去解释SHOW PROCEDURE STATUS的新细节:语法是: SHOW PROCEDURE STATUS [WHERE condition]; .特别注意的部分是:在WHERE子句中你必须使用INFORMATION_SCHEMA列的名字,结果显示的是SHOW PROCEDURE STATUS字段的名字.例如:

1 mysql> SHOW PROCEDURE STATUS WHERE Db = 'p';2 /*ERROR 1054 (42S22): Unknown column 'Db' in 'where clause'*/

3 mysql> SHOW PROCEDURE STATUS WHERE ROUTINE_NAME = 'p';