Вставка NULL в столбцы NOT NULL со значением по умолчанию
Для немного фона, мы используем Zend Framework 2 а также доктрина на работе. Учение всегда будет вставлять NULL для ценностей мы не населяем себя. Обычно это нормально, если поле имеет значение по умолчанию, тогда оно ДОЛЖНО заполнять поле этим значением по умолчанию.
Для одного из наших серверов работает MySQL 5.6.16 запрос, такой как приведенный ниже, выполняется и выполняется нормально. Хотя NULL вставляется в поле, которое не имеет значения NULL, MySQL заполняет поле значением по умолчанию при вставке.
На другом из наших серверов работает MySQL 5.6.20, мы запускаем запрос ниже, и он падает, потому что он жалуется, что ‘field_with_default_value’ не может быть нулевым.
INSERT INTO table_name(id, field, field_with_default_value)
VALUES(id_value, field_value, NULL);
Сама Doctrine не поддерживает прохождение через «DEFAULT» в запросы, которые она создает, так что это не вариант. Я полагаю, что это должно быть что-то вроде сервера MySQL, поскольку в одной версии все работает нормально, но не в другой, но, к сожалению, я понятия не имею, что это может быть. Наш режим SQL также идентичен на обоих серверах ('NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION').
Я, вероятно, должен упомянуть, что если я на самом деле запускаю вышеупомянутый SQL в Workbench, он все равно работает не так. Так что это не проблема Doctrine, а определенно проблема MySQL.
Любая помощь по этому вопросу будет принята с благодарностью.
Решение
По документации все работает как положено.
Прецедент:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.16 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
@@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+------------------------+
| sql_mode::GLOBAL | sql_mode::SESSION |
+------------------------+------------------------+
| NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+------------------------+------------------------+
1 row in set (0.00 sec)
mysql> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
@@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| sql_mode::GLOBAL | sql_mode::SESSION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE `table_name`;
+------------+----------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` ( |
| | `id` INT(11) UNSIGNED NOT NULL, |
| | `field` VARCHAR(20) DEFAULT NULL, |
| | `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' |
| | ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
VALUES
(1, 'Value', NULL);
ERROR 1048 (23000): Column 'field_with_default_value' cannot be null
Можно ли опубликовать соответствующую часть структуры вашей таблицы, чтобы увидеть, как мы можем помочь?
ОБНОВИТЬ
MySQL 5.7, использующий триггеры, может обеспечить возможное решение проблемы:
Изменения в MySQL 5.7.1 (2013-04-23, Milestone 11)
…
- Если столбец объявлен как NOT NULL, он не может быть вставлен
NULL в столбец или обновите его до NULL. Однако это ограничение
был применен, даже если была ПЕРЕД ВСТАВКОЙ (или ПЕРЕД ОБНОВЛЕНИЕМ)
триггер), который устанавливает столбец в ненулевое значение. Теперь ограничение
проверяется в конце оператора в соответствии со стандартом SQL. (Bug
#6295, Bug# 11744964).…
Возможное решение:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.4-m14 |
+-----------+
1 row in set (0.00 sec)
mysql> DELIMITER $$
mysql> CREATE TRIGGER `trg_bi_set_default_value` BEFORE INSERT ON `table_name`
FOR EACH ROW
BEGIN
IF (NEW.`field_with_default_value` IS NULL) THEN
SET NEW.`field_with_default_value` :=
(SELECT `COLUMN_DEFAULT`
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = DATABASE() AND
`TABLE_NAME` = 'table_name' AND
`COLUMN_NAME` = 'field_with_default_value');
END IF;
END$$
mysql> DELIMITER ;
mysql> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
VALUES
(1, 'Value', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT `id`, `field`, `field_with_default_value` FROM `table_name`;
+----+-------+--------------------------+
| id | field | field_with_default_value |
+----+-------+--------------------------+
| 1 | Value | myDefault |
+----+-------+--------------------------+
1 row in set (0.00 sec)
Другие решения
MySQL на самом деле работает как задумано, и это поведение, кажется, там, чтобы остаться. MariaDB теперь работает так же.
Удаление «строгий режим» (STRICT_TRANS_TABLES & STRICT_ALL_TABLES) предполагается вернуться к предыдущему поведению, но лично мне не повезло с этим (возможно, я делаю что-то не так, но оба мои @@GLOBAL.sql_mode & @@SESSION.sql_mode не содержат строгого режима).
Я думаю, что лучшим решением этой проблемы является использование значений по умолчанию на уровне PHP, а не использование базы данных для их предоставления. Существует существующий ответ, который объясняет это довольно хорошо. Комментарии также полезны.
Таким образом, вы также получаете дополнительное преимущество, заключающееся в том, что ваши модели / объекты будут иметь значение по умолчанию при создании экземпляра, а не при вставке в базу данных. Кроме того, если вы хотите отобразить эти значения пользователю после вставки, вы можете сделать это без дополнительных SELECT запрос после вашего INSERT,
Другой альтернативой отображению значений по умолчанию будет использование RETURNING пункт, как доступно в PostgreSQL, но не в MySQL (пока). Это может быть добавлено в какой-то момент в будущем, но сейчас MariaDB имеет только для DELETE заявления. Тем не менее, я считаю, что наличие значений по умолчанию на уровне PHP все еще лучше; даже если вы никогда не вставите запись, она все равно будет содержать значения по умолчанию. Я никогда не возвращался и не использовал значение базы данных по умолчанию с тех пор, как применил это на практике.
Основываясь на моих исследованиях, я бы сказал, что это может быть как «вы», так и «MySQL». Проверьте определения таблицы с помощью SHOW CREATE TABLE table_name;, Обратите внимание на любые поля, определенные с NOT NULL,
Справочное руководство по MySQL 5.6: 13.2.5 Синтаксис INSERT состояния:
Вставка NULL в столбец, который был объявлен NOT NULL. За
многострочные операторы INSERT или INSERT INTO … SELECT,
столбец установлен на неявный значение по умолчанию для данных столбца
тип. Это 0 для числовых типов, пустая строка (») для строки
типы, а также «нулевое» значение для типов даты и времени. ВСТАВИТЬ В …
Операторы SELECT обрабатываются так же, как вставки из нескольких строк
потому что сервер не проверяет набор результатов из SELECT для
посмотрите, вернет ли он одну строку. (Для однорядной вставки нет
Предупреждение появляется, когда NULL вставляется в столбец NOT NULL. Вместо,
утверждение завершается ошибкой.)
Это подразумевает, что не имеет значения, какой режим SQL вы используете. Если вы делаете один ряд INSERT (согласно вашему примеру кода) и вставка NULL значение в столбце, определенном с NOT NULL, это не должно работать.
По иронии судьбы, если вы просто опустите значение из списка значений, руководство MySQL скажет следующее, и режим SQL имеет значение в этом случае:
Если вы не работаете в строгом режиме SQL, любой столбец не явно
задано значение по умолчанию (явный или же неявный) значение. За
Например, если указать список столбцов, в котором не указаны все
столбцы в таблице, для безымянных столбцов установлены значения по умолчанию.
Назначение значений по умолчанию описано в разделе 11.6 «Тип данных».
Значения по умолчанию». См. Также Раздел 1.7.3.3, «Ограничения на недействительных
Данные».
Таким образом, вы не можете выиграть! 😉 Шучу. Что нужно сделать, это принять это NOT NULL на поле таблицы MySQL действительно означает Я не приму значение NULL для поля при выполнении одной строки INSERTнезависимо от режима SQL.’
Все это, как говорится, следующее из руководства также верно:
Для ввода данных в столбец NOT NULL, который не имеет явного DEFAULT
предложение, если оператор INSERT или REPLACE не содержит значения для
column, или оператор UPDATE устанавливает столбец в NULL, MySQL обрабатывает
столбец в соответствии с Режим SQL действует в то время:Если строгий режим SQL включен, возникает ошибка для транзакций
таблицы и заявление откатывается. Для нетранзакционных таблиц,
возникает ошибка, но если это происходит для второй или последующей строки
многострочного оператора, предыдущие строки были
вставленный.Если строгий режим не включен, MySQL устанавливает столбец в неявный
значение по умолчанию для типа данных столбца.
Итак, наберитесь души. Установите свои значения по умолчанию в бизнес-логике (объектах), и пусть уровень данных будет иметь направление от этого. По умолчанию базы данных кажутся хорошей идеей, но если бы их не было, вы бы их пропустили? Если дерево падает в лесу …
Я столкнулся с той же проблемой после обновления MySQL. Оказывается, есть настройка, позволяющая вставлять значения NULL в поля отметок NOT NULL и получать значение по умолчанию.
explicit_defaults_for_timestamp=0
Это задокументировано на https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
Если вы пропустите столбец (и имя, и значение) из оператора, будет использовано значение по умолчанию.
Несколько связанных советов:
- У вас не должно быть никаких «непустых» значений по умолчанию в ваших таблицах и не должно быть ненулевых значений по умолчанию для столбцов, допускающих значение NULL. Пусть все значения будут установлены из приложения.
- Не размещайте бизнес-логику на стороне базы данных.
Определите значение по умолчанию только в том случае, если это действительно необходимо, и только для столбцов, не допускающих значения NULL И удалите настройки по умолчанию, когда они больше не нужны. (они пригодятся при запуске таблицы изменений, чтобы установить значение нового столбца, но затем сразу же запустят новое (дешевое!) изменение, чтобы удалить значение по умолчанию)
Упомянутый выше «пустой» относится к типу:
— 0 для числовых столбцов,
— » для столбцов varchar / varbinary,
— «1970-01-01 12:34:56» для отметок времени,
— так далее.
Это экономит приложению много поездок в базу данных. Если созданная строка полностью предсказуема, то приложению не нужно читать ее после создания, чтобы узнать, чем она стала. (это предполагает: без триггеров, без каскадирования)
В MySQL мы делаем только несколько конкретных исключений из этих строгих правил:
-
Столбцы, называемые mysql_row_foo, устанавливаются только базой данных. Примеры:
отметка времени mysql_row_created_at (6) отлична от нуля по умолчанию '1970-01-01 12: 34: 56.000000', mysql_row_updated_at timestamp (6) null по умолчанию null при обновлении current_timestamp,
-
Для предотвращения дублирования данных приветствуются уникальные индексы для ненулевых столбцов. Например, для lookup.brand.name в таблице lookup.brand, которая выглядит следующим образом (id ++, name).
Столбцы mysql_row_foo похожи на атрибуты столбцов. Например, они используются инструментами синхронизации данных. Обычные приложения не читают их, и они хранят свои временные метки на стороне приложения как значения эпох. Примеры:
valid_until_epoch int без знака не по умолчанию 0, last_seen_epoch_ms bigint не ноль по умолчанию 0,