(mysql.info.gz) Silent column changes

Info Catalog

(mysql.info.gz) CREATE TABLE

(mysql.info.gz) CREATE TABLE

 
 13.2.6.1 Silent Column Specification Changes
 ............................................
 
 In some cases, MySQL silently changes column specifications from those
 given in a `CREATE TABLE' or `ALTER TABLE' statement:
 
    * `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 doesn't 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.   Storage engines.
 
    * From MySQL 4.1.0 on, 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'.  This is a compatibility feature.
 
    * `TIMESTAMP' display sizes are discarded from MySQL 4.1 on, due to
      changes made to the `TIMESTAMP' column type in that version.
      Before MySQL 4.1, `TIMESTAMP' display sizes must be even and in the
      range from 2 to 14.  If you specify a display size of 0 or greater
      than 14, the size is coerced to 14.  Odd-valued sizes in the range
      from 1 to 13 are coerced to the next higher even number.
 
    * You cannot store a literal `NULL' in a `TIMESTAMP' column; setting
      it to `NULL' sets it to the current date and time.  Because
      `TIMESTAMP' columns behave this way, the `NULL' and `NOT NULL'
      attributes do not apply in the normal way and are ignored if you
      specify them.  `DESCRIBE TBL_NAME' always reports that a
      `TIMESTAMP' column can be assigned `NULL' values.
 
    * Columns that are part of a `PRIMARY KEY' are made `NOT NULL' even
      if not declared that way.
 
    * Starting from MySQL 3.23.51, trailing spaces are automatically
      deleted from `ENUM' and `SET' member values when the table is
      created.
 
    * MySQL maps certain column types used by other SQL database vendors
      to MySQL types.   Other-vendor column types.
 
    * If you include a `USING' clause to specify an index type that is
      not legal for a storage engine, but there is another index type
      available that the engine can use without affecting query results,
      the engine will use the available type.
 
 
 To see whether MySQL used a column type other than the one you
 specified, issue a `DESCRIBE' or `SHOW CREATE TABLE' statement after
 creating or altering your table.
 
 Certain other column type changes can occur if you compress a table
 using `myisampack'.  Compressed format.
 

Info Catalog

(mysql.info.gz) CREATE TABLE

(mysql.info.gz) CREATE TABLE