mysql 强转bigint类型_MySQL中的CASE强制将列数据类型设置为BIGINT而不是INT

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

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

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.

Would be a great help.

解决方案

The following trick can help:

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`;