MySQL 字符類型大小寫敏感

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 test1G*************************** 1. row *************************** Table: test1Create 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 test1G*************************** 1. row *************************** Table: test1Create 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 test2G*************************** 1. row *************************** Table: test2Create 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;

繼承關系是列-->表-->庫，優先級是列>表>庫

以上就是MySQL 字符類型大小寫敏感的詳細內容，更多關于MySQL 字符類型大小寫的資料請關注好吧啦網其它相關文章！