(mysql.info) data-type-defaults

Info Catalog

(mysql.info) string-type-overview

(mysql.info) data-type-overview

 
 11.1.4 Data Type Default Values
 -------------------------------
 
 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()' or `CURRENT_DATE'. The
 exception is that you can specify `CURRENT_TIMESTAMP' as the default
 for a `TIMESTAMP' column. See  timestamp-4-1.
 
 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 other than those declared with the
      `AUTO_INCREMENT' attribute, the default is `0'. For an
      `AUTO_INCREMENT' column, 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 first `TIMESTAMP'
      column in a table, the default value is the current date and time.
      See  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. For data entry, if an `INSERT' or
 `REPLACE' statement includes no value for the column, MySQL handles the
 column according to the SQL mode in effect at the time:
 
    * If strict SQL mode is not enabled, MySQL sets the column to the
      implicit default value for the column data type.
 
    * If strict mode is enabled, an error occurs for transactional
      tables and the statement is rolled back. For non-transactional
      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.
 
 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  server-sql-mode.
 
 For a given table, you can use the `SHOW CREATE TABLE' statement to see
 which columns have an explicit `DEFAULT' clause.
 

Info Catalog

(mysql.info) string-type-overview

(mysql.info) data-type-overview