Часть 5.4: Аффинированные типы данных в SQLite3

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Разработчики SQLite3 максимально позаботились о совместимости баз данных SQLite3 с другими СУБД, для этого они ввели аффинированные типы данных для столбцов. Аффинированный тип данных – это рекомендуемый тип данных для столбца (можно сказать, что это приоритетный тип данных), конечно, это не отменяет того, что в любой столбец можно записать любое значение, но в некоторых ситуациях СУБД будет отдавать приоритет аффинированному типу данных и стараться преобразовать значения к рекомендуемому типу.


При создании таблиц мы можем присваивать аффинированный тип данных для столбца, аффинированных типов данных в SQLite3 всего пять:

  • BLOB;
  • TEXT;
  • REAL;
  • INTEGER;
  • NUMERIC.

Столбцы, у которых аффинированный тип данных предназначены для хранения значений с классом NULL, TEXT или BLOB. Если мы попытаемся добавить в столбец с аффинированным типом данных TEXT числовое значение, то оно будет преобразовано в строку.

Столбцы с аффинированным типом данных NUMERIC могут использоваться для хранения значений всех пяти классов. Если в такой столбец мы попытаемся положить текстовое значение, то SQLite3 постарается сконвертировать его либо в INTEGER, либо в REAL (если преобразование произойдет без потерь, и оно будет обратимо). Если преобразование не может произойти без потерь, то SQLite3 сохранит значение с классом TEXT.

Столбец с аффинированным классом INTEGER ведет себя так же, как и NUMERIC.

Столбец с аффинированным классом REAL ведет себя так же, как и NUMERIC, но целочисленные значения будут преобразованы в числа с плавающей точкой.

Столбец с аффинированным BLOB не предпочитает какой-либо определённый класс хранения и не предпринимает никаких попыток принудительного преобразования данных из одного класса в другой.

Определение аффинированности столбца в SQLite3

Аффинированность столбца в SQLite3 определяется неявно во время создания таблицы при объявлении типа данных, который будет храниться в столбце. В SQLite3 действует пять правил определения аффинированности столбца:

  1. Если объявление типа содержит строку «INT», то столбец ассоциируется с аффинированным INTEGER.
  2. Если объявление типа столбца содержит любую из строк «CHAR», «CLOB», или «TEXT», то аффинированность определяется как TEXT. Обратите внимание, что тип VARCHAR содержит подстроку «CHAR», и поэтому ему тоже сопоставляется аффинированный TEXT.
  3. Если объявление типа столбца содержит строку «BLOB» или если тип не указан, то столбец аффинируется с NONE.
  4. Если объявление типа столбца содержит любую из строк «REAL», «FLOA» или «DOUB», аффинированность определяется как REAL.
  5. В остальных случаях столбцу сопоставляется аффинированный NUMERIC.

Последовательность этих правил имеет значение. Приведем сводную таблицу, в которой покажем, как и по какому правилу различные типы данных будут преобразованы в аффинированный тип данных SQLite3

Тип данных столбца, который используется при создании таблицы Аффинированный тип данных Правило определенияаффинированности
INT
INTEGER
TINYINT
SMALLINT
MEDIUMINT
BIGINT
UNSIGNED BIG INT
INT2
INT8
INTEGER 1
CHARACTER (20)
VARCHAR (255)
VARYING CHARACTER (255)
NCHAR (55)
NATIVE CHARACTER (70)
NVARCHAR (100)
TEXT
CLOB
TEXT 2
BLOB
без явного указания типа данных
BLOB 3
REAL
DOUBLE
DOUBLE PRECISION
FLOAT
REAL 4
NUMERIC
DECIMAL (10,5)
BOOLEAN
DATE
DATETIME
NUMERIC 5

Дам пояснения: первое, что нужно запомнить – в SQLite3 нет типов данных, а есть всего лишь пять классов данных, которые указаны в центральной колонке. В левой колонке указаны только некоторые типы данных, которые могут использоваться в других СУБД при создании таблиц, а в правой колонки указано правило, которое используется для преобразования «типа данных другой СУБД» в класс данных СУБД SQLite3. Например, тип данных INT будет преобразован в класс INTEGER по первому правилу, а тип VARCHAR (25) будет преобразован в класс данных TEXT, в этом случае сработает второе правило.

