Mysql Procedure create_synonym_db - HelloWorld开发者社区

这个存储过程的含义是复制一个数据库，将其数据表变为视图。这是Mysql sys数据库中的存储过程

Mysql Procedure create_synonym_db

CREATE DEFINER=`mysql.sys`@`localhost` PROCEDURE `create_synonym_db`( IN in_db_name VARCHAR(64),  IN in_synonym VARCHAR(64) )
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
    COMMENT '\n Description\n \n Takes a source database name and synonym name, and then creates the \n synonym database with views that point to all of the tables within\n the source database.\n \n Useful for creating a "ps" synonym for "performance_schema",\n or "is" instead of "information_schema", for example.\n \n Parameters\n \n in_db_name (VARCHAR(64)):\n The database name that you would like to create a synonym for.\n in_synonym (VARCHAR(64)):\n The database synonym name.\n \n Example\n \n mysql> SHOW DATABASES;\n +--------------------+\n | Database           |\n +--------------------+\n | information_schema |\n | mysql              |\n | performance_schema |\n | sys                |\n | test               |\n +--------------------+\n 5 rows in set (0.00 sec)\n \n mysql> CALL sys.create_synonym_db(''performance_schema'', ''ps'');\n +---------------------------------------+\n | summary                               |\n +---------------------------------------+\n | Created 74 views in the `ps` database |\n +---------------------------------------+\n 1 row in set (8.57 sec)\n \n Query OK, 0 rows affected (8.57 sec)\n \n mysql> SHOW DATABASES;\n +--------------------+\n | Database           |\n +--------------------+\n | information_schema |\n | mysql              |\n | performance_schema |\n | ps                 |\n | sys                |\n | test               |\n +--------------------+\n 6 rows in set (0.00 sec)\n \n mysql> SHOW FULL TABLES FROM ps;\n +------------------------------------------------------+------------+\n | Tables_in_ps                                         | Table_type |\n +------------------------------------------------------+------------+\n | accounts                                             | VIEW       |\n | cond_instances                                       | VIEW       |\n | events_stages_current                                | VIEW       |\n | events_stages_history                                | VIEW       |\n ...\n '
BEGIN DECLARE v_done bool DEFAULT FALSE; 
 DECLARE v_db_name_check VARCHAR(64);
 DECLARE v_db_err_msg TEXT; DECLARE v_table VARCHAR(64); 
 DECLARE v_views_created INT DEFAULT 0;  
 DECLARE db_doesnt_exist CONDITION FOR SQLSTATE '42000'; 
 DECLARE db_name_exists CONDITION FOR SQLSTATE 'HY000';  
 DECLARE c_table_names CURSOR FOR  
 SELECT TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = in_db_name; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
 SELECT 
    SCHEMA_NAME
INTO v_db_name_check FROM
    INFORMATION_SCHEMA.SCHEMATA
WHERE
    SCHEMA_NAME = in_db_name; 
    IF v_db_name_check IS NULL THEN SET v_db_err_msg = CONCAT('Unknown database ', in_db_name); 
    SIGNAL SQLSTATE 'HY000' 
    SET MESSAGE_TEXT = v_db_err_msg;
    END IF;
SELECT 
    SCHEMA_NAME
INTO v_db_name_check FROM
    INFORMATION_SCHEMA.SCHEMATA
WHERE
    SCHEMA_NAME = in_synonym; 
    IF v_db_name_check = in_synonym THEN SET v_db_err_msg = CONCAT('Can\'t create database ', in_synonym, '; database exists'); 
    SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = v_db_err_msg; 
    END IF;  
    SET @create_db_stmt := CONCAT('CREATE DATABASE ', sys.quote_identifier(in_synonym));
    PREPARE create_db_stmt FROM @create_db_stmt; EXECUTE create_db_stmt; DEALLOCATE PREPARE create_db_stmt; 
    SET v_done = FALSE; OPEN c_table_names;
    c_table_names: LOOP FETCH c_table_names INTO v_table;
    IF v_done THEN LEAVE c_table_names; END IF;  
    SET @create_view_stmt = CONCAT( 'CREATE SQL SECURITY INVOKER VIEW ', sys.quote_identifier(in_synonym), '.', sys.quote_identifier(v_table), ' AS SELECT * FROM ', sys.quote_identifier(in_db_name), '.', sys.quote_identifier(v_table) );
    PREPARE create_view_stmt FROM @create_view_stmt; EXECUTE create_view_stmt; DEALLOCATE PREPARE create_view_stmt; 
    SET v_views_created = v_views_created + 1; END LOOP; CLOSE c_table_names;
    SELECT 
    CONCAT('Created ',
            v_views_created,
            ' view',
            IF(v_views_created != 1, 's', ''),
            ' in the ',
            sys.quote_identifier(in_synonym),
            ' database') AS summary; 
