mysql - 创建函数在 mysql 中抛出错误，有人可以帮助我吗？

Question

我只是想创建一个函数，这意味着检查表、函数或 View 是否存在于 mysql 数据库中。但是我的数据库中出现了一些错误。有人可以帮帮我吗？

DELIMITER $$
DROP FUNCTION IF EXISTS check_if_exists$$
CREATE FUNCTION check_if_exists
  (
    object_name VARCHAR(100),
    db_name     VARCHAR(100),
    object_type ENUM('t', 'f', 'v', 'p')
)
RETURNS INT
BEGIN
IF (object_type='t') THEN
SELECT COUNT(1) INTO @f_result
from information_schema.TABLES as t1
where t1.TABLE_SCHEMA=db_name
      and t1.TABLE_NAME=object_name;
ELSE IF (object_type='f') THEN
select count(1) INTO @f_result
FROM information_schema.ROUTINES as info
WHERE info.ROUTINE_SCHEMA = db_name
      AND info.ROUTINE_TYPE = 'FUNCTION' AND info.ROUTINE_NAME = object_name;
ELSE IF (object_type='v') THEN
select count(1) into @f_result
from information_schema.VIEWS as t1
where t1.TABLE_SCHEMA=db_name and t1.TABLE_NAME=object_name;
ELSE IF (object_type='p') THEN
SELECT COUNT(1) INTO @f_result
FROM information_schema.ROUTINES as info
WHERE info.ROUTINE_SCHEMA = db_name
      AND info.ROUTINE_TYPE = 'PROCEDURE'
      AND info.ROUTINE_NAME = object_name;
END IF;
return (@f_result);
END$$
delimiter ;

另外，mysql的信息:

mysql  Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using readline 5.1

错误信息是:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 31

如您所见，错误消息没有帮助。这个函数的定义不依赖于任何用户数据库。所以您可以在自己的 DBMS 中尝试。

Answer 1

您开始了四个 IF 语句，但最后只有一个 END IF。

关于 '' 附近的语法错误的错误消息表明它解析到语句的末尾，期望找到更多语法(如平衡 END IF剩余的嵌套 IF 语句)，但没有找到它。语法错误试图通过向您显示错误后语句的剩余部分中存在哪些文本来为您提供上下文，但如果它在发现错误之前到达末尾，则没有后续文本可报告。

您可以考虑使用 CASE statement相反:

DELIMITER $$
DROP FUNCTION IF EXISTS check_if_exists$$
CREATE FUNCTION check_if_exists (
  object_name VARCHAR(100),
  db_name     VARCHAR(100),
  object_type ENUM('t', 'f', 'v', 'p')
)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE f_result INT DEFAULT 0;
    CASE object_type
    WHEN 't' THEN
        SELECT COUNT(1) INTO f_result
        FROM information_schema.TABLES AS t1
        WHERE t1.TABLE_SCHEMA = db_name 
          AND t1.TABLE_NAME = object_name;
    WHEN 'f' THEN
        SELECT COUNT(1) INTO f_result
        FROM information_schema.ROUTINES AS info
        WHERE info.ROUTINE_SCHEMA = db_name 
          AND info.ROUTINE_TYPE = 'FUNCTION' 
          AND info.ROUTINE_NAME = object_name;
    WHEN 'v' THEN
        SELECT COUNT(1) INTO f_result
        FROM information_schema.VIEWS AS t1
        WHERE t1.TABLE_SCHEMA = db_name 
          AND t1.TABLE_NAME = object_name;
    WHEN 'p' THEN
        SELECT COUNT(1) INTO f_result
        FROM information_schema.ROUTINES as info
        WHERE info.ROUTINE_SCHEMA = db_name 
          AND info.ROUTINE_TYPE = 'PROCEDURE' 
          AND info.ROUTINE_NAME = object_name;
    END CASE;
    RETURN (f_result);
END$$
DELIMITER ;

---

回复你的评论:

I am trying to use if...else if...else like any other language. There is no else if in mysql?

不是您使用的方式。在标准 SQL 中，不存在无限数量的 else-if 子句的“阶梯”。

但是许多语言允许 else block 包含另一个 if/then/else 语句。所以你可以制作复杂的分支代码。但是您必须正确终止每个 if/then/else 语句。

IF ... THEN /* start 1st statement */
ELSE
    IF ... THEN /* start 2nd statement */
    ELSE
        IF ... THEN /* start 3rd statement */
        ELSE
        END IF /* end 3rd statement */
    END IF /* end 2nd statement */
END IF /* end 1st statement */

允许梯子的语言:

• Perl (elsif)
• ruby (elsif)
• PHP(其他)
• Python (elif)
• 基本(elseif)
• PL/SQL (elsif)
• PL/pgSQL (elsif)
• F#(elif)

不允许阶梯但允许嵌套控制结构的语言:

• C
• C++
• C#
• 目标-C
• Java
• Javascript
• ANSI SQL、Transact-SQL
• 帕斯卡，德尔福
• 哇
• 斯卡拉
• haskell
• R
• swift
• 飞镖
• 去