Thread by @vdbv

Сегодня я случайно решил почитать документацию по SQLite. Я офигел и прозрел. Если вкратце, то SQLite — это такой джаваскрипт в мире баз данных. Тред с весёлыми запросами в консоли:

1. В SQLite, как и в любой БД, полям таблиц задают типы. Но можно записать значение любого типа в любое поле. SQLite его сконвертирует в нужный тип, а если не сможет, запишет как есть.

2. Неявное приведение типов можно не заметить, пока явно не проверишь функцией typeof(). Видно, что строка '42' сконвертировалась в целое число 42, потому что у поля тип INTEGER, а число 33950 записалось строкой.

3. В SQLite у поля может вообще не быть типа! Тогда оно будет просто хранить любые значения как есть без конвертации типов.

4. Названия типов в SQLite могут быть любыми! 😱 Вот я создаю таблицу с полями a, b и c, у которых типа SMOOTHIE, TOASTER и POPCORN:

5. SQLite по названию типа тупо поиском по подстроке назначает ему так называемый Type Affinity, то есть к какому типу данных будет "тяготеть" это поле, когда в него вставляют значения. А от этого уже зависит, какими способами данные будут храниться на диске.

6. Если в названии типа содержится INT, то поле будет стремиться преобразовать вставляемые значения в целые числа и хранить их в ячейке размером от 1 до 8 байт в зависимости от величины числа. Ну а если не получится преобразовать, так в другом подходящем виде, как мы видели выше.

7. Аналогично, если есть CHAR, CLOB или TEXT, то поле будет стараться хранить всё в виде строк. Если BLOB или тип не задан, то как бинарные данные. REAL, FLOA или DOUB — как числа с плавающей точкой.

8. Наконец, если ни одно из предыдущих правил не сработало, поле получает Type Affinity по имени NUMERIC, и будет стараться конвертировать значения как в целые числа, так и в числа с плавающей точкой. Если получится, конечно.

9. Становится возможной полная анархия. Создаём поля типа MINTY (целочисленное, поскольку содержит INT), SURREAL (число с плавающей точкой), CHARMED (текстовое), LOLBLOB (внезапно, BLOB) и DINOSAUR (просто числовое, то есть numeric) и заполняем их разными значениями:

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

11. Первичные ключи в таблицах иногда могут быть NULL. Это официально признанный баг, который не стали фиксить, потому что к моменту обнаружения было слишком много баз данных, зависящих от этого бага.

12. Лечится это, например, указанием полного имени типа INTEGER для первичного ключа (а не просто INT), тогда вместо NULL будут вставляться автоинкрементированные значения:

13. Есть у SQLite и другие странности. И хоть я в шутку сравнил его с джаваскриптом, это чудесная и очень мощная технология, и пользоваться им можно и нужно (в отличие от джаваскрипта). А если понимать эти особенности, пользоваться будет легче.

14. Почитать больше:
Про странности и особенности — sqlite.org/quirks.html

Про хранение данных и преобразование типов — sqlite.org/datatype3.html