END;

View Code

1.存储过程的调用

CALL sys.create_synonym_db('INFORMATION_SCHEMA', 'info1');
SHOW DATABASES ;

效果是创建了一个Info1的数据库；INFORMATION_SCHEMA里面有多个数据表，但没有视图，创建的Info1中有视图没有表。

INFORMATION_SCHEMA数据库是MySQL系统自带的数据库，它提供了数据库元数据的访问方式。感觉information_schema就像是MySQL实例的一个百科全书，记录了数据库当中大部分我们需要了结的信息，比如字符集，权限相关，数据库实体对象信息，外检约束，分区，压缩表，表信息，索引信息，参数，优化，锁和事物等等。通过information_schema我们可以窥透整个MySQL实例的运行情况，可以了结MySQL实例的基本信息，甚至优化调优，维护数据库等。

Mysql Procedure create_synonym_db

CREATE DEFINER=`mysql.sys`@`localhost` PROCEDURE `create_synonym_db`( IN in_db_name VARCHAR(64),  IN in_synonym VARCHAR(64) )
    MODIFIES SQL DATA
    SQL SECURITY INVOKER

DEFINER :存储过程的创建者；决定用户权限，Mysql对存储过程具有3个权限：创建 CREATE ROUTINE，编辑或者删除 ALTER ROUTINE，运行EXECUTE。

INVOKER用于指定哪些用户有调用存储过程的权限。SQL SECURITY INVOKER 意思是SQL Security指定DEFINER 表示执行者拥有创建者的权限。

MODIFIES SQL DATA:表示子程序包含写数据的语句

入口参数有两个 in_db_name 需要复制的数据库（源），in_synonym 需要新建的数据库(目的)。

3. BEGIN END是procedure的主体程序

DECLARE v_done bool DEFAULT FALSE; 
 DECLARE v_db_name_check VARCHAR(64);
 DECLARE v_db_err_msg TEXT; DECLARE v_table VARCHAR(64); 
 DECLARE v_views_created INT DEFAULT 0;  
 DECLARE db_doesnt_exist CONDITION FOR SQLSTATE '42000'; 
 DECLARE db_name_exists CONDITION FOR SQLSTATE 'HY000';  
 DECLARE c_table_names CURSOR FOR  SELECT TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = in_db_name; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

1）DECLARE 声明变量。语法： DECLARE 变量名数据类型，默认值；

条件定义： DECLARE 变量名 CONDITION FOR 变量值；
异常处理：DECLARE harder_type FRO SQLSTATA SET operation;

harder_type : CONTINUE:在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句。

EIXT :在处理器操作完成之后，存储过程会终止，并将控制返回调用者。

UNDO：在处理器操作完成之后，会滚回存储过程中执行过的SQL操作，操作完成之后，终止存储过程。

系统定义的异常状态 SQLSTATA

NOT FOUND : 标识导致SQLCODE值为＋100或者SQLSATE值为02000的异常。这个异常通常在SELECT没有返回行的时候出现。

SQLEXCEPTIOIN: 标识导致SQLCODE值为负的异常。

SQLWARNING: 标识导致警告异常或者导致正100以上的SQLCODE值的异常。

例子：DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNINGSET stmt ='ABORTED';//在出现SQLWARNING异常时也终止存储过程，并将名为stmt的变量设为“ABORTED”

4)定义游标 DECLARE 游标名称 CURSOR FOR 集合（一般为Select语句）

游标：为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

游标使用步骤声明游标：DECLARE 游标名称 CURSOR FOR 集合（一般为Select语句）

打开游标：OPEN c_table_names;

使用游标操作数据：

