MySQL. Niebezpieczeństwo użycia atrybutu INT UNSIGNED. Nieokreślone ujemne wartości liczbowe.

Dzisiaj artykuł o niebezpieczeństwie użycia atrybutu UNSIGNED przy wartościach liczbowych w bazie MySQL (i być może w innych bazach też).

PRZYPOMNIENIE: z lekcji o typach danych, rozmiarze i zakresie poszczególnych typów w bazie MySQL:
Wartości całkowite dodatnie:
TINYINT UNSIGNED: 1 byte, zakres: 0 .. 255
SMALLINT UNSIGNED: 2 byte, zakres: 0 .. 65535
MEDIUMINT UNSIGNED: 3 byte, zakres: 0 .. 16777215
INT UNSIGNED: 4 byte, zakres: 0 .. 4294967295
BIGINT UNSIGNED: 8 byte, zakres: 0 .. 18446744073709551615

Wartości całkowite ujemne i dodatnie:
TINYINT: 1 byte, zakres: -128 .. 127
SMALLINT: 2 byte, zakres: -32768 .. 32767
MEDIUMINT: 3 byte, zakres: -8388608 .. 8388607
INT: 4 byte, zakres: -2147483648 .. 2147483647
BIGINT: 8 byte, zakres: -9223372036854775808 .. 9223372036854775807

Atrybut UNSIGNED przy tworzeniu tabeli w bazie informuje bazę, iż będziemy przechowywać w niej tylko wartości dodatnie, czyli 0, 1, 2, 3, … itp.
Dlatego jakakolwiek próba odjęcia od siebie tych wartości, w przypadku gdy wynik ten będzie niższy od 0 otrzymamy wartość nieokreśloną.
Dlaczego? Na to pytanie niestety nie znam odpowiedzi! Jeżeli ktoś zna proszę o wyjaśnienie w komentarzu. Ja jedynie przestrzegam przed niebezpieczeństwem użycia tego atrybutu.

Przykład. 1.
Stworzymy przykładową tabelę bez użycia atrybutu UNSIGNED, aby pokazać jak zachowuje się baza danych przy wartościach ujemnych.

CREATE TABLE tabela (
id_tabela INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 INT NOT NULL DEFAULT 0,
col2 INT NOT NULL DEFAULT 0
);
INSERT INTO tabela SET col1=10, col2=5;
INSERT INTO tabela SET col1=5, col2=10;
INSERT INTO tabela SET col1=7, col2=3;
INSERT INTO tabela SET col1=4, col2=8;
INSERT INTO tabela SET col1=5, col2=5;

mysql> SELECT col1, col2, col1-col2 FROM tabela;
+——+——+———–+
| col1 | col2 | col1-col2 |
+——+——+———–+
|   10 |    5 |         5 |
|    5 |   10 |        -5 |
|    7 |    3 |         4 |
|    4 |    8 |        -4 |
|    5 |    5 |         0 |
+——+——+———–+
5 rows in set (0.00 sec)

Przykład 2.
Stworzymy przykładową tabelę z użyciem atrybutu UNSIGNED.
Pokażemy w ten sposób niebezpieczeństwo użycia tego atrybutu w przypadku próby odjęcia od siebie wartości.

CREATE TABLE tabela (
id_tabela INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 INT UNSIGNED NOT NULL DEFAULT 0,
col2 INT UNSIGNED NOT NULL DEFAULT 0
);
INSERT INTO tabela SET col1=10, col2=5;
INSERT INTO tabela SET col1=5, col2=10;
INSERT INTO tabela SET col1=7, col2=3;
INSERT INTO tabela SET col1=4, col2=8;
INSERT INTO tabela SET col1=5, col2=5;

mysql> select col1, col2, col1-col2 from tabela;
+——+——+———————-+
| col1 | col2 | col1-col2            |
+——+——+———————-+
|   10 |    5 |                    5 |
|    5 |   10 | 18446744073709551611 |
|    7 |    3 |                    4 |
|    4 |    8 | 18446744073709551612 |
|    5 |    5 |                    0 |
+——+——+———————-+
5 rows in set (0.00 sec)

WNIOSEK: Dziwne prawda?
No może i dla prawdziwych wyjadaczy chleba (MySQL’a), jak to się mówi – nie zdziwi powyższy fakt.
Niemniej jednak należy uważać przy używaniu różnych atrybutów – bo zachowywać się mogą inaczej niż nasza logika podpowiada.
Zapewne istnieją różne funkcje konwertujące wartość w locie przy odejmowaniu.
Ale celem tego postu jest ukazanie niebezpieczeństwa nieświadomego użycia atrybutu UNSIGNED przy projektowaniu bazy danych.
Oczywiście powyższy przykład dotyczy wszystkich wartości numerycznych dostępnych w bazie MySQL: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, …

This entry was posted in MySQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply