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 default character set utf8mb4 collate utf8mb4_bin;
修改
alter database default character set utf8mb4 collate utf8mb4_bin;
表级别设置字符大小写敏感
创建
create table (
......
) engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;
修改
alter table engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;
列级别设置字符大小写敏感
创建
create table (
`field1` varchar(10) character set utf8mb4 collate utf8mb4_bin,
......
)
修改
alter table modify column `field1` varchar(10) character set utf8mb4 collate utf8mb4_bin default null;
继承关系是列-->表-->库,优先级是列>表>库
以上就是mysql 字符类型大小写敏感的详细内容,更多关于mysql 字符类型大小写的资料请关注萬仟网其它相关文章!
希望与广大网友互动??
点此进行留言吧!