(mysql.info.gz) Charset-collate-tricky
Info Catalog
(mysql.info.gz) Charset-binary-op
(mysql.info.gz) Charset-defaults
(mysql.info.gz) Charset-collation-charset
10.3.11 Some Special Cases Where the Collation Determination Is Tricky
----------------------------------------------------------------------
In the great majority of queries, it is obvious what collation MySQL
uses to resolve a comparison operation. For example, in the following
cases, it should be clear that the collation will be "the column
collation of column `x'":
SELECT x FROM T ORDER BY x;
SELECT x FROM T WHERE x = x;
SELECT DISTINCT x FROM T;
However, when multiple operands are involved, there can be ambiguity.
For example:
SELECT x FROM T WHERE x = 'Y';
Should this query use the collation of the column `x', or of the string
literal `'Y''?
Standard SQL resolves such questions using what used to be called
"coercibility" rules. The essence is: Because `x' and `'Y'' both have
collations, whose collation takes precedence? It's complex, but the
following rules take care of most situations:
* An explicit `COLLATE' clause has a coercibility of 0. (Not
coercible at all.)
* A concatenation of two strings with different collations has a
coercibility of 1.
* A column's collation has a coercibility of 2.
* A literal's collation has a coercibility of 3.
Those rules resolve ambiguities thus:
* Use the collation with the lowest coercibility value.
* If both sides have the same coercibility, then it is an error if
the collations aren't the same.
Examples:
`column1 = 'A'' Use collation of `column1'
`column1 = 'A' COLLATE x' Use collation of `'A''
`column1 COLLATE x = 'A' COLLATE y' Error
The `COERCIBILITY()' function can be used to determine the coercibility
of a string expression:
mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);
-> 0
mysql> SELECT COERCIBILITY('A');
-> 3
Information functions.
Info Catalog
(mysql.info.gz) Charset-binary-op
(mysql.info.gz) Charset-defaults
(mysql.info.gz) Charset-collation-charset
automatically generated byinfo2html