MySQL字符集、排序规则和大小写敏感相关问题总结

小鱼儿 2024-03-16 20:47 36阅读 0赞

#### 文章目录 ####

*  相关概念
 *  查看字符集和排序规则
 *   *  查看当前实例支持的字符集
     *   *  utf8mb4
         *  字符串长度计算注意
     *  查看当前实例支持的排序规则
     *  查看当前实例下的某个字符集支持的所有排序规则
     *  查看当前实例使用的字符集和排序规则
     *  查看数据库编码(包括字符集和排序规则)
     *  查看表编码(包括字符集和排序规则)
     *  查看表中所有字段的编码(包括排序规则)
 *  设置字符集和排序规则
 *   *  设置
     *  server
     *  database
     *  table
     *  column
 *  查询时显式指定collation
 *   *  大小写敏感问题
     *   *  lower\_case\_file\_system
         *  lower\_case\_table\_names
 *  参考

## 相关概念 ##

几个重要的术语和概念：  
编码：某个字符集成员的内部表示  
字符集： 字母符号的集合  
排序规则：规定字符如何比较和排序的指令(也叫 校验规则 或者 校对规则)  
不指定时(`默认情况`)：库会继承实例、表会继承库、字段会继承表的字符集和排序规则

## 查看字符集和排序规则 ##

### 查看当前实例支持的字符集 ###

同时显示每个字符集使用的默认collation

show charset;
    或者
    show character set;
    或者
    ## 在MySQL中，全部的字符集与排列字符集的信息都存放在information_schema库中，所以可以通过下面的方式
    select * from INFORMATION_SCHEMA.CHARACTER_SETS;

可以过滤：

show charset like '%utf8%';
    
    show charset where charset like '%utf8%';
    
    show character set like '%utf8%';

#### utf8mb4 ####

mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。建议使用utf8mb4。

utf8mb4中存在utf8mb4\_bin， utf8mb4\_unicode\_ci， utf8mb4\_general\_ci，其中bin将字符看做二进制串进行比较，因此，大小写敏感，另外两个大小写不敏感。unicode比general更新， 符合更新的标准。

#### 字符串长度计算注意 ####

由于多字节编码的缘故，字符个数不一定等于字节数，所以在查询时，注意区分，length是字节数，char\_length是字符数

select length('你'); 
    /*结果是3，3个字节，数据库采用的utf8编码*/
    
    select char_length('你'); 
    /*结果是1，1个字符*/

### 查看当前实例支持的排序规则 ###

show collation;
    或者
    select * from INFORMATION_SCHEMA.COLLATIONS;

collation中一般以"\_ci"，“\_cs”，"\_bin"结尾，其中，`ci`表示case insensitive， `cs`表示case sensitive，`bin`表示binary(将字符看做二进制串进行比较，因此，大小写敏感)。

例如，项目中使用charset utf8并使用其collation utf8\_unicode\_ci，所以，字符串比较是大小写不敏感。

可以过滤：

SHOW COLLATION WHERE Charset = 'utf8';

### 查看当前实例下的某个字符集支持的所有排序规则 ###

show collation where charset = 'utf8'

### 查看当前实例使用的字符集和排序规则 ###

备注：创建库不指定时，使用此默认值

SHOW VARIABLES LIKE 'character%';
    
    SHOW VARIABLES LIKE 'collation%';

注意，这个参数 `collation_server`很重要：该参数定义服务器使用的字符集和排序规则。  
对应在MySQL配置文件中找到或添加以下行，例如：

[mysqld]
    collation_server=utf8_general_ci

### 查看数据库编码(包括字符集和排序规则) ###

USE database_name;
    
    SHOW CREATE DATABASE db_name;
    
    或者
    
    SHOW VARIABLES LIKE 'character_set_database';
    SHOW VARIABLES LIKE 'collation_database';

### 查看表编码(包括字符集和排序规则) ###

SHOW CREATE TABLE tbl_name;

### 查看表中所有字段的编码(包括排序规则) ###

在指定的库中，使用SHOW FULL COLUMNS FROM table\_name语句即可查询表中所有列的信息。

USE database_name;
    SHOW FULL COLUMNS FROM table_name;

指定列  
为了查询指定列的信息，可以在上述语句中添加限制Field字段的条件。

SHOW FULL COLUMNS FROM table_name WHERE Field = column_name;

## 设置字符集和排序规则 ##

### 设置 ###

