主页 / dba / 问题 / 239539
Accepted
GWR
Asked: 2019-06-01 02:11:01 +0800 CST

Varchar 上的 MySQL 区分大小写连接

  • 772

我有两个表，它们都具有相同的数据类型、字符集和明确指定的排序规则。

CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL

对于字段上区分大小写的联接CLERK，我是否还需要在联接子句中指定排序规则，或者它在 DDL 级别指定的事实是否意味着联接中不需要它？

FROM
    CUSTOMER S JOIN 
    CLERK C ON S.CLERK = C.CLERK COLLATE latin1_bin
mysql mysql-5.7

1 个回答

  1. Best Answer

    对于字段上区分大小写的联接CLERK，我是否还需要在联接子句中指定排序规则，或者它在 DDL 级别指定的事实是否意味着联接中不需要它？

    简单的回答：无需在查询中指定排序规则。将使用 DDL 级别的排序规则（尤其是当两边都相同并且两边都是列时）（这就是我们首先在 DDL 级别指定它的原因）。

    详细答案：在给定操作（连接、谓词等）中使用的排序规则有一个优先级层次结构。使用的排序规则不仅取决于它是列还是文字等，还取决于它是 Unicode 还是非 Unicode，甚至是二进制还是非二进制。完整的描述可以在这里找到，Collat​​ion Coercibility in Expressions，这是 MySQL 5.7 文档，因为您使用的是该版本。一个相当有趣的规则是：

    对于操作数来自相同字符集但混合了_bin排序规则和_ci排序_cs规则的操作，使用_bin排序规则。这类似于混合非二进制和二进制字符串的操作如何将操作数评估为二进制字符串，除了它用于排序规则而不是数据类型。

    综上所述，我需要指出“二进制排序规则区分大小写”的说法虽然非常普遍，但绝对是不正确的。

    1. 在一个非常基本的层面上，排序是不同的。区分大小写的排序规则会将“a”与“A”排序（尽管哪个先出现可能取决于文化）、“b”与“B”等等。二进制排序规则将根据每个字符的基础值/代码点进行排序，当一个字符的大写和小写版本根据它们的值被其他字符分隔时，这一点变得非常明显。

    2. 大小写“敏感”意味着您也可以对字符的其他属性“不敏感”，主要是重音。这也是非 Unicode 字符集的唯一其他属性，但 Unicode 允许 Kana 类型敏感度、宽度敏感度和 SQL Server（截至 2017 版）甚至允许变体选择器敏感度。二进制排序规则不允许一个字符等于它自身以外的任何东西，即使它存在其他形式。同样，这在非 Unicode 字符集中并不会真正发生，但在 Unicode 中，一个字符可以有多个版本，包括宽、上标、下标、斜体、颠倒（称为“转身”）等. MySQL，从 8.0 版开始（据我所知），ut8mb4字符集和排序规则）：

    为了说明这两点，我在很棒的db<>fiddle上设置了一个演示。我不得不使用 MySQL 8.0 不仅获得_ai_ci排序规则，而且因为COLLATE latin1_general_ci子句（倒数第二个查询，#5）没有影响（出于某种奇怪的原因；文档指出，当排序规则名称只有_ci, then_ai是隐含的，然而对于db<>fiddle上的 MySQL 5.6 和 5.7，行为仍然是_as_csor _bin)。

    并且，甚至还有其他方式使二进制排序规则不“区分大小写”。他们无法解释：

    • 结合变音符号
    • 扩展
    • 宫缩

    我之前没有列出或提供这些示例，因为它们不属于 8 位编码，latin1而是 8 位编码。这些是 Unicode 的特性，因此应该适用于任何 Unicode 排序规则（虽然我没有在 MySQL 上测试它们，但它们在 SQL Server 中正确实现）。

    PS 我在下面的帖子中详细解释了所有这些（包括上面直接提到的 Unicode 特定行为）：不，二进制排序规则不区分大小写。这目前仅在 SQL Server 的上下文中构建，但我可以在我有时间时为这个答案提出的示例中工作。重要的是这个概念在 RDBMS 中是相同的。

    作为参考（以防db<>fiddle无法访问），查询是：

    查询 1

    CREATE TABLE CLERK (
  CLERK_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL
);

CREATE TABLE CUSTOMER (
  CUSTOMER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(10) NOT NULL,
  CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL,
  CLERK_CI_AI CHAR(3) CHARACTER SET latin1 COLLATE latin1_general_ci NULL
);

INSERT INTO CUSTOMER (NAME, CLERK, CLERK_CI_AI) VALUES('John Doe', 'Ü', 'Ü');

INSERT INTO CLERK (CLERK) VALUES('Ü');
INSERT INTO CLERK (CLERK) VALUES('ü');
INSERT INTO CLERK (CLERK) VALUES('U');
INSERT INTO CLERK (CLERK) VALUES('u');
INSERT INTO CLERK (CLERK) VALUES('Ù');
INSERT INTO CLERK (CLERK) VALUES('ù');
INSERT INTO CLERK (CLERK) VALUES('Û');
INSERT INTO CLERK (CLERK) VALUES('û');

INSERT INTO CLERK (CLERK) VALUES('Y');
INSERT INTO CLERK (CLERK) VALUES('y');
INSERT INTO CLERK (CLERK) VALUES('Ý');
INSERT INTO CLERK (CLERK) VALUES('ý');

    查询 2

    SELECT 0 AS "ver", C.*
FROM   CLERK C;

    查询 3

    SELECT "bin sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK;

    查询 4

    SELECT "cs sort" AS "ver", C.*
FROM   CLERK C
ORDER BY C.CLERK COLLATE latin1_general_cs;

    查询 5

    SELECT 1 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK;

    查询 6

    SELECT 2 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK = C.CLERK COLLATE latin1_bin;

    查询 7

    SELECT 3 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK;

    查询 8

    SELECT 4 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_bin;

    查询 9

    # is accent-sensitive even though documentation states it should be
# accent-INsensitive
# 
SELECT 5 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON S.CLERK_CI_AI = C.CLERK COLLATE latin1_general_ci;

    查询 10

    SELECT 6 AS "ver", S.*, C.*
FROM CUSTOMER S
JOIN CLERK C
  ON CONVERT(S.CLERK_CI_AI using utf8mb4)
       = CONVERT(C.CLERK USING utf8mb4) COLLATE utf8mb4_general_ci;
    • 3

相关问题