Комментарии 178
Зачем названия таблиц и полей в SQL запросе даны в кавычках? Ещё и константное значение взято в такие же кавычки…
И второй вопрос — при использовании int не придётся ли конструкторы обмазывать проверками передаваемых значений?
SQLYog такой формат предложил, я не стал отказываться т.к. выглядит, на мой взгляд, красиво.
Перед записью в базу валидацию лучше всегда прикручивать т.к. будь то строка, число или сам enum, всегда нужно убеждаться что в базу улетят правильные значения.
некоторые субд поддерживают case-sensitive (и наличие специмволов) для названий объектов таких как поля например. В итоге чтобы корректно их указывать применяют кавычки.
Там "не совсем кавычки" - это символ, который нам называли "мягкий апостроф" - многие конструкторы запросов его подставляют, да и точки с дефисами иногда в названиях полей допускаются - тут без ограничителей обойтись будет трудно...
Зачем названия таблиц и полей в SQL запросе даны в кавычках?
Ты пошутил?
Чтобы не было проблем, если название таблицы или поля совпадает с зарезервированными словами.
Например, запрос "CREATE TABLE user..." в Postgresql завершится ошибкой, а он же с кавычками "CREATE TABLE `user`..." выполнится корректно, потому что слово user в Postgresql зарезервированное.
Зачем создавать временную колонку, если можно поменять тип поля с enum на varchar, проапдейтить таблицу (если нужно), поправить enum и обратно поменять тип поля с varchar на enum?
Что будет с сервисами, которые будут стучаться в базу в промежуточный момент, когда колонка внезапно стала не enum'ом, а varchar'ом?
Ничего, скастятся в enum на бэке точно так же.
Что будет с сервисами, которые будут стучаться в базу в промежуточный момент, когда колонка внезапно стала не enum'ом, а varchar'ом?
Если сервисы работают со строковым представлением значения - они даже не заметят подмены. А если с числовым - то да, могут и обалдеть, и даже напоросячить..
Промежуточный вариант? Это как, у нас транзакций нету?
Вполне себе рабочий и безболезненный вариант.
А нормализация не требует, чтобы жанр был отдельной таблицей, а в таблице игр хранился ее id ?
В данном конкретном случае да, лучше выносить жанры в отдельную таблицу и связывать их через foreign связь.
Описанным в статье способом обычно статусы хранят, например, но для примера и жанры подойдут.
так это и есть статья о пользе нормализации без использования слова "нормализация"
так это и есть статья о пользе нормализации
Статья не имеет никакого отношения к нормализации.
Имеет самое прямое. В статье можно наблюдать все проблемы, отсутствие которых считается достоинством нормализованных бд. Собственно, я по заголовку и при постановки проблемы сразу ожидал решения "мы вынесли жанр в отдельную таблицу". Но почему-то вместо этого один костыль заменили другим...
Имеет самое прямое.
Замена ENUM на внешний ключ не влияет на нормализацию аж никак. Иными словами, в какой нормальной форме была таблица, в той и осталась.
А нормализация не требует, чтобы жанр был отдельной таблицей ?
Не требует. Но удобнее завести отдельную таблицу.
Таким образом, тип полей
enumв базе - это лютое зло.
Слишком категорично. В целом верно, но в некоторых случаях это поле вполне приемлемо. Например для колонки Пол (мужской, женский, не выбран). Enum можно использовать только в случаях, когда есть уверенность, что значения ВСЕГДА будут неизменными, тогда и проблем, описанных выше, не будет.
В моей практике такой уверенности никогда не было. Недавно ломали колонку гендера...
-- before
gender ENUM('man', 'woman')
-- after
gender ENUM('man', 'woman', 'other')
А кто такие other?
Либо "не указан", либо выполнение требований современного общества...
озербайджанцы наверное.
attack_helicopter
Другие
В нашем случае те, кто не хочет сообщать свой пол, записываются как null (поле в базе nullable), а те кто хочет сообщить, но не относит себя к мужчинам или женщинам - выбирает other.
Да вы безнадежно отстали от жизни! Вот вам, для референса, последняя ревизия политически одобренной формы для формы выбора пола
Повезло, что он был не BOOLEAN
В моей практике такой уверенности никогда не было. Недавно ломали колонку гендера...
Точно, с полом не все так просто. Вот, например, как определены коды для пола в стандарте ISO-5218 Representation of human sexes:
+-----+---------------+
| Код | Пол |
+-----+---------------+
| 0 | Неизвестен |
| 1 | Мужской |
| 2 | Женский |
| 9 | Неприменим |
+-----+---------------+
А в базах данных американского ФБР (Федеральное бюро расследований) используются следующие коды:
+-----+----------------------------------+
| Код | Пол |
+-----+----------------------------------+
| 0 | Неизвестен |
| 1 | Мужской |
| 2 | Женский |
| 3 | Мужской, бывший женский |
| 4 | Женский, бывший мужской |
| 5 | Мужской, изменяющийся на женский |
| 6 | Женский, изменяющийся на мужской |
| 7 | Невозможно определить |
| 9 | Неприменим |
+-----+----------------------------------+
Информация взята из книги Ханс Ладани "SQL. Энциклопедия пользователя", 1998 год, издательство "Диасофт".
мужской, женский, не выбран
true, false, NULL. Или false, true, NULL, кому как больше нравится.
Не надо так… СУБД не для этого развивались в то, чем они стали. У Вас даже семантика исчезла при данном представлении. Даже две представленные Вами альтернативы об этом свидетельствуют…
Для сохранения семантики надо назвать поле HAS_PENIS. Или завуалированно – IS_MAN. Правда, будет неполиткорректно.
А если пользователь мужчина, но по каким-то причинам лишился пениса? В таком случае нельзя будет поставить ему HAS_PENIS = true.
А если пол касается NPC, который может быть гермафродитом, или мало ли что в выдуманном мире?
Не нужно нести бред, лишь бы пошутить. Не забывайте девиз правильной лурки - "факты > лулзы"
А причём здесь выдуманный мир?
Может быть, вы объясните, с какой целью вообще отслеживать в таблице пол NPC в выдуманном мире, отличный от грамматической категории рода?
Грамматическая категория рода в русском языке принимает три значения, плюс особые случаи типа "сирота" и "сани". Т.е. если надо хранить род NPC для согласования слов в диалогах, то "родов" у него будет четыре.
А если нужно поддерживать несколько локалей - то чёрт ногу сломит, во что превратится дерево согласовательных классов.
Короче, рассчитывать, что какой-то список будет вечным и неизменным - не стоит. Лучше обрабатывать всё единообразно, через дополнительную таблицу.
Для отыгрыша ролевой составляющей?
Конечно, не надо. Я бы и сам использовал енам. Но мой комментарий был не о поле, а о значении "не выбрано". Которое как раз и означает отсутствие значение, т.е. то, для чего в соседнем треде сделан костыль:
-- before
gender ENUM('man', 'woman')
-- after
gender ENUM('man', 'woman', 'other')Вот этот other избыточен, если мы следуем формулировке "мужской/женский/не выбран". Причём "не выбран" - это вовсе не то же самое, что "предпочитаю не говорить" или "другой", ибо первое - это отсутствие значения, а второе - сознательный выбор пользователя.
Причём "не выбран" — это вовсе не то же самое, что "предпочитаю не говорить" или "другой", ибо первое — это отсутствие значения, а второе — сознательный выбор пользователя.
Вот вы и сами это признали. А теперь посмотрите на тот "костыль" ещё раз, и осознайте, что other и NULL — это разные значения, а вы их зачем-то решили объединить.
Мне кажется, проблема с NULL в том, что оно означает одно из "другой", "не скажу", "невозможно определить", "ещё не спрашивали", ..., а какое из них -- не очевидно (надо смотреть в документациии, которая (как обычно) уже устарела).
Следите за контекстом. Тред начался с формулировки
Например для колонки Пол (мужской, женский, не выбран).
И указанный код опубликован в этом треде. В этом контексте нет "другого", а в этом коде не указано, что поле с этим енамом может быть NULL. Из чего очевидный вывод: в этом коде other используется ВМЕСТО NULL.
План действий будет таков:
Если ничего не понимать в сути данного типа, можно и так. А если понимать, то можно и проще. Вот варианты (написаны для MySQL):
Вариант 1. Добавить нужное значение, обновить, убрать ошибочное значение.
ALTER TABLE games
MODIFY COLUMN `genre` ENUM('action','adventure','shooter','rasing','racing') NOT NULL;
UPDATE games SET genre = 'racing' WHERE genre = 'rasing';
ALTER TABLE games
MODIFY COLUMN `genre` ENUM('action','adventure','shooter','racing') NOT NULL;Вариант 2. Описан выше в комментарии от @nightlord189 - через VARCHAR.
ALTER TABLE games
MODIFY COLUMN `genre` VARCHAR(10) NOT NULL;
UPDATE games SET genre = 'racing' WHERE genre = 'rasing';
ALTER TABLE games
MODIFY COLUMN `genre` ENUM('action','adventure','shooter','racing') NOT NULL;Вариант 3. Конвертация в TINYINT (если список значений содержит более 63 значений - в SMALLINT) с последующей обратной конвертацией в ENUM, но с правильным описанием. Оптимальный вариант, ибо не требует UPDATE. И очень быстрый, потому что не затрагивает данных, корректируя исключительно метаданные.
ALTER TABLE `games`
MODIFY COLUMN `genre` TINYINT NOT NULL;
ALTER TABLE `games`
MODIFY COLUMN `genre` ENUM('action','adventure','shooter','racing') NOT NULL;Полигон для испытаний: https://dbfiddle.uk/2d7_al1e
Таким образом, тип полей
enumв базе - это лютое зло.
Безответственное и ничем не обоснованное утверждение.
Также можно ответить на вопрос "почему integer, а не string": дело в том, что поля типа
stringподвержены грамматическим ошибкам и всё что они дают - лишь удобство чтения таких данных в одной конкретной таблице без использования SQL запросов. Но если в слове будет допущена ошибка или слово нужно будет заменить на другое
То есть в переводе на русский: если ошибка допущена в значении в БД - это зло вселенское. А если она будет допущена в клиентской части - так это нормально...
Альтернатива этой боли - полный отказ от полей типа
enumв пользуinteger.
Вообще-то это называется "нормализация".
если список значений содержит более 63 значений - в SMALLINT
Выходит, в некоторых случаях можно сэкономить половину памяти, просто вспомнив, что числа могут быть UNSIGNED (каковыми и полагается быть почти всем этим перечислениям). ? Да и значение 63 кажется заниженным.
Если в БД реализован механизм сжатия строк, аналогичный row compression в MS SQL, то знаковые числа до 127 (без знаковые до 255) и так будут занимать 1 байт (точнее - полтора), даже если объявлены 64-битными.
Но мне больше импонирует реализация enum в PostgreSQL, когда enumsortorder float4, что позволяет добавлять новые значения в enum до или после указанного существующего значения. Все же порядок сортировки порой имеет значение.
Упс... это самый обычный ляпсус. Должно быть если список значений содержит более 255 значений - в SMALLINT.
А так нельзя?
CREATE TYPE gamegenre AS ENUM ('action','adventure','shooter','rasing');
Использовать для колонки этот тип. А для правки:
ALTER TYPE gamegenre RENAME VALUE 'rasing' TO 'racing';
Вроде не теряется ничего (PostgreSQL). И добавлять значения можно.
Минус енама в том еще, что Вы не можете в этом случае ограничиться чисто изменением в одном месте. Вам придется поправить его значения на уровне базы (если Вы хотите добавить новую опцию), а еще и в приложении, которое долждно этот новый вариант обрабатывать.
Зато если энам на уровне базы, то это гарантирует, что никакое "левое" (пустое или то, которого нет в перечислении) значение в таблицу никогда не сохранится. Мне ближе такой вариант, потому что с ним часто можно намного меньше смотреть в код, а сразу смотреть в базу, чтобы понять, что вообще делает приложение и какие данные у нас могут быть.
А так это шкала между гибкостью и гарантиями целостности, тут каждый сам выбирает, что удобнее для проекта.
Если энам меняется нечасто, то почему бы его и не прописать в базу?
А если новые значения добавляются каждый месяц - лучше оставить строкой/интом и хранить это в приложении.
чтобы полчить гарантии целостности как раз и нужна отдельная таблица справочник в которой список допустимых значений, и FK на нее из основной таблицы который и не даст вставить ничего чего нет в справочнике.
Ну так енум это почти тоже самое по функциональности. Некий сахар для удобства. И в ряде случаев вполне оправданный.
Можно и в отдельной хранить. Но тогда придется джойнить таблички, чтобы получить полную информацию о сущности. Опять же зайдя в таблицу, удобнее сразу видеть "order 1, status created, type juridical", а не "order 1, status 15, type 84".
В общем, тут кому какой фломастер нравится.
"любишь субд использовать - люби и таблички джойнить" (с) народная поговорка
Тут вопрос не в том, что нравится, а насколько пропускная способность соединения критична в данной задаче. В том же protobuf разница размеров записей в Вашем примере - в четыре раза.
Удобней конечно видеть
"Номер ПУ": 123456, "Регистр": "Накопленная реактивная энергия второй фазы", "Значение": 1234.5
чем
"Номер ПУ": 123456, "Регистр": 22, "Значение": 1234.5
Но передавать по каналам связи и хранить в БД второе намного эффективней.
"Отдельная таблица" и "нормализация" - имели смысл во времена дорогих носителей. И то уже тогда разработчики шли на компромисс между "так канонично и требуется" и "куча джойнов замедляют выборки". В своей жизни разработок и работы с бд, дай бог 2-ю нормальную форму видел, но никогда не первую.
Более того, относительно нормальных форм часто не учитывают один нюанс. Если вы вынесли данные в отдельную таблицу с первичным ключом, то у вас статичные данные превратились в объект с состоянием и поведением. Теперь можно обновить значение свойства, и оно обновится для всех строк в других таблицах с этим внешним ключом. А до вынесения можно было поменять значение для любой отдельной строки, не меняя другие, где указано то же значение. Это не эквивалентные структуры данных. Они эквивалентны только для данных, которые никогда не меняются.
Ну в том и нюанс, что сначала можно было сделать
UPDATE table SET status = 'new_status' WHERE id = 123
а после вынесения этого поля в отдельную таблицу сделать
UPDATE status_table SET status_name = 'new_status' WHERE id = 1
уже нельзя. Значение поменяется не только для строки table:123, а везде, где status_table_id = 1.
Раньше мы изменяли данные, и они становились 'другие', теперь изменение тех же данных это изменение свойства объекта с сохранением его identity для всех остальных, то есть объект остался 'тот же самый'. До вынесения такого эффекта не было, в этом и есть отличие.
Поэтому да, чтобы было эквивалентно, надо не просто выносить в отдельную таблицу, а еще и запрещать для нее UPDATE, и заменять UPDATE который был раньше на INSERT в новую таблицу и UPDATE основной таблицы.
Или другой пример. Сначала у нас была таблица заказов с полями ('client_first_name', 'client_last_name'), потом мы решили вынести их в таблицу 'clients'. А потом клиент Мария Иванова вышла замуж и стала Мария Петрова, и ее фамилия поменялась во всех предыдущих заказах, хотя правильно указывать новые данные только в новых.
То что вы описали это и есть то о чем я говорю. Мне непонятно, на что вы возражаете. "UPDATE запрещён вообще как класс" именно потому что данные, вынесенные в отдельную таблицу, превращаются в объект.
хоть нормализованная с ссылкой на новую запись о конкретной фамилии
Так у такой нормализации та же проблема, данные превращаются в объект с поведением и первичным ключом. Теперь нельзя поменять фамилию в одном конкретном заказе, а раньше было можно. Неважно, хранится ли в новой таблице история изменений клиента или актуальное состояние клиента.
Теория баз данных не делает различия, история там или нет, она работает с кортежами данных. И многие ее изложения обычно говорят только "Нормализация предназначена для приведения структуры БД к виду, обеспечивающему минимальную логическую избыточность", не упоминая, что логически результаты до и после вынесения колонок не эквивалентны, и такое вынесение не всегда подходит.
Что вообще есть такая проблема, как «обновить часть записей»
Я не говорил, что есть такая проблема. UPDATE нескольких записей вроде нормально работает во всех базах данных.
Я говорил, что результат до и после вынесения группы полей в таблицу и замену их на значение первичного ключа не эквивалентный, и проявляется это при изменениях данных. Раньше UPDATE "prev_status" на "new_status" делалась для одной строки, после замены их на первичный ключ UPDATE тех же данных "prev_status" на "new_status" повлияет на все связанные строки. Это даже не проблема, а эффект. А нюанс в том, что про него не говорят и часто не учитывают.
Эквивалентность есть, просто для получения того же результата необходимо сделать немного другую последовательность действий
Я говорю "'Сделать X до изменения' не эквивалентно 'Сделать X после изменения". Вы говорите "Сделать X+Y после изменения эквивалентно". Ну так я с этим и не спорил. Напротив, я именно на это и указывал изначально с первого комментария. Даже хотел написать, что для эквивалентности надо делать INSERT в новую таблицу, но подумал, что тут все специалисты, и про это и так знают.
просто потому, что у вас тогда есть иммутабельность/цепочка для аудита/етц
Бизнес-требования иммутабельности это другой вопрос. Эффект, про который я говорю, не зависит от их наличия. Обновление фамилии клиента в предыдущих заказах вполне может никак не влиять на бизнес, и обновление таблицы клиентов может быть разрешено, но разница между тем, когда имя клиента записано в таблице заказов, и когда оно связано через внешний ключ, все равно есть. Иногда она важна, иногда нет, а иногда сначала неважна, а потом становится важна.
Поэтому при вынесении имени клиента в отдельную таблицу надо подумать, на что это повлияет, нет ли у вас документов, где важно исходное имя, а не просто руководствоваться тем, что 'Это же повышение нормальной формы и устранение избыточности, теория рекомендует так делать'. Я об этом говорю.
Данные в отдельной таблице - суть есть справочник. Запись с константным значением. Enum тот же.
Если сменилось значение, то создается новая запись в таблице внешней, а в таблице ссылающейся на неё необходимо пробежать и изменить все ссылки на новое значение.
Чем это отличается от денормализации и пробегания по строкам таблицы с заменой значения поля? Ни чем. Вешай индекс на поле и пробегать по всей таблице не надо, будет проход по индексу с заменой значений в зависимой записи.
В целом изменение записи - дурной тон в большинстве систем. Проще новую создать, а старую для истории(кто-кто сменил паспортные данные или пароль) хранить, либо пометить к удалению в свободное для сервера время.
Если сменилось значение, то создается новая запись в таблице внешней, а в таблице ссылающейся на неё необходимо пробежать и изменить все ссылки на новое значение.
Чем это отличается от денормализации и пробегания по строкам таблицы с заменой значения поля?
Десять раз уже объяснил — да, пара операций INSERT+UPDATE нормализованного варианта ничем не отличается от UPDATE денормализованного. Я именно это и подразумевал. Из этого следует, что одного UPDATE в нормализованном недостаточно, хотя в денормализованном достаточно. На это я и указывал.
Давайте еще один пример приведу.
У нас есть таблица клиентов с полями first_name, last_name. Мы решили повысить нормальную форму и вынесли фамилии в таблицу фамилий. Значение last_name="Иванова" было заменено значением last_name_id=1. У нас есть клиент "Мария Иванова", которая вышла замуж и стала "Мария Петрова".
До нормализации UPDATE текстовых данных "Иванова" -> "Петрова" можно было сделать для одной строки в таблице клиентов. После нормализации UPDATE тех же данных "Иванова" -> "Петрова" повлияет на всех клиентов с фамилией "Иванова".
До нормализации все данные "Иванова" были разными независимыми данными. Значение "Иванова" в одной строке таблицы было не то же самое значение, что "Иванова" в другой строке. Можно было поменять одно значение "Иванова" на "Петрова", и другие бы не изменились.
После нормализации данные превратились в объект с первичным ключом и состоянием, и теперь last_name_id=1 всегда указывает на тот же самый объект, независимо от изменения значений его атрибутов. Если last_name_id=1 указано в нескольких строках, то они все ссылаются на тот же самый объект, и изменение "Иванова" на "Петрова" повлияет на все эти строки. Поэтому в контексте изменения данных эти структуры неэквивалентны.
Теперь поменять данные "Иванова" недостаточно и для этого примера вообще некорректно, надо менять ссылки. Действия для изменения данных становятся полностью другие.
Потому что, если хотят сохранять историю, то первичный ключ таблицы с фамилиями становится не id, а составным id, ValidFrom. А изменение фамилии, это лишь вставка новой записи с тем же id и новым ValidFrom. И никаких UPDATE.
Выборка же по любому идет через JOIN LATERAL (SELECT last_name FROM last_names_table WHERE id=last_name_id AND ValidFrom<=target_date ORDER BY id, ValidFrom DESC LIMIT 1) Q
Никто не хочет сохранять историю фамилий, в этом примере нет такого требования. Вынесение фамилий в таблицу фамилий с двумя полями (id, value) это повышение нормальной формы в полном соответствии с теорией. В данном случае это кажется будет 6-я нормальная форма, так как дальше выносить в другие таблицы уже нечего.
Може имхо - это того же уровня модель данных, что и JSON со множеством значений в поле таблицы
Статья называется "Почему тип поля enum на уровне базы — зло", а на самом деле она о "Почему тип поля enum на уровне базы (*в Mysql* при использовании *PHP*) — зло"
Таким образом, тип полей
enumв базе - это лютое зло.
Это инструмент. Как и любым инструментом, им нужно уметь пользоваться.
Также можно ответить на вопрос "почему integer, а не string": дело в том, что поля типа
stringподвержены грамматическим ошибкам и всё что они дают - лишь удобство чтения таких данных в одной конкретной таблице без использования SQL запросов. Но если в слове будет допущена ошибка или
Вообще непонятно. Как можно читать данные в таблице *без использования SQL запросов*? SELECT - это не SQL?
слово нужно будет заменить на другое - придётся отправлять запрос в базу, чего не нужно делать при использовании целочисленных значений.
Удачи в изменении порядка значений.
про "Удачи в изменении порядка значений." - целочисленный PK не стоит использовать для сортировки. Для сортировки стоит использовать отдельное поле в справочнике с условным названием "ordering"
целочисленный PK не стоит использовать для сортировки
Сортировка по первичному ключу - занятие бессмысленное, абсолютно согласен, но какое это имеет отношение к обсуждаемым enum'ам?
Даже если отставить PK в сторону, то дело не в сортировке. В предложенном варианте с последовательными интами в базе невозможно добавить новое значение в середину списка, ибо оно должно будет переиспользовать существующий индекс, а значит, нужна миграция, которая сдвинет все индексы ниже. Это бесполезная, никому не нужная работа.
В реальном мире проблемы сквозной нумерации могут решаться увеличением порядка, пример: аудитории в учебных заведениях, типа 1xx на первом этаже, 2xx на втором и т.д. Т.е. сознательное создание дырок. Здесь можно применить тот же подход, но зачем, если можно просто не использовать инты?
Может возникнуть "для чего вставлять новое значение именно в середину". Ответ: потому что это требуется для решения задачи.
если PK используется правильно, и не несет на себе всяких ненужных функций, а-ля сортировка, нумерация документов и т.п. - то на практике никогда и не возникает необходимости ничего вставлять в середину.
p.s. И вообще сейчас люди на UUID переходят, и правильно делают
Но откуда вообще взялся PK? О нём в посте речи не шло.
ну если речь зашла про "использование целочисленных значений" вместо строк, а мы все еще не подумали про PK, то где-то модель данных свернула не туда
Не улавливаю связи. Речь шла об использовании уникальных значений. Автору не нравятся строки, он предлагает использовать инты. Также, с разной эффективностью, можно было бы использовать float, decimal, uuid, да хоть битовые маски. То, что инты также могут использоваться в качестве первичных ключей - просто совпадение.
ну да, ну да, крякает как утка, плавает как утка, уникально идентифицирует сущность в таблице, что же это???
уникально идентифицирует сущность в таблице
enum? С какой стати? enum - это тип данных, допускающий установку одного из нескольких предопределённых значений. Например, [active, inactive]. Или [cart, address, deliver, payment]. Или [monday, tuesday, ..., sunday]. А записей в таблице может быть столько, сколько поддерживает СУБД. И у вас будут сотни миллионов active и миллиарды inactive. Ничто тут ничего уникально не идентифицирует.
Целочисленный индекс значения - это данные, а вот его строковое представление - это метаданные. И изменение описания типа потенциально затрагивает как то, так и другое. А такое изменение для параллельной транзакции - это безусловный пердимонокль. Так что постгресс совершенно логично посылает в даль туманную с подобным намерением.
Вообще непонятно. Как можно читать данные в таблице *без использования SQL запросов*? SELECT - это не SQL?
Скорректировал статью. Имелось ввиду без использования джойнов. Да и при просмотре таблицы через любой инструмент запрос руками не пишется. Например:
Удачи в изменении порядка значений.
enum StatusEnum: int
{
case New = 1;
case Progress = 2;
case Done = 3;
}
enum StatusEnum: int
{
case Done = 3;
case New = 1;
case Progress = 2;
}И ничего не изменится на уровне базы ¯\_(ツ)_/¯
Enum ограничивает список допустимых значений, а ваш integer - допускает огромный список значений. Что если кто-то залезет в базу ручками и установит значение 100? Как будет вести себя ваше приложение? Именно для этого и придуман enum. Да, в MySQL изменение структуры таблицы подразумевает создание новой таблицы. Но вы ни слова не сказали о ключевой функциональности enum - ограничения списком допустимых значений, а тупо проигнорировали это и перенесли решение на уровень приложения.
вы что-нибудь слышали про constraints в целом и Foreign Key в частности?
да, но причем тут это?
Содержать список допустимых значений в виде таблицы-словаря с foreign key.
зачем тут таблица и foreign key?
Именно чтобы не хардкодить константы в операторах sql.
приведите пример, непонятно ничего
Да вот выше человек захардкодил в базу enum ('man', 'woman'), а потом стал локализовывать код в страну, где 46 половых принадлежностей*, и всё сломалось. А на следующем этапе ещё получит за новый enum по голове от какой-нибудь полиции нравов в стране, где два пола.
*не является пропагандой нетрадиционных отношений.
а зачем для полов отдельная таблица?
А зачем вообще константы выносить из кода программы? Именно для того, чтобы пользователь мог настроить значения в соответствии со своими требованиями.
Я понимаю, зачем enum был нужен в языке Си, где из структур данных имелись только массивы, которые индексируются целыми числами. Но в SQL это ненужная вещь.
enum нужен ровно для того - чтобы ограничивать список допустимых значений. я не понимаю что вы вынесете в отдельную таблицу, тут же только значение айди интересует и не более. вы будете хранить в исходной таблице идентификатор, и в таблице пола идентификатор? ради fk только? это какая то фигня
Эта фигня называется шестой нормальной формой.
Это сейчас Вам нужно только два значения - 1 ("man") и 2 ("woman"). А потом Вы открываете для себя другие страны. И в одной стране за количество полов меньше 40 Вас отменят, а в другой за больше 2 Вас посадят. Вот и понадобится переделать в табличку, которая бы хранила пол и данные о том, кому и когда его можно показывать.
Хотя, конечно, данный конкретный пример довольно специфичен....
Это не поможет запретить создать новую запись и добавить её ключ. Т.е. любой, у кого есть доступ на инсерт, может создать левое значение, и сломать логику приложения.
Это узкий кейс, но он опровергает тезис- "Таким образом, тип полей enum в базе - это лютое зло.".
ограничения списком допустимых значений
так check constraint же
Что если кто-то залезет в базу ручками и установит значение 100? Как будет вести себя ваше приложение?
Совершенно спокойно упадет с исключением при конвертации из int в enum приложения. Ничем не отличается от ситуации, когда из-за сбоя оперативной памяти вместо 'racing' появилось 'raaing'.
Но вы ни слова не сказали о ключевой функциональности enum - ограничения списком допустимых значений, а тупо проигнорировали это
Во втором абзаце это написано:
Он обеспечивает ограничение значений, которые может принимать колонка, и позволяет более строго контролировать данные. Это может быть полезно для хранения статусов, категорий, типов или любых других значений, которые могут быть заданы только из ограниченного набора вариантов.
Шёл 2023 год, программисты продолжали не доверять системам управления базами данных в части управления базами данных...
Разве enum не хранится в базе как int + таблица имён ? Мне казалось, все современные субд используют эту обёртку автоматически. Тем более, это даёт огромный выигрыш по памяти.
Enum это не зло. Это просто еще один способ обеспечения целостности базы данных. История про то, что "enum не позволяет мне легко косячить" - ну, он как бы для этого и сделан. А то, что enum использовался для живого постоянно изменяющегося списка - дык это просто неправильно база спроектирована.
Скорее в посте есть архитектурная правда, заключающая в том что в живо продукте любой список или множество живые и изменяющиеся.
В целом да. Но есть же lean development, который рекомендует не делать лишнюю работу, если на горизонте нет ничего, что бы предвещало возможность изменений в будущем. Ну, т.е. список жанров - понятно что должен быть динамическим. Но ведь бывают какие-то чисто бизнесовые деления, например, статусы воркфлоу.
Т.е. ты на этапе разработки бизнес-процесса закладываешь допустимые статусы. И это может быть enum, потому что если потребовалось изменить значение в базе, это значит, что и процесс уже другой.
Короче, архитектор он на то и архитектор, чтобы голову включать. А то можно докатиться до того, что число Pi переменной делать, мало ли что :-)
lean development, который рекомендует не делать лишнюю работу, если на горизонте нет ничего, что бы предвещало возможность изменений в будущем.
Я считаю что это в корне неверное понимание принципов lean. Я очень часто сталкивался что люди оправдывают принципами lean или kiss закладывание сложно расширяемых принципов проектирования, когда заложить расширяемость на старте особо много не стоит, иногда эта стоимость пренебрежимо мала. Если строим небоскрёб, то фундамент, стройплощадку и краны сразу под небоскрёб надо подвозить, а не так что давайте одноэтажный домик построим, потом научимся достраивать на него этаж, и повторим 100 раз. Нет, это так не работает.
Вы уверены, что заведение отдельной таблицы и ссылка на неё это большое количество работы, которое не стоит делать, и, что вы этим что-то сэкономите. Это ничтожное количество работы за большое количество простоты расширения в будущем. Имхо lean говорит, что это нужно сделать. Не стоит делать сложного, но не вся избыточность сложная. И вообще, никакая человеческая деятельность сделанная без избыточности не стабильна. Надо закладывать избыточную прочность в здание, в машину, надо чтобы на званом ужине было больше еды, чем съедят, шведский стол в отеле не должен пустеть. Мы не боги, и не можем предсказать точные потребности в будущем, поэтому дёшево купить избыточность очень даже выгодно. И, на мой взгляд, это не противоречит lean. Если пытаться интерпретировать lean радикально и фанатично, то нужно не делать всё, что можно не делать, то давайте отступы в коде писать не будем, сэкономим байты на пробелах. Давайте гит не будем юзать, нам ведь пока не требуется откатывать изменения, вот когда потребуется, тогда гит и применим. Lean не про это.
Статусы тоже меняются. Реже чем жанры игр, но меняются. На прошлой работе, например, ввели новые статусы "ожидание доставки" и "ожидание возврата".
можно докатиться до того, что число Pi переменной делать
Даже это имеет некий смысл.
Есть списки неизменные, например, биологический пол: есть всего два варианта. А если изменяющиеся, например, гендер. И enum'ы, конечно, нужно использовать только для неизменных.
Проще использовать единообразный подход - отдельная табличка со списком значений и внешний ключ. Не надо думать что неизменно, а что может поменяться. Проблем с отдельной табличкой нет никаких.
Кроме того часто бывает что все эти статусы обрастают дополнительными атрибутами типа SortOrder, FullName, ShortName, etc.
Можно вместо enum использовать constraint check в PostgreSQL
alter table games drop constraint games_genre_check;
update games set genre = 'racing' where genre = 'rasing';
alter table games add constraint games_genre_check check (genre in ('action','adventure','shooter','racing'));
Предпочитаю varchar и constraint(по необходимости).
Не люблю инты в силу того, что через некоторое время хрен разберёшь что за status=4 и для интерпретации данных из таблицы, всё время приходится обращаться к энамам из приложения.
Поэтому предпочитаю справочники, в которых прописаны enum-ы приложения. Всегда запросом можно увидеть что есть status=4. Ну и расширить список статусов просто. Сложно только обеспечивать синхронность справочников и приложения. Но тут один раз сделал и работает :)
Запросом-то можно... Но часто лазишь по табличкам в каком-нибудь визуальном туле и на кажлый новый статус лазить в справочник задалбливает. А может вообще не быть лосиупа к запросам, а только какая-нибудь ограниченная визуализация необходимых полей. И, да, синхронизация может отставать или вообще не соответствовать.
DBeaver в помощь
Визуальный тул для построения запросов? Да, в запросы я и сам могу. Если позволяют. Но иногда не позволяют.
SQLYog в дата-вьювере на дабл-клик по полю с FK выдает поисковик по референсной таблице. Все жду когда это уже в PHPStorm завезут
Нет. Он при клике в ячейку, ссылающуюся на другую таблицу, может показывать значение из этой таблицы и вид настраивается. И не надо переписывать запрос, добавляя join'ы, выполнять его, а потом заново искать нужную строчку
Но да, всё равно в ответе видишь только Id'шники и надо везде кликать, чтоб узнать что же там скрывается за ним
Для таких случае придумали LowCardinality. Вероятно, не в той СУБД, которую вы используете, правда.
Возможно повторюсь, но:
1. нормализация прежде всего: все списки в отдельной таблице и ссылки по id
С ростом размера базы (кол-во таблиц, колонок, записей), приложение вам скажет спасибо.
2. если угораздило получить enum, незачем создавать доп. temp колонку: расширьте список на новое значение (+racing), замените в таблице значение колонки на новое (rasing->racing), удалите старый enum.
PostgreSQL прекрасно умеет делать модификацию значений перечислений без необходимости промежуточных "махинаций", и уже довольно давно https://www.postgresql.org/docs/current/sql-altertype.html. Неужели в MySQL это не так, что-то сомневаюсь. Честно говоря, статья притянута за уши.
Первое же предложение "Часто разработчики интересуются почему не рекомендуется использовать тип поля enum в базе данных" намекает на какую-то надуманность, т.к. за 15 лет ни разу не слышал подобной постановки вопроса о ENUM в БД.
А вся статья себя по итогу оправдывает тем, что разработчики выкатили на прод в базу кривое значение в enum: "Через какое-то время была замечена грамматическая ошибка в слове "racing" и принято решение её исправить."
Осознанно не читал ни основной текст ни комменты (тут этого не любят, знаю), чтобы выразить свое мнение:
"Потому что дает ложную уверенность в постоянстве"
Нюанс изменения enum поля в том, что при его редактировании сбрасываются значения колонки в
nullдля всех строк таблицы
Прочитал, очень удивился, пошел проверять.
CREATE TYPE dev.tmp_test_enum AS ENUM (
'male',
'female'
);
CREATE TABLE dev.tmp_test_table (
id serial PRIMARY KEY,
name varchar NOT NULL,
sex dev.tmp_test_enum NULL
);
INSERT INTO dev.tmp_test_table (name, sex) VALUES ('men', 'male'), ('woman', 'female');Добавим элемент в enum
ALTER TYPE dev.tmp_test_enum ADD VALUE IF NOT EXISTS 'unknow';
INSERT INTO dev.tmp_test_table (name, sex) VALUES ('men', 'male'), ('woman', 'female'), ('trans', 'unknow');
SELECT * FROM dev.tmp_test_table;Никаких проблем:
id|name |sex |
--+-----+------+
1|men |male |
2|woman|female|Вставим строку новым элементом enum
INSERT INTO dev.tmp_test_table (name, sex) VALUES ('men', 'male'), ('woman', 'female'), ('trans', 'unknow');
SELECT * FROM dev.tmp_test_table;Никаких проблем:
id|name |sex |
--+-----+------+
1|men |male |
2|woman|female|
3|men |male |
4|woman|female|
5|trans|unknow|Переименуем добавленный элемент:
ALTER TYPE dev.tmp_test_enum RENAME VALUE 'unknow' TO 'other';
SELECT * FROM dev.tmp_test_table;И снова все хорошо:
id|name |sex |
--+-----+------+
1|men |male |
2|woman|female|
3|men |male |
4|woman|female|
5|trans|other |Что я делаю не так?
Используете postgres, а у автора mysql.
Понял в чём причина.
Да, если напрямую отправлять в базу запрос на добавление элементов, например:
ALTER TABLE `payments`
CHANGE `status` `status` ENUM('new','progres','done','refund') CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
то ошибок нет и он отработает.
Но я работаю с базой через механику фреймворка Laravel и doctrine/dbal, которую он использует, по-умолчанию не умеет работать с полями типа enumв плане изменений. Создать - может, а изменить - нет. Поэтому приходилось прибегать к костылям вида:
DB::getDoctrineConnection()
->getDatabasePlatform()
->registerDoctrineTypeMapping('enum', 'string');И раньше такая механика обнуляла все записи в изменяемой enum колонке. Сейчас же при попытке это сделать, я получаю ошибку:
Unknown column type "enum" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType()
Это значит, что костыль, который я применял несколько лет назад, перестал корректно работать по причине изменений в проекте Doctrine.
Код, на котором я тестирую
<?php
declare(strict_types=1);
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
DB::getDoctrineConnection()
->getDatabasePlatform()
->registerDoctrineTypeMapping('enum', 'string');
Schema::dropIfExists('payments');
Schema::create('payments', function (Blueprint $table) {
$table->enum('status', ['new', 'progres', 'done']);
});
DB::table('payments')->insert([
['status' => 'new'],
['status' => 'progres'],
['status' => 'done'],
]);
Schema::table('payments', function (Blueprint $table) {
$table->enum('status', ['new', 'progres', 'done', 'refund', 'progress'])->change();
});
DB::table('payments')->insert([
['status' => 'refund'],
]);
DB::table('payments')
->where('status', 'progres')
->update(['status' => 'progress']);
Schema::table('payments', function (Blueprint $table) {
$table->enum('status', ['new', 'progress', 'done', 'refund'])->change();
});
}
};
Но тогда главный тезис статьи все же не верен и должен относиться не к базе, а к конкретному фреймворку.
Кривая база + кривые фреймворки, а виновато поле типа enum.
Автор статьи упустил тот факт, что тип ENUM позволяет 1-based indexing. Пример двух эквивалентных запросов:
SELECT * FROM `payments` WHERE status='fauled';
SELECT * FROM `payments` WHERE status=4;Более того, в MySQL тип ENUM реализован (в имплементации структур данных) используя целочисленный тип (16-битовое представление). Подробности в https://dev.mysql.com/doc/refman/8.0/en/enum.html
в MySQL тип ENUM реализован (в имплементации структур данных) используя целочисленный тип (16-битовое представление).
Либо 8-битное, либо 16-битное - зависит от количества значений в перечислении. См. Data Type Storage Requirements
Мы следуем следующей логике.
1) делаем справочник вместо перечислений, если изменение списка допустимых значений не приводит к необходимости доработки существующего кода
2) используем enum, если каждое изменение перечня значений - это изменение функциональности в коде, и выводится на пром релизом, в котором учтены скрипты миграции
Также можно ответить на вопрос "почему integer, а не string": дело в том, что поля типа
stringподвержены грамматическим ошибкам и всё что они дают - это удобство чтения таких данных в одной конкретной таблице без использования SQL запросов с применением JOIN функций.
Не стоит недооценивать удобство чтения.
У int есть один недостаток, через n лет красивый int enum превратится в:enum StatusEnum: int
{
case New = 0;
case Legacy = 1;
case Done = 2;
case Failed = 3;
case Deleted = 4;
case NewStatus = 376;
case AnotherNewStatus = 378;
}
А что мешает хранить перечень статусов в отдельной таблице? А в "основной" таблице, с платежами к примеру - хранить ID статуса? Ну и разумеется - внешним ключом эту колонку сделать.
Публикации
Почему тип поля enum на уровне базы — зло