Data Type Default Values
857
The
DEFAULT value
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
NOW()
[926]
or
CURRENT_DATE
[918]
. The exception is that you can specify
CURRENT_TIMESTAMP
[918]
as the default for a
TIMESTAMP
column. See
Section 11.1.5.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 the
AUTO_INCREMENT
attribute, 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 first
TIMESTAMP
column in a table, the default value is the current date and time. See
Section 11.1.5, “Date and Time Types”
.
• For string types other than
ENUM
, the default value is the empty string. For
ENUM
, 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)
[965]
cannot produce a value:
INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...