Mysql - 字符类型大小写敏感的讨论

蔚落 2021-12-20 15:31 169阅读 0赞

mysql字符类型默认是不区分大小写的，即select \* from t where name='AAA'与='aaa'没区别，以下是测试的例子

(root@localhost)[hello]> create table test1(id int, name varchar(10));
    (root@localhost)[hello]> insert into test1 values(1,'aaa'),(2,'AAA'),(3,'bbb'),(4,'BbB');
    (root@localhost)[hello]> select * from test1;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | aaa  |
    |    2 | AAA  |
    |    3 | bbb  |
    |    4 | BbB  |
    +------+------+
    
    (root@localhost)[hello]> select * from test1 where name = 'AAA';
    +------+------+
    | id   | name |
    +------+------+
    |    1 | aaa  |
    |    2 | AAA  |
    +------+------+
    
    (root@localhost)[hello]> select * from test1 where name = 'aaa';
    +------+------+
    | id   | name |
    +------+------+
    |    1 | aaa  |
    |    2 | AAA  |
    +------+------+

可以看到此时where条件后面的'AAA'与'aaa'，查出来的结果没啥区别。

如果只想找出'AAA'的可以有以下几种办法  
1.在sql中加入binary关键字

(root@localhost)[hello]> select * from test1 where binary name = 'AAA';
    +------+------+
    | id   | name |
    +------+------+
    |    2 | AAA  |
    +------+------+

2.修改列的定义

*先查看原始表的定义*

(root@localhost)[hello]> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

修改表test1的name列

alter table test1 modify column name varchar(10) character set utf8mb4 collate utf8mb4\_bin default null;  
collate utf8mb4\_bin表示where过滤或者order by排序区分大小写

此时查看test1的定义

(root@localhost)[hello]> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

接着再执行查询语句

(root@localhost)[hello]> select * from test1 where name='AAA';
    +------+------+
    | id   | name |
    +------+------+
    |    2 | AAA  |
    +------+------+

下面再创建一张test2表，就会发现上面修改列的语句其实相当于在创建表时varchar后面跟binary

(root@localhost)[hello]> create table test2(id int, name varchar(10) binary);
    (root@localhost)[hello]> show create table test2\G
    *************************** 1. row ***************************
           Table: test2
    Create Table: CREATE TABLE `test2` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

## 下面介绍如何设置字符大小写敏感 ##

数据库级别设置字符大小写敏感  
创建  
create database <db\_name> default character set utf8mb4 collate utf8mb4\_bin;  
修改  
alter database <db\_name> default character set utf8mb4 collate utf8mb4\_bin;

表级别设置字符大小写敏感  
创建  
create table <tb\_name> (  
......  
) engine=innodb default charset=utf8mb4 collate=utf8mb4\_bin;  
修改  
alter table <tb\_name> engine=innodb default charset=utf8mb4 collate=utf8mb4\_bin;

列级别设置字符大小写敏感  
创建  
create table <tb\_name> (  
\`field1\` varchar(10) character set utf8mb4 collate utf8mb4\_bin,  
......  
)  
修改  
alter table <tb\_name> modify column \`field1\` varchar(10) character set utf8mb4 collate utf8mb4\_bin default null;

继承关系是列-->表-->库，优先级是列>表>库

转载于:https://www.cnblogs.com/ddzj01/p/10736670.html