mysql where区分大小写_mysql – 如何在WHERE子句中进行区分大小写的搜索？

by default, MySQL does not consider the case of the strings

这不是真的.无论何时在MySQL中创建数据库,数据库/模式都有字符集和排序规则.每个字符集都有一个默认排序规则; see here获取更多信息.

字符集latin1的默认排序规则,即latin1_swedish_ci,恰好不区分大小写.

您可以选择区分大小写的排序规则,例如latin1_general_cs(MySQL grammar)：

CREATE SCHEMA IF NOT EXISTS `myschema`

DEFAULT CHARACTER SET latin1

COLLATE latin1_general_cs ;

这对分组和平等等问题有影响.例如,

create table casetable (

id int primary key,

thing varchar(50)

);

select * from casetable;

+----+-------+

| id | thing |

+----+-------+

| 3 | abc |

| 4 | ABC |

| 5 | aBc |

| 6 | abC |

+----+-------+

在区分大小写的数据库中,我们得到：

select thing, count(*) from casetable group by thing;

+-------+----------+

| thing | count(*) |

+-------+----------+

| ABC | 1 |

| aBc | 1 |

| abC | 1 |

| abc | 1 |

+-------+----------+

select * from casetable where thing = "abc";

+----+-------+

| id | thing |

+----+-------+

| 3 | abc |

+----+-------+

在不区分大小写的数据库中,我们得到：

select thing, count(*) from casetable group by thing;

+-------+----------+

| thing | count(*) |

+-------+----------+

| abc | 4 |

+-------+----------+

select * from casetable where thing = "abc";

+----+-------+

| id | thing |

+----+-------+

| 3 | abc |

| 4 | ABC |

| 5 | aBc |

| 6 | abC |

+----+-------+

请注意,您还可以更改排序规则from within a query.例如,在区分大小写的数据库中,我可以这样做

select * from casetable where thing collate latin1_swedish_ci = "abc";

+----+-------+

| id | thing |

+----+-------+

| 3 | abc |

| 4 | ABC |

| 5 | aBc |

| 6 | abC |

+----+-------+