Когда использовать NULL в таблицах MySQL

Я ценю семантическое значение значения NULL в таблице базы данных, отличное как от false, так и от пустой строки ''. Однако я часто читал о проблемах с производительностью, когда поля допускают значение NULL, и мне советовали использовать пустую строку в тех случаях, когда NULL на самом деле является семантически правильным.

Какие обстоятельства подходят для использования полей, допускающих значение NULL, и значений NULL? Какие компромиссы? Разумно ли вообще избегать использования NULL и просто использовать пустые строки, false или 0 для обозначения отсутствия значения?

ОБНОВИТЬ

Хорошо - я понимаю семантическую разницу между '' и NULL, а также обстоятельства (не зависящие от производительности), в которых NULL является подходящим значением поля. Однако позвольте мне подробнее остановиться на намеках на производительность. Это из превосходного «Высокопроизводительного MySQL» Шварца, Зейцева и др. http://www.borders.co.uk/book/high-performance-mysql-optimization-backups-replication-and-more/857673/:

It's harder for MySQL to optimize queries that refer to nullable coumns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size inded (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.

Подробнее здесь: Предварительный просмотр книг Google

Вполне возможно, что это окончательный ответ - я просто искал сторонних мнений и опыта на передовой.

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
48
0
28 105
11

Ответы 11

Обычно, если атрибут является обязательным, он определяется как Not NULL, а если он может быть опущен, он определяется как допускающий значение NULL.

В руководстве по MySQL есть хорошая статья о проблемы с NULL.

Надеюсь, поможет.

Также нашел этот другой ТАК сообщение о NULL и производительности

Если я правильно понимаю, нет никаких проблем с с NULL (с производительностью или иначе), но скорее с плохим пониманием поведения NULL, да?

Ben Blank 23.01.2009 03:16

Вы читаете это правильно, и Карвин всегда прав в вопросах, касающихся базы данных. (Кроме того, я согласен со всем, что он сказал.) Обратите особое внимание на слово «микрооптимизация», особенно рядом со словом «избегать».

dkretz 23.01.2009 03:26

Основное преимущество, конечно же, - это семантическое значение NULL, о котором вы упомянули.

В дополнение к этому - и это может зависеть от вашего механизма хранения, как всегда, проверьте документацию - но по крайней мере в некоторых базах данных NULL занимают намного меньше места, чем обычное значение. Например, если у вас есть столбец varchar, объявленный как 20 символов, и он редко заполняется, вы можете сэкономить много места на диске, сделав его NULL вместо пустой строки.

Я никогда не слышал о каких-либо проблемах с производительностью при использовании NULL, как раз наоборот. Я слышал, что люди портят счет, потому что неправильно подсчитывают NULL, но никогда не работают. Если это действительно так, я хотел бы услышать об этом!

Значение столбца NULL более или менее «не применимо в этом контексте». Обычно я использую NULL-столбцы в двух случаях:

  • Если поле не применяется (допустим, у вас есть логический столбец is_thirsty и вы добавляете два набора данных. Один человек и камень. В случае человека вы устанавливаете is_thirsty в значение true или false, тогда как в случае камня , вы, вероятно, установили бы его в NULL.
  • Если мне нужно что-то отметить и сохранить данные со значением. Подобно дате закрытия инвентаря, которую вы использовали бы, чтобы: а) указать, что инвентарь больше не может быть изменен, и б) указать, когда инвентарь был закрыт. Вместо двух столбцов (closed_at и is_closed) я просто создаю столбец closed_at и устанавливаю для него значение NULL, если набор инвентаризации все еще можно изменить, но устанавливаю дату после его закрытия.

В основном это сводится к тому, что я использую NULL, когда пустота поля имеет другую уникальную семантику, чем просто пустое поле. Отсутствие инициала в середине - вот и все. Отсутствие даты закрытия означает, что инвентарный набор все еще открыт для изменений.

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

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

Лично я использую столбцы NULL только в одном из двух вышеуказанных случаев. Я никогда не использую его для обозначения пустых полей, когда пустота не имеет другого значения, кроме отсутствия значения.

Самый простой вопрос: «Если я добавляю новую строку, нужно ли мне иметь значение для этого столбца?». Если нет, это значение NULLable, а наличие NULL в столбце означает, что вы не ввели значение (по какой-либо причине).

