数据库字符集与比较规则

  • 建表语句

    1
    CREATE SCHEMA `test01` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
  • CHARSET ：给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set)。
  • COLLATE 是指在同一字符集内字符之间的比较规则；确定比较规则后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系；

UTF8与utf8mb4

  • MySQL的utf8是utfmb3，只有三个字节，节省空间但不能表达全部的UTF-8。

  • MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。可以用utf8mb4字符编码直接存储emoj表情，而不是存表情的替换字符。

比较规则

  • COLLATE 会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。
  • mysql建索引的时候，如果索引列是字符类型，也会影响索引创建，总之，凡是涉及到字符类型比较或排序的地方，都和COLLATE有关。
  • 每个校验规则唯一对应一种字符集，但一个字符集可以对应多种校验规则，其中有一个是默认(Default Collation)；
    • 查询mysql数据库所支持的字符集种类：show character set;
    • 查询mysql数据库所支持字符集的校验规则：show collation;
  • 命名惯例：以对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。
    • 例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的；

默认设置

  • Mysql有4个级别的默认设置：服务器级，数据库级，表级和字段级

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    # 查看服务器级字符编码
    show variables like 'character_set_server'
    # 查看服务器级校验规则
    show variables like 'collation_server'


    # 服务器级字符集和校验规则，在Mysql启动时确定，在my.cnf中设置，如果没有指定字符集，默认为latin1，如果没有设置校验规则，默认使用字符集校验规则。
    # 在mysql8.0以下版本中，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。
    # 从mysql8.0开始，默认的CHARSET已经改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

    [mysqld]
    character-set-server=utf8
    collation_server=utf8_general_ci

    # 查看数据库级字符编码
    show variables like 'character_set_database'

    # 查看数据库级校验规则
    show variables like 'collation_database'

    # 表级别设置
    CREATE TABLE (
    	...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    # 列级别设置
    CREATE TABLE (
    `field1` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
    ...
    );

  • utf8_unicode_ci校对规则仅部分支持Unicode校对规则算法。一些字符还是不能支持。并且，不能完全支持组合的记号。这主要影响越南和俄罗斯的一些少数民族语言。

    • utf8_unicode_ci的最主要的特色是支持扩展，即当把一个字母看作与其它字母组合相等时。例如，在德语和一些其它语言中‘ß’等于‘ss’。

  • utf8_general_ci是一个遗留的 校对规则，不支持扩展。它仅能够在字符之间进行逐个比较。这意味着utf8_general_ci校对规则进行的比较速度很快，但是与使用utf8_unicode_ci的 校对规则相比，比较正确性较差。

数据库安装指定忽略大小写

  • 由于开发的时候，本地和测试环境的经常出现不一致的情况，于是我决定本地使用linux运行springboot的jar包，自己验证。数据库也放在虚拟机中，本地只用来编辑代码。
  • 结果调试过程中，使用Centos下的数据库就一直连接不上数据库，我使用命令行成功连接。在对日志的查看中发现了 java.sql.SQLSyntaxErrorException: Table 'XXXX.XXX' doesn't exist;
  • 仔细查看发现了是表名的问题，数据库下是t1,但是查询的时候sql语句是使用的是T1。我切换为开发环境的数据库发现查询T1表成功了。sql脚本也是直接dump下来的。那就是表名的问题导致的。通过查询文档后发现了一个属性：lower_case_table_names

lower_case_table_names 的值：

  • 如果设置为 0，表名将按指定方式存储，并且在对比表名时区分大小写。

  • 如果设置为 1，表名将以小写形式存储在磁盘上，在对比表名时不区分大小写。

  • 如果设置为 2，则表名按给定格式存储，但以小写形式进行比较。

  • 此选项还适用于数据库名称和表别名。有关其他详细信息，请参阅第 9.2.3 节 “标识符区分大小写“。

由于 MySQL 最初依赖于文件系统来作为其数据字典，因此默认设置是依赖于文件系统是否区分大小写。

  • 在 Windows 上，默认值为 1。在 macOS 上，默认值是 2。在 Linux 上，不支持值 2；服 务器会将该值设置为 0。

  • 在不区分大小写的文件系统上（比如 Windows 或 macOS 中的文件系统），运行 MySQL 时 ，不应使用 --lower_case_table_names=0 。 这将是不受支持的组合。

  • 如果尝试在不区分大小写的文件系统上使用--lower_case_table_names=0 启动服务器， 则会打印错误消息并退出服务器。

  • 如果使用 InnoDB 表，则应在所有平台上将此变量设置为 1，以强制将名称转换为小写。

必须在初始化 MySQL 服务器（安装 MySQL 后的首次启动）之前将 lower_case_table_names 配置为所需的值。 在大多数情况下，这需要在首次启动 MySQL 服务器之前在 MySQL 配置文件中设置 lower_case_table_names 。

  • 在 MySQL 8 中，数据目录初始化之后，不再允许更改 lower_case_table_names = 1 的 值；

  • MySQL 基于某些原因，禁止在重新启动 MySQL 服务时将 lower_case_table_names 设置 成不同于初始化 MySQL 服务时设置的 lower_case_table_names 值。也就是说启动（重启）MySQL 时，lower_case_table_names的值必须与初始化 MySQL 时（安装 MySQL 后的首次启动）的值相同。

  • 换一种说法： 一旦 MySQL 的数据目录被初始化后，该数据目录的中的标识符的排序和比较方式就被确定；而使用不同设置重新启动 MySQL 服务器时，MySQL 服务器检测到其于数据 目录不匹配，将会报错。

安装 MySQL 8时配置不区分大小写

在 CentOS 中安装MySQL8：

  1. 添加 MySQL YUM repository （添加 MySQL 的 yam 仓库，略）
  2. 卸载当前系统中的其它 MySQL。（如果需要同时安装不同版本的 MySQL，请使用 tarball 发行版。）
  3. 清除数据目录： 为了能够初始化 MySQL，数据目录必须为空。 您可以选择对数据目录 使用非默认位置；也可以删除 /var/lib/mysql 目录。 如果要保留旧的数据目录，请 先进行备份！
  4. 安装 MySQL 8 ：通过 yam install
  5. 初始化前指定 lower_case_table_names = 1： 方法，在初次使用 systemd 启动 mysqld 之前，在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1。
  6. 初始化： systemctl start mysqld

为已安装的MySQL8设置不区分大小写

  • 停止MySQL
  • 删除数据目录，即删除 /var/lib/mysql 目录
  • 在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1
  • 启动 MySQL

为Docker中的MySQL8容器设置不区分大小写

  • 官方MySQL镜像，是基于 debian 系统的，FROM debian:buster-slim，并在该系统中使用 apt-get 安装 mysql-community-server ；详见其 Dockerfile

  • 使用下面的方式运行 MySQL 8 容器：

  • 设置字符集和排序方式

    1
    docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

  • 先使用上面的命令创建并运行容器 some-mysql

  • 在主机中创建配置文件，比如 /root/docker/mysql8/conf.d/config-file.cnf，文件内容如下：

    1
    2
    [mysqld]
      lower_case_table_names = 1

  • 使用下面的命令将该文件复制到容器中

    1
    docker cp /root/docker/mysql8/conf.d/config-file.cnf some-mysql:/etc/mysql/conf.d/

  • 停止容器

  • 删除数据目录，比如删除 /var/lib/mysql

  • 重启容器