10.5.4. Some Special Cases Where the Collation Determination Is Tricky
In the great majority of statements, 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 is the collation of column
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
Standard SQL resolves such questions using what used to be
called “coercibility” rules. Basically, this means:
collations, so which collation takes precedence? This can be
difficult to resolve, but the following rules cover most
COLLATE clause has a
coercibility of 0. (Not coercible at all.)
The concatenation of two strings with different collations
has a coercibility of 1.
The collation of a column or a stored routine parameter or
local variable has a coercibility of 2.
A “system constant” (the string returned by
functions such as
VERSION()) has a coercibility of 3.
A literal's collation has a coercibility of 4.
NULL or an expression that is derived
NULL has a coercibility of 5.
The preceding coercibility values are current for MySQL
Those rules resolve ambiguities in the following manner:
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.
column1 = 'A'
|Use collation of
column1 = 'A' COLLATE x
|Use collation of
column1 COLLATE x = 'A' COLLATE y
COERCIBILITY() function can be used to
determine the coercibility of a string expression:
SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);
See Section 12.10.3, “Information Functions”.