MySQL中的CASE强制将列数据类型设置为BIGINT而不是INT [英] CASE in MySQL forces column datatype to BIGINT instead of INT

问题描述

那倒是一件很简单的事情，但我自己弄不清楚.

Well it would rather be a very simple thing but I couldn't figure out myself.

在MYSQL服务器的存储过程中尝试以下行时，它会将列数据类型转换或设置为BIGINT而不是预期的INT

While trying the below line in Stored Procedure in MYSQL server, it converts or sets the column datatype to BIGINT instead of expected INT

CASE `L`.`Code` WHEN 'ABCD' THEN 0 ELSE `XL`.`ID` END as XLID

神奇的事情是，如果我从中删除0，那么它将按预期返回INT作为数据类型.所以肯定要怪这个0，但是我不明白为什么以及如何解决这个问题?我尝试使用CAST，但不允许将INT强制转换为INT.

The magical thing is that if I remove the 0 from it, then it returns INT as datatype as expected. So this 0 is to blame for sure, but I couldn't understand why and how to resolve this? I tried to use CAST but it didn't allow to cast INT to INT.

将会有很大的帮助.

推荐答案

以下技巧可以帮助您:

DELIMITER //

CREATE FUNCTION `return_integer`(`number` INT UNSIGNED)
RETURNS INT UNSIGNED DETERMINISTIC
BEGIN
    RETURN `number`;
END//

DELIMITER ;

SELECT
    CASE `L`.`Code`
       WHEN 'ABCD' THEN `return_integer`(0)
       ELSE `L`.`ID`
    END AS XLID
FROM `table_name` `L`;

这篇关于MySQL中的CASE强制将列数据类型设置为BIGINT而不是INT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！