Знайомство з типами даних у SQL

На відміну від мови JavaScript, у мові SQL є сувора типізація. У кожній таблиці кожна колонка має свій власний жорстко фіксований тип даних.

Типів даних дуже багато, але на відміну від мови Java, їх так багато не тому, що є типи даних на всі випадки життя. Бази даних дуже залежить від розміру даних, тому багато типів даних відрізняються один від одного лише довжиною.

Усього типи даних можна розбити на 5 груп:

  • Числові типи
  • Строкові типи
  • Типи для зберігання дати та часу
  • Об'єкти: зазвичай представлені як набір байт
  • Транспортні : JSON та XML

Зазвичай різні СУБД мають свої власні типи даних. Кожна СУБД має свою спеціалізацію, тому додавання нових типів даних – дуже поширена річ.

Інша справа, просто додати новий тип даних недостатньо, потрібно додати і функції, які працюватимуть з ним, а також зробити цю роботу зручною і швидкою.

Якщо ти працюватимеш на якійсь промисловій (enterprise) СУБД, то швидше за все тобі доведеться розбиратися з її типами даних та її функціями. Навіщо потрібно буде прочитати 2-5 хороших книжок.

Ми ж у рамках знайомства з SQL розглянемо три основні групи типів даних:

  • Числа
  • Рядки
  • Дати

Числові типи в SQL

У мові SQL числові типи поділяються на три групи:

  • Цілі типи
  • Числа з фіксованою комою (фіксована кількість знаків після коми)
  • Числа з плаваючою комою

Почнемо з цілих чисел . Їх лише 5, і вони можуть бути описані однією таблицею:

# Ім'я типу Довжина в байтах Аналог з Java Мінімальне значення Максимальне значення
1 TINYINT 1 byte -128 127
2 SMALLINT 2 short -32,768 32,767
3 MEDIUMINT 3 -8,388,608 8,388,607
4 INT 4 int -2,147,483,648 2,147,483,647
5 BIGINT 8 long -2 63 2 63-1

Типи даних дуже схожі на типи даних в Java, проте тут є ще один тип integer довжиною три байти. Так зроблено задля можливості економити на розмірі.

Далі йдуть типи з плаваючою комою , як і в Java, їх всього два:

# Ім'я типу Довжина в байтах Аналог з Java Мінімальне значення Максимальне значення
1 FLOAT 4 float -3.40E+38 +1.18E+38
2 DOUBLE 8 doble -1.79E+308 +1.79E+308

Знов-таки, нічого нового. Все, як і в Java. Однак, на відміну від Java, у SQL є ще один спеціальний тип – речове число з фіксованою комою. Називається воно DECIMAL.

Зазвичай цей тип використовується зберігання сум грошей. Коли пишеться назва цього типу, після нього зазвичай вказується, скільки в кількості десяткових знаків до коми і після коми. Загальний формат виглядає так:

DECIMAL(всього_знаків, після_комою)

І невеликий приклад:

salary DECIMAL(5,2)

Так ми описали те, що колонка salary може містити цілі числа (максимум 3 знаки до коми) і дрібну частину - 2 знаки після коми.

Максимальна кількість знаків, що підтримує тип DECIMAL, – 65.

Рядкові типи в SQL

Рядки у базі даних можуть зберігатися у двох видах:

  • Рядки з фіксованою довжиною
  • Рядки зі змінною довжиною

Рядки з фіксованою довжиною задаються типом CHAR :

CHAR(довжина)

Фіксована довжина означає, що всі значення цієї колонки будуть містити строго фіксовану кількість символів.

Приклад рядка з фіксованою довжиною:

country_code CHAR(2)

Рядки зі змінною довжиною задаються типом VARCHAR :

VARCHAR(макс_довжина)

Змінна довжина означає, що це значення даної колонки будуть містити текст будь-якої довжини, але з більше, ніж максимальна довжина.

Приклад рядка зі змінною довжиною:

phone VARCHAR(12)

