MySQL has many operators and functions that return a string. This section answers the question: What is the character set and collation of such a string?
For simple functions that take string input and return a
string result as output, the output's character set and
collation are the same as those of the principal input value.
For example,
UPPER(
returns a string whose character string and collation are the
same as that of X)X. The same applies
for INSTR(),
LCASE(),
LOWER(),
LTRIM(),
MID(),
REPEAT(),
REPLACE(),
REVERSE(),
RIGHT(),
RPAD(),
RTRIM(),
SOUNDEX(),
SUBSTRING(),
TRIM(),
UCASE(), and
UPPER().
Note: The REPLACE() function,
unlike all other functions, always ignores the collation of
the string input and performs a case-sensitive comparison.
If a string input or function result is a binary string, the
string has no character set or collation. This can be checked
by using the CHARSET() and
COLLATION() functions, both of
which return binary to indicate that their
argument is a binary string:
mysql> SELECT CHARSET(BINARY 'a'), COLLATION(BINARY 'a');
+---------------------+-----------------------+
| CHARSET(BINARY 'a') | COLLATION(BINARY 'a') |
+---------------------+-----------------------+
| binary | binary |
+---------------------+-----------------------+
For operations that combine multiple string inputs and return a single string output, the “aggregation rules” of standard SQL apply for determining the collation of the result:
If an explicit
COLLATEoccurs, useXX.If explicit
COLLATEandXCOLLATEoccur, raise an error.YOtherwise, if all collations are
X, useX.Otherwise, the result has no collation.
For example, with CASE ... WHEN a THEN b WHEN b THEN
c COLLATE , the
resulting collation is X ENDX. The same
applies for UNION,
||,
CONCAT(),
ELT(),
GREATEST(),
IF(), and
LEAST().
For operations that convert to character data, the character
set and collation of the strings that result from the
operations are defined by the
character_set_connection and
collation_connection system
variables. This applies only to
CAST(),
CONV(),
FORMAT(),
HEX(), and
SPACE().
If you are uncertain about the character set or collation of
the result returned by a string function, you can use the
CHARSET() or
COLLATION() function to find
out:
mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());
+----------------+-----------------+-------------------+
| USER() | CHARSET(USER()) | COLLATION(USER()) |
+----------------+-----------------+-------------------+
| test@localhost | utf8 | utf8_general_ci |
+----------------+-----------------+-------------------+