dkretz 23.01.2009 03:29

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

В первую очередь следует обратить внимание на использование базы данных (включая NULL) по назначению; затем беспокойтесь о последствиях оптимизации, когда и если они произойдут.

Совокупный эффект неправильных значений столбца с нулевым значением NULL как в сложности, так и в точности SQL почти наверняка перевесит преимущества обмана с материнской СУБД. Кроме того, это запутает вашу голову, а также голову любого, кто позже попытается выяснить, что вы пытались сделать.

Пустую строку не следует использовать вместо NULL. NULL ничего не представляет там, где пустая строка - это что-то, внутри ничего нет. NULL всегда будет ложным при сравнении с другим значением (даже NULL), а NULL не будет суммироваться в функции COUNT.

Если вам нужно представить неизвестную информацию, нет замены NULL.

В некоторых базах данных, таких как Oracle, может быть, что кое-что о MySQL верно:

  • Нулевые значения не индексируются, поэтому поиск нулевых значений может стать узким местом.
  • Конечные нули в строках экономят место.

However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct.

Я собираюсь немного привередничать с выбором слов:

  • Даже если бы это был значительный фактор производительности, это не делает семантически правильным для использования значения вместо NULL. В SQL NULL играет семантическую роль для обозначения отсутствующего или неприменимого значения. Характеристики производительности NULL в данной реализации СУБД не зависят от этого. Производительность может варьироваться от бренда к бренду или от версии к версии, но назначение NULL в языке остается неизменным.

В любом случае, я не слышал никаких доказательств того, что NULL работает плохо. Мне были бы интересны любые ссылки на измерения производительности, которые показывают, что столбцы, допускающие значение NULL, работают хуже, чем столбцы, не допускающие значения NULL.

Я не говорю, что я не ошибаюсь или что в некоторых случаях это не может быть правдой - просто бессмысленно строить праздные предположения. Наука не состоит из предположений; нужно показать доказательства с помощью повторяемых измерений.

Метрики также сообщают вам по сколько, что производительность различается, поэтому вы можете судить, стоит ли беспокоиться об этом. То есть влияние может быть измеримым и отличным от нуля, но все же незначительным по сравнению с более высокими факторами производительности, такими как правильная индексация таблиц или определение размера кэша базы данных.

В MySQL поиск NULL может выиграть от индекса:

mysql> CREATE TABLE foo (
  i INT NOT NULL,
  j INT DEFAULT NULL,
  PRIMARY KEY (i),
  UNIQUE KEY j_index (j)
);

mysql> INSERT INTO foo (i, j) VALUES 
  (1, 1), (2, 2), (3, NULL), (4, NULL), (5, 5);

mysql> EXPLAIN SELECT * FROM foo WHERE i = 3;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | foo   | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

mysql> EXPLAIN SELECT * FROM foo WHERE j IS NULL;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | foo   | ref  | j_index       | j_index | 5       | const |    2 | Using where | 
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

Обратите внимание, что это все еще не показатель производительности. Я только показал, что вы можете использовать индекс при поиске NULL. Я собираюсь утверждать (правда, без измерений, но это всего лишь StackOverflow), что преимущество индекса затмевает любые возможные штрафы при поиске NULL по сравнению с пустой строкой.

Выбор нуля, пустого или любого другого значения для замены NULL - неправильное проектное решение. Возможно, вам придется использовать эти значения как значимые в столбце. Вот почему NULL существует как значение, которое по определению находится за пределами области значений любого типа данных, поэтому вы можете использовать полный диапазон значений целых чисел или строк или чего-то еще, и все же иметь что-то, что означает «ни одно из вышеперечисленных значений». "

mysql 5.6.34-log внутри экземпляра AWS или RDS ужасен. Sql_mode по умолчанию ужасен и установлен на NO_ENGINE_SUBSTITUTION, что позволяет вставлять NULLS в столбец NOT NULL. и производительность ужасна, так как без предупреждений или строгих требований вы ждете экспоненциально дольше, поскольку он преобразует NULLS в ''! очень-очень плохое поведение! пожалуйста, всегда проверяйте свои таблицы и включайте более строгий SQL_MODE, даже если он находится только в вашем SESSION
JayRizzo 03.06.2017 03:00

