mysql - boolean 变量始终在select…into中假定为1-MySQL

我正在执行一个存储过程来更新表，该表具有一个名为“ Finished”的布尔值作为字段。此字段通知我们游戏是否结束。在我的问题中，能够将某些内容设置为在到期日期之前完成是有道理的，因此，因此，我正在检查要更新的行是否将“ Finished”字段设置为true或到期日期是否已过。

SET @isFinished=0;
SELECT Finished INTO @isFinished FROM game WHERE ID = gameID;
-- gameID comes as a parameter
-- date comes as a parameter as well
IF DATEDIFF(STR_TO_DATE(date, "%Y-%m-%d") , CURDATE()) < 0  OR @isFinished<>0 THEN
    select CONCAT("Game can't be updated because it's already finished. Days missing:",DATEDIFF( STR_TO_DATE(date, "%Y-%m-%d"), CURDATE() )," and @finished=", @isFinished, ", game=",gameID)
    into @msg;
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = @msg;
END IF;

问题是，当我尝试更新未完成的游戏时，它会进入if并抛出错误消息：
“ sqlMessage：'游戏已完成，无法更新。缺少的天数：337，@ finished = 1，game = 2'”。
快速说明：变量@isFinished从未使用，而是在此代码块中使用。

我一直保证在测试之前，“ Finished”的值是0，但是它一直选择它为1，因此进入if。
我认为可能是从select ...进入的，所以我在存储过程中尝试了一下（实际上是复制粘贴，只是将“ gameID”更改为我正在使用的实际ID），并且效果很好。

SELECT Finished INTO @isFinished FROM game WHERE ID = 2;
SELECT @isFinished

此后，我不知道还能检查什么。如果有人可以帮助我，我会很感激。

隔离测试：

create database test;
use test;

create table Tournament(
    ID int(10) not null unique auto_increment,
    Name varchar(250) not null,
    Start_Date date not null,
    End_Date date not null,
    Primary key(ID)
);

create table Game(
    ID int(10) not null unique auto_increment,
    Tournament_ID int(10),
    Date date,
    Finished boolean,
    Foreign Key(Tournament_ID) references Tournament(ID) ON DELETE CASCADE,
    Primary Key(ID)
);

INSERT INTO Tournament VALUES(NULL, "tournament1", str_to_date("2020-06-01","%Y-%m-%d"), str_to_date("2020-07-01","%Y-%m-%d"));
INSERT INTO Game VALUES(NULL, 1, str_to_date("2020-06-02","%Y-%m-%d"), 0);

DELIMITER $$
DROP PROCEDURE IF EXISTS `UpdateGame`$$
CREATE PROCEDURE `UpdateGame`(IN gameID int, date varchar(10), finished boolean)
BEGIN
SET @isFinished=0;
SELECT Finished INTO @isFinished FROM game WHERE ID = gameID;

IF DATEDIFF(STR_TO_DATE(date, "%Y-%m-%d") , CURDATE()) < 0  OR @isFinished<>0 THEN
    select CONCAT("Game can't be updated because it's already finished. Days missing:",DATEDIFF( STR_TO_DATE(date, "%Y-%m-%d"), CURDATE() )," and @finished=", @isFinished, ", game=",gameID)
    into @msg;
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = @msg;
END IF;
UPDATE game SET Date=STR_TO_DATE(date, "%Y-%m-%d"), Finished=finished WHERE ID=gameID;
END$$
call UpdateGame(1,"2020-06-03",1);

SELECT * FROM game;

SELECT Finished INTO @isFinished FROM game WHERE ID = 1;
SELECT @isFinished;

最佳答案

您具有IN参数Finished，因此在存储过程中，查询表时，实际上是在查询IN参数。

因此，您需要删除/重命名Finished IN参数或将内部选择查询编写为：

SELECT `game`.`Finished`
INTO @isFinished
FROM game
WHERE ID = gameID;

附言人们通常在输入参数上使用某种前缀，以在存储过程中从视觉上将它们与表，局部变量列区分开。

关于mysql - boolean 变量始终在select…into中假定为1-MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56829560/