MySQL-无法在列中插入NULL值，但是我指定了默认值吗? [英] MySQL - Cannot insert NULL value in column, but I have a default value specified?

问题描述

我在MySQL中有一个表，其中有几列指定了默认值，但是当我尝试插入一行时(未为那些默认列指定值)，它抛出一个错误，说我不能插入NULL值.

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.

这是表格示例；

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`)
)

它抱怨TimeZoneId，当我填充TimeZoneId时，它抱怨CultureInforId.

It complains about TimeZoneId, and when I populate TimeZoneId, it complains about CultureInforId.

我正在使用MySQL版本:5.1.43-community

I am using MySQL Version: 5.1.43-community

这是我要插入的插入查询，是从NHibernate Profiler抓取的:

Here is the insert query I am trying to insert, grabbed from NHibernate Profiler:

INSERT INTO Users
           (UniqueName,
            Password,
            PublicFlag,
            NoTimesLoggedIn,
            DateTimeLastLogin,
            SiteName,
            TimeZoneId,
            DateCreated,
            DateLastUpdated,
            Private,
            CountryId,
            CultureInfoId,
            UserCreated,
            LastUpdatedBy,
            UserStatusTypeId)
VALUES     ('[e-mail]','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)

推荐答案

改用DEFAULT关键字:

INSERT INTO users (TimeZoneId) VALUES (DEFAULT);

这篇关于MySQL-无法在列中插入NULL值，但是我指定了默认值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！