MySQL String Data Types


From: NetworkWorld
http://www.networkworld.com/columnists/2007/012907drinternet.html

The differences in processing efficiency between MySQL's three character storage types should not have a big impact on processing times unless your data sets are extremely large. 

CHAR items, which are fixed length, are the fastest to store and retrieve but can waste storage space.

VARCHAR, a variable-length string, can be slower to store and retrieve but does not waste storage space.

TEXT is a character BLOB that requires more storage space and I/O than the other two.

Using all CHAR fields for text data technically is the fastest choice, but CHAR data items have a maximum length of 255 characters. The latest versions of MySQL allow VARCHAR items to be up to 65,535 characters long, which is the maximum length of a row. 

TEXT is appealing because, unlike with VARCHAR, you can put multiple full-size TEXT items in one row. To enable full-text indexing for the data, you will need to use the MyISAM table type for your database regardless of which variable types you choose for your data fields. 


----------------

From: MySQL
http://dev.mysql.com/doc/refman/5.1/en/string-types.html

The string types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in your queries. For string type storage requirements, see Section 10.5, "Data Type Storage Requirements". 
http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

Storage Requirements for String Types 
In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value. 

Data Type Storage Required 

CHAR(M) M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set
 
BINARY(M) M bytes, 0 <= M <= 255 

VARCHAR(M), VARBINARY(M)  L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes 

TINYBLOB, TINYTEXT  L + 1 bytes, where L < 28  

BLOB, TEXT  L + 2 bytes, where L < 216  

MEDIUMBLOB, MEDIUMTEXT  L + 3 bytes, where L < 224  

LONGBLOB, LONGTEXT  L + 4 bytes, where L < 232  

ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) 

SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum) 

Variable-length string types are stored using a length prefix plus data. The length prefix requires from one to four bytes depending on the data type, and the value of the prefix is L (the byte length of the string). For example, storage for a MEDIUMTEXT value requires L bytes to store the value plus three bytes to store the length of the value. 

To calculate the number of bytes used to store a particular CHAR, VARCHAR, or TEXT column value, you must take into account the character set used for that column and whether the value contains multi-byte characters. In particular, when using the utf8 Unicode character set, you must keep in mind that not all characters use the same number of bytes and can require up to three bytes per character. For a breakdown of the storage used for different categories of utf8 characters, see Section 9.1.10, "Unicode Support". 

VARCHAR, VARBINARY, and the BLOB and TEXT types are variable-length types. For each, the storage requirements depend on these factors: 
The actual length of the column value 
The column's maximum possible length 
The character set used for the column, because some character sets contain multi-byte characters 

For example, a VARCHAR(255) column can hold a string with a maximum length of 255 characters. Assuming that the column uses the latin1 character set (one byte per character), the actual storage required is the length of the string (L), plus one byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is five bytes. If the same column is instead declared to use the ucs2 double-byte character set, the storage requirement is 10 bytes: The length of 'abcd' is eight bytes and the column requires two bytes to store lengths because the maximum length is greater than 255 (up to 510 bytes). 

The effective maximum number of bytes that can be stored in a VARCHAR or VARBINARY column is subject to the maximum row size of 65,535 bytes, which is shared among all columns. For a VARCHAR column that stores multi-byte characters, the effective maximum number of characters is less. For example, utf8 characters can require up to three bytes per character, so a VARCHAR column that uses the utf8 character set can be declared to be a maximum of 21,844 characters. See Section E.7.4, "Table Column-Count and Row-Size Limits". 

The NDBCLUSTER storage engine in MySQL 5.1 supports variable-width columns. This means that a VARCHAR column in a MySQL Cluster table requires the same amount of storage as it would using any other storage engine, with the exception that such values are 4-byte aligned. Thus, the string 'abcd' stored in a VARCHAR(50) column using the latin1 character set requires 8 bytes (rather than 6 bytes for the same column value in a MyISAM table). This represents a change in behavior from earlier versions of NDBCLUSTER, where a VARCHAR(50) column would require 52 bytes storage per record regardless of the length of the string being stored. 

TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 – (size – 256) % 2000). 

The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations having between 256 and 65,535 possible values. See Section 10.4.4, "The ENUM Type". 

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4