Podczas tworzenia nowej kolumny w tabelach SQL, mamy do wyboru typ danych. Bardzo to przypomina sytuację, kiedy pisząc program w języku z silnym typowaniem deklarujemy typ zmiennej. Warto poznać typy danych w MySQL, ponieważ trafny wybór pozwala nam przechowywać dane w odpowiednim formacie co ułatwi ich obsługę, istotnie wpływa na wydajność w przypadku dużej skali i nakłada pewne ograniczenia.

W tym artykule przyjrzymy się typom danych jakie możemy definiować w schema oraz przyjrzymy się różnicom pomiędzy różnymi typami. Typy można podzielić na kilka kategorii:

  1. Numeryczne
  2. Data i czas
  3. Ciągi znaków
  4. JSON
  5. Przestrzenne

Numeryczne typy danych MySQL

MySQL wspiera wszystkie standardowe numeryczne typy danych.

INT

INT czyli INTEGER (liczba całkowita) może przechowywać wartości od -2147483648 do 2147483647. W przypadku wykorzystania atrybutu UNSIGNED (czyli bez znaku) pozwala przechowywać wartości od 0 do 4294967295.

Przykłady wykorzystania INTEGER:

Jest to dość uniwersalny typ danych numerycznych – może mieć zastosowanie w obliczeniach. Z atrybutem bez znaku może być wykorzystany w kolumnach ID. Pojedyncza wartość INT zajmuje 4 bajty.

TINYINT

TINYINT to odchudzona (maleńka) wersja INTEGER. Zajmując tylko 1 bajt przestrzeni pozwala przechować wartości od -128 do 127. Bez znaku (atrybut UNSIGNED) pozwala przechować wartości od 0 do 255.

Przykład wykorzystania TINYINT

TINYINT nadaje się do przechowywania ustawień, stanów oraz kluczy w tabelach, w których z góry wiadomo, ze będą miały stosunkowo małą ilość rekordów. W tabeli z użytkownikami może to być poziom uprawnień.

SMALLINT

SMALLINT czyli „mała liczba całkowita” pozwala na zapis wartości od -32768 do 32767. Bez znaku: od 0 do 65535. Zajmuje przestrzeń 2bajtów.

MEDIUMINT

MEDIUMINT (średniej wielkości liczba całkowita) na 3 bajtach pamięci przechowuje wartości od -8388608 do 8388607. Bez znaku potrafi przechować wartość od 0 do 16777215.

Zarówno SMALLINT jak i MEDIUMINT są tak jakby pośrednimi typami pomiędzy TINYINT a INTEGER.

BIGINT

BIGINT (duża liczba całkowita) pozwala na 8 bajtach przechować wartości całkowite od -263 do 263-1. Bez znaku będą to wartości od 0 do 264-1.

BGINT jest w stanie przechowywać wartości tak duże, że już trudne do wyobrażenia. Może być wykorzystany we wszelkiego rodzaju obliczeniach.

DECIMAL

Typ DECIMAL wykorzystujemy do zapisu wartości stałoprzecinkowych czyli o określonej precyzji. Dla typu DECIMAL należy też zdefiniować długość – domyślna to (10,0) co oznacza że wartość przechowa 10 cyfr przed przecinkiem i 0 cyfr po przecinku.

Przykład wykorzystania:

DECIMAL często znajduje zastosowane w cenach i kwotach walutowych. Przykładowo dla polskich cen odpowiedni będzie format (10,2). Pozwoli przechować kwoty do maks. 999999999.99 zł. Jeżeli kwota ta stanowi ograniczenie można zmienić 10 w deklaracji długości na inną wartość.

FLOAT

FLOAT to typ do zapisu liczb zmiennoprzecinkowych pojedynczej precyzji. (ang. floating-point number). Oznacza to, że może przechowywać wartości o różnej precyzji. Wartością w polu może być zarówno 1.6 jak i 1.6180339887. Zauważ że do rozdzielenia użyłem kropki a nie przecinka. (To już akurat wada/niekompatybilność języka polskiego).

FLOAT do zapisania pojedynczej precyzji wykorzystuje 4 bajty.

DOUBLE

DOUBLE to również typ do zapisu liczb zmiennoprzecinkowych ale z podwójną precyzją. MySQL do podwójnej precyzji wykorzystuje 8 bajtów.

REAL

REAL to synonim dla typu DOUBLE o ile nie jest wyłączony tryb REAL_AS_FLOAT.

Opcjonalnie, dla FLOAT, DOUBLE i REAL możemy zadeklarować długość. MySQL dopuszcza tutaj niestandardową składnię np. FLOAT(M,D). Oznacza to że w polu możemy zapisać wartość z M cyframi z czego D cyfr może znajdować się po przecinku. Deklaracja FLOAT (8,4) pozwoli przechować wartość -9999.999. MySQL automatycznie wykona odpowiednie zaokrąglenia np. chcąc zapisać wartość 9999.00009 do pola w kolumnie FLOAT(8,4) zostanie zapisana wartość 9999.0001

BIT

Typ BIT przechowuje M bitową wartość binarną. Domyślnie jest to jeden bit a maksymalnie może ich być 64.

BOOLEAN

To typ wartości boolowskich (prawda lub fałsz). Wartość 0 uznaje się za fałsz (FALSE) a wszelkie inne wartości (niezerowe) uznaje się za prawdę. Synonim dla TINYINT(1). Wartości BOOLEAN mogą mieć szerokie wykorzystanie w aplikacjach np. zapisu stanu ustawień Tak/Nie np. czy checkbox został zaznaczony.

SERIAL

SERIAL jest synonimem (czyli aliasem) dla BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Jest czymś w rodzaju dedykowanym typem dla kluczy głównych.

Typy danych daty i czasu w MySQL

MySQL ma tez kilka typów dla przechowywania wartości związanych z datą i godziną.

DATE

DATE pozwala zapisać datę (bez czasu) w zakresie od dnia 1000-01-01 do dnia 9999-12-31.

Formatem jest tak jak w przykładzie: YYYY-MM-DD ale separatorem może być inny znak np. YYYY/MM/DD, YYYY^MM^DD lub YYYY@MM@DD. MySQL poradzi sobie nawet z zapisem YYYYMMDD.

DATETIME

DATETIME pozwala zapisać datę z czasem. MySQL pobiera i wyświetla takie wartości w formacie YYYY-MM-DD hh:mm:ss. Obsługiwany zakres to od 1000-01-01 00:00:00 do 9999-12-31 23:59:59.

TIMESTAMP

TIMESTAMP czyli pieczątka czasu obsługuje zakres od 1970-01-01 00:00:01 UTC do 2038-01-19 03:14:07 UTC.

W języku MySQL możemy łatwo wybierać daty za pomocą zakresów oraz przeprowadzać na nich standardowe dla takich typów działania.

Czym się różni DATETIME od TIMESTAMP w SQL?

  1. różnią się zakresami co wyraźnie widać powyżej.
  2. DATETIME wymaga 8 bajtów pamięci a TIMESTAMP 7 bajtów.
  3. TIMESTAMP można konwertować w zależności od czasu lokalnego względem UTC. DATETIME nie umożliwia takiej funkcji.
  4. TIMESTAMP może być indeksowany, DATETIME nie umożliwia indeksowania
  5. Wartości TIMESTAMP można ponadto przechowywać w query cache.

TIME

Time jest typem zarezerwowanym do samego czasu bez daty. Przykładowym zastosowaniem może być długość utworu multimedialnego lub tzw. Cue Pointy.

YEAR

YEAR jest oszczędnym typem, który może zawierać tylko rok. Obsługuje zapis czterocyfrowy (np. 2020) i dwucyfrowy. Zapis z minusem oznacza wiek dwudziesty np. -20 to rok 2020 a 88 oznacza rok 1988.

Typy danych dla ciągów znaków w MySQL

