unsigned int 上的負值與 union all

當我對帶有 unsigned int 欄位的查詢進行聯合時，它會返回否定結果。

有人可以向我解釋這種行為嗎？如何在“ticketIn”中擁有真實身份？

架構（MySQL v5.7）

CREATE TABLE IF NOT EXISTS `history` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `ticket` INT(11) unsigned NOT NULL,
 `group` INT(11) unsigned NOT NULL,
 `prev_group` INT(11) unsigned NOT NULL,
 `date_mod` DATETIME NOT NULL,
 PRIMARY KEY(Id)
);

-- data
INSERT INTO history
   (`ticket`, `group`, `prev_group`, `date_mod`)
VALUES
   (2201100001, 53042, 0, '2022-01-10 00:04:05.000'),
   (2201100002, 53042, 0, '2022-01-10 00:10:01.000'),
   (2201100003, 53042, 0, '2022-01-10 00:10:02.000'),
   (2201100003, 52973, 53042, '2022-01-10 00:11:25.000'),
   (2201100004, 53043, 0, '2022-01-10 00:54:41.000'),
   (2201100004, 52972, 53043, '2022-01-10 01:08:20.000')
;

---

查詢 #1

SET
 @s = CAST('2022-01-10' AS DATE);

---

查詢 #2

SELECT
 DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate",
 `history_out`.group,
 `history_out`.ticket AS "ticketOut",
 NULL AS "ticketIn",
 `history_out`.id
FROM
 history `history_out`
WHERE
 (`history_out`.prev_group IN (53042))
 AND (`history_out`.date_mod >= @s)
UNION
ALL
SELECT
 DATE_FORMAT(`history_in`.date_mod, '%Y-%m-%d') AS "thedate",
 `history_in`.group,
 NULL AS "ticketOut",
 `history_in`.ticket AS "ticketIn",
 `history_in`.id
FROM
 history `history_in`
WHERE
 (`history_in`.group IN (53042))
 AND (`history_in`.date_mod >= @s);

---

在 DB Fiddle 上查看

試試這個：

SELECT
 DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate",
 `history_out`.group,
 `history_out`.ticket AS "ticketOut",
 cast(NULL as unsigned int) AS "ticketIn",
 `history_out`.id
FROM
 history `history_out`
WHERE
 (`history_out`.prev_group IN (53042))
 AND (`history_out`.date_mod >= @s)
UNION
ALL
SELECT
 DATE_FORMAT(`history_in`.date_mod, '%Y-%m-%d') AS "thedate",
 `history_in`.group,
 NULL AS "ticketOut",
 `history_in`.ticket AS "ticketIn",
 `history_in`.id
FROM
 history `history_in`
WHERE
 (`history_in`.group IN (53042))
 AND (`history_in`.date_mod >= @s);

dbfiddle.uk

PS。當我使用 RDBMS 時，聯合中的第一個選擇決定了列的格式。MySQL 5.7 可能已經很老了。

引用自：https://dba.stackexchange.com/questions/305877