Рекомендации по выбору типа данных в SQL-таблицах

https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names вспомнилось.

Ты из принципа привел только текстовые примеры?

email адреса

Тут лучше использовать тип, который сравнивает адреса без учёта регистра. В Postgres есть для такого citext. Можно также сделать кастомный тип, в котором будет базовая проверка формата (например, что email содержит символ @ и точку, но это вообще спорный вопрос).

А есть какие-нибудь универсальные рекомендации по выбору типа данных в SQL-таблицах для таких значений…

Универсального нет, всё зависит от того, что ты с этими данными будешь делать внутри самой БД. Если просто хранить (и, например, сравнивать), то хватит обычного строкового типа.

А какие варианты кроме text для всего перечисленного?

People’s names are case sensitive.

People’s names are case insensitive.

Простите, а как оба варианта могут одновременно быть неправильными?

Есть только одно важное правило. Для денег используй decimal, а не float. Остальное по желанию. Я например считаю, что статусы должны быть текстовые, но это вкусовщина.

Это достаточно большая тема, думаю, что со временем понимание придёт само, без подсказок.

Кстати, между VARCHAR и TEXT во многих базах нет разницы, но с TEXT не надо задумываться о размере.

И с UUID для первичного ключа есть подводные камни, но это тема на огромную статью. Например, в MySQL нет нормального встроенного типа UUID, поэтому джойны по гигантским таблицам, использующим UUID в качестве первичного ключа, будут адски тормозить (и это соверешенно контр-интуитивно, пока не столкнешься). В других базах с UUID может быть по-разному, у меня в закладках есть несколько статей об этом, но сейчас некогда искать.

Names are incomparable, значит.

Если просто хранить (и, например, сравнивать), то хватит обычного строкового типа.

На примере email адреса, как его объявить в таблице: varchar(200), varchar(300) или varchar(1000)? Понятно что где-то есть описание формата и оттуда можно узнать максимальную длину адреса, но задача то ведь типовая и значит должно быть типовое решение (например, в большинстве случаев используй email varchar(320) и не знай проблем).

Для денег используй decimal, а не float.

Это понятно, но опять же вопрос: сколько знаков после запятой?

varchar. в коде можешь конвертировать потом в любое что тебе там нужно, но могут быть проблемы с сортировкой. тогда есть какое-то типа sort (cast as int) asc; к примеру

varchar

3

Например, в MySQL нет нормального

MySQL вообще все шаблоны SQL рвет.

сколько знаков после запятой

Я 4 делаю по умолчанию, но вообще возможны варианты.

между VARCHAR и TEXT во многих базах нет разницы, но с TEXT не надо задумываться о размере

/0

То что text безразмерный уже говорит о том, что разница таки есть.

Например, в MySQL нет нормального встроенного типа UUID

Он и не особо нужен, есть BINARY(16). На крайняк CHAR(32) CHARSET ASCII

джойны по гигантским таблицам, использующим UUID в качестве первичного ключа, будут адски тормозить

Не будут, если ты не олень. Но само по себе использование uuid в качестве PK так себе идея, т.к. обычно для разных целей требуется упорядоченность записей.

Это понятно, но опять же вопрос: сколько знаков после запятой?

3

Я 4 делаю по умолчанию, но вообще возможны варианты.

Ну вот, мнения разошлись. Допустим надо хранить значение в рублях с учетом копеек. Сколько знаков после запятой желательно хранить в этом случае?

Для денег используй decimal

Почему в копейках не хранить?

Почему в копейках не хранить

Потому что можно хранить не в копейках. Данные из БД могут использовать люди которые не имеют специальной подготовки или представления о том, что «там копейки». Принцип наименьшего удивления.

То что text безразмерный уже говорит о том, что разница таки есть.

https://stackoverflow.com/questions/4848964/difference-between-text-and-varchar-character-varying

Для душнил: разница есть в названии, а на практике её нет, поэтому проще во всех ситуациях использовать TEXT, чтобы не париться с размером, который ни на что не влияет, кроме валидации.

Он и не особо нужен, есть BINARY(16). На крайняк CHAR(32) CHARSET ASCII

Сразу видно человека, который не работал ни с реляционными СУБД, ни с MySQL, ни с UUID, ни с большими базами.

Не будут, если ты не олень. Но само по себе использование uuid в качестве PK так себе идея, т.к. обычно для разных целей требуется упорядоченность записей.

А ты типа пробовал? У тебя есть базы на сотни миллионов записей с ключами UUID, и ты их джойнил, и говоришь исходя из опыта?

Явно нет, потому что у меня такие базы есть, и они тормозят. Хотя MySQL — вообще боль, и я бы никогда ни в одном проекте её не использовал, но существует legacy, к сожалению.

И про упорядоченность записей просто шедевр.

И про упорядоченность записей просто шедевр.

Почему? На упорядоченных записях можно, например, построить секции. Как их построить на UUID?

сотни миллионов записей с ключами UUID

Типа да. И это не очень уж большая база.

Явно нет, потому что у меня такие базы есть, и они тормозят

Ну сделай выводы.

MySQL — вообще боль, и я бы никогда ни в одном проекте её не использовал

Отличная СУБД, если тебе не нужно программирование в БД (километровые хранимки и т.п.).

про упорядоченность записей просто шедевр

ЛОЛ, ну о чём с тобой вообще говорить?

А с адресами все еще хуже.

Почему? На упорядоченных записях можно, например, построить секции. Как их построить на UUID?

Ну так любой выбор типа имеет как преимущества, так и недостатки. Секционировать по UUID нельзя, зато легко шардить, например.

Плюс секционировать можно не только по первичному ключу, насколько я помню. Да и вообще, секционирование - довольно редкая штука, потому что немного ситуаций в жизни, когда её можно применить.

Отличная СУБД, если тебе не нужно программирование в БД (километровые хранимки и т.п.).

Говносубд, которая отстаёт всегда от всего остального на световые годы. По поддержке типов данных (UUID, JSON, даже, пилять, unsigned int в этой базе нет - привет побитовым операциям), по поддержке фич SQL, реально полезных притом, по поддержке инструментов вокруг, и т.д. и т.п.

Даже в каких-то мелочах у них всё сделано, простите за выражение, через жопу. Достаточно вспомнить проблему их «гениальной» кодировки utf8mb3, за одно только это терпеть не могу эту базу. Кстати, многие люди пытались найти того гения, кто разработал эту кодировку, и внедрил её как кодировку по умолчанию. Но сделать это так и не удалось.

ЛОЛ, ну о чём с тобой вообще говорить?

О чём можно говорить с человеком, который умеет упорядочивать только по первичному ключу?

jsonb для всего.

Шутка.

Чо?

Тип JSON появился в версии 5.7, с релиза которой почти 10 лет прошло. А что с unsigned int не так?

Отличная СУБД, если тебе не нужно программирование в БД (километровые хранимки и т.п.).

Которая позволяет сравнивать строки и числа и даже ошибку не выдаст. Знаем, видели, проходили.

Поддержка операций над JSON слабая.

Отсутствие unsigned int создаёт сложности при хранении в БД битовых полей и выборки по ним.

Отсутствие unsigned int создаёт сложности при хранении в БД битовых полей и выборки по ним.

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html там же есть unsigned типы. Можно ссылку или пример, в котором видно проблему?

Сейчас не могу вспомнить в чём проблема, но она точно была (или есть) с тем, что не работали запросы вида:

SELECT * FROM foo WHERE bitmask &= 0xF00741

К сожалению, я нигде не записал, из-за чего именно они не работают, а т.к. возился с этим два года назад, то подробности забыл.

Но точно помню, что тогда прочитал всю документацию, попробовал все варианты, и оно так и не заработало у меня правильно. Был какой-то важный нюанс… Сейчас нет времени повторить всё с самого начала.

При этом, в том же Postgres вообще таких проблем нет. Как и со многим другим. MySQL постоянно преподносит сюрпризы, чаще всего, неприятные.

который умеет упорядочивать только по первичному ключу?

А есть какой-то сакральный смысл делать инкрементный столбец не PK? Типа чтобы БД была пожирнее и упорядочивание тормозило, т.к. нужно ходить в отдельный индекс?

unsigned int в этой базе нет - привет побитовым операциям

Всё там есть. Отдельный привет говнокодерам, которые делают побитовые операции на числах. Они должны страдать, да.

