DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) String type overview

Info Catalog (mysql.info.gz) Date and time type overview (mysql.info.gz) Column type overview
 
 11.1.3 Overview of String Types
 -------------------------------
 
 A summary of the string column types follows.  For additional
 information, see  String types.  Column storage requirements are
 given in  Storage requirements.
 
 In some cases, MySQL may change a string column to a type different from
 that given in a `CREATE TABLE' or `ALTER TABLE' statement.  
 Silent column changes.
 
 A change that affects many string column types is that, as of MySQL 4.1,
 character column definitions can include a `CHARACTER SET' attribute to
 specify the character set and, optionally, a collation. This applies to
 `CHAR', `VARCHAR', the `TEXT' types, `ENUM', and `SET'. For example:
 
      CREATE TABLE t
      (
          c1 CHAR(20) CHARACTER SET utf8,
          c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
      );
 
 This table definition creates a column named `c1' that has a character
 set of `utf8' with the default collation for that character set, and a
 column named `c2' that has a character set of `latin1' and the binary
 collation for the character set. The binary collation is not case
 sensitive.
 
 For `CHAR', `VARCHAR', and the `TEXT' types, the `BINARY' attribute
 causes the column to be assigned the binary collation of the column
 character set as of MySQL 4.1.  Before 4.1, `BINARY' is disallowed for
 the `TEXT' types, and causes `CHAR' and `VARCHAR' to be treated as
 binary strings.
 
 Character column sorting and comparison are based on the character set
 assigned to the column. Before MySQL 4.1, sorting and comparison are
 based on the collation of the server character set. For `CHAR' and
 `VARCHAR' columns, you can declare the column with the `BINARY'
 attribute to cause sorting and comparison to use the underlying
 character code values rather then a lexical ordering.
 
 For more details, see  Charset.
 
 Also as of 4.1, MySQL interprets length specifications in character
 column definitions in characters. (Earlier versions interpret them in
 bytes.)
 
 `[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]'
      A fixed-length string that is always right-padded with spaces to
      the specified length when stored.  M represents the column length.
      The range of M is 0 to 255 characters (1 to 255 prior to MySQL
      3.23).
 
      * Trailing spaces are removed when `CHAR' values are
      retrieved.
 
      From MySQL 4.1.0, a `CHAR' 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, `CHAR(500)' is
      converted to `TEXT', and `CHAR(200000)' is converted to
      `MEDIUMTEXT'.  This is a compatibility feature.  However, this
      conversion causes the column to become a variable-length column,
      and also affects trailing-space removal.
 
      `CHAR' is shorthand for `CHARACTER'.  `NATIONAL CHAR' (or its
      equivalent short form, `NCHAR') is the standard SQL way to define
      that a `CHAR' column should use the default character set.  This
      is the default in MySQL.
 
      As of MySQL 4.1.2, the `BINARY' attribute is shorthand for
      specifying the binary collation of the column character set.
      Before 4.1.2, `BINARY' attribute causes the column to be treated
      as a binary string.  In either case, sorting and comparisons
      become case sensitive.
 
      From MySQL 4.1.0 on, the `ASCII' attribute can be specified. It
      assigns the `latin1' character set to a `CHAR' column.
 
      From MySQL 4.1.1 on, the `UNICODE' attribute can be specified. It
      assigns the `ucs2' character set to a `CHAR' column.
 
      MySQL allows you to create a column of type `CHAR(0)'. This is
      mainly useful when you have to be compliant with some old
      applications that depend on the existence of a column but that do
      not actually use the value.  This is also quite nice when you need
      a column that can take only two values: A `CHAR(0)' column that is
      not defined as `NOT NULL' occupies only one bit and can take only
      the values `NULL' and `''' (the empty string).
 
 `CHAR'
      This is a synonym for `CHAR(1)'.
 
 `[NATIONAL] VARCHAR(M) [BINARY]'
      A variable-length string.  M represents the maximum column length.
      The range of M is 0 to 255 characters (1 to 255 prior to MySQL
      4.0.2).  In MySQL 5.0.3 this is extended to `65535' bytes. (The
      exact number of characters is depending on the character set).
 
      * Before 5.0.3, trailing spaces where removed when `VARCHAR'
      values was stored, which differs from the standard SQL
      specification.
 
      From MySQL 4.1.0 - 5.0.2 on, a `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.
      However, this conversion affects trailing-space removal.
 
      `VARCHAR' is shorthand for `CHARACTER VARYING'.
 
      As of MySQL 4.1.2, the `BINARY' attribute is shorthand for
      specifying the binary collation of the column character set.
      Before 4.1.2, `BINARY' attribute causes the column to be treated
      as a binary string.  In either case, sorting and comparisons
      become case sensitive.
 
      Starting from MySQL 5.0.3, `VARCHAR' is stored with a 1 byte or 2
      byte length prefix + data.  The length prefix is 1 byte if the
      storage size for the `VARCHAR' column is less than 256.
 
 `BINARY(M)'
      The `BINARY' type is similar to the `CHAR' type, but stores binary
      strings rather than non-binary strings.
 
      This type was added in MySQL 4.1.2.
 
 `VARBINARY(M)'
      The `VARBINARY' type is similar to the `VARCHAR' type, but stores
      binary strings rather than non-binary strings.
 
      This type was added in MySQL 4.1.2.
 
 `TINYBLOB'
 `TINYTEXT'
      A `BLOB' or `TEXT' column with a maximum length of 255 (2^8 - 1)
      characters.
 
 `BLOB'
 `TEXT'
      A `BLOB' or `TEXT' column with a maximum length of 65,535 (2^16 -1)
      characters.
 
 `MEDIUMBLOB'
 `MEDIUMTEXT'
      A `BLOB' or `TEXT' column with a maximum length of 16,777,215
      (2^24 - 1) characters.
 
 `LONGBLOB'
 `LONGTEXT'
      A `BLOB' or `TEXT' column with a maximum length of 4,294,967,295 or
      4GB (2^32 - 1) characters.  Up to MySQL 3.23, the client/server
      protocol and `MyISAM' tables had a limit of 16MB per communication
      packet / table row. From MySQL 4.0, the maximum allowed length of
      `LONGBLOB' or `LONGTEXT' columns depends on the configured maximum
      packet size in the client/server protocol and available memory.
 
 `ENUM('VALUE1','VALUE2',...)'
      An enumeration.  A string object that can have only one value,
      chosen from the list of values `'VALUE1'', `'VALUE2'', `...',
      `NULL' or the special `''' error value.  An `ENUM' column can have
      a maximum of 65,535 distinct values.  `ENUM' values are
      represented internally as integers.
 
 `SET('VALUE1','VALUE2',...)'
      A set.  A string object that can have zero or more values, each of
      which must be chosen from the list of values `'VALUE1'',
      `'VALUE2'', `...' A `SET' column can have a maximum of 64 members.
      `SET' values are represented internally as integers.
 
Info Catalog (mysql.info.gz) Date and time type overview (mysql.info.gz) Column type overview
automatically generated byinfo2html