A.5. String Datatypes

These datatypes are case-sensitive. So, lowercase and uppercase letters remain unchanged when stored or retrieved. For a few of the string datatypes, a maximum column width may be specified. If a string is entered in a column that exceeds the width set for the column, the string will be right-truncated when stored. The CHAR datatype is a fixed-width column. Columns are right-padded with spaces when stored. The VARCHAR datatype adjusts its width and does not pad the strings stored. Any trailing spaces contained in a string that is stored are removed.

As of Version 4.1 of MySQL, the ASCII attribute may be specified for use with the CHAR datatype. This will set the column to the latin1 character set. As of Version 4.1 of MySQL, the UNICODE attribute may be specified for use with the CHAR datatype. This will set the column to the ucs2 character set.

The BINARY and VARBINARY datatypes store data as binary strings and not character strings like CHAR. Table A-4 lists the various types and their maximum sizes. Some types are listed together: BLOB (Binary Large Object) and TEXT. They have the same maximum value, but TEXT-type columns are handled based upon their character set, whereas BLOB types are not; they're handled as binary strings. This becomes a factor in sorting and comparing data. Binary strings are case-sensitive. As of Version 4.1 of MySQl, you can assign a character set to a TEXT type of column. Values are sorted based on the collation of the character set for the column if one is assigned, or, if not, on the server's character set.

The datatype of BINARY replaces CHAR BINARY. The datatype of VARBINARY replaces VARCHAR BINARY. Before Version 4.1.2 of MySQL, adding the BINARY flag after CHAR or VARCHAR instructed MySQL to treat the values as byte strings for sorting and comparisons. If a BINARY column is used in an expression, all elements of the expression are treated as a binary.

Table A-4. Maximum sizes od MySQL datatypes

Datatype	Maximum size
CHAR(width) [BINARY|ASCII|UNICODE]	255 characters
VARCHAR(width) [BINARY]	255 characters
BINARY(width)	255 characters
VARBINARY(width)	255 characters
TINYBLOB, TINYTEXT	255 bytes
BLOB, TEXT	65535 bytes
MEDIUMBLOB, MEDIUMTEXT	16777215 bytes
LONGBLOB, LONGTEXT	4294967295 bytes
ENUM('value', ...)	65535 elements
SET('value', ...)	64 elements

An ENUM column is one in which all possible choices are enumerated (e.g., ENUM('yes', 'no', 'maybe')). It's possible for it to contain a blank value (i.e., ") and NULL. If an ENUM column is set up to allow NULL values, NULL is allowed and will be the default value. If an ENUM column is set up with NOT NULL, NULL isn't allowed and the default value becomes the first element given. MySQL stores a numeric index of the enumerated values in the column: 1 being the first value. The values can be retrieved when the column is used in a numeric context (e.g., SELECT col1 + 0 FROM table1;). The reverse may be performed when entering data into a column (e.g., UPDATE table1 SET col1 = 3;) to set the value to the third element. The column values are sorted in ascending order based on the numeric index, not their corresponding enumerated values. The SET datatype is similar to ENUM, except that a SET column can hold multiple values (e.g., UPDATE table1 SET col1 = 'a, b';). For the SET datatype, values may be filtered with the FIND_IN_SET( ) function.