- 11.1 Data Type Overview
- 11.1.1 Overview of Numeric Types
- 11.1.2 Overview of Date and Time Types
- 11.1.3 Overview of String Types
- 11.1.4 Data Type Default Values
The DEFAULT
clause in a data type specification indicates a default value
for a column. With one exception, the default value must be a
constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column
to be the value of a function such as valueNOW()
or CURRENT_DATE. The exception is that you
can specify CURRENT_TIMESTAMP as the default
for a TIMESTAMP column as of MySQL 4.1.2. See
Section 11.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.
If a column definition includes no explicit
DEFAULT value, MySQL determines the default
value as follows:
If the column can take NULL as a value, the
column is defined with an explicit DEFAULT
NULL clause.
If the column cannot take NULL as the value,
MySQL defines the column with an explicit
DEFAULT clause, using the implicit default
value for the column data type. Implicit defaults are defined as
follows:
For numeric types other than integer types declared with the
AUTO_INCREMENTattribute, the default is0. For anAUTO_INCREMENTcolumn, the default value is the next value in the sequence.For date and time types other than
TIMESTAMP, the default is the appropriate “zero” value for the type. For the firstTIMESTAMPcolumn in a table, the default value is the current date and time. See Section 11.3, “Date and Time Types”.For string types other than
ENUM, the default value is the empty string. ForENUM, the default is the first enumeration value.
BLOB and TEXT columns
cannot be assigned a default value.
For a given table, you can use the SHOW CREATE
TABLE statement to see which columns have an explicit
DEFAULT clause.
SERIAL DEFAULT VALUE in the definition of an
integer column is an alias for NOT NULL AUTO_INCREMENT
UNIQUE.
User Comments
The function DEFAULT(col_name) could be useful in certain situations (since 4.1 version).
Add your own comment.