mysql - 无法创建存储函数

Question

当我尝试运行此代码时:

DELIMITER $$

create function formatJJhhMMssToSuffixes(duree varchar(100))
returns varchar(100)
begin
    DECLARE
        hhmmss VARCHAR(100);
    if duree = '' then
        return '';
  else if cast(substring_index(duree, ':', 1) as int) = 0 then
        set hhmmss = substring_index(duree, ':', -3);
        return concat(substring_index(hhmmss, ':', 1), 'h ', substring(hhmmss, 4, 2), 'm ', substring_index(hhmmss, ':', -1), 's');
    else
        return concat(substring_index(duree, ':', 1), 'jours ', substring(duree, 4, 2), 'h ', substring(duree, 7, 2), 'm ', substring_index(duree, ':', -1), 's');
    end IF;
end $$

DELIMITER ;

然后我收到错误:

[SQL] 

create function formatJJhhMMssToSuffixes(duree varchar(100))
returns varchar(100)
begin
    DECLARE
        hhmmss VARCHAR(100);
    if duree = '' then
        return '';
  else if cast(substring_index(duree, ':', 1) as int) = 0 then
        set hhmmss = substring_index(duree, ':', -3);
        return concat(substring_index(hhmmss, ':', 1), 'h ', substring(hhmmss, 4, 2), 'm ', substring_index(hhmmss, ':', -1), 's');
    else
        return concat(substring_index(duree, ':', 1), 'jours ', substring(duree, 4, 2), 'h ', substring(duree, 7, 2), 'm ', substring_index(duree, ':', -1), 's');
    end IF;
end $$

DELIMITER ;

[Err] 1064 - 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 'int) = 0 then
        set hhmmss = substring_index(duree, ':', -3);
        re' at line 5

我的代码有什么问题？

Answer 1

CAST 没有选项 INT，请尝试使用 SIGNED，

在此处查找各种CAST选项 https://www.w3schools.com/sql/func_mysql_cast.asp

编辑了你的代码，试试这个

create function formatJJhhMMssToSuffixes(duree varchar(100))
returns varchar(100)
begin
    DECLARE
        hhmmss VARCHAR(100);
    if duree = '' then
        return '';
    else if cast(substring_index(duree, ':', 1) as signed) = 0 then
        set hhmmss = substring_index(duree, ':', -3);
        return concat(substring_index(hhmmss, ':', 1), 'h ', substring(hhmmss, 4, 2), 'm ', substring_index(hhmmss, ':', -1), 's');
    else
        return concat(substring_index(duree, ':', 1), 'jours ', substring(duree, 4, 2), 'h ', substring(duree, 7, 2), 'm ', substring_index(duree, ':', -1), 's');
    end IF;
end $$