05 05 2011

MySQL cz. 3: Typy danych w MySQL – zestawienie

Dziejszy wpis jest kontynuacją serii wpisów nt. MySQL. Poprzednio opisywałem jak korzystać z mysql w konsoli linuxa oraz operacje na bazach danych i tabelach. Odpowiedni dobór typów danych przy tworzeniu tabel stanowi ważny element optymalizacji bazy danych. Dlatego dziś przyjrzymy się bliżej typom danym jakie można spotkać w MySQL.

Jak już wpomniałem, ważnym aspektem w optymalizacji bazy danych jest odpowiedni dobór typów danych dla pól. Powinniśmy używać takich typów danych i rozmiarów pól jakich rzeczywiście będziemy potrzebowali. I tak np. do przechowywania informacji o płci użyjemy pola BIT lub CHAR.

Typy danych w MySQL możemy podzielić na 3 kategorie: Numeric Types (numeryczne), Date and Time Types (związane z datą i/lub czasem) oraz String Types (napisowe).

Poniżej przedstawiam opracowane przeze mnie tabele z najpopularniejszymi typami danych.

Numeric Types – numeryczne

Nazwa Name	Zakres* Range	Atrybuty Attributes	Pamięć Storage
TINYINT[(M)]	-128 – 127 0 – 255	UNSIGNED ZEROFILL AUTO_INCREMENT	1 bajt
BOOL	false == 0 true != 0
BOOLEAN	false == 0 true != 0
SMALLINT[(M)]	-32768 – 32767 0 – 65535	UNSIGNED ZEROFILL AUTO_INCREMENT	2 bajty
MEDIUMINT[(M)]	-8388608 – 8388607 0 – 16777215		3 bajty
INT[(M)]	-2147483648 – 2147483647 0 – 4294967295		4 bajty
INTEGER[(M)]	-2147483648 – 2147483647 0 – 4294967295		4 bajty
BIGINT[(M)]	-9223372036854775808 – 9223372036854775807 0 – 18446744073709551615		8 bajtów
FLOAT[(M,D)]	-3.402823466E+38 – -1.175494351E-38 0 1.175494351E-38 – 3.402823466E+38	UNSIGNED ZEROFILL	4 bajty
DOUBLE[(M,D)]	-1.7976931348623157E+308 – -2.2250738585072014E-308 0 2.2250738585072014E-308 to 1.7976931348623157E+308		8 bajtów
DOUBLE PPRECISION[(M,D)]			8 bajtów
DECIMAL[(M,D)]			(M + 2) znaków
DEC[(M,D)]
NUMERIC[(M,D)]

* – zielone wpisy onaczają przyjmowane wartości dla UNSIGNED
Wszystkie powyższe typy danych domyślnie przyjmują NULL, a jeśli pole jest zdefiniowane jako NOT NULL, wówczas przyjmują zero.

BOOL i BOOLEAN są synonimami dla TINYINT(1).

SERIAL jest aliasem dla BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL DEFAULT VALUE jako atrybut pola typu integer (od TINYINT do BIGINT) jest aliasem dla. NOT NULL AUTO_INCREMENT

Date and Time Types – typu daty i czasu

Nazwa Name	Zakres Range	Format Format	Domyślnie Default value if NOT NULL !	Pamięć Storage
DATE	’1000-01-01′ – ’9999-12-31′	‘YYYY-MM-DD’	’0000-00-00′	3 bajty
DATETIME	’1000-01-01 00:00:00′ – ’9999-12-31 23:59:59′	‘YYYY-MM-DD hh:mm:ss’	’0000-00-00 00:00:00′	8 bajtów
TIMESTAMP	’1970-01-01 00:00:01′ UTC – ’2038-01-19 03:14:07′ UTC	‘YYYY-MM-DD hh:mm:ss’	CURRENT TIMESTAMP	4 bajty
TIME	‘-838:59:59′ – ’838:59:59′	‘HH:MM:SS’		3 bajty
YEAR[(2\|4)] *	1901 – 2155, 0000 70 – 69 [1970 to 2069]	YYYY	0000	1 bajt

* – domyślnie YEAR reprezentowany jest przez 4 cyfry

Mamy jeszcze typy napisowe:

String Types

Nazwa Name	Zakres Range	Atrybuty Attributes	Pamięć Storage
CHAR[(M)]	M = 0 – 255 znaków	BINARY CHARACTER SET	M znaków
VARCHAR(M)	M = 0 – 65535 znaków	BINARY CHARACTER SET	M znaków + 1 lub 2 bajty *
BINARY(M)	M = 0 – 255 bajtów		M bajtów
VARBINARY(M)	M = 0 – 65535 bajtów		M bajtów
TINYBLOB	X = 0 – 255 bajtów		(X+1) bajtów
TINYTEXT	X = 0 – 255 znaków	CHARACTER SET charset_name COLLATE collation_name	X znaków + 1 bajt
BLOB	X = 0 – 65535 bajtów (do 64kB)		(X+2) bajtów
TEXT	X = 0 – 65535 znaków	CHARACTER SET charset_name COLLATE collation_name	X znaków + 2 bajt
MEDIUMBLOB	X = 0 – 16777215 bajtów (do 16MB)		(X+3) bajtów
MEDIUMTEXT	X = 0 – 16777215 znaków	CHARACTER SET charset_name COLLATE collation_name	X znaków + 3 bajty
LONGBLOB	X = 0 – 4294967295 bajtów (do 4GB)		(X+4) bajtów
LONGTEXT	X = 0 – 4294967295 znaków	CHARACTER SET charset_name COLLATE collation_name	X znaków + 4 bajtu
ENUM(‘value1′,’value2′,…)	1 – 65 535 wartości		1-2 bajty
SET(‘value1′,’value2′,…)	1 – 64 wartości		1-8 bajtów

Wszystkie powyższe typy napisowe domyślnie przyjmują NULL, a jeśli pole jest zdefiniowane jako NOT NULL, wówczas domyślna wartość to: ”.

CHARSET jest synonimem dla CHARACTER SET.

Różnica między CHAR a VARCHAR jest taka, że CHAR jest łańcuchem znakowym o stałej, a VARCHAR o zmiennej długości.

* – 1 lub 2 bajty to tzw. prefix, który przechowuje info o ilości znaków w łańcuchu. 1 bajt używany jest, gdy łańcych nie przekracza 255 bajtów, w przeciwnym wypadku, prefix zapisywany jest na dwóch bajtach.

BINARY jest typem podobnym do CHAR, VARBINARY do VARCHAR. Różnica jest taka, że BINARY i VARBINARY przechowują łańuchy bajtowe, a nie znakowe.

BLOB – Binary Large OBjects

Tabele zostały opracowane na podstawie dokumentacji do MySQL w wersji 5.6.

Zobacz inne wpisy w kategorii: MySQL