MySQL - 无法在列中插入 NULL 值，但我指定了默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3384668/
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
MySQL - Cannot insert NULL value in column, but I have a default value specified?
提问by Ryk
I have a table in MySQL that have a few columns that have default values specified, but when I try to insert a row, (not specifying values for those default columns), it throws an error saying I cannot insert NULL values.
我在 MySQL 中有一个表，其中有几列指定了默认值，但是当我尝试插入一行时（不指定这些默认列的值），它会抛出一个错误，提示我无法插入 NULL 值。
Here is the table example;
这是表格示例；
CREATE TABLE `users` (
`Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`UniqueName` varchar(120) NOT NULL,
`Password` varchar(1000) NOT NULL,
`PublicFlag` tinyint(1) NOT NULL,
`NoTimesLoggedIn` int(10) unsigned NOT NULL DEFAULT '0',
`DateTimeLastLogin` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',
`UserStatusTypeId` int(10) unsigned NOT NULL,
`Private` tinyint(1) NOT NULL DEFAULT '0',
`SiteName` varchar(255) NOT NULL,
`CountryId` int(10) NOT NULL DEFAULT '0',
`TimeZoneId` varchar(255) NOT NULL DEFAULT 'UTC',
`CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',
`DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`UserCreated` int(10) unsigned NOT NULL,
`LastUpdatedBy` int(10) unsigned NOT NULL,
`DateLastUpdated` datetime DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `UniqueName_UNIQUE` (`UniqueName`),
KEY `Index 3` (`SiteName`)
)
It complains about TimeZoneId, and when I populate TimeZoneId, it complains about CultureInforId.
它抱怨 TimeZoneId，当我填充 TimeZoneId 时，它抱怨 CultureInforId。
I am using MySQL Version: 5.1.43-community
我使用的是 MySQL 版本：5.1.43-community
Here is the insert query I am trying to insert, grabbed from NHibernate Profiler:
这是我试图插入的插入查询，从 NHibernate Profiler 中获取：
INSERT INTO Users
(UniqueName,
Password,
PublicFlag,
NoTimesLoggedIn,
DateTimeLastLogin,
SiteName,
TimeZoneId,
DateCreated,
DateLastUpdated,
Private,
CountryId,
CultureInfoId,
UserCreated,
LastUpdatedBy,
UserStatusTypeId)
VALUES ('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1,
0,'1/01/1971 12:00:00 AM','V9O1T80Q6D',NULL,'2/08/2010 2:13:44 AM',
'2/08/2010 2:13:44 AM',0, NULL, NULL, 4, 4,31)
采纳答案by Bill Karwin
Use the DEFAULTkeyword instead:
改用DEFAULT关键字：
INSERT INTO users (TimeZoneId) VALUES (DEFAULT);
回答by Mark Eirich
Do not insert NULL values. I'm assuming you were trying this syntax:
不要插入 NULL 值。我假设你正在尝试这种语法：
INSERT INTO users VALUES (null, 'Jones', 'yarg', 1, null, null, null);
Instead, use this syntax:
相反，请使用以下语法：
INSERT INTO users SET UniqueName='Jones', Password='yarg';
For more info, see the MySQL docs on INSERT.
回答by User123342234
You have "NOT NULL" set on fields that you are trying to INSERT NULL on.
您在尝试插入 NULL 的字段上设置了“NOT NULL”。
eg. CountryId, CultureInfoId, TimeZoneId
例如。CountryId、CultureInfoId、TimeZoneId
execute the following:
执行以下操作：
ALTER TABLE `users` MODIFY `CountryId` int(10) DEFAULT '0' NULL;
ALTER TABLE `users` MODIFY `CultureInfoId` int(10) unsigned DEFAULT '0' NULL;
ALTER TABLE `users` MODIFY `TimeZoneId` varchar(255) DEFAULT 'UTC' NULL;
EDIT: Didn't realize he wanted the default value instead of NULL on "null" insert. Basically as already has been suggested use the DEFAULT keyword in place of NULL on the values.
编辑：没有意识到他想要“空”插入时的默认值而不是 NULL。基本上已经建议使用 DEFAULT 关键字代替 NULL 值。
OR leave the NULL fields and values out altogether and mysql will use the defined defaults eg.
或者完全保留 NULL 字段和值，mysql 将使用定义的默认值，例如。
INSERT INTO Users
(UniqueName,
Password,
PublicFlag,
NoTimesLoggedIn,
DateTimeLastLogin,
SiteName,
DateCreated,
DateLastUpdated,
Private,
UserCreated,
LastUpdatedBy,
UserStatusTypeId)
VALUES ('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1,
0,'1/01/1971 12:00:00 AM','V9O1T80Q6D','2/08/2010 2:13:44 AM',
'2/08/2010 2:13:44 AM',0, 4, 4,31)
回答by Ross
As an alternative to using the DEFAULT keyword you can also just not specify values for the fields which you want to have default values. For instance if you just removed TimeZoneId, CountryId and CultureInfoId from your query entirely those columns will receive the default values automatically:
作为使用 DEFAULT 关键字的替代方法，您也可以不指定要具有默认值的字段的值。例如，如果您刚刚从查询中完全删除 TimeZoneId、CountryId 和 CultureInfoId，这些列将自动接收默认值：
INSERT INTO Users
(UniqueName,
Password,
PublicFlag,
NoTimesLoggedIn,
DateTimeLastLogin,
SiteName,
DateCreated,
DateLastUpdated,
Private,
UserCreated,
LastUpdatedBy,
UserStatusTypeId)
VALUES
('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1,0,
'1/01/1971 12:00:00 AM','V9O1T80Q6D','2/08/2010 2:13:44 AM',
'2/08/2010 2:13:44 AM',0,4,4,31)
I'm not sure how that would work in the context of NHibernate however as that part of the question wasn't quite as well explained.
我不确定这在 NHibernate 的上下文中如何工作，但是因为问题的那部分没有得到很好的解释。
回答by Ankur Mukherjee
TimeZoneIdvarchar(255) NOT NULL DEFAULT 'UTC',
CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',
TimeZoneId varchar(255) NOT NULL DEFAULT 'UTC',
CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',
For this fields you have set constraints as "Not Null" and hence values inserted can't be null and hence either alter the table structure or just not specify values for the fields which you want to have default values.
对于此字段，您已将约束设置为“非空”，因此插入的值不能为空，因此要么更改表结构，要么只是不为您希望具有默认值的字段指定值。