6 комментариев к записи Часть 5.4: Аффинированные типы данных в SQLite3

P@XaH

Всем привет!

Не мог бы пояснить более подробно про аффинированность столбцов из посста я не совсем понял. Спасибо!

Кирилл

Привет! 🙂

Давай еще раз попробую, по пунктам 🙂

1. В SQlite3 нет типов данных, есть только классы данных

2. Классов данных всего пять.

3. В реляционных базах данных есть таблицы и есть столбцы.

4. В обычных СУБД и серверах баз данных у каждого столбца есть тип данных, который определяет значение, которое будет храниться в столбце (число целое, число дробное, текст и др.).

5. В SQLite3 все это не так, в sqlite нельзя явно задать тип (класс) данных столбцу, можно только рекомендовать: мы как бы говорим SQLite: чувак, я хочу, чтобы в этом столбце был NUMERIC, а она отвечает: окей, но если ты в этот столбец будешь подсовывать TEXT, то я буду стараться превратить его в NUMERIC, а если у меня ничего не выйдет, то я сохраню его в этот столбец, как TEXT, а дальше — твоя головная боль.

6. Рекомендованный столбец из п.5 = аффинированный столбец.

7. Разработчики хотели сделать sqlite3 максимально совместимой с другими субд.

8. Поэтому sqlite3 легко преобразуют всякие чары, варчары, тиниинты и прочие типы данных в свои классы данных. Например, если ты задашь столбцу тип tinyint, то SQLite автоматически его преобразует в класс данных INTEGER, который станет аффинированным для столбца.

Надеюсь, пояснил.

Teman

День добрый.

В части 5.2 вы пишете, что есть следующие 5 классов:

NULL, INTEGER, REAL,TEXT, BLOB.

Здесь вы называете 5 классов уже с отличиями:

NUMERIC, INTEGER, REAL,TEXT, BLOB

Также 3 правило определения аффинированности ясности не вносит:

«Если объявление типа столбца содержит строку «BLOB» или если тип не указан, то столбец аффинируется с NONE». Притом, что в таблице в 3 правиле указано BLOB.

Кирилл

Спасибо, что внимание обратили! Типизация в SQLite — не самая простая вещь... У столбцов вообще нет явного типа данных, а есть только рекомендуемый. Документация SQLite выделяет действительно пять классов данных: NULL, INTEGER, REAL,TEXT, BLOB.

Но для столбца аффинированный (рекомендованный) тип данных можно задать: NUMERIC, INTEGER, REAL,TEXT, BLOB. Не логично как-то использовать NULL, как рекомендованный тип данных, поэтому его отбрасываем.

Теперь, собственно, насчет аффинированного типа данных. В SQLite есть только классы данных, указанные выше. А аффинированный тип данных — это абстрактное понятие. Если вы зададите столбцу аффинированный тип данных NUMERIC, то SQLite будет считать предпочтительным классом данных хранения INTEGER или REAL (класса данных NUMERIC нет). Грубо говоря, даже не смотря на то, что вы говорите: данный столбец NUMERIC, sqlite записывает в него данные, отдавая приоритет классам INTEGER или REAL.

По второй части — моя вина, извиняюсь за неполную информацию. Выдержка из документации SQLite: «(Historical note: The „BLOB“ type affinity used to be called „NONE“. But that term was easy to confuse with „no affinity“ and so it was renamed.)».

В данном случае NONE и BLOB считайте синонимами.

Кирилл

Для простоты объяснения попробую пример привести. Представьте, что у вас есть яблоки зеленые, яблоки красные, бананы и груши — это классы данных. И есть три коробки с надписями: яблоки, груши, бананы — это аффинированные типы данных. Понятно, что в коробку с надписью «Яблоки» стоит класть зеленые яблоки и красные яблоки, но по невнимательности в этой коробке могут оказать бананы или груши.

Teman

Теперь понятно, что аффинированные типы данных и классы данных хоть и могут носить одинаковые наименования, но, по сути, разные вещи.

Текст комментария: