MySQL存儲的字段是不區(qū)分大小寫的

2024-07-24 12:40:16

字體：大中小

來源：轉(zhuǎn)載

供稿：網(wǎng)友

00 簡單回顧

之前寫過一篇關(guān)于mysql 對表大小寫敏感的問題，其實(shí)在mysql中字段存儲的內(nèi)容是不區(qū)分大小寫的，本篇進(jìn)行簡單的總結(jié)。

想回顧一下：

MySQL在Linux下數(shù)據(jù)庫名、表名、列名、別名大小寫規(guī)則是這樣的：

1、數(shù)據(jù)庫名與表名是嚴(yán)格區(qū)分大小寫的；

2、表的別名是嚴(yán)格區(qū)分大小寫的；

3、列名與列的別名在所有的情況下均是忽略大小寫的；

4、字段內(nèi)容默認(rèn)情況下是大小寫不敏感的。

01 一個(gè)例子

簡單例子：

CREATE TABLE `tb_user` (    `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用戶id',    `username` VARCHAR (50) NOT NULL COMMENT '用戶名',    PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用戶表';INSERT INTO `u2s`.`tb_user` (`id`, `username`) VALUES ('1', 'user');INSERT INTO `u2s`.`tb_user` (`id`, `username`) VALUES ('2', 'User');INSERT INTO `u2s`.`tb_user` (`id`, `username`) VALUES ('3', 'USER');

使用查詢語句查詢 username 為全部小寫的user 的用戶，結(jié)果查詢出這個(gè)三條記錄全部都查詢到了。

mysql> SELECT username from tb_user where username = 'user';+----------+| username |+----------+| user     || User     || USER     |+----------+3 rows in set

通過這個(gè)例子簡單說明，字段內(nèi)容默認(rèn)情況下是大小寫不敏感的。

02 解決方案

因?yàn)槟J(rèn)情況下字段內(nèi)容是不區(qū)分大小寫的，也即大小寫不敏感。所以解決方案就是要新增字段內(nèi)容的校驗(yàn)規(guī)則。

使用mysql 的BINARY 關(guān)鍵字使搜索區(qū)分大小寫。

在查詢的sql中加入BINARY 關(guān)鍵字

mysql> select * from tb_user where BINARY username ='user';+----+----------+| id | username |+----+----------+|  1 | user     |+----+----------+1 row in set

這種方式相對較簡單，不用改動(dòng)表結(jié)構(gòu)，只需在需要區(qū)分查詢的字段前加上關(guān)鍵字。這種方式也是有缺點(diǎn)的，每次寫查詢的時(shí)候都要注意加關(guān)鍵字，并可能需要改動(dòng)的代碼較多。

在創(chuàng)建表的時(shí)候進(jìn)行限制

CREATE TABLE `tb_user1` (    `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用戶id',    `username` VARCHAR (50) BINARY NOT NULL COMMENT '用戶名',    PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '用戶表';mysql> show create table tb_user1;tb_user1 | CREATE TABLE `tb_user1` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用戶id',  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '用戶名',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶表'1 row in set

或者使用

CREATE TABLE `tb_user2` (    `id` BIGINT (20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用戶id',    `username` VARCHAR (50) NOT NULL COMMENT '用戶名',    `info` VARCHAR (100) NOT NULL COMMENT '詳情描述',    PRIMARY KEY (`id`)) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE=utf8_bin COMMENT = '用戶表';mysql> show create table tb_user2;tb_user2 | CREATE TABLE `tb_user2` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用戶id',  `username` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '用戶名',  `info` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '詳情描述',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用戶表'

使用 NGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin會(huì)將字段中varchar類型的全部設(shè)置區(qū)分大小寫。這兩種查看表的詳情，本質(zhì)上都是在字段上加上了 COLLATE utf8_bin。

03 總結(jié)

字段值的大小寫由mysql的校對規(guī)則來控制。提到校對規(guī)則，就不得不說字符集。字符集是一套符號和編碼，校對規(guī)則是在字符集內(nèi)用于比較字符的一套規(guī)則。一般而言，校對規(guī)則以其相關(guān)的字符集名開始，通常包括一個(gè)語言名，并且以ci（大小寫不敏感）、cs（大小寫敏感）或_bin（二元）結(jié)束。

比如 utf8字符集，，如下表：

1）utf8bin：utf8bin將字符串中的每一個(gè)字符用二進(jìn)制數(shù)據(jù)存儲，區(qū)分大小寫。

2）utf8generalci：utf8generaci不區(qū)分大小寫，ci為case insensitive的縮寫，即大小寫不敏感。

3）utf8generalcs：utf8generalcs區(qū)分大小寫，cs為case sensitive的縮寫，即大小寫敏感。

注：我本機(jī)使用5.7 版本不支持 utf8generalcs 字符集，創(chuàng)建報(bào)錯(cuò)。

通過上一篇和這一篇的內(nèi)容，詳細(xì)大家對mysql對大小寫敏感的問題也有一定的認(rèn)識了，在實(shí)際的開發(fā)中，庫和表名最好使用小寫字母，注意字段存儲內(nèi)容的大寫問題。并且讓本地開發(fā)環(huán)境mysql的配置和服務(wù)器上mysql的配置保持一致，這樣防止因?yàn)榄h(huán)境不一致而出現(xiàn)一些詭異問題。

你在開發(fā)中有沒有遇到一些詭異的問題呢？歡迎留言分享。