MySQL 字符類型大小寫敏感
mysql字符類型默認(rèn)是不區(qū)分大小寫的,即select * from t where name=’AAA’與=’aaa’沒區(qū)別,以下是測試的例子
(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’,查出來的結(jié)果沒啥區(qū)別。
如果只想找出’AAA’的可以有以下幾種辦法1.在sql中加入binary關(guān)鍵字
(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排序區(qū)分大小寫
此時查看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
接著再執(zhí)行查詢語句
(root@localhost)[hello]> select * from test1 where name=’AAA’;+------+------+| id | name |+------+------+| 2 | AAA |+------+------+
下面再創(chuàng)建一張test2表,就會發(fā)現(xiàn)上面修改列的語句其實相當(dāng)于在創(chuàng)建表時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
下面介紹如何設(shè)置字符大小寫敏感
數(shù)據(jù)庫級別設(shè)置字符大小寫敏感創(chuàng)建
create database <db_name> default character set utf8mb4 collate utf8mb4_bin;
修改
alter database <db_name> default character set utf8mb4 collate utf8mb4_bin; 表級別設(shè)置字符大小寫敏感
創(chuàng)建
create table <tb_name> (......) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;
修改
alter table <tb_name> engine=innodb default charset=utf8mb4 collate=utf8mb4_bin; 列級別設(shè)置字符大小寫敏感
創(chuàng)建
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;
繼承關(guān)系是列-->表-->庫,優(yōu)先級是列>表>庫
以上就是MySQL 字符類型大小寫敏感的詳細(xì)內(nèi)容,更多關(guān)于MySQL 字符類型大小寫的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章!
相關(guān)文章:
1. Windows NT平臺下Oracle優(yōu)化策略簡介2. navicat導(dǎo)入oracle導(dǎo)出的dmp文件3. sqlserver數(shù)據(jù)庫導(dǎo)入方法的詳細(xì)圖文教程4. MySql中流程控制函數(shù)/統(tǒng)計函數(shù)/分組查詢用法解析5. mysql-bin.000001文件的來源及處理方法6. SQL Server和Oracle并行處理方法對比7. 通過Oracle的緩沖區(qū)內(nèi)部機(jī)制調(diào)整性能8. Oracle數(shù)據(jù)庫中的表外鍵的更名細(xì)則9. Oracle中利用ADO對象實現(xiàn)存取和訪問10. Oracle數(shù)據(jù)庫中SQL語句性能調(diào)整原則
