Database Reference
In-Depth Information
Discussion
For a table created as follows, you know that values stored in the column
c
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.
To determine a string's character set or collation, 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 |
+------------------+-----------------+-------------------+
String values that take their character set and collation from the current configuration
may change properties if the configuration changes. This is true for literal strings:
mysql>
SET NAMES 'latin1';
mysql>
SELECT CHARSET('abc'), COLLATION('abc');
+----------------+-------------------+
| CHARSET('abc') | COLLATION('abc') |
+----------------+-------------------+
| latin1 | latin1_swedish_ci |
+----------------+-------------------+
mysql>
SET NAMES utf8 COLLATE 'utf8_bin';
mysql>
SELECT CHARSET('abc'), COLLATION('abc');
+----------------+------------------+
| CHARSET('abc') | COLLATION('abc') |
+----------------+------------------+
| utf8 | utf8_bin |
+----------------+------------------+
For a binary string, the
CHARSET()
or
COLLATION()
functions return a value of
binary
,
which means that the string is compared and sorted based on numeric byte values, not
character collation values.
To convert a string from one character set to another, use the
CONVERT()
function:
