MySQL Cookbook, 2nd Edition

Checking a String’s Character Set or Collation

Problem

You want to know the character set or collation of a string.

Solution

Use the CHARSET() or COLLATION() function.

Discussion

If you create a table using the following definition, you know that values stored in the column will have a character set of utf8 and a collation of utf8_danish_ci:

CREATE TABLE t (c CHAR(10) CHARACTER SET utf8 COLLATE utf8_danish_ci);

But sometimes it’s not so clear what character set or collation applies to a string. Server configuration affects literal strings and some string functions, and other string functions return values in a specific character set. Symptoms that you have the wrong character set or collation are that a collation-mismatch error occurs for a comparison operation, or a lettercase conversion doesn’t work properly. This section shows how to check what character set or collation a string has. Changing a String’s Character Set or Collation shows how to convert strings to a different character set or collation.

To find out what character set or collation a string has, use the CHARSET() or COLLATION() function. For example, did you know that the USER() function returns a Unicode string?

mysql>SELECT USER(), CHARSET(USER()), COLLATION(USER()); +------------------+-----------------+-------------------+ | USER() | CHARSET(USER()) | COLLATION(USER()) | +------------------+-----------------+-------------------+ | cbuser@localhost | utf8 | utf8_general_ci | +------------------+-----------------+-------------------+ ...