# 测试utf8mb4的大小写敏感性及其修改方法
-- 以下是utf8不区分大小写
1 # 修改数据库:
2 ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
3 # 修改表:
4 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
5 # 修改表字段:
6 ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
-- 以下是utf8区分大小写
1 # 修改数据库:
2 ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
3 # 修改表:
4 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
5 # 修改表字段:
6 ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;
-- 1、删除库
drop database if exists db2020;
mysql> drop database if exists db2020;
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 2、创建字符集为utf8的库
create database db2020 DEFAULT CHARACTER SET utf8mb4;
mysql> create database db2020 DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)
-- 3、查看建库语句
show create database db2020;
mysql> show create database db2020;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| db2020 | CREATE DATABASE `db2020` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 4、创建测试表和数据
use db2020;
-- drop table if exists tbl_test ;
create table tbl_test (
id bigint(20) NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
PRIMARY KEY (id),
KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
-- 5、查看创建表的语句
-- use db2020;
show create table tbl_test\G;
mysql> show create table tbl_test\G;
*************************** 1. row ***************************
Table: tbl_test
Create Table: CREATE TABLE `tbl_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
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 | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
-- 方法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 | utf8_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
-- 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 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)
-- 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 * from tbl_test;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 3 | AAA |
| 2 | bbb |
| 4 | BBB |
+----+------+
4 rows in set (0.00 sec)
mysql> select * from tbl_test where name='aaa';
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 3 | AAA |
+----+------+
2 rows in set (0.00 sec)
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 * from tbl_test;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 3 | AAA |
| 2 | bbb |
| 4 | BBB |
+----+------+
4 rows in set (0.00 sec)
mysql> select * from tbl_test where name='aaa';
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 3 | AAA |
+----+------+
2 rows in set (0.00 sec)
-- 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 set utf8mb4 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.00 sec)
mysql> show create database db2020;
+----------+----------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------+
| db2020 | CREATE DATABASE `db2020` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tbl_test where name='aaa';
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 3 | AAA |
+----+------+
2 rows in set (0.00 sec)
-- 此时只修改库级别的还不行，仍然还需要修改表级别的
mysql> alter table tbl_test convert to character set utf8mb4 collate utf8mb4_bin;
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from tbl_test where name='aaa';
+----+------+
| id | name |
+----+------+
| 1 | aaa |
+----+------+
1 row in set (0.00 sec)
-- 附录
修改MySQL配置文件,新增如下参数：
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
