Что означает default null
Заметка про NULL
Основные положения
Для удобства сделаем процедуру, печатающую состояние булевого параметра:
и включим опцию печати сообщений на консоль:
Привычные операторы сравнения пасуют перед NULLом:
Сравнение с NULLом
Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:
DECODE идёт против системы:
Пример с составными индексами находится в параграфе про индексы.
Логические операции и NULL
В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :
Отрицание неизвестности даёт неизвестность:
Операторы IN и NOT IN
Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL
Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.
Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :
Попробуем теперь с NOT IN :
Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :
Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.
NULL и пустая строка
Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.
NULL и пустая строка эквивалентны:
непременный атрибут жёсткого спора:
Длина пустой строки не определена:
Сравнение с пустой строкой невозможно:
Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…
Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом » (пустая строка), но не позволяют сохранить элемент с индексом NULL:
Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.
Математика NULLа
Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.
Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
NULL и агрегатные функции
Таблица с данными. Используется ниже много раз:
Пустые значения игнорируются агрегатами:
Набор данных только из NULLов:
Пустой набор данных:
NULL в OLAP
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
Проверяем дуализм NULLа в многомерном кубе:
Разница между NULL DEFAULT NULL и DEFAULT NULL в MySQL?
Недавно я столкнулся с новой проблемой в MySQL. Я собирался создать новую таблицу с
col1 TIMESTAMP DEFAULT NULL
(т. е. столбец, имеющий значение по умолчанию NULL ), но при создании это дало мне ошибку:
Недопустимое значение по умолчанию для столбца
Я хочу знать, в чем разница между этими двумя синтаксисами. Я также столкнулся с этой проблемой раньше в некоторых значениях insert NULL в столбце.
Может ли кто-нибудь объяснить причину этой проблемы, например, это проблема конкретной версии или что-то еще с MySQL?
1 ответ
null=True blank=True default = 0 Какая разница? Когда вы используете что?
Если у вас есть только значение по умолчанию, но столбец отклоняет значения null, то это значение по умолчанию не может быть использовано.
Похожие вопросы:
Следующее определение таблицы SQL иллюстрируется одним из операторов create table из моей базы данных MYSQL, разработанной бывшим разработчиком моей компании. DROP TABLE IF EXISTS.
В чем разница между дефолтом NULL и CHECKBOX NULL mysql? Я могу установить значение по умолчанию как NULL или есть checkbox ниже которого я могу проверить, чтобы сказать NULL? Какая разница?
В чем разница между: t.boolean :test, :default => true и t.boolean :test, :null => true и t.boolean :test, :default => true, :null => true EDIT Имеет ли смысл следующее? t.boolean :test.
null=True blank=True default = 0 Какая разница? Когда вы используете что?
Есть ли разница между значениями столбца по умолчанию в PostgreSQL? Важно ли это? state character varying(255) DEFAULT NULL и state character varying(255) DEFAULT NULL::character varying
MySQL отклонить вставить значение NULL в столбец default null : (столбец: tid) Структура таблицы: CREATE TABLE `ww_uid_tid_qid_aid` ( `id` int(11) NOT NULL, `qzid` int(11) NOT NULL, `uid` int(32).
Есть ли какая-то разница в использовании default(int?) или (int?)null для назначения переменной? Это одно и то же? Или есть какие-то плюсы и минусы использования каждого способа?
Есть ли разница между этими двумя запросами? Если есть, то что будет быстрее? ALTER TABLE mytable ADD COLUMN newcolumn VARCHAR(64) DEFAULT NULL; ALTER TABLE mytable ADD COLUMN newcolumn VARCHAR(64).
NULL поля в MySQL
Вступление
Часто на форумах и даже в учебниках пишут о том, что лучше не использовать NULL поля в MySQL. В этих утверждениях смущает тот факт, что никто не удосуживается объяснить, почему NULL – это зло. Эта заметка призвана разобраться, что такое NULL в MySQL и так ли страшен чёрт, как его малюют.
Что такое NULL?
Наряду с множеством типов данных в БД, NULL стоит особняком. NULL означает отсутствие значения.
Зачем использовать особый тип данных для того, чтоб указать отсутствие значения, когда можно просто вставить пустую строку, например? Этот вопрос мне всегда казался глупым, и я удивляюсь тому, как в книгах и статьях уделяют ему достаточное количество внимания.
Чем опасен NULL?
Сравнение NULL с любым другим значением, даже с родственным (в большинстве языков программирования, в частности, в PHP null, 0, false это одно и то же, если не применять строгого сравнения, которое включает сравнение типов) ему FALSE вернёт NULL. Отсюда следует первая ловушка.
Допустим, у нас есть таблица:
Добавим в неё 2 записи:
INSERT INTO users (name, family) VALUES(‘Андрей’, ‘Романов’), (‘Иван’, NULL);
В случае, если вы захотите объединить имя и фамилию, получив ФИО одним полем, например, таким запросом:
SELECT CONCAT(name, ‘ ‘, family) FROM `users`
MySQL не оправдает ваших ожиданий. Вы получите NULL вместо Ивана.
Немного неожиданно, правда?
На деле же ничего неожиданного нет, если вы помните, что любая операция с NULL вернёт NULL, кроме специальных операций, предназначенных для работы с NULL: IS NULL, IS NOT NULL, IFNULL()
Сортировка по NULL
Всего лишь хочу опровергнуть некоторые фразы из русского мануала MySQL о том, что при сортировке по столбцу, содержащим NULL значения, эти самые NULL значиния всега оказываются наверху. Это не так.
SELECT name, family FROM `users` ORDER BY family ASC
SELECT name, family FROM `users` ORDER BY family DESC
Как видим, NULL считается наименьшим значением, и порядок сортировки на него действует.
Группировки и NULL
Все просто.
MySQL группирует по NULL так же как и по любому другому полю.
Добавим нашей таблице users столбец score INT UNSIGNED NULL;
Подсчитаем сколько всего пользователей набрали то или иное количество очков, т.е. сгруппируем выборку по полю score
SELECT COUNT(*), score FROM `users` GROUP BY score
Как видно, MySQL сгруппировала 2 строки с score = NULL
Индексы и NULL
Откуда-то ходит заблуждение о том, что MySQL не использует индексы, если столбец может принимать значения NULL.
Это не так!
Проведём несколько экспериментов.
Выберем все записи, где score = NULL. Не забываем, что мы для этого должны использовать конструкцию IS NULL
EXPLAIN SELECT * FROM `users` WHERE score IS NULL
Индекс используется.
Выберем все записи, где количество очков больше, например, пяти.
EXPLAIN SELECT * FROM `users` WHERE `score` > 5
Индекс используется.
Найдем пользователя, у которого ровно 7 очков
EXPLAIN SELECT * FROM `users` WHERE `score` = 7
Индекс используется.
Можно сделать вывод: заблуждение действительно таковым и оказалось.
Можно смело использовать NULL-поля при создании индекса и индекс будет работать.
Когда следует использовать NULL?
Ранее я привел пример таблицы сайтов, которая содержит поле PR.
PR – это целочисленное значение, которое может принимать значение 0, к тому же оно может быть в состоянии «не посчитано». Как реализовать хранение такого свойства в таблице?
Зачем отказываться от того, что язык предлагает тебе «из коробки»?
Значение NULL для такого поля подходит идеально. NULL – означает «нет значения», т.е. оно ещё не посчитано. Если значение равно нулю, значит оно действительно равно нулю. Все просто.
Второй пример подходящего случая для использования NULL – это поле-потомок.
Поле-потомок указывает на id записи из другой (или же этой же таблицы). Примером такого поля может быть parent_id.
Выводы
Нет причин бояться создания NULL полей. Надо хорошо понимать, что NULL в MySQL это не ноль и не false – это отсутствие значения. Надо знать, как MySQL работает с NULL: нюансы есть, но их не много. У MySQL нет проблем с индексацией NULL полей.
Обработка значений NULL
Значения NULL и логика трех значений
Разрешение значений NULL в определениях столбцов вводит в приложение логику трех значений. Результатом сравнения может быть одно из трех условий:
Так как значение NULL считается неизвестным, два значения NULL, сравниваемые друг с другом, не считаются равными. В выражениях, использующих арифметические операторы, если какой-либо из операндов имеет значение NULL, результат также равен NULL.
Значения NULL и SqlBoolean
При сравнении между любыми типами System.Data.SqlTypes будет возвращаться значение SqlBoolean. Функция IsNull для каждого типа SqlType возвращает SqlBoolean и может использоваться для проверки на наличие значений NULL. В следующих таблицах истинности показано, как работают операторы AND, OR и NOT при наличии значения NULL. (T = true, F = false и U = неизвестно или NULL.)
Основные сведения о параметре ANSI_NULLS
Стандарт ANSI SQL-92 не поддерживает columnName = NULL в предложении WHERE. В SQL Server параметр ANSI_NULLS управляет допустимостью значений NULL по умолчанию в базе данных и вычислением сравнений со значениями NULL. Если параметр ANSI_NULLS включен (по умолчанию), то при проверке на наличие значений NULL в выражениях должен использоваться оператор IS NULL. Например, результатом следующего сравнения всегда является неизвестность при включенном параметре ANSI_NULLS:
Сравнение с переменной, содержащей значение NULL, также приводит к неизвестному результату:
Для тестирования на значение NULL используются предикаты IS NULL и IS NOT NULL. Это может усложнить предложение WHERE. Например, столбец TerritoryID в таблице AdventureWorks Customer допускает значения NULL. Если инструкция SELECT используется для тестирования на значения NULL в дополнение к другим, она должна включать предикат IS NULL:
Если в SQL Server параметр ANSI_NULLS отключен, можно создать выражения, которые используют оператор равенства для сравнения со значением NULL. Однако нельзя запретить другим подключениям задавать параметры NULL для этого подключения. Использование параметра IS NULL для проверки на наличие значений NULL всегда работает, независимо от установленного значения ANSI_NULLS для подключения.
Присвоение значений NULL
Значения NULL являются специальными, и их семантика хранения и назначения различается в разных системах типов и системах хранения. Dataset предназначен для использования с различными системами типов и хранения.
В этом разделе описывается семантика значений NULL для присвоения значений NULL для DataColumn в DataRow в различных системах типов.
Назначение нескольких столбцов (строк)
Кроме того, следующие правила применяются к экземпляру назначений NULL DataRow.[«columnName»] :
Используемое по умолчанию значение default является DbNull.Value для всех столбцов, за исключением строго типизированных нулевых столбцов с допустимыми строго типизированными значениями NULL.
Значения NULL никогда не записываются во время сериализации в XML-файлы (как в xsi:nil).
Все значения, в том числе по умолчанию, отличные от NULL, всегда записываются при сериализации в XML. Это отличается от семантики XSD/XML, где значение NULL (xsi: nil) является явным, а значение по умолчанию — неявным (если отсутствует в XML, то проверяющее средство синтаксического анализа может получить его из связанной схемы XSD). Обратное верно для DataTable : значение NULL является неявным, а значение по умолчанию — явным.
Всем отсутствующим значениям столбцов для строк, считываемых из входных данных XML, присваивается значение NULL. Строкам, созданным с помощью NewRow или аналогичных методов, присваивается значение по умолчанию DataColumn.
Присвоение значений NULL для SqlTypes
Значение по умолчанию для любого экземпляра System.Data.SqlTypes— NULL.
Значения NULL могут быть назначены DataColumn, как показано в следующем примере кода. Вы можете напрямую назначить значения NULL для переменных SqlTypes без запуска исключения.
Пример
В следующем примере кода показано создание DataTable с двумя столбцами, определенными как SqlInt32 и SqlString. Код добавляет одну строку известных значений, одну строку значений NULL, а затем выполняет итерацию по DataTable, присваивая значения переменным и отображая выходные данные в окне консоли.
В этом примере отображаются следующие результаты:
Сохранение значений NULL или значений по умолчанию при массовом импорте данных (SQL Server)
Сохранение значений NULL
Следующие квалификаторы указывают, что вместо пустого поля в файле данных необходимо вставить не значение по умолчанию, а значение NULL. Для инструкции OPENROWSETпо умолчанию любым столбцам, не участвующим в операции массовой загрузки, присваивается значение NULL.
* Для инструкции BULK INSERT, если значения по умолчанию недоступны, для столбца таблицы должны быть допустимы значения NULL.
Эти квалификаторы отключают проверку определений DEFAULT для таблицы командами массового импорта. Однако для одновременно выполняемых инструкций INSERT определения DEFAULT требуются.
Можно указать, что вместо пустых полей в файле данных необходимо вставить значения по умолчанию соответствующих столбцов (если они заданы). Чтобы использовать значения по умолчанию, используйте табличную подсказку KEEPDEFAULTS.
Пример условий теста
Примеры в этом разделе основаны на таблице, файле данных и файле форматирования, которые определены ниже.
Образец таблицы
Образец файла данных
С помощью Блокнота создайте пустой файл D:\BCP\myNulls.bcp и вставьте данные, приведенные ниже. Обратите внимание, что в третьей записи в четвертом столбце значение отсутствует.
Кроме того, можно выполнить следующий сценарий PowerShell для создания и заполнения файла данных:
Образец файла форматирования в формате, отличном от XML
Убедитесь, что файл форматирования не в формате XML заканчивается символом перевода строки или возврата каретки. В противном случае, скорее всего, появится следующее сообщение об ошибке:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]I/O error while reading BCP format file
Дополнительные сведения о создании файла форматирования см. в статье Создание файла форматирования (SQL Server).
Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных
В приведенных ниже примерах используется база данных, файл данных и файлы форматирования, созданные ранее.
Использование команды bcp и сохранение значений NULL без файла форматирования
Использование команды bcp и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML
Использование команды bcp и значений по умолчанию без файла форматирования
В командной строке введите следующую команду:
Использование команды bcp и значений по умолчанию с помощью файла форматирования в формате, отличном от XML
Использование инструкции BULK INSERT и сохранение значений NULL без файла форматирования
Использование инструкции BULK INSERT и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML
Использование инструкции BULK INSERT и значений по умолчанию без файла форматирования
Выполните следующий запрос Transact-SQL в Microsoft SQL Server Management Studio (SSMS):
Использование инструкции BULK INSERT и значений по умолчанию с помощью файла форматирования в формате, отличном от XML
Использование инструкции OPENROWSET(BULK. ) и сохранение значений NULL с помощью файла форматирования в формате, отличном от XML
Использование инструкции OPENROWSET(BULK. ) и значений по умолчанию с помощью файла форматирования в формате, отличном от XML
Связанные задачи
Использование файла форматирования