你可以玩一些游戏mysql.proc，它是所有存储过程和存储函数的物理家园。

首先，这是我电脑上的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| david              |
| dufran             |
| jimelliott         |
| junk               |
| karto              |
| mysql              |
| performance_schema |
| preeti             |
| rolando            |
| sandro             |
| telwit             |
| telwit_french      |
| test               |
| user1162541        |
| yellowseason       |
+--------------------+
16 rows in set (0.00 sec)

mysql>

rolando让我们在名为的数据库中创建一个存储函数GetUnixTimeStamp：

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `GetUnixTimeStamp` $$
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE DEFINER=`root`@`localhost` FUNCTION `GetUnixTimeStamp`(x INT) RETURNS int(11)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN UNIX_TIMESTAMP();
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT rolando.GetUnixTimeStamp(9);
+-----------------------------+
| rolando.GetUnixTimeStamp(9) |
+-----------------------------+
|                  1352263567 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

让我们尝试根据函数本身来雕刻 CREATE FUNCTION SQL 语句

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `',db,'`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

这会产生什么？

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `',db,'`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `rolando`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

到目前为止还可以，很好。让我们更改 SQL 以将此函数放入数据库中david

mysql> SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
    -> '` ',type,' `david`.`',name,'`(',param_list,') ',
    -> IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
    -> IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
    -> INTO @ProcedureCreationSQL from mysql.proc
    -> WHERE db='rolando' AND name='GetUnixTimeSTamp';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @ProcedureCreationSQL\G
*************************** 1. row ***************************
@ProcedureCreationSQL: CREATE DEFINER=`root`@`localhost` FUNCTION `david`.`GetUnixTimeStamp`(x INT) RETURNS int(11) DETERMINISTIC BEGIN
    RETURN UNIX_TIMESTAMP();
END
1 row in set (0.00 sec)

mysql>

好，很好。我们可以制作SQL。我可以执行新功能的创建吗？？？

echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

函数存在？？？

mysql> show create function david.getunixtimestamp\G
*************************** 1. row ***************************
            Function: getunixtimestamp
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `getunixtimestamp`(x INT) RETURNS int(11)
    DETERMINISTIC
BEGIN     RETURN UNIX_TIMESTAMP(); END
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

最大的问题：新复制的功能是否运行？

mysql> select david.getunixtimestamp(8);
+---------------------------+
| david.getunixtimestamp(8) |
+---------------------------+
|                1352265404 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

万岁！这就是您如何将函数从一个数据库复制到另一个数据库的方法。

最后一个问题：这一切都值得吗？？？

概括

这是生成函数创建的原始SQL

SELECT CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),
'` ',type,' `david`.`',name,'`(',param_list,') ',
IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',
IF(is_deterministic='YES','DETERMINISTIC',''),' ',body)
INTO @ProcedureCreationSQL from mysql.proc
WHERE db='rolando' AND name='GetUnixTimeSTamp';
SELECT @ProcedureCreationSQL\G

这也将创建存储过程

这是复制操作，必须从调用 mysql 客户端的操作系统级别进行

echo DELIMITER $$ > newproc.txt
mysql -uroot --delimiter="$$" -ANe"SELECT REPLACE(CONCAT('CREATE DEFINER=`',REPLACE(definer,'@','`@`'),'` ',type,' `david`.`',name,'`(',param_list,') ',IF(type='FUNCTION',CONCAT('RETURNS ',returns),''),' ',IF(is_deterministic='YES','DETERMINISTIC',''),' ',body),'\n',' ') from mysql.proc WHERE db='rolando' AND name='GetUnixTimeSTamp';" >> newproc.txt
echo $$ >> newproc.txt
mysql -uroot < newproc.txt

第 1 步：在创建函数/过程时获取 SQL 脚本

SHOW CREATE FUNCTION my_schema_1.my_function;

我们会得到一个看起来像的结果

CREATE DEFINER=`mysqladmin`@`%` FUNCTION `my_function`
    (i_paramter1 char(255), i_paramter2 int) 
    RETURNS char(3) CHARSET utf8 COLLATE utf8_bin
READS SQL DATA
BEGIN

  DECLARE result_value char(3);

  SELECT if(count(*) > 0, 'Yes', 'No') into result_value
    FROM
      ...
    where 
      ...
  ;
return result_value;

END

步骤 2。

• 添加目标架构，my_schema_2对代码说
• 在开始添加一行delimiter $$
• 在最后一行之后添加一行$$

作为

delimiter $$
CREATE DEFINER=`mysqladmin`@`%` FUNCTION `my_schema_2`.`my_function`
...
... (same as above)
...
...
END
$$

步骤 3。运行生成的 SQL Script 以在目标模式中再次生成函数my_schema_2

在此处查看帮助文档

• MySQL SHOW CREATE FUNCTION 语法
• MySQL SHOW CREATE PROCEDURE 语法