Collation Issues
783
Examples:
Comparison
Collation Used
column1 = 'A'
Use collation of
column1
column1 = 'A' COLLATE x
Use collation of
'A' COLLATE x
column1 COLLATE x = 'A' COLLATE y
Error
The
COERCIBILITY()
[958]
function can be used to determine the coercibility of a string
expression:
mysql>
SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);
-> 0
mysql>
SELECT COERCIBILITY(VERSION());
-> 3
mysql>
SELECT COERCIBILITY('A');
-> 4
See
Section 12.13, “Information Functions”
.
For implicit conversion of a numeric or temporal value to a string, such as occurs for the argument
1
in the expression
CONCAT(1, 'abc')
[887]
, the result is a binary string for which the character set
and collation are
binary
. See
Section 12.2, “Type Conversion in Expression Evaluation”
.
10.1.7.6. The
_bin
and
binary
Collations
This section describes how
_bin
collations for nonbinary strings differ from the
binary
“collation” for
binary strings.
Nonbinary strings (as stored in the
CHAR
,
VARCHAR
, and
TEXT
data types) have a character set and
collation. A given character set can have several collations, each of which defines a particular sorting
and comparison order for the characters in the set. One of these is the binary collation for the character
set, indicated by a
_bin
suffix in the collation name. For example,
latin1
and
utf8
have binary
collations named
latin1_bin
and
utf8_bin
.
Binary strings (as stored in the
BINARY
,
VARBINARY
, and
BLOB
data types) have no character set
or collation in the sense that nonbinary strings do. (Applied to a binary string, the
CHARSET()
and
COLLATION()
functions both return a value of
binary
.) Binary strings are sequences of bytes and the
numeric values of those bytes determine sort order.
The
_bin
collations differ from the
binary
collation in several respects.
The unit for sorting and comparison. Binary strings are sequences of bytes. Sorting and comparison
is always based on numeric byte values. Nonbinary strings are sequences of characters, which might
be multi-byte. Collations for nonbinary strings define an ordering of the character values for sorting
and comparison. For the
_bin
collation, this ordering is based solely on binary code values of the
characters (which is similar to ordering for binary strings except that a
_bin
collation must take into
account that a character might contain multiple bytes). For other collations, character ordering might
take additional factors such as lettercase into account.
Character set conversion. A nonbinary string has a character set and is converted to another
character set in many cases, even when the string has a
_bin
collation:
• When assigning column values from another column that has a different character set:
UPDATE t1 SET utf8_bin_column=latin1_column;
INSERT INTO t1 (latin1_column) SELECT utf8_bin_column FROM t2;
• When assigning column values for
INSERT
or
UPDATE
using a string literal:
SET NAMES latin1;
INSERT INTO t1 (utf8_bin_column) VALUES ('string-in-latin1');
• When sending results from the server to a client:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...