MySQL a rozsahy celočíselných typů

Aktualizováno: 10. 11. 2019, datum vydání: 23. 4. 2011

Jaké jsou rozsahy celočíselných typů a jak je možné využívat rozsahy zobrazovaných hodnot?

Definice počtu míst pro zobrazování

V MySQL máme tyto celočíselné typy: numeric, decimal, integer (resp. int) a smallint.

V manuálu MySQL je přesně popsáno, jaké hodnoty můžeme uložit do jednotlivých celočíselných typů.
Zde je malý souhrn pro MySQL verze 4.0., ale shodné rozsahy používá i aktuální verze MySQL 5.6.:

Typ:Rozsah hodnot
tinyint(4) -128..127
tinyint(3) unsigned 0..255
smallint(6) -32768..32767
smallint(5) unsigned 0..65535
mediumint(9) -8388608..8388607
mediumint(8) unsigned 0..16777215
int(11) -2147483648..2147483647
int(10) unsigned 0..4294967295
bigint(20) -9223372036854775808..9223372036854775807
bigint(19) unsigned 0..18446744073709551615

Číslo v závorce za typem

Číslo v závorce za typem, např. tinyint(4), udává počet míst pro zobrazování čísla. Tedy tinyint(4) nám říká, že pro zobrazování budou použita 4 místa (jedno pro případné znaménko a maximálně tři pro číslice). Pokud při vytváření tabulky pomocí CREATE TABLE nezadáme počet míst pro zobrazování, automaticky se definice tabulky doplní na jejich maximální počet pro daný číselný typ.
Zde je příklad vytvoření tabulky a následného výpisu informací o sloupcích tabulky:

mysql> CREATE TABLE Test (
-> Id int auto_increment,
-> Number smallint,
-> NumberUns smallint unsigned,
-> PRIMARY KEY (Id)
-> );
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW COLUMNS FROM Test;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| Id | int(11) | | PRI | NULL | auto_increment |
| Number | smallint(6) | YES | | NULL | |
| NumberUns | smallint(5) unsigned | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> DROP TABLE Test;
Query OK, 0 rows affected (0.07 sec)

Snížení počtu zobrazovaných míst

Pokud chceme mít počet míst určených pro zobrazování menší, stačí ho omezit přímo v definici tabulky.
Zde je příklad:

mysql> CREATE TABLE Test (
-> Id int(11) NOT NULL auto_increment,
-> Number smallint(5) default NULL,
-> NumberUns smallint(4) unsigned default NULL,
-> PRIMARY KEY (Id)
-> );
Query OK, 0 rows affected (0.13 sec)

mysql> SHOW COLUMNS FROM Test;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| Id | int(11) | | PRI | NULL | auto_increment |
| Number | smallint(5) | YES | | NULL | |
| NumberUns | smallint(4) unsigned | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql>

Příkazem SHOW COLUMNS FROM Test; si pak necháme vypsat definici tabulky, případně si můžeme zavolat SHOW CREATE TABLE Test;, ta vypíše zpětně definici celé tabulky jako příkaz CREATE....

Zobrazovaná místa nemají nic spolčeného s uloženými hodnotami a jejich rozsahem

Samotná definice počtu zobrazovaných míst nám však nebrání v tom, abychom do tabulky nemohli vložit číslo větší. Tak například, když budu mít Number definováno jako smallint(5), tak mi nic nezabrání v tom, abych do tabulky nemohla vložit číslo 32767 či -32768, což jsou krajní hodnoty pro smallint(6).
Zde je ukázka vložení čísla 32767 do sloupce Number definovaného pouze jako smallint(5).

mysql> SHOW COLUMNS FROM Test;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| Id | int(11) | | PRI | NULL | auto_increment |
| Number | smallint(5) | YES | | NULL | |
| NumberUns | smallint(4) unsigned | YES | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> INSERT INTO Test SET Number=32767;
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM Test;
+----+--------+-----------+
| Id | Number | NumberUns |
+----+--------+-----------+
| 1 | 32767 | NULL |
+----+--------+-----------+
1 row in set (0.04 sec)

mysql>

Z toho vyplývá, že prostor, který je vyhražený pro číslo typu smallint zůstane pořád stejný, i když omezíme počet zobrazovaných číslic na menší hodnotu.

Návaznost na PHP formuláře

Proč omezovat rozsah zobrazování

Ptáte se tedy, k čemu omezovat rozsah zobrazování?

