0%

MySQL忽略大小写问题

MySQL忽略大小写问题

今天使用MySQL查询时遇到一个问题,使用一个字符串去查询时,查出来两条数据,但是查到的数据大小写并不一样

1
2
3
4
5
6
7
mysql> select * from good where name = 'a';
+----+------+-------+
| id | name | price |
+----+------+-------+
| 3 | a | 100 |
| 4 | A | 100 |
+----+------+-------+

我查到了name为a和A的两条数据,这就很奇怪,难道mysql会忽略大小写吗?

然后查了一下资料,说是utf8_general_ci中的_ci是指忽略大小写Case-insensitive,但是我只设置了字符编码utf8,没有设置为utf8_general_ci呀,原来utf8默认的是就是utf8_general_ci

1
2
-- 查看字符集
show collation;
1
2
3
4
5
-- 查看数据库中表的字符集
show table status from 库名 like 表名;

-- 查看表中所有字段的字符集
show full columns from 表名;

果然是utf8_general_ci,好吧,那么如何修改呢?只能改成utf8_bin了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--修改数据库字符集:
ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

--把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]
-- 示例:ALTER TABLE good CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

--修改表的默认字符集:
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
-- 示例:ALTER TABLE good DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- 修改字段的字符集:
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
-- 示例:ALTER TABLE good CHANGE name name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE good CHANGE name name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

改完之后就OK了

1
2
3
4
5
6
mysql> select * from good where name = 'a';
+----+------+-------+
| id | name | price |
+----+------+-------+
| 3 | a | 100 |
+----+------+-------+