MySQL 错误 1364 (HY000)：字段“MY_DATE”没有默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5000547/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR 1364 (HY000): Field 'MY_DATE' doesn't have a default value
提问by garima
create table MYTABLE
{
MY_DATE int NOT NULL AUTO_INCREMENT,
NAME varchar(255) NOT NULL UNIQUE
};
INSERT INTO MYTABLE(NAME)values(jessica);
Why do I get this error?
为什么我会收到这个错误？
ERROR 1364 (HY000): Field 'MY_DATE' doesn't have a default value
错误 1364 (HY000)：字段“MY_DATE”没有默认值
回答by paxdiablo
From the docs:
从文档：
There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.
每个表只能有一个 AUTO_INCREMENT 列，它必须被索引，并且不能有 DEFAULT 值。
I think you'll find that, because you're not indexing MY_DATE, it may be silently ignoring the AUTO_INCREMENToption (I can't guarantee that, it's just supposition, but the note in the documentation is still relevant).
我想你会发现，因为你没有 indexing MY_DATE，它可能会默默地忽略该AUTO_INCREMENT选项（我不能保证，这只是假设，但文档中的注释仍然相关）。
All the samples I can see on the AUTO_INCREMENTstuff have (the equivalent of):
我在这些AUTO_INCREMENT东西上看到的所有样本都有（相当于）：
PRIMARY KEY (MY_DATE)
Alternatively, you maybe running in strict SQL mode. All the docs I've seen seem to indicate that, in the absence of a specific default, unlisted columns in an insert will get NULL if they're nullable, or the type default if the not nullable:
或者，您可能在严格的 SQL 模式下运行。我见过的所有文档似乎都表明，在没有特定默认值的情况下，插入中未列出的列如果可以为空，则为 NULL，如果不可为空，则为类型默认值：
If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 10.1.4, “Data Type Default Values”.
如果您不是在严格的 SQL 模式下运行，则任何未显式指定值的列都将设置为其默认（显式或隐式）值。例如，如果您指定的列列表未命名表中的所有列，则未命名的列将设置为其默认值。默认值分配在第 10.1.4 节“数据类型默认值”中描述。
For an integral type, that default is 0 (which should kick in the auto increment anyway).
对于整数类型，默认值为 0（无论如何都应该启动自动增量）。
However, in strict SQL mode (from here):
但是，在严格的 SQL 模式下（从这里开始）：
an error occurs for transactional tables and the statement is rolled back.
事务表发生错误并且语句被回滚。
回答by RichardTheKiwi
Which version of MySQL are you using?
您使用的是哪个版本的 MySQL？
I'm using 5.1.41 community and the create table SQL gives
我正在使用 5.1.41 社区并且创建表 SQL 给出
SQL Error (1075): Incorrect table definition; there can be only one auto column and it must be defined as a key
SQL 错误 (1075)：表定义不正确；只能有一个自动列，并且必须将其定义为键
Next correct it to the below
接下来将其更正为以下
create table MYTABLE
(
MY_DATE int NOT NULL AUTO_INCREMENT primary key,
NAME varchar(255) NOT NULL UNIQUE
);
The insert statement
插入语句
INSERT INTO MYTABLE(NAME)values(jessica);
Results in
结果是
SQL Error (1054): Unknown column 'jessica' in 'field list'
SQL 错误 (1054)：“字段列表”中的未知列“jessica”
Because it has not been quoted. Once that is corrected to the below:
因为它没有被引用。一旦将其更正为以下内容：
INSERT INTO MYTABLE(NAME)values('jessica');
It works. You don't have tosupply any values for the auto_increment primary key, but you can.
有用。您不必为 auto_increment 主键提供任何值，但您可以.
Now, let's talk about MySQL quirks. You can optionally include the column, and set a value of NULL. You can also give it a SPECIFIC value and MySQL will happily use it, even ifit is defined as auto_increment.
现在，让我们谈谈 MySQL 的怪癖。您可以选择包含该列，并将值设置为 NULL。你也可以给它一个 SPECIFIC 值，MySQL 会很乐意使用它，即使它被定义为 auto_increment。
INSERT INTO MYTABLE(my_date,NAME)values(41,'jessica2');
INSERT INTO MYTABLE(my_date,NAME)values(null,'jessica3');
INSERT INTO MYTABLE(NAME)values('jessica4');
DELETE FROM MYTABLE where my_date=43;
INSERT INTO MYTABLE(my_date,NAME)values(3,'jessica5');
INSERT INTO MYTABLE(NAME)values('jessica?');
select * from mytable;
When you set the number specifically to 41, the next number becomes 42, then 43. It allows us to specifically use 3, even then though 43 was deleted before we used the specific number 3, the next value is still 44.
当你专门设置数字为41时，下一个数字变为42，然后是43。它允许我们专门使用3，即使在我们使用特定数字3之前删除了43，下一个值仍然是44。
Output
输出
MY_DATE NAME
41 jessica2
42 jessica3
3 jessica5
44 jessica?
回答by Rajeev Rathor
Edit this file /etc/mysql/mysql.conf.d/mysqld.cnf
编辑这个文件 /etc/mysql/mysql.conf.d/mysqld.cnf
add the below statement under [mysqld]
在 [mysqld] 下添加以下语句
sql_mode =
sql_mode =
This is working perfectly for me.
这对我来说非常有效。
回答by Ciaran Keating
You don't supply a value for MY_DATE in your INSERT statement, and there is no default value defined. (And as paxdiablo points out, there can't be a default value defined in this situation.) Unintuitively, you do need to give a value for MY_DATE, and if that value is zero or null then MySQL will assign the next autoincrement value. Like this:
您没有在 INSERT 语句中为 MY_DATE 提供值，并且没有定义默认值。（正如paxdiablo 指出的那样，在这种情况下不能定义默认值。） 不直观的是，您确实需要为 MY_DATE 提供一个值，如果该值为零或为空，则 MySQL 将分配下一个自动增量值。像这样：
insert into mytable values(null,'jessica');
