SIGNED vs UNSIGNED w MySQL

Odejmowanie w MySQL’u wydaje się być proste. No bo jaka może być filozofia w odejmowaniu jednej liczby od drugiej, zwłaszcza że obie są typu INT? Jeden przypadek sprawił, że jednak wcale nie jest tak do końca oczywiste.

Załóżmy, że mamy w bazie dwie tabelki X i Y:

CREATE TABLE X (
id_y INT(10) UNSIGNED NOT NULL,
amount INT(10) UNSIGNED NOT NULL
);
CREATE TABLE Y (
id_y INT(10) UNSIGNED NOT NULL,
stock INT(10) SIGNED NOT NULL
);

Chcielibyśmy w jednym zapytaniu zmniejszać kolumnę “stock” z tabeli Y o wartość “amount” z tabeli X. W sumie co to za filozofia? (zakładamy, że stock może być ujemny):

UPDATE
Y
SET
stock = stock - (
SELECT
amount
FROM
X
WHERE
X.id_y = Y.id_y
)

Zapytanie działa, cieszymy się, ale co w przypadku kiedy “amount” jest większy od “stock”? Okazuje się, że MySQL zamiast zapisać wartość ujemną do pola, które zdefiniowaliśmy jako SIGNED, przekręca licznik i wstawia nam 7-cyfrową wartość dodatnią! Powodem tej sytuacji jest bit znakowy, wykorzystywany w polach typu SIGNED i jego brak w polu typu UNSIGNED. Aby zapytanie działało dokładnie tak jakbyśmy tego chcieli, musimy użyć funkcji CAST i wtedy będziemy operować na dwóch identycznych typach.

UPDATE
Y
SET
stock = stock - (
SELECT
CAST(amount AS SIGNED)
FROM
X
WHERE
X.id_y = Y.id_y
)