| The final part of a column definition (following the data type) can include optional attributes that modify how MySQL handles the column. The following table contains an integer column that is UNSIGNED and cannot contain NULL values, a string column that has a character set of utf8, and a date column that has a default value of '1999-12-31': CREATE TABLE t ( i INT UNSIGNED NOT NULL, c CHAR(10) CHARACTER SET utf8, d DATE DEFAULT '1999-12-31' );
The following sections describe the allowable column attributes. 5.6.1. Numeric Column Attributes Numeric data types other than BIT may have the following attributes: UNSIGNED causes negative values to be disallowed. ZEROFILL causes retrieved values to be left-padded with leading zeros up to the column's display width. For example, if you store the values 0, 14, and 1234 in a column that's defined as INT(5) ZEROFILL, MySQL displays them as 00000, 00014, and 01234 when you retrieve them. Using the ZEROFILL attribute for a column causes it to be UNSIGNED as well. AUTO_INCREMENT applies to integer data types. It's used to generate sequences of successive unique values. Defining a column with AUTO_INCREMENT causes a special behavior: When you insert NULL into the column, MySQL generates the next value in the sequence automatically and stores that in the column instead. Use of AUTO_INCREMENT carries with it other requirements: There may be only one AUTO_INCREMENT column per table, the column must be indexed, and the column must be defined as NOT NULL. Section 5.7, "Using the AUTO_INCREMENT Column Attribute," provides further details on AUTO_INCREMENT columns.
5.6.2. String Column Attributes The following attributes apply to the non-binary string data types (CHAR, VARCHAR, and TEXT): CHARACTER SET specifies the character set to use for the column. CHARSET is a synonym for CHARACTER SET. COLLATE specifies the character set collation. BINARY is shorthand for specifying the binary collation of the column's character set. Note that the BINARY attribute differs from the BINARY data type. The former sets the collation for a non-binary string column. The latter creates a binary string column.
If both CHARACTER SET and COLLATE are given, the collation must be legal for the character set. Specifying CHARACTER SET without COLLATE sets the collation to the default collation for the character set. Specifying COLLATE without CHARACTER SET sets the character set to the collation's character set. (Each collation is unique to a specific character set.) If both the CHARACTER SET and COLLATE attributes are omitted, the table defaults are used. The character set binary is special and modifies the column's data type: It causes columns declared using the CHAR, VARCHAR, and TEXT non-binary string types to be created using the BINARY, VARBINARY, and BLOB binary string types, respectively. 5.6.3. General Column Attributes The following attributes can be used with all data types, subject to the exceptions noted: NULL and NOT NULL apply to all types of columns. They indicate whether a column can contain NULL values. If you specify neither attribute, the default is to allow NULL values in the column. The exceptions are that NULL cannot be stored in AUTO_INCREMENT columns (the next sequence number is stored instead), or in TIMESTAMP columns that are defined to update automatically with the current timestamp when set to NULL. DEFAULT value provides a column with a default value to be used when you create a new record but don't explicitly specify a value for the column. For example, default values are used when you execute an INSERT statement that doesn't provide values for all columns in the table. There are certain limitations on when DEFAULT can be given and on the values that you can specify: DEFAULT can be used with all data types with the exception of TEXT and BLOB columns, or integer columns that have the AUTO_INCREMENT attribute. A default value must be a constant, not an expression whose value is calculated at record-creation time. The exception is that DEFAULT for a single TIMESTAMP column in a table can be given as the CURRENT_TIMESTAMP function to specify a default of "the current date and time." The rules for declaring TIMESTAMP columns are discussed in Section 5.5.2, "The TIMESTAMP Data Type." It is an error to specify a default value of NULL for a NOT NULL column. It is an error to specify a default value that is out of range for the data type, such as a negative number for an UNSIGNED numeric column.
If you specify no DEFAULT value for a column, MySQL determines whether to add a DEFAULT clause to the column definition based on whether the column allows NULL values. If the column allows NULL, MySQL adds DEFAULT NULL to the column definition. If the column does not allow NULL, MySQL adds no DEFAULT clause to the definition. In this case, the default value is implicit and may or may not be used when the column is missing from an INSERT statement, depending on whether the server is operating in strict SQL mode. Treatment of missing values is described in Section 5.8, "Handling Missing or Invalid Data Values." Implicit default values are defined as follows: For numeric columns, the default is zero. For string columns other than ENUM, the default is the empty string. For ENUM columns, the default is the first enumeration member. For temporal columns, the default value is the "zero" value for the data type, represented in whatever format is appropriate to the type (for example, '0000-00-00' for DATE and '00:00:00' for TIME). For TIMESTAMP, the implicit default is the current timestamp if the column is defined to be automatically initialized, or the "zero" value otherwise.
For all data types except BLOB and TEXT, it's also possible to specify a PRIMARY KEY or UNIQUE clause at the end of a column definition, although these aren't really column attributes as such. They cause the creation of a PRIMARY KEY or UNIQUE index for the column. Adding either of these clauses to a column definition is the same as defining the index in a separate clause. For example, the following table definitions are equivalent: CREATE TABLE t (i INT NOT NULL PRIMARY KEY); CREATE TABLE t (i INT NOT NULL, PRIMARY KEY (i));
|
