mysql utf8mb4 大小写_utf8mb4的大小写敏感性测试及其修改方法

--1、删除库 drop database if exists db2020;

mysql> drop database if existsdb2020;

Query OK,0 rows affected, 1 warning (0.00sec)--2、创建字符集为utf8的库 create database db2020 DEFAULT CHARACTER SET utf8mb4;

mysql> create database db2020 DEFAULT CHARACTER SETutf8mb4;

Query OK,1 row affected (0.00sec)--3、查看建库语句 show create database db2020;

mysql> show create databasedb2020;+----------+--------------------------------------------------------------------+

| Database | Create Database |

+----------+--------------------------------------------------------------------+

| db2020 | CREATE DATABASE `db2020` /*!40100 DEFAULT CHARACTER SET utf8mb4*/ |

+----------+--------------------------------------------------------------------+

1 row in set (0.00sec)--4、创建测试表和数据 use db2020;--drop table if exists tbl_test ;

create tabletbl_test (

idbigint(20) NOT NULLAUTO_INCREMENT,

namevarchar(20) NOT NULL,PRIMARY KEY(id),KEYidx_name (name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;--5、查看创建表的语句--use db2020;

show create tabletbl_test\G;

mysql> show create tabletbl_test\G;*************************** 1. row ***************************

Table: tbl_testCreate Table: CREATE TABLE`tbl_test` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`name`varchar(20) NOT NULL,PRIMARY KEY(`id`),KEY`idx_name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.05sec)

ERROR:

No query specified--6、查看默认字符集--方法1、show variables like '%character%';

mysql> show variables like '%character%';+--------------------------+----------------------------------------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /opt/mysql/mysql-5.6.43-linux-glibc2.12-x86_64/share/charsets/ |

+--------------------------+----------------------------------------------------------------+

8 rows in set (0.00sec)--方法2、show variables like 'collation%';

mysql> show variables like 'collation%';+----------------------+--------------------+

| Variable_name | Value |

+----------------------+--------------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

+----------------------+--------------------+

3 rows in set (0.00sec)--8、查看默认校对规则 show collation like 'utf8mb4%';

mysql> show collation like 'utf8mb4%';+------------------------+---------+-----+---------+----------+---------+

| Collation | Charset | Id | Default | Compiled | Sortlen |

+------------------------+---------+-----+---------+----------+---------+

| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |

| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |

| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |......

......+------------------------+---------+-----+---------+----------+---------+

26 rows in set (0.52sec)--9、插入测试数据--use db2020;

insert into tbl_test(name) values('aaa');insert into tbl_test(name) values('bbb');insert into tbl_test(name) values('AAA');insert into tbl_test(name) values('BBB');

mysql> select * fromtbl_test;+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 3 | AAA |

| 2 | bbb |

| 4 | BBB |

+----+------+

4 rows in set (0.08sec)

mysql> select * from tbl_test where name='aaa';+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 3 | AAA |

+----+------+

2 rows in set (0.04sec)usedb2020;insert into tbl_test(name) values('aaa');insert into tbl_test(name) values('bbb');insert into tbl_test(name) values('AAA');insert into tbl_test(name) values('BBB');

mysql> select * fromtbl_test;+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 3 | AAA |

| 2 | bbb |

| 4 | BBB |

+----+------+

4 rows in set (0.00sec)

mysql> select * from tbl_test where name='aaa';+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 3 | AAA |

+----+------+

2 rows in set (0.00sec)--10、默认情况下,不区分大小写,修改成大小写敏感--alter database db2020 character set=utf8mb4;

alter database db2020 character set=utf8mb4 collate=utf8mb4_bin;--alter table tbl_test convert to character set utf8mb4 ;

alter table tbl_test convert to character setutf8mb4 collate utf8mb4_bin;--只修改这个即可实现区分大小写--alter table tbl_test change name name varchar(20) character set utf8mb4 collate utf8mb4_general_ci not null;--alter table tbl_test modify name varchar(20) character set utf8mb4 collate utf8mb4_general_ci not null;

alter table tbl_test change name name varchar(20) character set utf8mb4 collate utf8mb4_bin not null;alter table tbl_test modify name varchar(20) character set utf8mb4 collate utf8mb4_bin not null;

mysql> alter database db2020 character set=utf8mb4 collate=utf8mb4_bin;

Query OK,1 row affected (0.00sec)

mysql> show create databasedb2020;+----------+----------------------------------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------------------------------+

| db2020 | CREATE DATABASE `db2020` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin*/ |

+----------+----------------------------------------------------------------------------------------+

1 row in set (0.00sec)

mysql> select * from tbl_test where name='aaa';+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 3 | AAA |

+----+------+

2 rows in set (0.00sec)--此时只修改库级别的还不行，仍然还需要修改表级别的

mysql> alter table tbl_test convert to character setutf8mb4 collate utf8mb4_bin;

Query OK,4 rows affected (0.08 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> select * from tbl_test where name='aaa';+----+------+

| id | name |

+----+------+

| 1 | aaa |

+----+------+

1 row in set (0.00sec)--附录 修改MySQL配置文件,新增如下参数：

[client]

default-character-set =utf8mb4[mysql]

default-character-set =utf8mb4[mysqld]

character-set-client-handshake =FALSEcharacter-set-server =utf8mb4

collation-server =utf8mb4_unicode_ci

init_connect='SET NAMES utf8mb4'