mysql报错无效默认值1067,MySQL INSERT，而不必指定每个非默认字段（＃1067-“表”的默认值无效）...

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.

解决方案

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.

Example:

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