MySQL 字符集：排序规则问题

来自Wikioe
跳到导航 跳到搜索


在 SQL 语句中使用“COLLATE”

使用“COLLATE”子句，您可以覆盖默认的默认排序规则以进行比较。“COLLATE”可用于 SQL 语句的各个部分。这里有些例子：

  1. 使用“ORDER BY”：【对中文排序呢？？？拼音升降序使用什么排序规则？？？】 
    SELECT k
FROM t1
ORDER BY k COLLATE latin1_german2_ci;'''
  2. 使用“AS”： 
    SELECT k COLLATE latin1_german2_ci AS k1
FROM t1
ORDER BY k1;
  3. 使用“GROUP BY”： 
    SELECT k
FROM t1
GROUP BY k COLLATE latin1_german2_ci;
  4. 具有聚合功能： 
    SELECT MAX(k COLLATE latin1_german2_ci)
FROM t1;
  5. 使用“DISTINCT”： 
    SELECT DISTINCT k COLLATE latin1_german2_ci
FROM t1;
  6. 使用“WHERE”： 
    SELECT *
     FROM t1
     WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k;

    SELECT *
     FROM t1
     WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;
  7. 使用“HAVING”： 
    SELECT k
FROM t1
GROUP BY k
HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

“COLLATE”子句优先级

COLLATE子句具有较高的优先级（高于“||”），因此以下两个表达式是等效的： 

x || y COLLATE z
x || (y COLLATE z)

【？？？有啥用】

字符集和排序规则兼容性

每个字符集都有一个或多个排序规则，但是每个排序规则都与一个且只有一个字符集相关联。因此，以下语句会导致错误消息，因为 latin2_bin 排序规则与 latin1 字符集不合法： 

mysql> SELECT _latin1 'x' COLLATE latin2_bin;
ERROR 1253 (42000): COLLATION 'latin2_bin' is not valid
for CHARACTER SET 'latin1'

【废话……】

表达式中的排序规则强制性

在绝大多数语句中，MySQL 使用什么排序规则是很明显的。例如，在以下情况下，应该清楚排序规则是 x 列的排序规则： 

SELECT x FROM T ORDER BY x;
SELECT x FROM T WHERE x = x;
SELECT DISTINCT x FROM T;

但是，对于多个操作数，可能会有歧义。例如，此语句在列 x 和字符串常量 'Y' 之间进行比较： 

SELECT x FROM T WHERE x = 'Y';

如果x和'Y'具有相同的排序规则，则对于用于比较的排序规则没有歧义。但是，如果它们具有不同的排序规则，则比较应使用 x 还是 'Y' 的排序规则？ x 和 'Y' 都有排序规则，那么哪个排序规则优先？


为了解决这些问题，MySQL 检查是否可以将一项的排序规则强制为另一项的排序规则。 MySQL 分配强制性值如下：

  1. 显式的“COLLATE”子句的强制性为 0(根本不可强制)。
  2. 具有不同排序规则的两个字符串的串联的强制性为 1.
  3. 列或存储的例程参数或局部变量的排序规则的强制性为 2.
  4. “系统常数”(由“USER()”或“VERSION()”之类的函数返回的字符串)的强制性为 3.
  5. 常量的排序规则的强制性为 4.
  6. 数字或时间值的排序规则的强制性为 5.
  7. NULL 或 从NULL派生的表达式的强制性为 6.

MySQL 使用强制性值和以下规则来解决歧义：

  1. 使用具有最低强制性的排序规则。
  2. 如果双方具有相同的强制性，则：
    1. 如果双方都是 Unicode，或者双方都不是 Unicode，则错误。
    2. 如果其中一方具有 Unicode 字符集，而另一方具有非 Unicode 字符集，则具有 Unicode 字符集的一方获胜，并且自动字符集转换将应用于非 Unicode 一方。例如，以下语句不返回错误： 
      SELECT CONCAT(utf8_column, latin1_column) FROM t1;
      它返回一个字符集为 utf8 且排序规则与 utf8_column 相同的结果。串联之前，latin1_column 的值会自动转换为utf8。
  3. 对于具有相同字符集但混合了“_bin”排序规则和“_ci”或“_cs”排序规则的操作数的操作，将使用“_bin”排序规则。这类似于将非二进制和二进制字符串混合在一起的操作如何将操作数评估为二进制字符串(应用于排序规则而不是数据类型)。


示例：

Comparison Collation Used
column1 = 'A' 使用排序规则 column1
column1 = 'A' COLLATE x 使用排序规则 'A' COLLATE x
column1 COLLATE x = 'A' COLLATE y Error


要确定字符串表达式的可强制性，请使用“COERCIBILITY()”函数： 

mysql> SELECT COERCIBILITY(_utf8'A' COLLATE utf8_bin);
        -> 0
mysql> SELECT COERCIBILITY(VERSION());
        -> 3
mysql> SELECT COERCIBILITY('A');
        -> 4
mysql> SELECT COERCIBILITY(1000);
        -> 5
mysql> SELECT COERCIBILITY(NULL);
        -> 6


  • 对于将数值或时间值隐式转换为字符串(例如在表达式“CONCAT(1, 'abc')”中的自变量1发生的情况)，结果是一个字符(非二进制)字符串，该字符串具有由“character_set_connection”和“collation_connection”系统变量确定的字符集和排序规则。

“binary排序规则”与“_bin排序规则”【？？？】

二进制字符串（使用“BINARY”，“VARBINARY”和“BLOB”数据类型存储）具有字符集和排序规则“binary”。二进制字符串是字节序列，这些字节的数字值确定比较和排序顺序。


非二进制字符串（使用“CHAR”，“VARCHAR”和“TEXT”数据类型存储）具有除“binary”以外的字符集和排序规则。给定的非二进制字符集可以具有多个排序规则，每个排序规则定义该字符集中的字符的特定比较和排序 顺序。其中一种是二进制排序规则，由排序规则名称中的“_bin”后缀表示。

例如，utf8 和 latin1 的二进制排序规则分别命名为 utf8_bin 和 latin1_bin。


“binary排序规则”与“_bin排序规则”在以下几个方面有所不同：

比较和排序单位

二进制字符串是字节序列。对于“binary排序规则”，比较和排序基于数字字节值。

非二进制字符串是字符序列，可能是多字节。非二进制字符串的排序规则定义了用于比较和排序的字符值的顺序。对于“_bin排序规则”，此排序基于数字字符代码值。【这类似于二进制字符串的排序，除了字符代码值可能是多字节。】

字符集转换

对于二进制字符串列，不会进行任何转换。对于与前面类似的情况，字符串值将按字节复制。


非二进制字符串具有一个字符集，即使在字符串具有“_bin排序规则”的情况下，在许多情况下，它也会自动转换为另一个字符集：

  1. 将列值分配给具有不同字符集的另一列时：
UPDATE t1 SET utf8_bin_column=latin1_column;
INSERT INTO t1 (latin1_column) SELECT utf8_bin_column FROM t2;
  1. 使用字符串常量为“INSERT”或“UPDATE”分配列值时： 
    SET NAMES latin1;
INSERT INTO t1 (utf8_bin_column) VALUES ('string-in-latin1');
  2. 将结果从服务器发送到 Client 端时： 
    SET NAMES latin1;
SELECT utf8_bin_column FROM t2;

字母大小写转换

大小写的概念不适用于二进制字符串中的字节。要执行字母大小写转换，必须首先使用适合于存储在字符串中的数据的字符集将字符串转换为非二进制字符串： 

mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));
+-------------+------------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |
+-------------+------------------------------------+
| aA          | aa                                 |
+-------------+------------------------------------+


非二进制字符集的排序规则提供有关字符的字母大小写的信息，因此即使对于忽略字母大小写排序的“_bin排序规则”，非二进制字符串中的字符也可以从一个字母大小写转换为另一个字母大写： 

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+

比较中的尾随空格处理

对于二进制字符串，所有字节在比较中都是有效的，包括尾随空格： 

mysql> SET NAMES binary;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+


非二进制字符串的所有排序规则（包括“_bin排序规则”）都具有“PAD SPACE”行为。在比较中，尾随空格无关紧要： 

mysql> SET NAMES utf8 COLLATE utf8_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+

插入和检索的尾随空格处理

“BINARY(N)”列存储二进制字符串“N”字节长。对于插入，小于“N”字节的值将扩展为0x00字节。对于检索，不会删除任何内容。始终返回声明长度的值。 

mysql> CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
mysql> INSERT INTO t1 VALUES ('x','x');
mysql> INSERT INTO t1 VALUES ('x ','x ');
mysql> SELECT a, b, HEX(a), HEX(b) FROM t1;
+------+------------+--------+----------------------+
| a    | b          | HEX(a) | HEX(b)               |
+------+------------+--------+----------------------+
| x    | x          | 78     | 78000000000000000000 |
| x    | x          | 78     | 78200000000000000000 |
+------+------------+--------+----------------------+


“CHAR(N)”列存储非二进制字符串“N”个字符长。对于插入，短于“N”字符的值以空格扩展。对于检索，删除了尾随空格。

在“INFORMATION_SCHEMA”搜索中使用排序规则

取自“http://wiki.eijux.com/index.php?title=MySQL_字符集：排序规则问题&oldid=2181”