时间戳

首页 > 解决方案 > 我的 mariadb-10.3 版本出错

raspberry-pi - 我的 mariadb-10.3 版本出错

问题描述

我正在寻找一个在数据不存在时插入数据的函数。

我试过 IF not exists 但我认为我的 mariadb 版本不足以处理这个问题。

所以我正在尝试NOT IN，但遇到同样的问题......

CREATE DEFINER=`root`@`localhost` PROCEDURE `close_ticket`(IN idT INT,IN accuse VARCHAR(45),IN lienAccuse VARCHAR(60), IN lienG VARCHAR(60),IN jours INT,IN reason VARCHAR(250), IN isclosed BOOLEAN)
BEGIN
    INSERT INTO bot_onet.accuse (`Pseudo`,`lienFaceit`) SELECT accuse,lienAccuse
        WHERE lienAccuse NOT IN(SELECT `lienFaceit` FROM `bot_onet`.`accuse` );
    SELECT @ID :=  `idAccuse` FROM bot_onet.accuse WHERE `lienFaceit` = lienAccuse;
    INSERT INTO bot_onet.ticket_has_accuse (`Ticket_idTicket`,`Accuse_idAccuse`,`duree`,`timecode`,`unbaned`,`raison`) VALUES (idT,@ID,jours,unix_timestamp(),FALSE,reason) ;
    UPDATE bot_onet.ticket SET `Fermeture` = unix_timestamp(),`closed` = isclosed, `LienGame` = LienG WHERE idTicket = idT;
END

创建错误的一段代码

INSERT INTO `bot_onet`.`Accuse` (`Pseudo`,`lienFaceit`) SELECT 'C0casio45','test' 
WHERE 'C0casio45' NOT IN(SELECT `Pseudo` FROM `bot_onet`.`Accuse`)

错误代码

09:52:12    
INSERT INTO bot_onet.Accuse (`Pseudo`,`lienFaceit`) SELECT 'test','faceit/test'   
WHERE lienAccuse NOT IN(SELECT lienAccuse FROM bot_onet.Accuse )    Error Code: 1064. 
You have an error in your SQL syntax; 
check the manual that corresponds to your MariaDB server version for the right syntax to use near 
'WHERE lienAccuse NOT IN(SELECT lienAccuse FROM bot_onet.Accuse )' at line 2    
0.110 sec

如果我使用 from 语句，它不会返回错误，但他也没有插入数据

INSERT INTO `bot_onet`.`Accuse` (`Pseudo`,`lienFaceit`) SELECT 'C0casio45','test' 
FROM `bot_onet`.`Accuse` WHERE 'C0casio45' NOT IN(SELECT `Pseudo` FROM `bot_onet`.`Accuse`)

标签： raspberry-pimariadb-10.3

解决方案

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `close_ticket`(IN idT INT,
                         IN accuse VARCHAR(45),
                         IN lienAccuse VARCHAR(60), 
                         IN lienG VARCHAR(60),
                         IN jours INT,
                         IN reason VARCHAR(250), 
                         IN isclosed BOOLEAN)
BEGIN
    INSERT INTO bot_onet.accuse (`Pseudo`,`lienFaceit`) 
    SELECT accuse,              -- IN accuse VARCHAR(45) is used
                  lienAccuse    -- IN lienAccuse VARCHAR(60) is used
-- FROM clause is lost
    WHERE lienAccuse            -- IN lienAccuse VARCHAR(60) is used
                     NOT IN( SELECT `lienFaceit` 
                             FROM `bot_onet`.`accuse` );
    SELECT @ID := `idAccuse` 
    FROM bot_onet.accuse 
    WHERE `lienFaceit` = lienAccuse;   -- IN lienAccuse VARCHAR(60) is used
    INSERT INTO bot_onet.ticket_has_accuse (`Ticket_idTicket`,`Accuse_idAccuse`,`duree`,`timecode`,`unbaned`,`raison`) 
    VALUES (idT,@ID,jours,unix_timestamp(),FALSE,reason);  -- idT,jours and reason SP parameters are used
    UPDATE bot_onet.ticket 
    SET `Fermeture` = unix_timestamp(),
        `closed` = isclosed, 
        `LienGame` = LienG 
    WHERE idTicket = idT;   -- IN idT INT is used
END

错误代码

错误消息告诉 FROM 子句不存在。

推荐阅读