MySQL 字符类型大小写敏感
16lz
2021-12-14
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 |+------+------+
如果只想找出'AAA'的可以有以下几种办法
1.在sql中加入binary关键字
(root@localhost)[hello]> select * from test1 where binary name = 'AAA';+------+------+| id | name |+------+------+| 2 | AAA |+------+------+
先查看原始表的定义
(root@localhost)[hello]> show create table test1\G*************************** 1. row *************************** Table: test1Create Table: CREATE TABLE `test1` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
alter table test1 modify column name varchar(10) character set utf8mb4 collate utf8mb4_bin default null;
此时查看test1的定义
(root@localhost)[hello]> show create table test1\G*************************** 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 |+------+------+
(root@localhost)[hello]> create table test2(id int, name varchar(10) binary);(root@localhost)[hello]> show create table test2\G*************************** 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;
更多相关文章
- android 中使用TextView实现分段显示不同颜色的字符串
- android中json文件的写法
- exp: 修改Android中strings.xml文件, 动态改变数据
- 【【【常用的ubuntu第三方工具及android命令（自存档）】】】二
- Android(安卓)中数据库查询方法 query() 中的 select
- android中SqLite query中用selectionArgs处理字符传值
- android中字符替换成表情
- 修改系统Android版本，版本号
- 获取Android系统信息