Для небольшого фона мы используем Zend Framework 2 и Doctrine на работе. Доктрина всегда вставляет NULL для значений, которые мы не заполняем. Обычно это нормально, как будто поле имеет значение по умолчанию, затем оно ДОЛЖНО заполнить поле с этим значением по умолчанию.
Для одного из наших серверов, на которых запущен MySQL 5.6.16, запрос, такой как приведенный ниже, выполняется и выполняется отлично. Хотя 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, этап 11)
…
- Если столбец объявлен как NOT NULL, не разрешается вставлять NULL в столбец или обновлять его до NULL. Однако это ограничение было применено даже в том случае, если перед вводом INSERT (или перед запуском UPDATE) был установлен столбец с не-NULL значением. Теперь ограничение проверяется в конце инструкции по стандарту SQL. (Ошибка
#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 для числовых типов, пустая строка ('') для типов строк и «нулевое» значение для типов даты и времени. Операторы INSERT INTO … SELECT обрабатываются так же, как вставки нескольких строк, потому что сервер не проверяет набор результатов из SELECT, чтобы увидеть, возвращает ли он одну строку. ( Для однострочного INSERT предупреждения не появляются, когда 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 не содержит значения для столбца, или оператор UPDATE устанавливает столбец в NULL, MySQL обрабатывает столбец в соответствии с режимом SQL, действующим на время:
Если включен строгий режим SQL , возникает ошибка для транзакционных таблиц, и инструкция возвращается. Для нетранзакционных таблиц возникает ошибка, но если это произойдет для второй или следующей строки оператора с несколькими строками, предыдущие строки будут вставлены .
Если строгий режим не включен, MySQL устанавливает столбец неявным значением по умолчанию для типа данных столбца.
Итак, с сердцем. Задайте свои значения по умолчанию в бизнес-логике (объектах), и пусть слой данных примет направление от этого. Базы данных по умолчанию кажутся хорошей идеей, но если их не было, вы бы пропустили их? Если дерево попадает в лес …