Implicit Default Column Values in MySQL 5

[wp_ad_camp_1]

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:

Zero (0) for numeric dtata types
Empty string for string data types (except ENUM type)
“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.

[wp_ad_camp_3]

CREATE TABLE `ImplicitDefaultValue` (
 `i` tinyint(4) NOT NULL,
 `name` char(10) NOT NULL,
 `ts` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `ImplicitDefaultValue` (

`i` tinyint(4) NOT NULL,

`name` char(10) NOT NULL,

`ts` datetime NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> insert into ImplicitDefaultValue values();
Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'i' doesn't have a default value    |
| Warning | 1364 | Field 'name' doesn't have a default value |
| Warning | 1364 | Field 'ts' doesn't have a default value   |
+---------+------+-------------------------------------------+
3 rows in set (0.01 sec)

mysql> select * from ImplicitDefaultValue;
+---+------+---------------------+
| i | name | ts                  |
+---+------+---------------------+
| 0 |      | 0000-00-00 00:00:00 |
+---+------+---------------------+
1 row in set (0.00 sec)

mysql> insert into ImplicitDefaultValue values();

Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> show warnings;

+---------+------+-------------------------------------------+

| Level | Code | Message |

+---------+------+-------------------------------------------+

| Warning | 1364 | Field 'i' doesn't have a default value |

| Warning | 1364 | Field 'name' doesn't have a default value |

| Warning | 1364 | Field 'ts' doesn't have a default value |

+---------+------+-------------------------------------------+

3 rows in set (0.01 sec)

mysql> select * from ImplicitDefaultValue;

+---+------+---------------------+

| i | name | ts |

+---+------+---------------------+

| 0 | | 0000-00-00 00:00:00 |

+---+------+---------------------+

1 row in set (0.00 sec)

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

[wp_ad_camp_2]

mysql> set sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.01 sec)

mysql> select @@session.sql_mode;
+---------------------------------------+
| @@session.sql_mode                    |
+---------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> insert into ImplicitDefaultValue values();
ERROR 1364 (HY000): Field 'i' doesn't have a default value