如何在 PHP/MySQL 中使 SELECT 不区分大小写？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5938037/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a SELECT in PHP/MySQL case insensitive?
提问by Clément
Her's my probleme, i guess its really basic.
她是我的问题，我想它真的很基本。
I'm trying to lookup in the database if a line does exist. heres my code :
如果确实存在一行，我正在尝试在数据库中查找。继承人我的代码：
$req="SELECT * FROM INSTITUTS WHERE inst_name='$fc_inst'";
$result=mysql_query($req) or die ('Erreur :'.mysql_error());
if (mysql_num_rows($result)){
echo ' name exist';
}
else {
echo ' does not exist.';
}
Probleme is, when imm looking for "test", it says does not exist, even if i have "Test" in my database.
问题是，当我在寻找“测试”时，它说不存在，即使我的数据库中有“测试”。
回答by Naftali aka Neal
Try using LIKEinstead of =:
尝试使用LIKE代替=：
$req="SELECT * FROM INSTITUTS WHERE `inst_name` LIKE '$fc_inst'";
回答by James C
you can use LIKE:
你可以使用LIKE：
WHERE foo LIKE 'bar'
Or you can cast both lowercase with:
或者您可以使用以下两种小写形式：
WHERE LOWER(foo) = LOWER("bar")
The example with LOWER()is most effective where you know that all of your data in the database is already lower cased and then you can just execute:
LOWER()当您知道数据库中的所有数据已经小写，然后您可以执行以下命令时，示例最有效：
WHERE foo = LOWER("bar")
This would be a cheaper comparison than the LIKEif you can lower case all of the data in your database.
LIKE如果您可以小写数据库中的所有数据，这将是一个更便宜的比较。
回答by stevecomrie
It could also be a problem with your table COLLATE setting
您的表 COLLATE 设置也可能有问题
This CREATE statement will force your select queries to be case sensitive even when using LIKE operators:
即使使用 LIKE 运算符，此 CREATE 语句也将强制您的选择查询区分大小写：
CREATE
table instituts (inst_name VARCHAR(64))
CHARACTER SET latin1 COLLATE latin1_general_cs;
Whereas this one will ensure case-insensitivity:
而这将确保不区分大小写：
CREATE
table instituts (inst_name VARCHAR(64))
CHARACTER SET latin1
回答by PachinSV
you can solve it using "LIKE" as other people told you, BUTit is important to know that the case sensitivity is determined by the collation in the database. For example if you select a collation utf8_general_ci... that "ci" at the end means "case insensitive" so the comparisons you do in the future will be case insensitive.
您可以像其他人告诉您的那样使用“LIKE”来解决它，但重要的是要知道区分大小写是由数据库中的排序规则决定的。例如，如果您选择排序规则 utf8_general_ci ... 末尾的“ci”表示“不区分大小写”，因此您将来进行的比较将不区分大小写。
In a few words: you have to be careful about the collation you select.
简而言之：您必须小心选择的排序规则。
回答by Yann Saint-Dizier
You can use a MD5() comparison if you want a case sensitiveselect:
如果需要区分大小写的选择，可以使用 MD5() 比较：
$req="SELECT * FROM INSTITUTS WHERE MD5(inst_name)=MD5('$fc_inst')";
of course you consume a little bit of the server's cpu but it's rather simpler than those boring collations.
当然你会消耗一点服务器的cpu，但它比那些无聊的整理要简单得多。
回答by Anubhav
You can use LIKE BINARY in your query..
您可以在查询中使用 LIKE BINARY ..
Like this:
像这样：
SELECT * FROM
table_nameWHEREcolumn_nameLIKE BINARY 'search_string'
SELECT * FROM
table_nameWHEREcolumn_nameLIKE BINARY 'search_string'
this will check "search_string" data in case sensitive
这将检查“search_string”数据是否区分大小写
回答by Anubhav
Try:
尝试：
$req="SELECT * FROM INSTITUTS WHERE UCASE(inst_name)=UCASE('$fc_inst')";
$result=mysql_query($req) or die ('Erreur :'.mysql_error());
if (mysql_num_rows($result)){
echo ' name exist';
}
else {
echo ' does not exist.';
}
