Home mysql uses "on update" and sets a default albeit I never told it to do so
Reply: 1

mysql uses "on update" and sets a default albeit I never told it to do so

Fuzzyma
1#
Fuzzyma Published in 2017-12-07 18:49:03Z

I executed the following query on my mysql server:

CREATE TABLE user(
  created_at timestamp NOT NULL,
  updated_at timestamp NOT NULL,
  id integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
  count integer NOT NULL,
  name varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

When I looked into phpmyadmin I was suprised. The created_at column had a default set to CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP was also there.

How can this even happen? It shouldnt do that, right?

Gordon Linoff
2#
Gordon Linoff Reply to 2017-12-07 18:53:14Z

Although this behavior is surprising to me, it is actually explained in the documentation:

For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

  • An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.

  • An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.304944 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO