MySQL – Why Default Timestamp Added on Insert/Update

MySQLtimestamp

I have a table that is as follows: 

CREATE TABLE SomeTable (  
    id int unsigned NOT NULL AUTO_INCREMENT,  
    status enum('broken','repaired') NOT NULL,  
    from datetime NOT NULL,  
    until datetime NOT NULL,  
    brought_in timestamp,  
    brought_by varchar(255) NOT NULL,  
    repair_status enum('unpayed', 'payed') DEFAULT NULL,  
    payed_when timestamp,    
    delivered_when timestamp,  
    primary key(id)    
);

When I create the table if I do a SHOW CREATE TABLE then I see that the column brought_in is defined to have the current timestamp ON INSERT and ON UPDATE.
But I did not add such an instruction in my create table.
Why doesn't it let it be empty like the other columns?
Now on every insert or update of another column it adds the current timestamp. I don't want that.
It does not do that with the other columns. What am I doing wrong?

Best Answer

This is the default behaviour of the (first) timestamp column. Many more details can be read in the documentation: Automatic Initialization and Updating for TIMESTAMP.

If you don't want this, you have to explicitly tell MySQL when you create the table. You can declare a default value (or default null) and the automatic properties will be supressed. Any of the following will do (with differences in behaviour explained):

Option 1: not null and default 0

brought_in timestamp NOT NULL DEFAULT 0,             -- option 1a

brought_in timestamp DEFAULT 0,                      -- option 1b

These two options are identical. The column is defined as NOT NULL and has a default value of 0. Note that TIMESTAMP is the only datatype that has a default property of NOT NULL, in contrast with all other datatypes and with the ISO/ANSI standard.

If you try to insert NULL in the column, the CURRENT_TIMESTAMP is inserted (surprise, another MySQL feature.)

Option 2: null and default null

brought_in timestamp NULL DEFAULT NULL,              -- option 2a

brought_in timestamp NULL,                           -- option 2b

These are identical, they allow NULL (and both result in default being NULL as well.)

If you try to insert NULL in the column, it is allowed and inserted fine (no surprises here.)

Option 3: null and default 0 or some other constant

brought_in timestamp NULL DEFAULT 0,                 -- option 3a

brought_in timestamp NULL DEFAULT '2013-01-01',      -- option 3b

This is similar to option 2 but has a different default value.

If you try to insert NULL in the column, it is allowed and inserted fine, too (no surprises here either.)

Related Question