caiocpricci2 caiocpricci2 - 25 days ago 8
MySQL Question

How to set a default constant value for timestamp columns?

I have a table with a nullable timestamp field (enddate)

CREATE TABLE `fee` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(50) NOT NULL DEFAULT 'FIXED',
`name` varchar(100) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`startdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`enddate` datetime NOT NULL DEFAULT '2038-01-18 22:00:00',
PRIMARY KEY (`id`),
) ENGINE=InnoDB;


I run an insert, passing a null to startdate and enddate, expecting that it would use my default values. So running:

INSERT INTO `fee` (`type`, `name`, `description`, `startdate`, `enddate`)
VALUES
('FIXED', 'Delivery fee', NULL, NULL, NULL);


Would insert:

27, FIXED, Delivery fee, NULL, NOW(), '2038-01-18 22:00:00'


But it inserts:

27, FIXED, Delivery fee, NULL, NOW(), NOW()


I tried this in mysql 5.5, 5.6 and 5.7, based on their documentation:


Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.


Question is, what am I doing wrong?

Answer

When you set default value for any field in table, you only should pass the value when you actually wants to insert something else rather then default value. You were setting it to null with your query but IN your CREATE query

`enddate` datetime NOT NULL DEFAULT '2038-01-18 22:00:00'

you have set it as it should be 'NOT NULL'.

Comments