Часть 2.4: Другие полезные команды SQLite3. Dot-команды терминала SQLite3
Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3.…
Часть 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 действует пять правил определения аффинированности столбца:
- Если объявление типа содержит строку «INT», то столбец ассоциируется с аффинированным INTEGER.
- Если объявление типа столбца содержит любую из строк «CHAR», «CLOB», или «TEXT», то аффинированность определяется как TEXT. Обратите внимание, что тип VARCHAR содержит подстроку «CHAR», и поэтому ему тоже сопоставляется аффинированный TEXT.
- Если объявление типа столбца содержит строку «BLOB» или если тип не указан, то столбец аффинируется с NONE.
- Если объявление типа столбца содержит любую из строк «REAL», «FLOA» или «DOUB», аффинированность определяется как REAL.
- В остальных случаях столбцу сопоставляется аффинированный 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, в этом случае сработает второе правило.
Всем привет!
Не мог бы пояснить более подробно про аффинированность столбцов из посста я не совсем понял. Спасибо!
Привет! 🙂
Давай еще раз попробую, по пунктам 🙂
1. В SQlite3 нет типов данных, есть только классы данных
2. Классов данных всего пять.
3. В реляционных базах данных есть таблицы и есть столбцы.
4. В обычных СУБД и серверах баз данных у каждого столбца есть тип данных, который определяет значение, которое будет храниться в столбце (число целое, число дробное, текст и др.).
5. В SQLite3 все это не так, в sqlite нельзя явно задать тип (класс) данных столбцу, можно только рекомендовать: мы как бы говорим SQLite: чувак, я хочу, чтобы в этом столбце был NUMERIC, а она отвечает: окей, но если ты в этот столбец будешь подсовывать TEXT, то я буду стараться превратить его в NUMERIC, а если у меня ничего не выйдет, то я сохраню его в этот столбец, как TEXT, а дальше — твоя головная боль.
6. Рекомендованный столбец из п.5 = аффинированный столбец.
7. Разработчики хотели сделать sqlite3 максимально совместимой с другими субд.
8. Поэтому sqlite3 легко преобразуют всякие чары, варчары, тиниинты и прочие типы данных в свои классы данных. Например, если ты задашь столбцу тип tinyint, то SQLite автоматически его преобразует в класс данных INTEGER, который станет аффинированным для столбца.
Надеюсь, пояснил.
День добрый.
В части 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 считайте синонимами.
Для простоты объяснения попробую пример привести. Представьте, что у вас есть яблоки зеленые, яблоки красные, бананы и груши — это классы данных. И есть три коробки с надписями: яблоки, груши, бананы — это аффинированные типы данных. Понятно, что в коробку с надписью «Яблоки» стоит класть зеленые яблоки и красные яблоки, но по невнимательности в этой коробке могут оказать бананы или груши.
Теперь понятно, что аффинированные типы данных и классы данных хоть и могут носить одинаковые наименования, но, по сути, разные вещи.