MySQL posiada też wiele typów dla obsługi stringów czyli ciągów znaków.

CHAR

CHAR to ciąg znaków o konkretnej długości. Domyślnie jest to 1 znak ale można też zdefiniować CHAR o długości maksymalnie 255 znaków. W przypadku wprowadzenia ciągu krótszego od zadeklarowanego jest prawo dopełniony spacjami. Nie widać ich przy pobieraniu danych jeżeli nie włączono opcji PAD_CHAR_TO_FULL_LENGTH.

VARCHAR

VARCHAR reprezentuje ciąg znaków o różnej długości (od 0 do 65,535 znaków). Nie jest dopełniany spacjami do maksymalnego rozmiaru.

Czym się różni CHAR od VARCHAR w SQL?

Dane są przechowywane w inny sposób: CHAR jest dopełniany spacjami a VARCHAR nie. CHAR zawsze zajmuje stały rozmiar a VARCHAR nie zajmuje przestrzeni, jeżeli wprowadzono krótsze wartości.

Dla zobrazowania można stwierdzić następujące fakty:

Wartość ” (pusta) w CHAR(4) zajmie 4 bajty będzie zapisana jako cztery spacje '____’ VARCHAR zostanie zapsany jako ” – wartość pusta i zajmie 1 bajt.

Wartość ’La’ zostanie zapisana w CHAR(4) jako 'La__’ i zajmie 4 bajty. W VARCHAR(4) zajmie tylko 3 bajty bo 'La’ wymaga dwóch bajtów na znaki i 1 bajt na deklarację długości.

Wartość ’Lana’ zostanie zapisana w CHAR(4) jako 'Lana’ i zajmie 4 bajty. W VARCHAR(4) zajmie natomiast 5 bajtów.

Wartość ’Lana Nowak’ zostanie zapisana w CHAR(4) jako 'Lana’ (ciąg będzie obcięty) zajmie 4 bajty a w VARCHAR 'Lana’ (ciąg będzie również obcięty) jak wyżej zajmie 5 bajtów.

Uwaga: wartości będą obcięte tylko kiedy opcja SQL strict jest wyłączona. Jeżeli opcja SQL strict jest włączona, próba zapisu dłuższego ciągu od deklaracji kolumny CHAR albo VARCHAR spowoduje błąd.

TEXT

TEXT też służy do przechowywania danych tekstowych. Pozwala przechować pełne 65,535 znaki. TEXT ma stały rozmiar i nie można deklarować innych długości.

Różnice pomiędzy TEXT a VARCHAR

Czym się różni VARCHAR od TEXT w SQL? Zarówno TINYTEXT, TEXT, MEDIUMTEXT i LONGTEXT pozwalają przechować tekst. Od typów CHAR i VARCHAR różnią się te typy tym, że tak naprawdę w komórkach tabeli zawierają tylko referencję do tekstu umieszczonego poza tabelą. Kolejną różnicą jest to, że tekst w CHAR i VARCHAR może być częścią indeksu.

Pod kątem wydajnościowym warto wyrobić sobie nawyk wybierania VARCHAR dla tekstu, który nie będzie przekraczał 65,535 znaków.

TINYTEXT

TINYTEXT pozwala przechować już tylko 255 znaki.

MEDIUMTEXT

MEDIUMTEXT jak można się domyślić pozwala przechować większą ilość tekstu w tym wypadku maksymalny rozmiar danych to 16MB.

LONGTEXT

LONGTEXT może przechowywać duże zbiory tekstu aż do 4GB.

BINARY i VARBINARY

BINARY i VARBINARY są bardzo podobne do CHAR i VARCHAR z tą różnicą że przechowują ciągi binarne zamiast ciągi znaków. W praktyce oznacza to, że porównywanie i sortowanie są oparte o wartości liczbowe bajtów.

Maksymalne długości wartości są takie same jak w przypadku CHAR (255) i VARCHAR (65535) z tą różnicą że mówimy o bajtach a nie o znakach.