Například pokud chceme udělat jednoduchou kontrolu uživatelského vstupu pro číslo a využíváte technologii PHP, můžeme využít právě počet zobrazovaných míst. Konkrétě můžeme dynamicky nastavit šířku input boxu ve formuláři v HTML místo toho, abychom ručně nastavovali jeho hodnotu.

Počet zobrazovaných míst zjistíme zavoláním PHP funkce funkce Mysql_Field_Len().

Místo toho, abychom psali natvrdo něco takového:

<input type="text" name="NumberUns" value="" size="4" maxlength="4">

využijeme funkci Mysql_Field_Len() a parametry size="" a maxlength="" si naplníme dynamicky.

Pokud necháme počet zobrazovaných míst nastaven na defaultní hodnoty, musíme počítat s tím, že sice velikost input boxu bude odpovídat velikosti políčků v databázi, ale uživatel do nich může napsat hodnotu větší či menší, než je možné uložit do políčka v databázi. Jde o to, že v počtu zobrazovaných míst se počítá se znaménky, dále například třímístná hodnota může uchovat maximálně hodnotu 255, nikoli už 999 apod. Ještě složitější je to v případe typu decimal, kde mohou být desetinná místa.
V těchto případech musíme kontrolu uživatelského vstupu zkombinovat ještě s přísnější kontrolou podle přesného možného rozsahu políček.

Pokud si však nechceme moc lámat hlavu s ověřováním, zda uživatel náhodou nezadal číslo mimo rozsah, lze využít ještě následujicí fintu. Sice se plýtvá místem v tabulkách, ale zamezíme uživateli v zadání větší hodnoty, než by byla možná přípustná hodnota. Programování a ověřování vstupu si vcelku hodně usnadníme.
Stačí, když u hodnot bez znaménka snížíme zobrazitelnou hodnotu čísla o jedničku a u čísel se znaménkem snížíme zobrazitelnou hodnotu čísla o dvojku. (Např. tinyint(2) unsigned). MySQL přesto budou počítat s tří-místným tinyint a bude možné ručně v SQL dotazu vložit číslo v plném možném rozsahu 0..255. V MySQL je ale uložena informace, že zobrazovaná velikost bude jen dvě místa. Pozor, toto jednoduché omezení neplatí pro typ decimal.
Když pak dynamicky nastavíme parametry size="" a maxlength="" pomocí PHP funkce Mysql_Field_Len(), máme jistotu, že uživatel nemohl zadat číslo mimo rozsah a rozsahy dále již nemusíme ověřovat. Maximálně si ověříme, zda zadal opravdu číslo, a to PHP funkcemi Is_Int() či Is_Numeric().

Ověřování přesného rozsahu zadaných hodnot

Kdo chce využívat velikosti polí naplno, bude lepší plně kontrolovat rozsahy hodnot po zadání čísel uživatelem. Pro zjistění potřebných informací o sloupcích tabulky můžeme využít funkcí PHP: MySQL_Field_Type(), MySQL_Field_Name(), MySQL_Field_Len(), MySQL_Field_Flags().

Rozsahy hodnot pro přesné ověřování si můžeme vypočítat, nebo můžeme použít přímo předepsané rozsahy MySQL. Asi je nejlepší si zjistit, pomocí PHP funkcí, o jaký celočíselný typ se jedná a podle toho natvrdo zkontrolovat rozsahy hodnot.

Zde je tabulka, která vám objasní rozsahy hodnot, použité pro číslené typy v MySQL.

Typ v MySQLVelikostPočet možných hodnotRozsah hodnot
tinyint(4) 1 byte (8 bitů) 28 = 256 -128..127
tinyint(3) unsigned 1 byte (8 bitů) 28 = 256 0..255
smallint(6) 2 bytes (16 bitů) 216 = 65536 -32768..32767
smallint(5) unsigned 2 bytes (16 bitů) 216 = 65536 0..65535
mediumint(9) 3 bytes (32 bitů) 224 = 16777216 -8388608..8388607
mediumint(8) unsigned 3 bytes (32 bitů) 224 = 16777216 0..16777215
int(11) 4 bytes (32 bitů) 232 = 4294967296 -2147483648..2147483647
int(10) unsigned 4 bytes (32 bitů) 232 = 4294967296 0..4294967295
bigint(20) 8 bytes (64 bitů) 264 = 18446744073709551616 -9223372036854775808 .. 9223372036854775807
bigint(19) unsigned 8 bytes (64 bitů) 264 = 18446744073709551616 0..18446744073709551615

Vysvětlení pojmů bit a byte

Pokud někomu nejsou jasné pojmy jako bit a byte, přikládám ještě následující vysvětlení v článku Vysvětlení pojmů bit a byte.

Další články