Далі розглянемо типи DATETIME, DATE і TIMESTAMP, які є спорідненими типами даних. Опишемо їх властивості, загальні риси і відмінності.
Тип даних DATETIME використовується для величин, що містять інформацію як про дату, так і про час. MySQL витягує і виводить величини DATETIME у форматі 'YYYY-MM-DD HH:MM:SS'. Підтримується діапазон величин від '1000-01-01 00:00:00' до '9999-12-31 23:59:59'. ("підтримується" означає, що хоча величини з більш ранніми тимчасовими значеннями, можливо, теж працюватимуть, але немає гарантії того, що вони правильно зберігатимуться і відображатимуться).
Тип DATE використовується для величин з інформацією тільки про дату, без частини, що містить час. MySQL витягує і виводить величини DATE у форматі 'YYYY-MM-DD'. Підтримується діапазон величин від '1000-01-01' до '9999-12-31'.
Тип стовпця TIMESTAMP забезпечує тип представлення даних, який можна використовувати для автоматичного запису поточних дати і часу при виконанні операцій INSERT або UPDATE. За наявності декількох стовпців типа TIMESTAMP тільки перший з них обновляється автоматично.
Для решти (окрім першого) стовпців типа TIMESTAMP також можна задати установку в значення поточних дати і часу. Для цього необхідно просто встановити стовпець в NULL або в NOW().
Будь-який стовпець типа TIMESTAMP (навіть перший стовпець даного типу) можна встановити в значення, відмінне від поточних дати і часу. Це робиться шляхом явної установки його в бажане значення. Дану властивість можна використовувати, наприклад, якщо необхідно встановити стовпець TIMESTAMP в значення поточних дати і часу при створенні рядка, а при подальшому оновленні цього рядка значення стовпця не повинне змінюватися.
Величини типа TIMESTAMP можуть приймати значення від початку 1970 року до деякого значення в 2037 році з дозволом в одну секунду. Ці величини виводяться у вигляді числових значень.
Формат даних, в якому MySQL витягує і показує величини TIMESTAMP, залежить від кількості символів, що показуються. Це проілюстровано в таблиці 4.4. Повний формат TIMESTAMP складає 14 десяткових розрядів, але можна створювати стовпці типа TIMESTAMP і з більш коротким рядком виведення:
Таблиця 3.4.
Формат даних TIMESTAMP залежно від кількості витягуваних розрядів
Тип стовпця Формат виведення
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY
Величини DATETIME, DATE і TIMESTAMP можуть бути задані будь-яким стандартним набором форматів:
Як рядок у форматі 'YYYY-MM-DD HH:MM:SS' або у форматі 'YY-MM-DD HH:MM:SS'. Допускається "полегшений" синтаксис - можна використовувати будь-який знак пунктуації як розділове між частинами розділів дати або часу. Наприклад, величини '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' і '98@12@31 11^30^45' є еквівалентними.
Як рядок у форматі 'YYYY-MM-DD' або у форматі 'YY-MM-DD'. Тут також допустимий "полегшений" синтаксис. Наприклад, величини '98-12-31', '98.12.31', '98/12/31' і '98@12@31' є еквівалентними.
Як рядок без розділових знаків у форматі 'YYYYMMDDHHMMSS' або у форматі 'YYMMDDHHMMSS', за умови, що рядок розуміється як дата. Наприклад, величини '19970523091528' і '970523091528' можна інтерпретувати як '1997-05-23 09:15:28', але величина '971122129015' є неприпустимою (значення розділу хвилин є абсурдним) і перетвориться в '0000-00-00 00:00:00'.
Як рядок без розділових знаків у форматі 'YYYYMMDD' або у форматі 'YYMMDD', за умови, що рядок інтерпретується як дата. Наприклад, величини '19970523' і '970523' можна інтерпретувати як '1997-05-23', але величина '971332' є неприпустимою (значення розділів місяця і дня не мають сенсу) і перетвориться в '0000-00-00'.
Як число у форматі YYYYMMDDHHMMSS або у форматі YYMMDDHHMMSS, за умови, що число інтерпретується як дата. Наприклад, величини 19830905132800 і 830905132800 інтерпретуються як '1983-09-05 13:28:00'.
Як число у форматі YYYYMMDD або у форматі YYMMDD, за умови, що число інтерпретується як дата. Наприклад, величини 19830905 і 830905 інтерпретуються як '1983-09-05'.
Як результат виконання функції, що повертає величину, прийнятну в контекстах типів даних DATETIME, DATE або TIMESTAMP (наприклад, функції NOW() або CURRENT_DATE()).
MySQL витягує і виводить величини типа TIME у форматі 'HH:MM:SS' (або у форматі 'HHH:MM:SS' для великих значень годинника). Величини TIME можуть змінюватися в межах від '-838:59:59' до '838:59:59'. Причина того, що "годинна" частина величини може бути настільки великою, полягає в тому, що тип TIME може використовуватися не тільки для представлення часу дня (яке повинне бути менше 24 годин), але також для представлення загального минулого часу або тимчасового інтервалу між двома подіями (який може бути значно більше 24 годин або навіть негативним).
Величини TIME можуть бути задані в різних форматах:
Як рядок у форматі 'D HH:MM:SS.дробна частина' (слід враховувати, що MySQL поки не забезпечує зберігання дробової частини величини в стовпці даного типу). Можна також використовувати одне з слідуючого "полегшених" уявлень: HH:MM:SS.дробная частина, HH:MM:SS, HH:MM, D HH:MM:SS, D HH:MM, D HH або SS. Тут D - це дні з інтервалу значень 0-33.
Як рядок без роздільників у форматі 'HHMMSS', за умови, що рядок інтерпретується як дата. Наприклад, величина '101112' розуміється як '10:11:12', але величина '109712' буде неприпустимою (значення розділу хвилин є абсурдним) і перетвориться в '00:00:00'.
Як число у форматі HHMMSS, за умови, що рядок інтерпретується як дата. Наприклад, величина 101112 розуміється як '10:11:12'. MySQL розуміє і наступні альтернативні формати: SS, MMSS, HHMMSS, HHMMSS.дробная частина. При цьому слід враховувати, що зберігання дробової частини MySQL поки не забезпечує.
Як результат виконання функції, що повертає величину, прийнятну в контексті типу даних типа TIME (наприклад, такої функції, як CURRENT_TIME).
Тип YEAR - це однобайтний тип даних для представлення значень року.
MySQL витягує і виводить величини YEAR у форматі YYYY. Діапазон можливих значень - від 1901 до 2155.
Величини типа YEAR можуть бути задані в різних форматах:
Як чотиризначний рядок в інтервалі значень від '1901' до '2155'.
Як чотиризначне число в інтервалі значень від 1901 до 2155.
Як двухзначний рядок в інтервалі значень від '00' до '99'. Величини в інтервалах від '00' до '69' і від '70' до '99' при цьому перетворяться у величини YEAR в інтервалах від 2000 до 2069 і від 1970 до 1999 відповідно.
Як двухзначне число в інтервалі значень від 1 до 99. Величини в інтервалах від 1 до 69 і від 70 до 99 при цьому перетворяться у величини YEAR в інтервалах від 2001 до 2069 і від 1970 до 1999 відповідно. Необхідно взяти до уваги, що інтервали для двузначних чисел і двузначних рядків дещо розрізняються, оскільки не можна вказати "нуль" безпосередньо як число і інтерпретувати його як 2000. Необхідно задати його як рядок '0' або '00', або ж воно буде інтерпретовано як 0000.
Як результат виконання функції, що повертає величину, прийнятну в контексті типу даних YEAR (такий як NOW() ).
Неприпустимі величини YEAR перетворяться в 0000.
Символьні типи даних
Існують наступні символьні типи даних: CHAR, VARCHAR, BLOB, TEXT, ENUM і SET. Розглянемо опис їх роботи, вимог до їх зберігання і використовування їх в запитах. В таблиці 4.5 приведені символьні типи даних і їх розмірність.
Таблиця 3.5. Символьні типи даних
Тип Макс.размер Байт
TINYTEXT або TINYBLOB 2^8-1 255
TEXT або BLOB 2^16-1 (64K-1) 65535
MEDIUMTEXT або MEDIUMBLOB 2^24-1 (16M-1) 16777215
LONGBLOB 2^32-1 (4G-1) 4294967295
Типи даних CHAR і VARCHAR
Типи даних CHAR і VARCHAR дуже схожі між собою, але розрізняються за способами їх зберігання і витягання.
В стовпці типа CHAR довжина поля постійна і задається при створенні таблиці. Ця довжина може приймати будь-яке значення між 1 і 255. Величини типа CHAR при зберіганні доповнюються справа пропусками до заданої довжини. Ці кінцеві пропуски віддаляються при витяганні бережених величин.
Величини в стовпцях VARCHAR є рядками змінної довжини. Так само як і для стовпців CHAR, можна задати стовпець VARCHAR будь-якої довжини між 1 і 255. Проте, в протилежність CHAR, при зберіганні величин типа VARCHAR використовується тільки та кількість символів, яка необхідна, плюс один байт для запису довжини. Бережені величини пропусками не доповнюються, навпаки, кінцеві пропуски при зберіганні віддаляються.
Якщо CHAR або VARCHAR величина, що задається в стовпці, перевершує максимально допустиму довжину стовпця, то ця величина відповідним чином усікається.
Відмінність між цими двома типами стовпців в представленні результату зберігання величин з різною довжиною рядка в стовпцях CHAR(4) і VARCHAR(4) проілюстрована наступною таблицею 3.6.
Таблиця 3.6.
Результат зберігання величин з різною довжиною рядка типів Char і Varchar
Величина CHAR(4) Пам'ять VARCHAR(4) Пам'ять
'' ' ' 4 байти ' ' 1 байт
'ab' 'ab ' 4 байти 'ab' 3 байти
'abcd' 'abcd' 4 байти 'abcd' 5 байтів
'abcdefgh' 'abcd' 4 байти 'abcd' 5 байтів
Витягнуті із стовпців CHAR(4) і VARCHAR(4) величини в кожному випадку будуть одними і тими ж, оскільки при витяганні кінцеві пропуски із стовпця CHAR віддаляються.
Якщо при створенні таблиці не був заданий атрибут BINARY для стовпців, то величини в стовпцях типа CHAR і VARCHAR сортуються і порівнюються без урахування регістра. При завданні атрибута BINARY величини в стовпці сортуються і порівнюються з урахуванням регістра відповідно до порядку таблиці ASCII на тому комп'ютері, де працює сервер MySQL.
Тип даних BLOB є двійковим об'єктом великого розміру, який може містити змінну кількість даних. Існують 4 модифікації цього типу - TINYBLOB, BLOB, MEDIUMBLOB і LONGBLOB, відмінні тільки максимальною завдовжки бережених величин.
Тип даних TEXT також має 4 модифікації - TINYTEXT, TEXT, MEDIUMTEXT і LONGTEXT, відповідні згаданим чотирьом типам BLOB і мають ті ж максимальну довжину і вимоги до об'єму пам'яті. Єдина відмінність між типами BLOB і TEXT полягає в тому, що сортування і порівняння даних виконуються з урахуванням регістра для величин BLOB і без урахування регістра для величин TEXT. Іншими словами, TEXT - це незалежний від регістра BLOB.
Якщо розмір BLOB або TEXT значення, що задається в стовпці, перевершує максимально допустиму довжину стовпця, то це значення відповідним чином усікається.
ENUM (перелік) - це стовпець, який може приймати значення із списку допустимих значень, явно перерахованих в специфікації стовпця у момент створення таблиці.
Перелік може мати максимум 65535 елементів.
Регістр не грає ролі, коли ви робите вставку в стовпець ENUM. Проте регістр значень, одержуваних з цього стовпця, співпадає з регістром в написанні відповідного значення, заданого під час створення таблиці.
Якщо вам потрібно одержати список можливих значень для стовпця ENUM, ви повинні викликати SHOW COLUMNS FROM імя_таблиці LIKE імя_колонки_enum і проаналізувати визначення ENUM в другому стовпці.
SET - це рядковий тип, який може приймати нуль або більш значень, кожне з яких повинне бути вибрано із списку допустимих значень, визначених при створенні таблиці. Елементи множини SET розділяються комами. Як наслідок, самі елементи множини не можуть містити ком.
Наприклад, стовпець, визначений як SET("один", "два") NOT NULL може приймати такі значення:
""
"один"
"два"
"один, два"
Множина SET може мати максимум 64 різні елементи.
Крайові пропуски віддаляються із значень множини SET у момент створення таблиці.
Якщо ви вставляєте в стовпець SET некоректну величину, це значення буде проігноровано.
Якщо вам потрібно набути всі можливі значення для стовпця SET, вам слід викликати SHOW COLUMNS FROM імя_таблиці LIKE імя_колонки_set і проаналізувати SET-визначення в другому стовпці.
Для того, щоб пам'ять використовувалася найбільш ефективно, завжди слід прагнути застосовувати тип даних, що забезпечує максимальну точність. Наприклад, для величин в діапазоні між 1 і 99999 в цілочисельному стовпці якнайкращим типом буде MEDIUMINT UNSIGNED.
Часто доводиться стикатися з такою проблемою, як точне представлення грошових величин. В MySQL для представлення таких величин необхідно використовувати тип даних DECIMAL. Оскільки дані цього типу зберігаються у вигляді рядка, втрат в точності не відбувається. А у випадках, коли точність не має дуже великого значення, цілком підійде і тип даних DOUBLE.
Якщо ж потрібна висока точність, завжди можна виконати конвертацію в тип даних з фіксованою точкою. Такі дані зберігаються у вигляді BIGINT. Це дозволяє виконувати всі обчислення з ними як з цілими числами, а згодом при необхідності результати можна перетворити назад у величини з плаваючою точкою.