High Performance MySQL: Optimization, Backups, Replication, and More
The MySQL string types are commonly used to store text, but are general-purpose types and may hold arbitrary data. Types are available to hold values of varying maximum lengths and can be chosen according to whether or not you want values to be treated in case sensitive fashion. As of MySQL 4.1, you can specify character sets on a column-specific basis for the CHAR, VARCHAR, and TEXT types. The syntax is CHARACTER SET charset, where charset is a character set identifier such as latin1, greek, or utf8. The allowable character sets supported by the server can be determined by issuing a SHOW CHARACTER SET statement. Note that specifying a character set for CHAR or VARCHAR columns precludes use of the BINARY attribute that normally is allowable for those types. CHAR[(M)]
Meaning: A fixed-length character string 0 to M bytes long. M should be an integer from 0 to 255 prior (1 to 255 prior to MySQL 3.23). If M is omitted, it defaults to 1. Strings longer than M characters are chopped to length M when stored. Strings shorter than M characters are right-padded with spaces when stored. Trailing spaces are removed when values are retrieved. Allowable attributes: BINARY, CHARACTER SET (as of MySQL 4.1). Allowable length: 0 to M bytes. Default value: NULL if column can be NULL, '' (empty string) if NOT NULL. Storage required: M bytes. Comparisons: Not case sensitive, unless the BINARY attribute is specified. Synonyms: CHAR with no argument is a synonym for CHAR(1). BINARY(M) is a synonym for CHAR(M) BINARY. As of MySQL 3.23.5, NCHAR(M) and NATIONAL CHAR(M) are synonyms for CHAR(M). VARCHAR(M)
Meaning: A variable-length character string 0 to M bytes long. M should be an integer from 0 to 255 prior (1 to 255 prior to MySQL 4.0.2). Strings longer than M characters are chopped to length M when stored. Trailing spaces are removed from values when stored. (Trailing space removal is scheduled to become optional in MySQL 4.1, but that has not been implemented as of this writing.) Allowable attributes: BINARY, CHARACTER SET (as of MySQL 4.1). Allowable length: 0 to M bytes. Default value: NULL if column can be NULL, '' (empty string) if NOT NULL. Storage required: Length of value, plus 1 byte to record the length. Comparisons: Not case sensitive, unless the BINARY attribute is specified. Synonyms: CHAR VARYING(M). As of MySQL 3.23.5, NCHAR VARYING(M) and NATIONAL CHAR VARYING(M) are synonyms for VARCHAR(M). TINYBLOB
Meaning: A small BLOB value Allowable attributes: None, other than the global attributes Allowable length: 0 to 255 (0 to 28 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 1 byte to record the length Comparisons: Case sensitive BLOB
Meaning: A normal-sized BLOB value Allowable attributes: None, other than the global attributes Allowable length: 0 to 65535 (0 to 216 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 2 bytes to record the length Comparisons: Case sensitive MEDIUMBLOB
Meaning: A medium-sized BLOB value Allowable attributes: None, other than the global attributes Allowable length: 0 to 16777215 (0 to 224 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 3 bytes to record the length Comparisons: Case sensitive Synonyms: LONG VARBINARY LONGBLOB
Meaning: A large BLOB value Allowable attributes: None, other than the global attributes Allowable length: 0 to 4294967295 (0 to 232 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 4 bytes to record the length Comparisons: Case sensitive TINYTEXT
Meaning: A small TEXT value Allowable attributes: CHARACTER SET (as of MySQL 4.1) Allowable length: 0 to 255 (0 to 28 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 1 byte to record the length Comparisons: Not case sensitive TEXT
Meaning: A normal-sized TEXT value Allowable attributes: CHARACTER SET (as of MySQL 4.1) Allowable length: 0 to 65535 (0 to 216 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 2 bytes to record the length Comparisons: Not case sensitive MEDIUMTEXT
Meaning: A medium-sized TEXT value Allowable attributes: CHARACTER SET (as of MySQL 4.1) Allowable length: 0 to 16777215 (0 to 224 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 3 bytes to record the length Comparisons: Not case sensitive Synonyms: LONG VARCHAR LONGTEXT
Meaning: A large TEXT value Allowable attributes: CHARACTER SET (as of MySQL 4.1) Allowable length: 0 to 4294967295 (0 to 232 1) bytes Default value: NULL if column can be NULL, '' (empty string) if NOT NULL Storage required: Length of value plus 4 bytes to record the length Comparisons: Not case sensitive ENUM('value1','value2',...)
Meaning: An enumeration; column values may be assigned exactly one member of the value list Allowable attributes: None, other than the global attributes listed in the chapter introduction Default value: NULL if column can be NULL, first enumeration value if NOT NULL Storage required: 1 byte for enumerations with 1 to 255 members, 2 bytes for enumerations with 256 to 65535 members Comparisons: Not case sensitive (case sensitive prior to MySQL 3.22.1) SET('value1','value2',...)
Meaning: A set; column values may be assigned zero or more members of the value list Allowable attributes: None, other than the global attributes listed in the chapter introduction Default value: NULL if column can be NULL, '' (empty set) if NOT NULL Storage required: 1 byte (for sets with 1 to 8 members), 2 bytes (9 to 16 members), 3 bytes (17 to 24 members), 4 bytes (25 to 32 members), or 8 bytes (33 to 64 members) Comparisons: Not case sensitive (case sensitive prior to MySQL 3.22.1) |