mysql - 在 mysql select 中设置不同的 lower 和 upper

Question

我想在数据库中搜索一个名字。但我只想选择 Bill ，而不是 biLL 或 BiLL 或...只是“Bill”。但是当我使用这个查询时，它显示 Bill 、 BiLL 、 BILL 、 bill 和 ...

query=`select * from names where name='Bill'`

Answer 1

引用the documentation :

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation

您可以通过显式使用区分大小写的排序规则来解决这个问题:

select * from names where name='Bill' COLLATE latin1_general_cs