设置character set和collation：  
MySQL允许在server, database, table, column等**4个级别**来设置字符集和collation。越小层次设置的字符集属性，优先级越大，即column级别的字符集属性会覆盖其他层次设置的字符集属性。

> server < database < table < column

### server ###

字符集默认使用utf8mb4，若设定字符集但不设定collation，使用该字符集的默认collation。

下面命令在`server`层次设置字符集为utf8，collation为utf8\_unicode\_ci。这样，server下的所有database便使用server设置的字符集信息。

> mysqld --character-set-server=utf8 --collation-server=utf8\_unicode\_ci

### database ###

下面SQL在DB层次设置字符集和collation。这样，DB中所有创建的table会使用db设置的字符集属性。

CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_bin;
    
    ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_bin;

注意：新设置的字符集属性不影响已经创建的table，只会对新创建未指定字符集的表赋予默认字符集

### table ###

CREATE TABLE TABLE_NAME (
       ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    
    ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
    
    ALTER TABLE table_name CHARACTER SET charset_name COLLATE collation_name;

### column ###

可以对CHAR, VARCHAR, TEXT等数据类型的column设置字符集属性。  
格式如下：

column_name [CHAR | VARCHAR | TEXT] (length) CHARACTER SET character_set_name COLLATE collation_name
    
    -- 案例：
    ALTER TABLE table_name MODIFY column_name column_type CHARACTER SET charset_name COLLATE collation_name;

## 查询时显式指定collation ##

上面讲述在不同层次设置字符集和collation。我们可以在查询数据时显式指定collation来覆盖表列上指定的collation，或者使用binary。

格式如下：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;
    
    SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;
    
    -- 在每一个条件前加上binary关键字
    select * from user where binary username = 'admin' and binary password = 'admin';
    
    -- 将参数以binary('')包围
    select * from user where username like binary('admin') and password like binary('admin');

### 大小写敏感问题 ###

查看当前mysql的大小写敏感配置

show global variables like '%case%';

#### lower\_case\_file\_system ####

该参数是用于描述data所在的操作系统的文件目录是否区分大小写，该参数为bool类型，只读参数，无法修改。  
此变量是只读的，因为它反映了文件系统的属性，设置它对文件系统没有影响。  
0 – 大小写敏感，OFF  
1 – 大小写不敏感，ON

比如跑在linux上的都是OFF或者0。

#### lower\_case\_table\_names ####

表示表名是否大小写敏感，可以修改，该参数为静态，可设置为0、1、2。

0 – 大小写敏感。（Unix，Linux默认）  
创建的库表将原样保存在磁盘上。如create database TeSt;将会创建一个TeSt的目录，create table AbCCC …将会原样生成AbCCC.frm。  
SQL语句也会原样解析。

1 – 大小写不敏感。（Windows默认）  
创建的库表时，MySQL将所有的库表名转换成小写存储在磁盘上。  
SQL语句同样会将库表名转换成小写。  
如需要查询以前创建的Test\_table（生成Test\_table.frm文件），即便执行select \* from Test\_table，也会被转换成select \* from test\_table，致使报错表不存在。

2 – 大小写不敏感（OS X默认）  
创建的库表将原样保存在磁盘上。  
但SQL语句将库表名转换成小写。

修改lower\_case\_table\_names导致的常见不良隐患：  
如果在lower\_case\_table\_names=0时，创建了含有大写字母的库表，改为lower\_case\_table\_names=1后，则会无法被查到。

注意事项：  
将默认的lower\_case\_tables\_name为0设置成1，需先将已经存在的库表名转换为小写：

1）针对仅表名存在大写字母的情况：  
①、lower\_case\_tables\_name=0时，执行rename table成小写。  
②、设置lower\_case\_tables\_name=1，重启生效。

2）针对库名存在大写字母的情况：  
①、lower\_case\_tables\_name=0时，使用mysqldump导出，并删除老的数据库。  
②、设置lower\_case\_tables\_name=1，重启生效。  
③、导入数据至实例，此时包含大写字母的库名已转换为小写。

转换操作需要自行测试，不同操作系统，不同MySQL版本可能有不同的情况。

## 参考 ##

> [MySQL官网 server-system-variables][MySQL_ server-system-variables]  
> [MySQL数据库配置信息查看和修改][MySQL]  
> [MySQL show语句常用用法汇总][MySQL show]

[MySQL_ server-system-variables]: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
[MySQL]: https://blog.csdn.net/u014163312/article/details/120840255
[MySQL show]: https://blog.csdn.net/u014163312/article/details/116081446