我想創建一個與我的舊函式相同的函式。

當我創建一個表時，我可以使用 `AS 到其他表，如下所示：

CREATE TABLE dummy2 AS SELECT * FROM dummy1;

我可以對函式做同樣的事情嗎？

你可以玩一些遊戲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

引用自：https://dba.stackexchange.com/questions/28304