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)

最新文章

  1. WPScan用法
  2. 玩蛇记－－Python处理海量手机号码
  3. C#获取EF实体对象或自定义属性类的字段名称和值
  4. HT for Web 3D游戏设计设计--汉诺塔（Towers of Hanoi）
  5. centos7 下手动安装MySQL-5.6.32-1.linux_glibc2.5.x86_64.rpm-bundle
  6. STL 常见容器
  7. Java运算符（一）equals方法与“==”
  8. SQL server 变量、运算符
  9. centos 6安装报错
  10. IOS UIlabel设置文本距离边框距离
  11. ParseChat应用源码ios版
  12. PhpSrom安装xdebug
  13. Chapter 1 First Sight——12
  14. python基本运算
  15. this语句
  16. data数据不一致的问题
  17. 51Nod 1331 狭窄的通道
  18. 安装Git Bash图文教程
  19. MYSQL 表大小限制
  20. Pointer-network的tensorflow实现-1

热门文章

  1. 【原】Java学习笔记024 - 包装类
  2. php二维数组根据某个字段去重
  3. python 3.7 安装mysqlclient 错误解决
  4. Jquery自动补全插件的使用
  5. 新的编辑工具IDE
  6. Taro项目遇到的问题
  7. @getMapping与@postMapping
  8. java 下载word freemaker
  9. [详细实例]MicroPython拼插编程实战：DIY一台会思考的壁障车
  10. Java内存模型知识点小结---《深入理解Java内存模型》（程晓明）读书总结