Is it possible to define a timestamp column that i

问题:

When I create a table with a timestamp column, that column is magically defined as NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP. Ignoring how weird this is so far, I would like to change it to have no default and no special "on update" behavior.

I found that if I change the column to be NULL, the default is magically set to NULL and the "on update" magically disappears. This is great, however I would like the column to be NOT NULL. When I change it back, both the default and "on update" (set to CURRENT_TIMESTAMP) magically reappear.

I know I could use datetime instead of timestamp, but I'm interested in timestamp because it is timezone-aware (seems to be like "timestamp with time zone" in Postgres).

回答1:

Timestamp columns are a special case. See here: By default, TIMESTAMP columns are NOT NULL, cannot contain NULL values, and assigning NULL assigns the current timestamp.

For more detailed information read up on Data Type Default Values.

Specifically that situation applies when not running in strict mode. If running in strict mode, inserting a NULL will throw an error.

This should take care of it:

ALTER TABLE tableName ALTER COLUMN columnName DROP DEFAULT;

If that doesn't work, doing this is supposed to leave you with the default (easily overwritten) but remove the ON UPDATE:

ALTER TABLE tableName CHANGE columnName columnName NOT NULL DEFAULT CURRENT_TIMESTAMP;

Note the repeated column name.

回答2:

Here is a possible workaround: Disable ON UPDATE

Basically, you can set the ts to its original value like that:

UPDATE woot SET col=<value_to_set>, ts=ts;