Databases Reference
In-Depth Information
collations sort according to the byte values of the characters, whereas case-sensitive
collations might have complex sorting rules such as those regarding multiple characters
in languages like German.
When you specify a character set explicitly, you don't have to name both a character
set and a collation. If you omit one or both, MySQL fills in the missing pieces from the
applicable default.
Table 7-2
shows how MySQL decides which character set and col-
lation to use.
Table 7-2. How MySQL determines character set and collation defaults
If you specify
Resulting character set
Resulting collation
Both character set and collation
As specified
As specified
Character set only
As specified
Character set's default collation
Collation only
Character set to which collation belongs
As specified
Neither
Applicable default
Applicable default
The following commands show how to create a database, table, and column with ex-
plicitly specified character sets and collations:
CREATE DATABASE d CHARSET latin1;
CREATE TABLE d.t(
col1 CHAR(1),
col2 CHAR(1) CHARSET utf8,
col3 CHAR(1) COLLATE latin1_bin
) DEFAULT CHARSET=cp1251;
The resulting table's columns have the following collations:
mysql>
SHOW FULL COLUMNS FROM d.t;
+------+---------+-------------------+
|Field | Type | Collation |
+------+---------+-------------------+
|col1 | char(1) | cp1251_general_ci |
|col2 | char(1) | utf8_general_ci |
|col3 | char(1) | latin1_bin |
+------+---------+-------------------+
How Character Sets and Collations Affect Queries
Some character sets might require more CPU operations, consume more memory and
storage space, or even defeat indexing. Therefore, you should choose character sets and
collations carefully.
Converting between character sets or collations can add overhead for some operations.
For example, the
sakila.film
table has an index on the
title
column, which can speed
up
ORDER BY
queries:
