Implicit Default Column Values in MySQL 5

In a non-strict mode MySQL server, columns with NOT NULL but without DEFAULT <value> attributes are given implicit default values based on their data types when no values are explicitly set for them during SQL INSERT. MySQL will issue warnings though.

The following are the general rules governing implicit default values:

  1. Zero (0) for numeric dtata types
  2. Empty string for string data types (except ENUM type)
  3. “Zero” values for temporal data types.
    • Special case for TIMESTAMP type: the column must be explicitly set to ON UPDATE CURRENT_TIMESTAMP. Without which, MySQL automatically adds DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes to the column definition.

If MySQL is in strict mode, the SQL INSERT above would not work and cause error(s).

Karl San Gabriel

Karl San Gabriel

Java and Enterprise Technologies Expert