“`CHARACTER”何时包括在MySQL中的列定义中？

Question

描述

为了测试我们的更新，我们比较了一个旧的更新的数据库模式和一个新的安装。这两种模式应该是相同的。

我们使用SHOW CREATE TABLE来找出差异。但是，我们注意到，在MySQL 8中，我们根据表的构造方式发现了一些不同之处。有些列包括CHARACTER SET utf8mb4，而另一些列则不包含。

如何复制：

CREATE TABLE t1 (
    col1 VARCHAR(1024) NOT NULL,
    col2 VARCHAR(1024) COLLATE utf8mb4_unicode_ci NOT NULL
);
SHOW CREATE TABLE t1;

以及产出：

CREATE TABLE `t1` (
  `col1` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
  `col2` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我试着找出这两列的不同之处，但它们看起来完全相同：

SELECT * FROM information_schema.`COLUMNS`
    WHERE table_schema = "db" AND table_name = "t1";

                          col1                               col2
                          -----                              ----
           TABLE_CATALOG: def                                def 
            TABLE_SCHEMA: db                                 db
              TABLE_NAME: t1                                 t1
             COLUMN_NAME: col1                               col2
        ORDINAL_POSITION: 1                                  2
          COLUMN_DEFAULT:
             IS_NULLABLE: NO                                 NO
               DATA_TYPE: varchar                            varchar
CHARACTER_MAXIMUM_LENGTH: 1024                               1024
  CHARACTER_OCTET_LENGTH: 4096                               4096
       NUMERIC_PRECISION: NULL                               NULL
           NUMERIC_SCALE: NULL                               NULL 
      DATETIME_PRECISION: NULL                               NULL
      CHARACTER_SET_NAME: utf8mb4                            utf8mb4
          COLLATION_NAME: utf8mb4_unicode_ci                 utf8mb4_unicode_ci
             COLUMN_TYPE: varchar(1024)                       varchar(1024)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references    select,insert,update,references
          COLUMN_COMMENT:
   GENERATION_EXPRESSION:
                  SRS_ID: NULL                               NULL

问题

这些列有何不同？它们都使用字符集utf8mb4和排序规则utf8mb4_unicode_ci。只是个旗子吗？那面旗子会做什么？

Answer 1

MySQL 8.0.11改变了SHOW CREATE TABLE的行为。它现在显示排序规则和字符集(如果它们已经显式指定)。(https://bugs.mysql.com/bug.php?id=46239)

为此，每一列都会记住是否已显式分配了排序规则或字符集。相关代码可以在store_create_info中找到。

/*
  For string types dump charset name only if field charset is same as
  table charset or was explicitly assigned.
 */
if (field->charset() != share->table_charset ||
    column_has_explicit_collation) {
    packet->append(STRING_WITH_LEN(" CHARACTER SET "));
    packet->append(field->charset()->csname);
}

(注:我认为这个评论是错误的。)

否则，列是完全相同的。我相信这样做是为了使SHOW CREATE TABLE创建的代码可移植到使用其他默认字符集和排序规则的数据库中。

Answer 2

您应该阅读MySQL的优秀文档，它解释了字符集和排序规则之间的区别。字符集决定哪些字符可以存储在列或表中，而排序规则则决定如何比较字符。以下是您的列定义：

col1 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''

这意味着utf8mb4字符可以存储在col1中。但是，它提供utf8mb4_unicode_ci作为排序规则，这是不区分大小写的。这意味着比较(例如m和M )将导致两个字符在排序和其他操作上是相同的。