Insert NULL into DATE field MySQL 5.6
I seem to be running into an issue inserting NULL into a DATE field in MySQL 5.6. The error is Error Code: 1292. Incorrect date value: '' for column 'StartDate' at row 1.
Here is the table description. The StartDate and EndDate are both DATE type, NULLABLE and default value of NULL.
Active bit(1) NO b'1'
ClientContact varchar(255) YES
ClientID int(11) unsigned NO MUL
ClientReferenceNumber varchar(100) YES
Description mediumtext YES
EndDate date YES
HourlyRate decimal(6,2) YES
Hours int(11) YES
Notes text YES
OffShoreAmount decimal(10,2) YES
OnShoreAmount decimal(10,2) YES
ParentWorkOrderID int(11) unsigned YES MUL
ReferenceNumber varchar(100) YES
StartDate date YES
TotalAmount decimal(10,2) YES
WorkOrderID int(10) unsigned NO PRI auto_increment WorkOrderTypeID int(10) unsigned NO MUL
However the following queries do NOT work.
INSERT INTO WorkOrder
(ParentWorkOrderID, WorkOrderTypeID, ClientID, ReferenceNumber, ClientReferenceNumber,
Description, StartDate, EndDate, OnShoreAmount, OffShoreAmount, TotalAmount, HourlyRate,
Hours, Notes, Active, ClientContact)
VALUES (NULL, 1, 89, 'et-care-001', 'HG453443', '', '', '', 10, 0, 10, NULL, NULL,
'', 1, 'Jebus');
INSERT INTO WorkOrder
(ParentWorkOrderID, WorkOrderTypeID, ClientID, ReferenceNumber, ClientReferenceNumber,
Description, StartDate, EndDate, OnShoreAmount, OffShoreAmount, TotalAmount, HourlyRate,
Hours, Notes, Active, ClientContact)
VALUES (NULL, 1, 89, 'et-care-001', 'HG453443', '', '', '', 10, 0, 10, 'NULL', 'NULL',
'', 1, 'Jebus');
I'm positive this was working in MySQL 5.5. What is the proper way to insert null into the field?
Insert NULL and not ''. Also, 'Null' is not equal to NULL
INSERT INTO WorkOrder (
ParentWorkOrderID,
WorkOrderTypeID,
ClientID,
ReferenceNumber,
ClientReferenceNumber,
Description,
StartDate,
EndDate,
OnShoreAmount,
OffShoreAmount,
TotalAmount,
HourlyRate,
Hours,
Notes,
Active,
ClientContact
) VALUES (
NULL,
1,
89,
'et-care-001',
'HG453443',
'',
'',
'',
10,
0,
10,
NULL,
NULL,
NULL,
1,
'Jebus');
The proper way to insert null into a field is by using NULL .
For example, for a field you may specify values such as 'thisisastring' and 1 . NULL is also valid and only acceptable if the field allows nulls. Note: '' means an empty string, not null and 'null' means a string that contains the word null.
For null checking you use IS NULL or IS NOT NULL
Hope that helps.
链接地址: http://www.djcxy.com/p/25092.html上一篇: 如何索引这个查询？