mysql - #1305 - 功能 xxxx 不存在

Question

我是 mysql 的新手，我正在尝试将数据库从旧站点导入到新服务器，但我不断收到以下错误:

#1305 - FUNCTION data_mysql.sp_get_user_balance does not exist

这是我正在使用的 SQL:

DELIMITER $$
--
-- Functions
--
DROP FUNCTION IF EXISTS `sp_get_user_balance`$$
$$

DROP FUNCTION IF EXISTS `sp_get_user_balance_sys`$$
$$

DELIMITER ;

CREATE VIEW `transactions_detail` AS 
  select `tx`.`id` AS `id`
   , `tx`.`transaction_id` AS `transaction_id`
   ,date_format(`tx`.`tdate`,'%Y/%m/%d') AS `tdate`
   ,date_format(`tx`.`tdate`,'%T') AS `ttime`
   ,`tx`.`tdate` AS `tdatetime`
   ,`tx`.`sender_id` AS `sender_id`
   ,if((`t`.`transaction_type` = 'Withdrawal'),`sp_get_user_balance`(`tx`.`sender_id`),0) AS `sender_balance`
   ,`tx`.`receiver_id` AS `receiver_id`
   ,`st`.`username` AS `sender_name`
   ,`rt`.`username` AS `receiver_name`
   ,`tx`.`invoice_number` AS `invoice_number`
   ,`p`.`name` AS `product_name`
   ,`p`.`price` AS `product_price`
   ,cast(if(isnull(`tx`.`sender_id`),`tx`.`amount`,-(`tx`.`amount`)) as decimal(11,2)) AS `oamount`
   ,cast(`tx`.`amount` as decimal(11,2)) AS `amount`
   ,cast(if((`tx`.`receiver_id` is not null)
    ,(`tx`.`amount` - `tx`.`fees`),0) as decimal(11,2)) AS `nets`
   ,cast(`tx`.`fees` as decimal(11,2)) AS `fees`
   ,`s`.`transaction_status` AS `status`
   ,`t`.`transaction_type` AS `type`
   ,ifnull(`tx`.`comments`,'--') AS `comments`
   ,`tx`.`can_view` AS `can_view`
   ,`tx`.`can_refund` AS `can_refund`
   ,`tx`.`cc_email` AS `cc_email`
   ,`tx`.`cc_name` AS `cc_name` 
 from (((((`t_transactions` `tx` 
     join `s_transaction_type` `t` on((`tx`.`transaction_type_id` = `t`.`id`))) 
     join `s_transaction_status` `s` on((`tx`.`transaction_status_id` = `s`.`id`))) 
     left join `t_products` `p` on((`tx`.`product_id` = `p`.`id`))) 
     left join `t_members` `st` on((`tx`.`sender_id` = `st`.`id`))) 
    left join `t_members` `rt` on((`tx`.`receiver_id` = `rt`.`id`))
 );   

如有任何帮助，我们将不胜感激。

Answer 1

CREATE VIEW 语句使用您在上一行中删除的 sp_get_user_balance 函数。

您缺少重建它的 CREATE FUNCTION 语句。

您可以使用 SHOW CREATE FUNCTION sp_get_user_balance 从旧站点导出它。

或者，如果它在导入文件的后面，您可以重新排序语句，使其位于 transactions_detail View 定义之前。