Contact MySQL | Login | Register
11.7 Data Type Storage Requirements »
11.6 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. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.
Prior to MySQL 5.0.2, 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, the default is
0, with the exception that for integer or floating-point types declared with theAUTO_INCREMENTattribute, the default 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.
As of MySQL 5.0.2, 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. This is the same as before 5.0.2.
If the column cannot take NULL as the value,
MySQL defines the column with no explicit
DEFAULT clause. Exception: If the column is
defined as part of a PRIMARY KEY but not
explicitly as NOT NULL, MySQL creates it as a
NOT NULL column (because PRIMARY
KEY columns must be NOT NULL), but
also assigns it a DEFAULT clause using the
implicit default value. To prevent this, include an explicit
NOT NULL in the definition of any
PRIMARY KEY column.
For data entry into a NOT NULL column that has
no explicit DEFAULT clause, if an
INSERT or
REPLACE statement includes no value
for the column, or an UPDATE
statement sets the column to NULL, MySQL
handles the column according to the SQL mode in effect at the
time:
If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.
If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
Suppose that a table t is defined as follows:
CREATE TABLE t (i INT NOT NULL);
In this case, i has no explicit default, so in
strict mode each of the following statements produce an error and
no row is inserted. When not using strict mode, only the third
statement produces an error; the implicit default is inserted for
the first two statements, but the third fails because
DEFAULT(i) cannot produce a value:
INSERT INTO t VALUES(); INSERT INTO t VALUES(DEFAULT); INSERT INTO t VALUES(DEFAULT(i));
See Section 5.1.7, “Server SQL Modes”.
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