Working with Strings - MySQL - page 145

essary when stored. For BINARY , the pad value is 0x00 (the zero-valued byte, also known
as ASCII NUL). CHAR values are padded with spaces for storage and trailing spaces are
stripped upon retrieval.
For VARBINARY , VARCHAR , and the BLOB and TEXT types, MySQL stores values using only
as much storage as required, up to the maximum column length. No padding is added
or stripped when values are stored or retrieved.
To preserve trailing pad values that are present in the original strings that are stored,
use a data type for which no stripping occurs. For example, if you store character (non‐
binary) strings that might end with spaces, and want to preserve them, use VARCHAR or
one of the TEXT data types. The following statements illustrate the difference in trailing-
space handling for CHAR and VARCHAR columns:
mysql> CREATE TABLE t (c1 CHAR(10), c2 VARCHAR(10));
mysql> INSERT INTO t (c1,c2) VALUES('abc ','abc ');
mysql> SELECT c1, c2, CHAR_LENGTH(c1), CHAR_LENGTH(c2) FROM t;
+------+------------+-----------------+-----------------+
| c1 | c2 | CHAR_LENGTH(c1) | CHAR_LENGTH(c2) |
+------+------------+-----------------+-----------------+
| abc | abc | 3 | 10 |
+------+------------+-----------------+-----------------+
This shows that if you store a string that contains trailing spaces into a CHAR column,
they're removed when you retrieve the value.
A table can include a mix of binary and nonbinary string columns, and its nonbinary
columns can use different character sets and collations. When you declare a nonbinary
string column, use the CHARACTER SET and COLLATE attributes if you require a particular
character set and collation. For example, if you need to store utf8 (Unicode) and sjis
(Japanese) strings, you might define a table with two columns like this:
CREATE TABLE mytbl
(
utf8str VARCHAR ( 100 ) CHARACTER SET utf8 COLLATE utf8_danish_ci ,
sjisstr VARCHAR ( 100 ) CHARACTER SET sjis COLLATE sjis_japanese_ci
);
The CHARACTER SET and COLLATE clauses are each optional in a column definition:
• If you specify CHARACTER SET and omit COLLATE , the default collation for the char‐
acter set is used.
• If you specify COLLATE and omit CHARACTER SET , the character set implied by the
collation name (the first part of the name) is used. For example, utf8_danish_ci
and sjis_japanese_ci imply utf8 and sjis , respectively. This means that the
CHARACTER SET attributes could have been omitted from the preceding CREATE TABLE
statement.