The repertoire of a character set is the collection of characters in the set.
As of MySQL 5.1.21, 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:
CREATE TABLE t1 ( c1 CHAR(1) CHARACTER SET latin1, c2 CHAR(1) CHARACTER SET ascii ); INSERT INTO t1 VALUES ('a','b'); SELECT CONCAT(c1,c2) FROM t1;
Without repertoire, this error occurs:
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (ascii_general_ci,IMPLICIT) for operation 'concat'
Using repertoire, subset to superset
(ascii
to latin1
)
conversion can occur and a result is returned:
+---------------+ | CONCAT(c1,c2) | +---------------+ | ab | +---------------+
Functions with one string argument inherit the repertoire of
their argument. The result of
UPPER(_utf8'
has abc
')ASCII
repertoire, because its
argument has ASCII
repertoire.
For functions that return a string but do not have string
arguments and use
character_set_connection
as
the result character set, the result repertoire is
ASCII
if
character_set_connection
is
ascii
, and UNICODE
otherwise:
FORMAT(numeric_column
, 4);
Use of repertoire changes how MySQL evaluates the following example:
SET NAMES ascii; CREATE TABLE t1 (a INT, b VARCHAR(10) CHARACTER SET latin1); INSERT INTO t1 VALUES (1,'b'); SELECT CONCAT(FORMAT(a, 4), b) FROM t1;
Without repertoire, this error occurs:
ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat'
With repertoire, a result is returned:
+-------------------------+ | CONCAT(FORMAT(a, 4), b) | +-------------------------+ | 1.0000b | +-------------------------+
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.
User Comments
Add your own comment.