Но само по себе использование uuid в качестве PK так себе идея, т.к. обычно для разных целей требуется упорядоченность записей

Чиво?

А есть какой-то сакральный смысл делать инкрементный столбец не PK?

При чём здесь инкрементный столбец? Этого понятия не существует вообще в реляционной теории, это артефакт MySQL.

Первичный ключ не обязан быть суррогатным, не обязан состоять из одного столбца, не обязан быть инкрементальным. И ситуаций, когда нужен UUID или что-то похожее на него — в больших высоконагруженных системах — вагон и маленькая тележка.

По крайней мере в pg varchar никогда не надо использовать.

На примере email адреса, как его объявить в таблице

citext.

Крипта не влезет, например.

ChatGPT:

Для имен людей, названий улиц, доменных имен, email адресов и URL-адресов рекомендуется использовать тип данных VARCHAR. Для файловых путей можно использовать VARCHAR или TEXT, в зависимости от длины пути. Для телефонных номеров рекомендуется использовать тип данных INTEGER или BIGINT. В любом случае, рекомендуется соблюдать длину данных, которая является достаточной для хранения всех возможных значений.

/thread :)

Но само по себе использование uuid в качестве PK так себе идея, т.к. обычно для разных целей требуется упорядоченность записей.

Тащемта эту проблему решают новые типы UUID.

обычно для разных целей требуется упорядоченность записей

Объясните кто-нибудь, зачем? Есть к примеру модели пользователь, продукт и компания, нафига их сортировать по id?

Еще раз - как пример - для секционирования.

Вот я смотрю на таблицу, распиленную на 27 секций по id, по ~70ГБ каждая. Как вы себе представляете ворочить таблицу в ~2ТБ одним куском?

Это не про термины «моделей». Это про кишки БД.

---

UUIDv7, да, прикольный. Не знал, что такое пытаются пилить.

MySQL вообще все шаблоны SQL рвет.

Что насчёт MariaDB? Она лучше MySQL в чём-нибудь?

citext

Это расширение доступно только в PosgtreSQL.

Секции (partition) или шарды?

Потому что если мы просто распиливаем таблицу по секциям, к которым обращения идут равномерно, то что мы выигрываем?.. Ну разве что если база поддерживает вынесение каждой секции на отдельный жесткий диск, и мы это поддерживаем, и ОС настроена так, что обращение к отдельным дискам эффективнее, чем к одному.

Найти ситуации, когда таблицу удобно секционировать, довольно непросто. И необязательно секционировать её по первичному суррогатному ключу. Например, в таблице с логами будет дата, и логично и эффективно бить её на секции по датам. Но поле с датой вряд ли будет первичным ключом.

Например, в таблице с логами будет дата

поле с датой вряд ли будет первичным ключом

Так-то с оговоркой, что есть лучшие варианты для логов чем РСУБД, где как-раз дата и будет.

Ок, а есть ещё примеры? В контексте:

обычно для разных целей требуется упорядоченность записей

Правда интересно.

При чём здесь инкрементный столбец? Этого понятия не существует вообще в реляционной теории, это артефакт MySQL.

Инкрементный столбец не только в MySQL есть. Например, в PostgreSQL для подобного поведения есть serial, а в MSSQL есть identity. При этом в самом SQL есть возможность объявить колонку как identity column.

Инкрементный столбец не только в MySQL есть. Например, в PostgreSQL для подобного поведения есть serial, а в MSSQL есть identity. При этом в самом SQL есть возможность объявить колонку как identity column.

Рекомендую тогда уж копнуть глубже и выяснить, что на самом деле происходит в PostgreSQL при объявлении SERIAL или IDENTITY. Тем более, что копать несложно и неглубоко.

И какое это вообще имеет отношение к обсуждаемому наезду, что «UUID ключи не нужны».

а есть ещё примеры?

Я не знаю, что имел в виду под этой фразой no-such-file. Лучше его и спросите.

Могу предположить, что так, гипотетически, можно отличать источники данных, если они в конце концов оказываются в одной БД, но формировались изначально в разных местах. Допустим с ключом от 1 до 100 (условно) - данные из пункта А, а от 101 до 300 - из пункта Б. И никакого другого способа нет, кроме как программно их сливать вместе. Честно говоря я такого не видел, но почему бы нет?