10.1.8 String Repertoire

The repertoire of a character set is the
collection of characters in the set.

String expressions have a repertoire attribute, which can have
two values:

ASCII: The expression can contain only
characters in the Unicode range U+0000 to
U+007F.

UNICODE: The expression can contain
characters in the Unicode range U+0000 to
U+FFFF.

The ASCII range is a subset of
UNICODE range, so a string with
ASCII repertoire can be converted safely
without loss of information to the character set of any string
with UNICODE repertoire or to a character set
that is a superset of ASCII. (All MySQL
character sets are supersets of ASCII with
the exception of swe7, which reuses some
punctuation characters for Swedish accented characters.) The use
of repertoire enables character set conversion in expressions
for many cases where MySQL would otherwise return an
“illegal mix of collations” error.

The following discussion provides examples of expressions and
their repertoires, and describes how the use of repertoire
changes string expression evaluation:

The repertoire for string constants depends on string
content:

SET NAMES utf8; SELECT 'abc';
SELECT _utf8'def';
SELECT N'MySQL';

Although the character set is utf8 in
each of the preceding cases, the strings do not actually
contain any characters outside the ASCII range, so their
repertoire is ASCII rather than
UNICODE.

Columns having the ascii character set
have ASCII repertoire because of their
character set. In the following table, c1
has ASCII repertoire:

CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET ascii);

The following example illustrates how repertoire enables a
result to be determined in a case where an error occurs
without repertoire:

Functions with two or more string arguments use the
“widest” argument repertoire for the result
repertoire (UNICODE is wider than
ASCII). Consider the following
CONCAT() calls:

CONCAT(_ucs2 0x0041, _ucs2 0x0042)
CONCAT(_ucs2 0x0041, _ucs2 0x00C2)

For the first call, the repertoire is
ASCII because both arguments are within
the range of the ascii character set. For
the second call, the repertoire is
UNICODE because the second argument is
outside the ascii character set range.

The repertoire for function return values is determined
based only on the repertoire of the arguments that affect
the result's character set and collation.

IF(column1 < column2, 'smaller', 'greater')

The result repertoire is ASCII because
the two string arguments (the second argument and the third
argument) both have ASCII repertoire. The
first argument does not matter for the result repertoire,
even if the expression uses string values.