BLOB

BLOB można wykorzystać do przechowywania dużych obiektów binarnych. BLOB może przechowywać zmienną ilość danych. Od typu TEXT różni się tym, że nie przechowuje danych w postaci tekst tylko w postaci binarnej.

TINYBLOB, MEDIUMBLOB, LONGBLOB

Jak można się domyślić, różnią się od BLOB maksymalnym rozmiarem danych. Obowiązują analogiczne wartości jak w przypadku TINYTEXT z tą różnicą, że mówimy o bajtach.

W praktyce typ BLOB może być wykorzystywany do przechowywania np. obrazów czy innych plików multimedialnych.

ENUM

ENUM to obiekt łańcuchowy z wartością wybraną z predefiniowanej listy listy dozwolonych wartości. Wartości te są jawnie wyliczane podczas specyfikacji kolumny w czasie tworzenia tabeli.

Stosowanie typu ENUM ma wiele zalet. Zajmuje mało miejsca a odczytywane wartości to zrozumiałe ciągi znaków. Praktycznym wykorzystaniem możemy być priorytet na liście TODO. Zamiast tworzyć TINYINT z cyframi odpowiadającymi różnym stopniom (1,2,3), ENUM może przechowywać takie ciągi jak (Niski, Średni, Wysoki). Zapytania i odpowiedzi aplikacji są czytelniejsze.

SET

SET przechowuje obiekt typu string, który może mieć zero lub więcej predefiniowanych wartości, które tak jak w ENUM zostały zamieszczone w specyfikacji kolumny. Wartości SET są określane za pomocą elementów rozdzielonych przecinkami i z tego powodu same wartości nie powinny zawierać przecinków.

Typ JSON w MySQL

JSON nie pasuje do żadnej z kategorii dlatego ma własny rozdział.

JSON

W tabelach MySQL można przechowywać dane w formacie JSON. MySQL obsługuje natywny typ danych JSON zdefiniowany w RFC 7159. Umożliwia to efektywny dostęp do danych w takich obiektach.

Wykorzystanie tego typu ma wiele zalet. Pierwszą z nich jest fakt, że MySQL automatycznie waliduje obiekty JSON wprowadzane do tabeli. Drugą zaletą jest optymalizacja formatu przechowywania. Dokumenty JSON przechowywane w kolumnach JSON są konwertowane do formatu wewnętrznego, który umożliwia szybki dostęp do odczytu elementów dokumentu bez konieczności analizowania reprezentacji tekstowej. Ten binarny format przechowywania ma strukturę przyspieszającą wyszukiwanie podobiektów i mocno zagnieżdżonych wartości bezpośrednio według klucza lub indeksu tablicy bez odczytywania całego obiektu JSON.

Więcej o tym formacie, przykładach i zastosowaniach możesz przeczytać w osobnym artykule: co to jest JSON.

Przestrzenne typy danych

MySQL ma też kilka typów do opisywania danych przestrzennych i obiektów geometrycznych. Nie mam praktycznie żadnej wiedzy w tym zakresie, dlatego wypiszę tę typy i odeślę do dokumentacji.

  1. GEOMETRY
  2. POINT
  3. LINESTRING
  4. POLYGON
  5. MULTIPOINT
  6. MULTILINESTRING
  7. MULTIPOLYGON
  8. GEOMETRYCOLLECTION

Więcej informacji o tych typach można znaleźć tutaj: https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html

Podsumowanie

Poprawne dobieranie typów dla pól w bazach danych MySQL to kluczowa umiejętność każdego kto chce konstruować i optymalizować bazy danych MySQL.

Poprawny typ danych obniża koszt utrzymania i rokuje lepszą wydajność. Wreszcie, poprawny typ zapewnia poprawne działanie bazy danych i łatwiejszy development aplikacji.

Źródła

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Oceń artykuł na temat: Typy danych w MySQL
Średnia : 4.9 , Maksymalnie : 5 , Głosów : 36