MySQL INSERT，而不必指定每个非默认字段(#1067-“表"的默认值无效) [英] MySQL INSERT without having to specify every non-default field (#1067 - Invalid default value for 'table')

问题描述

我已经看过几次了.我有一台服务器可以让我插入某些值，而无需像这样指定其他值:INSERT INTO table SET value_a='a', value_b='b';(value_c是没有设置默认值的字段，但在这里工作正常).当脚本移至新服务器时，某些INSERT查询会中断，因为它要求查询指定所有非默认值，如果首次出现未指定非默认值的情况，则会出现以下错误:

I have seen this several times. I have one server that allows me to insert some of the values, without specifying the others like so: INSERT INTO table SET value_a='a', value_b='b'; (value_c is a field that does not have a default value set, but it works fine here). When the script is moved to a new server some INSERT queries break because it requires the query to specify all non-default values, giving me the following error for the first occurrence of not specifying a non-default value:

#1364 - Field 'value_c' doesn't have a default value

设置表的默认值可能会破坏其他区域的功能，否则我会这样做.我很想知道这里到底发生了什么.

Setting default values for the table might break functionality in other areas, otherwise I would just do that. I would love to know what exactly is going on here.

推荐答案

默认情况下，您的一台服务器在严格模式下运行，而另一台则不在. 如果服务器在严格模式下运行(或在连接中进行设置)，并且尝试将NULL值插入定义为NOT NULL的列中，则会出现#1364错误.如果没有严格模式，则您的NULL值将替换为空字符串或0.

One of your servers is running in strict mode by default and the other not. If a server runs in strict mode (or you set it in your connection) and you try to insert a NULL value into a column defined as NOT NULL you will get #1364 error. Without strict mode your NULL value will be replaced with empty string or 0.

示例:

CREATE TABLE `test_tbl` (
 `id` int(11) NOT NULL,
 `someint` int(11) NOT NULL,
 `sometext` varchar(255) NOT NULL,
 `somedate` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SET sql_mode = '';
INSERT INTO test_tbl(id) VALUES(1);
SELECT * FROM test_tbl;
+----+---------+----------+---------------------+
| id | someint | sometext | somedate            |
+----+---------+----------+---------------------+
|  1 |       0 |          | 0000-00-00 00:00:00 |
+----+---------+----------+---------------------+
SET sql_mode = 'STRICT_ALL_TABLES';
INSERT INTO test_tbl(id) VALUES(2);
#1364 - Field 'someint' doesn't have a default value 

这篇关于MySQL INSERT，而不必指定每个非默认字段(#1067-“表"的默认值无效)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！