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 12.4, “Connection Character Sets and Collations”). This applies only toBIN_TO_UUID(),CAST(),CONV(),FORMAT(),HEX(), andSPACE().

An exception to the preceding principle occurs for expressions for virtual generated columns. In such expressions, the table character set is used forBIN_TO_UUID(),CONV(), 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 | utf8mb3         | utf8mb3_general_ci |+----------------+-----------------+--------------------+mysql> SELECT CHARSET(COMPRESS('abc')), COLLATION(COMPRESS('abc'));+--------------------------+----------------------------+| CHARSET(COMPRESS('abc')) | COLLATION(COMPRESS('abc')) |+--------------------------+----------------------------+| binary                   | binary                     |+--------------------------+----------------------------+