Movatterモバイル変換

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(X) returns a string with the same character string and collation asX. The same applies forINSTR(),LCASE(),LOWER(),LTRIM(),MID(),REPEAT(),REPLACE(),REVERSE(),RIGHT(),RPAD(),RTRIM(),SOUNDEX(),SUBSTRING(),TRIM(),UCASE(), andUPPER().

If a string input or function result is a binary string, the string has thebinary character set and collation. This can be checked by using theCHARSET() andCOLLATION() functions, both of which returnbinary for a binary string argument:

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:

For example, withCASE ... WHEN a THEN b WHEN b THEN c COLLATEX END, the resulting collation isX. The same applies forUNION,||,CONCAT(),ELT(),GREATEST(),IF(), andLEAST().

For operations that convert to character data, the character set and collation of the strings that result from the operations are defined by thecharacter_set_connection andcollation_connection system variables that determine the default connection character set and collation (seeSection 10.4, “Connection Character Sets and Collations”). This applies only toCAST(),CONV(),FORMAT(),HEX(), andSPACE().

As of MySQL 5.7.19, an exception to the preceding principle occurs for expressions for virtual generated columns. In such expressions, the table character set is used forCONV() orHEX() results, regardless of connection character set.

If there is any question about the character set or collation of the result returned by a string function, use theCHARSET() orCOLLATION() function to find out:

mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());+----------------+-----------------+-------------------+| USER()         | CHARSET(USER()) | COLLATION(USER()) |+----------------+-----------------+-------------------+| test@localhost | utf8            | utf8_general_ci   |+----------------+-----------------+-------------------+mysql> SELECT CHARSET(COMPRESS('abc')), COLLATION(COMPRESS('abc'));+--------------------------+----------------------------+| CHARSET(COMPRESS('abc')) | COLLATION(COMPRESS('abc')) |+--------------------------+----------------------------+| binary                   | binary                     |+--------------------------+----------------------------+