php MySQL小写比较数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9970313/
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
MySQL lowercase to compare data
提问by Dylan Cross
I want to get the contents from a row in the database and compare the lowercase version of it to a lowercase version of a user imput to check if it exists in the database:
我想从数据库中的一行获取内容，并将它的小写版本与用户输入的小写版本进行比较，以检查它是否存在于数据库中：
"SELECT `id` FROM `user_accounts` WHERE `username` = '".strtolower($username)."'"
How can i get username to be lowercase from mysql?
如何从mysql中将用户名设为小写？
采纳答案by Ignacio Vazquez-Abrams
If you really want case to not matter then you should set the collation of the column to be case-insensitive.
如果您真的希望大小写无关紧要，那么您应该将列的排序规则设置为不区分大小写。
mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT 'foobar' = 'FoObAr';
+---------------------+
| 'foobar' = 'FoObAr' |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.01 sec)
mysql> SELECT 'fOoBaR' = 'FoObAr';
+---------------------+
| 'fOoBaR' = 'FoObAr' |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT 'fOoBaR' = 'FoObAz';
+---------------------+
| 'fOoBaR' = 'FoObAz' |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
回答by Andreas Wong
You use lower(username) http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lower
你使用lower( username) http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lower
"SELECT `id` FROM `user_accounts` WHERE LOWER(`username`) = '".strtolower($username)."'"
回答by Jake
As answered above, Ideally this should work,
如上所述，理想情况下这应该有效，
$query = "SELECT `id`
FROM `user_accounts`
WHERE LOWER(`username`) = '".strtolower($username)."'";
but it won't work if the "username" column in "user_accounts" table is defined as VARBINARY. The reason is VARBINARY requires the data to be case sensitive
但是如果“user_accounts”表中的“username”列被定义为VARBINARY，它就不会起作用。原因是 VARBINARY 要求数据区分大小写
回答by PyromonkeyGG
Using one of the above queries should work just fine but CAUTION! If you have your username column indexed, using LOWER on it will make it so your query doesn't use that index.
使用上述查询之一应该可以正常工作，但请注意！如果您的用户名列已被索引，则在其上使用 LOWER 将使您的查询不使用该索引。
回答by Vi8L
Simply use:
只需使用：
SELECT `id`
FROM `user_accounts`
WHERE LOWER(`username`)='".strtolower($username)."'";
Or Use
或使用
SELECT `id`
FROM `user_accounts`
WHERE LCASE(`username`)='".strtolower($username)."'";
Both will work the same.
两者的工作方式相同。
回答by Tamik Soziev
$query = "SELECT `id`
FROM `user_accounts`
WHERE LOWER(`username`) = '".strtolower($username)."'"
回答by devsnd
You can use the following query to do so:
您可以使用以下查询来执行此操作：
$myquery = "SELECT `id` FROM `user_accounts` WHERE
LOWER(`username`) = '".strtolower($username)."'"
LOWERis the SQL function that converts all characters to lower case, just like PHP's strtolower
LOWER是将所有字符转换为小写的 SQL 函数，就像 PHP 的 strtolower
On a side note: You should escape the $usernameusing mysql_real_escape_stringto avoid possible sql injection at that point.
附带说明：您应该避免$username使用 usingmysql_real_escape_string以避免此时可能发生 sql 注入。
回答by Sel?uk
"SELECT `id` FROM `user_accounts` WHERE lower(`username`) = '".strtolower($username)."'"
