utf8的大小写敏感性测试及其修改方法
# 测试utf8的大小写敏感性及其修改方法 -- 以下是utf8不区分大小写
1 # 修改数据库:
2 ALTER DATABASE database_name CHARACTER SET = utf8 COLLATE = utf8_general_ci;
3 # 修改表:
4 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
5 # 修改表字段:
6 ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; -- 以下是utf8区分大小写
1 # 修改数据库:
2 ALTER DATABASE database_name CHARACTER SET = utf8 COLLATE = utf8_bin;
3 # 修改表:
4 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
5 # 修改表字段:
6 ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL; -- 1、删除库
drop database if exists db2019;
mysql> drop database if exists db2019;
Query OK, 0 rows affected, 1 warning (0.00 sec) -- 2、创建字符集为utf8的库
create database db2019 DEFAULT CHARACTER SET utf8;
mysql> create database db2019 DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec) -- 3、查看建库语句
show create database db2019;
mysql> show create database db2019;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| db2019 | CREATE DATABASE `db2019` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec) -- 4、创建测试表和数据
use db2019;
-- 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=utf8 ; -- 5、查看创建表的语句
-- use db2019;
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=utf8
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 | utf8 |
| 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 | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec) -- 8、查看默认校对规则
show collation like 'utf8%';
mysql> show collation like 'utf8%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_bin | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8_general_ci | utf8 | 45 | Yes | Yes | 1 |
| utf8_bin | utf8 | 46 | | Yes | 1 |
| utf8_bin | utf8 | 224 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 225 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 226 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 227 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 228 | | Yes | 8 |
| utf8_polish_ci | utf8 | 229 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 230 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 231 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 232 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 233 | | Yes | 8 |
| utf8_czech_ci | utf8 | 234 | | Yes | 8 |
| utf8_danish_ci | utf8 | 235 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 236 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 237 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 238 | | Yes | 8 |
| utf8_roman_ci | utf8 | 239 | | Yes | 8 |
| utf8_persian_ci | utf8 | 240 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 241 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 242 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 243 | | Yes | 8 |
| utf8_german2_ci | utf8 | 244 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 245 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 246 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 247 | | Yes | 8 |
+--------------------------+---------+-----+---------+----------+---------+
53 rows in set (0.00 sec) mysql> -- 9、插入测试数据
-- use db2019;
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 db2019 character set=utf8;
alter database db2019 character set=utf8 collate=utf8_bin; -- alter table tbl_test convert to character set utf8 ;
alter table tbl_test convert to character set utf8 collate utf8_bin; -- 只修改这个即可实现区分大小写
-- alter table tbl_test change name name varchar(20) character set utf8 collate utf8_general_ci not null;
-- alter table tbl_test modify name varchar(20) character set utf8 collate utf8_general_ci not null;
alter table tbl_test change name name varchar(20) character set utf8 collate utf8_bin not null;
alter table tbl_test modify name varchar(20) character set utf8 collate utf8_bin not null; mysql> alter database db2019 character set=utf8 collate=utf8_bin;
Query OK, 1 row affected (0.00 sec) mysql> show create database db2019;
+----------+----------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------+
| db2019 | CREATE DATABASE `db2019` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_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 utf8 collate utf8_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)