Типи даних в базі MySQL
Проектуючи структуру таблиць бази даних MySQL необхідно задати для кожного стовпця необхідний тип даних та інші параметри, щоб забезпечити оптимальне витрачання пам'яті та швидкість вибірки інформації. Типи даних можна сегментувати на такі групи:
Текстовий тип даних
CHAR – зберігання даних зазначеної довжини. Наприклад, CHAR(5) завжди зберігатиме 5 символів (5 байт). При перевищенні зазначеного ліміту дані можуть бути обрізані або зовсім не збережені. При спробі зберегти меншу кількість знаків, недостатня кількість буде доповнено пробілами, щоб в комірці завжди містилося 5 знаків. Максимальний обсяг даних типу CHAR становить 255 байт.
VARCHAR – текстовий тип змінної довжини. Залежно від версії MySQL, максимальна довжина поля може відрізнятися. У версіях до 5.0.3 довжина становила від 0 до 255, після версії 5.0.3 максимальна довжина поля збільшена до 65 535 байт. На відміну від типу CHAR, тут поле займатиме рівно стільки байт, скільки ви в нього запишете, але пошук по стовпцю CHAR працює швидше.
Зведемо ці та інші типи текстових даних у таблицю:
|
Тип |
Максимальний об'єм |
Байти під адресацію |
|
CHAR |
255 байт |
1 байт для CHAR(1) |
|
VARCHAR |
255 байт для версії 5.0.3 і менше |
1 байт |
|
TINYTEXT |
255 байт |
1 байт |
|
TEXT |
65535 байт (64 Кб) |
2 байти |
|
MEDIUMTEXT |
16 Мб |
3 байти |
|
LONGTEXT |
4 Гб |
4 байти |
Байти під адресацію – це обсяг місця, яке займає порожній осередок у таблиці бази. Тому важливо не встановлювати величину осередків з великим запасом, оскільки в базах з великою кількістю рядків зберігання порожніх осередків займатиме зайве місце.
Бінарні типи
BINARY – служить для запису бінарної інформації з лімітованою довжиною X байт. Приклад BINARY (1000). Межі значень X від 1 до 8000.
VARBINARY – для зберігання бінарної інформації із змінною довжиною X у межах від 1 до 8000.
Типи BINARY та VARBINARY аналогічні CHAR та VARCHAR, за винятком того, що вони зберігають двійкові рядки. Тобто зберігають рядки байтів, а не рядки символів. Це означає, що вони мають двійковий набір символів та зіставлення, а порівняння та сортування засновані на числових значеннях байтів у значеннях.
BLOB – використовується для збереження двійкових даних. Наприклад, фото, відео, PDF-файли. Застосування такого типу даних дозволяє уникнути використання файлової системи серверу, спростити створення бекапів сайту, налагодити синхронізацію даних та спростити перенесення інформації на інший сервер. База даних виходить ізольованою від файлів на диску. Максимальний обсяг інформації, що зберігається 65535 байт.
Поряд із типом BLOB існують і аналогічні йому типи:
- TINYBLOB – максимальний об'єм до 255 байт;
- MEDIUMBLOB - 16Mb;
- LARGEBLOB – дозволяє записати не більше 4 Gb даних.
Складові типи даних
ENUM – використовується для запису до бази одного з дозволених варіантів. Наприклад, ENUM('UA','RU','EN'). Цей тип даних не підходить, якщо значення будуть динамічними та не входять до дозволеного переліку.
SET – аналогічний типу ENUM, але дозволяє зберігати не одне, а кілька значень одразу з переліку допустимих. Максимальна кількість значень, що зберігаються – 64. Якщо значень декілька, вони розділяються між собою комою.
Числові типи
TINYINT – для запису цілих числових даних від -128 до 127 (TINYINT(3)). Використання атрибуту UNSIGNED дозволяє зберігати дані від 0 до 255. Використовує 1 байт місця.
BOOL або BOOLEAN - це щось аналогічне TINYINT(1), може використовуватися для запису 1 або 0, або для збереження даних у вигляді констант TRUE або FALSE, але для таких констант краще підійде поле з типом даних BIT. Підтримка цих типів була додана в MySQL починаючи з версії 4.1.
SMALLINT – для запису цілих чисел у діапазоні від -32768 до 32767. Використання атрибуту UNSIGNED дозволяє зберігати дані в межах від 0 до 65535. Використовує 2 байти місця.
MEDIUMINT - для запису цілих значень в діапазоні від -8388608 до 8388607. Використання атрибуту UNSIGNED дозволяє зберігати дані в межах від 0 до 16777215. Використовує 3 байти.
INT - для запису цілих значень в діапазоні від - 2147483648 до 2147483647. Використання атрибуту UNSIGNED дозволяє зберігати дані в межах від 0 до 4294967295. Використовує 4 байти.
BIGINT - для запису цілочисельних значень в діапазоні від -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807. Використання атрибуту UNSIGNED дозволяє зберігати дані в межах від 0 до 18 446 744 073 709 551 615.
DECIMAL (NUMERIC, DEC, FIXED) – дозволяє записувати числові значення фіксованої точності. При створенні стовпця з цим типом даних можна встановити два параметри. Наприклад, DECIMAL(5,2) – перше число (precision) задає максимальну к-ть знаків, що можна зберігати, друге число (scale) – к-ть знаків після коми. Величина precision має бути в межах від 1 до 65. Scale від нуля і не більше числа precision. Для вказаного прикладу ви зможете записати число в діапазоні від -99.99 до 99.99. Обсяг займаного місця в байтах залежить від обсягу записаної інформації. Зберігання числа у форматі DECIMAL не дозволить проводити обчислення через запити SQL, це по суті текстове значення.
FLOAT – використовується для зберігання числових значень із плаваючою комою одинарної точності в діапазоні від -3,4028*10^38 до 3,4028*10^38. Використовує 4 байти місця. При створенні такого типу вказується precision та scale, аналогічно DECIMAL.
DOUBLE (REAL, DOUBLE PRECISION) - використовується для зберігання числових значень з плаваючою комою подвійної точності в діапазоні -1.7976*10^308 до 1.7976*10^308. Використовує 8 байт місця. При створенні такого типу вказується precision та scale, аналогічно DECIMAL.
SERIAL - числові дані з авто інкрементом від числа з попереднього запису. Займає 4 байти місця та дозволяє зберігати числа в діапазоні від 1 до 2147483647. Застосовується в основному для зберігання ID рядків у базі. Аналогічно типу INT(11) із автоінкрементом.
Типи дати та часу
DATE – дозволить записати дату у форматі РІК-МІСЯЦЬ-ДЕНЬ (YYYY-MM-DD). Використовує 3 байти.
TIME – дозволяє зберігати час у форматі ГОДИНИ:ХВИЛИНИ:СЕКУНДИ (HH:MM:SS). Використовує 3 байти.
DATETIME – поєднує зберігання перших двох типів разом у форматі YYYY-MM-DD HH:MM:SS. Використовує 8 байт місця.
TIMESTAMP – аналогічний попередньому типу, але займає 4 байти через зменшений діапазон зберігання періоду.
YEAR – для зберігання року у форматі YYYY. Ви зможете записати 4-х значне число від 1901 до 2155. Займає 1 байт місця.
Перед записом у базу завжди змінюйте дату та час до потрібного формату, інакше дані можуть бути спотворені та неправильно записані.
Типи даних для геометричних форм
POINT – зберігання даних про координати точки Х та У. Приклад (20,25)
RING – задає координати багатокутника без порожніх областей усередині. Приклад, [(1,0),(15,18),(60,10),(5,5)]
POLYGON - визначає координати багатокутника з порожніми областями всередині. Приклад, [[(20, 20), (50, 20), (50, 50), (20, 50)], [(30, 30), (50, 50), (50, 30)]]
MULTIPOLYGON – дозволяє зберігати координати багатокутника у вигляді масиву декількох багатокутників. Приклад, [[[(0, 0), (10, 0), (10, 10), (0, 10)]], [[(20, 20), (50, 20), (50, 50) , (20, 50)], [(30, 30), (50, 50), (50, 30)]]]
Атрибути для полів баз даних
UNSIGNED – використовується для числових типів даних і дозволяє записувати лише числа більше нуля. Його використання розширює діапазон допустимих значень вдвічі для відповідного типу.
ZEROFILL – доповнить записане значення нулями, що бракують, спереду. Наприклад, якщо ви використовуєте тип даних TINYINT(3) і записуєте число 23, то в базі збережеться значення 023 при встановленому атрибуті ZEROFILL. Використання цього атрибуту автоматично застосує атрибут UNSIGNED.
ONUPDATE CURRENT_TIMESTAMP – застосування цього атрибуту до типу TIMESTAMP дозволить вам автоматично записувати та оновлювати дату та час зміни даних у рядку у форматі YYYY-MM-DD HH:MM:SS. Значення за замовчуванням для стовпця також слід встановити в CURRENT_TIMESTAMP. Якщо застосувати в таблиці кілька подібних стовпців, лише один з них автоматично записуватиме дату і час останньої операції над рядком.
BINARY – для стовпців з таким атрибутом дані будуть порівнюватися з огляду на регістр відповідно до порядку символів сервера MySQL. Цей атрибут не впливає на запис та читання. Якщо стовпці з атрибутом BINARY застосовуються у виразах, всі такі вирази порівнюються як величини BINARY.
