MySQL 5.0 Reference Manual  /  ...  /  Silent Column Specification Changes Silent Column Specification Changes

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:

  • TIMESTAMP display sizes are discarded.

    Also note that TIMESTAMP columns are NOT NULL by default.

  • Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way.

  • Trailing spaces are automatically deleted from ENUM and SET member values when the table is created.

  • 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 USING clause 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, VARCHAR columns with a length less than four are changed to CHAR.

  • 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 (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is 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 CHAR or VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, VARCHAR(500) is converted to TEXT, and VARCHAR(200000) is converted to MEDIUMTEXT. Similar conversions occur for BINARY and VARBINARY, except that they are converted to a BLOB type.

    Note that these conversions result in a change in behavior with regard to treatment of trailing spaces.

    As of MySQL 5.0.3, a CHAR or BINARY column with a length specification greater than 255 is not silently converted. Instead, an error occurs. From MySQL 5.0.6 on, silent conversion of VARCHAR and VARBINARY columns 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 DECIMAL(M,D), if M is not larger than D, it is adjusted upward. For example, DECIMAL(10,10) becomes DECIMAL(11,10). As of MySQL 5.0.10, DECIMAL(10,10) is created as specified.

  • Specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition:

      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:

      c1 VARBINARY(10),
      c2 BLOB,
      c3 ENUM('a','b','c') CHARACTER SET binary

To see whether MySQL used a data type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering the table.

Certain other data type changes can occur if you compress a table using myisampack. See Section, “Compressed Table Characteristics”.

User Comments
Sign Up Login You must be logged in to post a comment.