In some cases, MySQL silently changes column specifications from
those given in a
CREATE TABLE or
ALTER TABLE statement. These
might be changes to a data type, to attributes associated with a
data type, or to an index specification.
All changes are subject to the internal row-size limit of 65,535 bytes, which may cause some attempts at data type changes to fail. See Section C.7.4, “Limits on Table Column Count and Row Size”.
Some silent column specification changes include modifications to attribute or index specifications:
TIMESTAMPdisplay sizes are discarded.
Also note that
NOT NULLby default.
Columns that are part of a
PRIMARY KEYare made
NOT NULLeven if not declared that way.
MySQL maps certain data types used by other SQL database vendors to MySQL types. See Section 11.9, “Using Data Types from Other Database Engines”.
If you include a
USINGclause to specify an index type that is not legal for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type.
Possible data type changes are given in the following list. If a version number is given, the change occurs only up to the versions listed. After that, an error occurs if a column cannot be created using the specified data type.
Before MySQL 5.0.3, if any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (
CHARcolumns longer than three characters are changed to
VARCHARcolumns. This does not affect how you use the columns in any way; in MySQL,
VARCHARis just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See Chapter 14, Storage Engines.
Before MySQL 5.0.3, a
VARCHARcolumn with a length specification greater than 255 is converted to the smallest
TEXTtype that can hold values of the given length. For example,
VARCHAR(500)is converted to
VARCHAR(200000)is converted to
MEDIUMTEXT. Similar conversions occur for
VARBINARY, except that they are converted to a
Note that these conversions result in a change in behavior with regard to treatment of trailing spaces.
As of MySQL 5.0.3, a
BINARYcolumn with a length specification greater than 255 is not silently converted. Instead, an error occurs. From MySQL 5.0.6 on, silent conversion of
VARBINARYcolumns with a length specification greater than 65535 does not occur if strict SQL mode is enabled. Instead, an error occurs.
Before MySQL 5.0.10, for a specification of
Mis not larger than
D, it is adjusted upward. For example,
DECIMAL(11,10). As of MySQL 5.0.10,
DECIMAL(10,10)is created as specified.
CHARACTER SET binaryattribute for a character data type causes the column to be created as the corresponding binary data type:
BLOB. For the
SETdata types, this does not occur; they are created as declared. Suppose that you specify a table using this definition:
CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary );
The resulting table has this definition:
CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary );
Certain other data type changes can occur if you compress a table using myisampack. See Section 22.214.171.124, “Compressed Table Characteristics”.