　　 c_table_names: LOOP FETCH c_table_names INTO v_table;
    IF v_done THEN LEAVE c_table_names; END IF;  
    SET @create_view_stmt = CONCAT( 'CREATE SQL SECURITY INVOKER VIEW ', sys.quote_identifier(in_synonym), '.', sys.quote_identifier(v_table), ' AS SELECT * FROM ', sys.quote_identifier(in_db_name), '.', sys.quote_identifier(v_table) );
    PREPARE create_view_stmt FROM @create_view_stmt; EXECUTE create_view_stmt; DEALLOCATE PREPARE create_view_stmt; 
    SET v_views_created = v_views_created + 1; END LOOP;

关闭游标：CLOSE c_table_names;

4.下面一段代码的意思是，从INFORMATION_SCHEMA.SCHEMATA中查找SCHEMA_NAME为in_db_name（传入的参数：数据库名），并将其赋值给v_db_name_check中。

如果v_db_name_check 为空则设置v_db_err_msg 为‘Unknown database’

SELECT  SCHEMA_NAME INTO v_db_name_check FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = in_db_name; 
    IF v_db_name_check IS NULL THEN SET v_db_err_msg = CONCAT('Unknown database ', in_db_name); 
    SIGNAL SQLSTATE 'HY000' 
    SET MESSAGE_TEXT = v_db_err_msg;
    END IF;

SELECT col_names INTO var_names FROM TABLE :给变量复制，用来将查询返回的一行的各个列值保存到局部变量中

SELECT col_names INTO table_name FROM table :复制列到新表中。

另外需要注意的是如果拼接后的语句中有字符串，那么四个单引号表示一个引号，这是很重要的，谨记于心。

例如：'''',name2,'''' 拼接后的形式为 ‘name2’。

SELECT  SCHEMA_NAME INTO v_db_name_check FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = in_synonym; 
    IF v_db_name_check = in_synonym THEN SET v_db_err_msg = CONCAT('Can\'t create database ', in_synonym, '; database exists'); 
    SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = v_db_err_msg; 
    END IF;

MESSAGE_TEXT的效果。

Mysql Procedure create_synonym_db

5.创建Sql语句，并执行；

SET @create_db_stmt := CONCAT('CREATE DATABASE ', sys.quote_identifier(in_synonym));
    PREPARE create_db_stmt FROM @create_db_stmt; EXECUTE create_db_stmt; DEALLOCATE PREPARE create_db_stmt;

PREPARE statement_name FROM preparable_SQL_statement; /*定义*/ preparable_SQL_statement为Sql语句
EXECUTE statement_name [USING @var_name [, @var_name] …]; /*执行预处理语句*/
{DEALLOCATE | DROP} PREPARE statement_name /*删除定义*/ ;

6.游标操作

c_table_names: 
    LOOP FETCH c_table_names INTO v_table;
    IF v_done THEN LEAVE c_table_names; END IF;  
    SET @create_view_stmt = CONCAT( 'CREATE SQL SECURITY INVOKER VIEW ', sys.quote_identifier(in_synonym), '.', sys.quote_identifier(v_table), ' AS SELECT * FROM ', sys.quote_identifier(in_db_name), '.', sys.quote_identifier(v_table) );
    PREPARE create_view_stmt FROM @create_view_stmt; EXECUTE create_view_stmt; DEALLOCATE PREPARE create_view_stmt; 
    SET v_views_created = v_views_created + 1; 
    END LOOP;

LOOP .... END LOOP 循环体名称为c_table_name，

FETCH 集合 INTO 变量名，诸葛取出数据到变量中。

跳出循环体的条件 v_done = true; 个人理解当从c_table_names 中取不到数据的时候，捕获到NOT FOUND的异常，v_done=true;退出循环。

如果取到值，则继续往下执行， @create_view_satmt =' CREATE SQL SECURITY INVOKER VIEW `info1`.`CHARACTER_SETS` as select * from `INFORMATION_SCHEMA`.`CHARACTER_SETS`;'

执行Sql语句，至此完成根据in_db_name中的表结构创建，新数据库in_synonym中的View视图。

7. 执行完之后显示结果

SELECT 
    CONCAT('Created ',
            v_views_created,
            ' view',
            IF(v_views_created != 1, 's', ''),
            ' in the ',
            sys.quote_identifier(in_synonym),
            ' database') AS summary;

最后执行的Sql语句为： select 'Created 30 views in the `info1` database as summary';

Mysql Procedure create_synonym_db