Мы не допускаем значения NULL в наших базах данных, кроме как для числовых значений или дат. Причина, по которой мы это делаем, заключается в том, что числовые значения иногда не должны быть по умолчанию равными нулю, поскольку это очень и очень плохо. Я разработчик для биржевых брокеров, и есть большая разница между НОЛЬ и 0. Использование COALESCE пригодится, если мы действительно хотим вернуть значения по умолчанию к нулю, даже если мы не храним их как таковые.

MyVal = COALESCE(TheData, 0)

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

Даты по умолчанию имеют любое значение, которое может показаться в зависимости от сопоставления, которое, как я полагаю, но у нас по умолчанию примерно 1900, и, опять же, даты чрезвычайно важны. Другие значения в виде обычного текста не так важны, и если оставить их пустыми, они обычно считаются приемлемыми.

Установленные значения по умолчанию - это ваш table_structure, не допускающий нулей, но ваш SQL_MODE, скорее всего, NO_ENGINE_SUBSTITUTION, который будет automagically-convert ваших NULLS в '' (по отношению к MySQL)

JayRizzo 03.06.2017 03:08

Как сказал @ForYourOwnGood - Null следует использовать для «неизвестной» информации. Например: если у вас много полей, которые клиент должен заполнить при регистрации, и некоторые из них являются необязательными. По какой-то причине вы можете захотеть зарезервировать идентификатор для этого конкретного клиента, и поскольку вы не знаете, являются ли необязательные поля реальным выбором клиента, чтобы оставить их пустыми, вы должны установить их как NULL, т.е. «неизвестно», когда вы впервые сохраняете ряд. Если клиент отправляет форму, проходит всю вашу проверку, а затем вы сохраняете информацию, то вы точно знаете, что необязательное поле намеренно оставлено пустым.

Это просто хороший случай использования NULL.

NULL имеет большой смысл, если вы думаете о таблице, в которой есть поле типа DATETIME, и это поле должно содержать время обновления. Когда вы вставляете данные в строку, вы не хотите, чтобы это поле имело значение, но вы также не можете добавить 0. Так что NULL имеет смысл.

user1105491 12.10.2014 14:29

Я понимаю, что бывают случаи, когда семантика MySQL NULL полностью подходит.

Тем не менее, они серьезно мешают, особенно с текстовыми полями.

Вот пример из реального мира.

Мы хотим скопировать данные из базы данных FileMaker в таблицу mysql.

если мы сделаем «ВЫБРАТЬ * из таблицы, где текстовое поле <> 'test'», строки с текстовым полем, равным NULL, НЕ будут возвращены. Скорее всего, это не то, чего вы ожидали или желали.

если поле, допускающее значение NULL, используется в запросе where, либо отдельно, либо как часть AND, записи, которые имеют значение NULL, НИКОГДА не будут возвращены, если не используется тест IS NULL. Мы должны сделать что-то вроде "where ((textfield <>" test ") OR (textfield IS NOT NULL))", что в лучшем случае некрасиво.

Поэтому в этом случае нам, вероятно, не нужно, чтобы поле допускало значение NULL.

Проблема здесь в том, что вы НЕ МОЖЕТЕ вставить пустую строку в MySQL с помощью Filemaker. Он преобразуется в NULL, что приводит к ошибке, если вы сделали столбец ненулевым! Если вы разрешите NULL, то передача в mysql будет работать, но тогда ваши запросы not не будут действовать так, как вы хотите!

обходной путь состоит в том, чтобы дважды изменить таблицу, преобразовать существующие нули после импорта в пустую строку, а затем снова изменить таблицу, чтобы снова разрешить null. ты!

проклятый файлмейкер.

Просто столкнулся с этой проблемой. Имел поле, которое на передней панели сайта WordPress не было обязательным. Поле в MySQL НЕ ПУСТО (ошибочно), но WP отправляет пустую строку, что приемлемо. Приложение FMP напрямую обращается к данным для редактирования. Пользователь должен только сохранить текст в пустом поле, а затем попытаться удалить его. Прискорбно то, что FMP (по крайней мере, в том виде, в котором создано это приложение) не имеет кнопки отправки, следующая строка кнопки данных отправляется молча. Тогда пользователь попадает в неожиданную делиму. Ошибка ODBC: ... Столбец «xxxx» не может быть пустым.

nic 06.10.2020 00:12

Другие вопросы по теме