MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”

by

In MySQL 5.6.5 there are several updates regarding this initialization, you can see on this link (Automatic Timestamp Properties Before MySQL 5.6.5).

If you’re using MySQL <= 5.6.5, in order to ignore this initialization you need to set the DEFAULT value to 0 or NULL with NULL allowed.

CREATE TABLE tbl
(
    field1 TIMESTAMP DEFAULT 0,
    field2 TIMESTAMP NULL DEFAULT NULL
)

If you’re using MySQL >= 5.6.6, there is parameter called explicit_defaults_for_timestamp which is disabled by default. You can enable this setting or set the DEFAULT value to 0 or NULL, same approach for previous MySQL versions.

If you’re using MySQL >= 8.0.2, then explicit_defaults_for_timestamp is enabled by default. This disables the non-standard behaviour (thankfully). Also, MySQL generates a warning when you disable this setting. So, for instance, if you don’t define DEFAULT value for a TIMESTAMP column, it is automatically set to NULL.

More Related Contents:

  1. Should I use the datetime or timestamp data type in MySQL?
  2. MySQL convert date string to Unix timestamp
  3. How to get the difference between two timestamps in seconds
  4. Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?
  5. Timestamp with a millisecond precision: How to save them in MySQL
  6. Group mysql query by 15 min intervals
  7. How to select rows that have current day’s timestamp?
  8. MySql difference between two timestamps in days?
  9. MySQL CURRENT_TIMESTAMP on create and on update
  10. Calculating time difference between 2 dates in minutes
  11. Use MySQL to determine whether today is a user’s birthday
  12. When is a timestamp (auto) updated?
  13. How do I alter a mysql table column defaults?
  14. How to use IN & NOT IN same query [closed]
  15. MySQL: Large VARCHAR vs. TEXT?
  16. Check overlap of date ranges in MySQL
  17. Multiple-column foreign key in MySQL?
  18. mysql – making a mechanism similar to Oracle’s seqences
  19. Get the latest date from grouped MySQL data
  20. UUID performance in MySQL?
  21. MySQL: NULL vs “”
  22. Optimal MySQL settings for queries that deliver large amounts of data?
  23. mysql configuration stops at “starting server”
  24. MySQL Error 1264: out of range value for column
  25. Like Case Sensitive in MySQL
  26. What is the best way to delete old rows from MySQL on a rolling basis?
  27. mysql equivalent data types
  28. Get difference in years between two dates in MySQL as an integer
  29. Using MySQL triggers to log all table changes to a secondary table
  30. mySQL regex in the where clause

Leave a Comment