У рядків із фіксованою довжиною є дуже велика перевага. Якщо клієнт попросив SQL-сервер повернути йому 1,000,000-й рядок з таблиці, а таблиці рядки фіксованої довжини, то, знаючи довжину рядка, можна легко обчислити байти, які стосуються шуканому рядку.

У випадку зі змінною довжиною рядків, так швидко знайти потрібний рядок таблиці не вийде. Згадай швидкість доступу до ArrayList і LinkedList, тут приблизно схожа ситуація.

Давай порівняємо, як зберігатимуться рядки різної довжини в таблиці, залежно від типу даних.

Рядок CHAR(4) Байт для зберігання VARCHAR(4) Байт для зберігання
'' '' 4 '' 1
'ab' 'ab' 4 'ab' 3
'abcd' 'abcd' 4 'abcd' 5
'abcdefgh' 'abcd' 4 'abcd' 5

Примітка. Тип VARCHAR вимагає на один байт більше за тієї ж довжини, так як він змушений додатково зберігати довжину рядка.

Тимчасові типи в SQL

SQL також має спеціальні типи для зберігання дати та часу. Усього таких типів п'ять:

# Ім'я типу Аналог із Java DateTime API приклад Мінімальне значення Максимальне значення
1 DATE LocalDate '2022-06-30' '1000-01-01' '9999-12-31'
2 TIME LocalTime 'hh:mm:ss[.fraction]' '-838:59:59.000000' '838:59:59.000000'
3 DATETIME LocalDateTime '1000-01-01 00:00:00.000000' '9999-12-31 23:59:59.999999'
4 TIMESTAMP Date '1970-01-01 00:00:01.000000' '2038-01-19 03:14:07.999999'
5 YEAR 1901 2155

Всі дані у запитах записуються у вигляді рядка – в одинарних лапках. Формат запису йде від більшого до меншого:

  • Рік
  • Місяць
  • День
  • Година
  • Хвабона
  • Секунда
  • Частки секунди

Типи DATE, TIME і DATETIME можна вважати аналогами типів з Java DateTme API: LocalDate, LocalTime, LocalDateTime. Логіка приблизно та сама.

Тип TIMESTAMP зберігає дані як мілісекунд, минулих початку 1970 року (стандарт операційної системи UNIX). Саме в такому вигляді їх зберігає тип Date в мові Java.

Ну і нарешті є тип YEAR, довжина якого 1 байт і який зберігає значення від 1 до 255. Тому доступний йому діапазон років - це 1901-2155. Рік 1900 цей тип зберігати неспроможна, оскільки значення 0 використовується для кодування значення NULL.

Зберігання об'єктів у SQL

Для зберігання об'єктів чи текстів великої довжини є особливі типи. Особливо їх розбирати не будемо, але я все ж таки їх перерахую:

# Ім'я типу Пояснення
1 TEXT Використовується для збереження великої довжини текстів. При порівнянні та сортування цього поля використовується тільки перші 100 символів.
2 BLOB Назва розшифровується як Byte Large Object. Зберігається як просто набору байт. Можна використовувати, щоб, наприклад, зберігати в базі даних зображення.
3 CLOB Назва розшифровується як Char Large Object. Використовується для збереження великої довжини текстів.
4 ENUM Дозволяє задати фіксований набір значень і зберігати як значення одне з них.
5 SET Дозволяє задати фіксований набір значень і зберігати як значення будь-яке їхнє підмножина. Зазвичай зберігає їх як бінарну маску.

Теоретично ти можеш будь-який Java об'єкт серіалізувати у вигляді набору байт і зберегти його в базі даних у тип BLOB. Зберегти об'єкт – не проблема. Як із ним працювати далі?

Припустимо, таблиця зберігає мільйон об'єктів у серіалізованому вигляді – як ти виконуватимеш по них пошук? СУБД лише тоді підтримує певний тип даних, коли надає великий набір функцій до роботи з ним.

Вебінар JavaRush