(mysql.info) silent-column-changes

 
 13.1.5.1 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.
 
 Possible data type changes are given in the following list.  These
 occur prior to MySQL 5.0.3. As of 5.0.3, an error occurs if a column
 cannot be created using the specified data type.
 
    * `VARCHAR' columns with a length less than four are changed to
      `CHAR'.
 
    * 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 
      storage-engines.
 
    * Previous to 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'. Note that this conversion results in a
      change in behavior with regard to treatment of trailing spaces.
 
      Similar conversions occur for `BINARY' and `VARBINARY', except
      that they are converted to a `BLOB' type.
 
      Starting with 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 65,535 does not occur if strict SQL mode is enabled.
      Instead, an error occurs.
 
    * 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)'.
 
 Other silent column specification changes include changes to attribute
 or index specifications:
 
    * `TIMESTAMP' display sizes are discarded.  Note that `TIMESTAMP'
      columns have changed considerably in recent versions of MySQL
      prior to 5.0; for a description of these changes, see the MySQL
      3.23, 4.0, 4.1 Reference Manual.
 
    * 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  other-vendor-data-types.
 
    * 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.
 
 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  compressed-format.
Info Catalog
(mysql.info) create-table
automatically generated byinfo2html