重新表达此查询的最简单方法是什么,以便用户名不区分大小写,但密码区分大小写?
$query = mysql_query("select * from login where password='$password' AND username='$username'", $connection) or die(mysql_error());
正确的方法是让数据库处理大小写敏感性。为了做到这一点,你必须改变排序
字段username
或password
(这取决于它们当前的排序)。您所要做的就是将字段username
的排序规则更改为名称以_ci
结尾的排序规则(它代表case insensitive
),并将字段password
的排序规则更改为名称以_cs
结尾的排序规则(从case sensitive
)。
排序规则的实际名称取决于表的字符集。
例如,下面是使用latin1
作为字符集的表的定义:
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`password` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
如果你改变排序,你不需要担心值的大小写敏感性,你不需要改变查询。
使用上面表格的示例代码
mysql> INSERT INTO `users` (`username`, `password`)
-> VALUES ('John Doe', 'passwd1'), ('john doe', 'PASSWD1');
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM `users` WHERE `username` = 'JOHN DOE';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | John Doe | passwd1 |
| 2 | john doe | PASSWD1 |
+----+----------+----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM `users` WHERE `password` = 'passwd1';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | John Doe | passwd1 |
+----+----------+----------+
1 row in set (0.00 sec)
另一种解决方案,使用@spencer7593的优秀建议
您不需要更改字段的排序(建议这样做,但如果您不能这样做,那么这是另一种解决方案)。您可以在查询中强制使用所需的排序规则:
强制区分大小写的username
比较:
mysql> SELECT * FROM `users` WHERE `username` COLLATE latin1_general_cs = 'john doe';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 2 | john doe | PASSWD1 |
+----+----------+----------+
1 row in set (0.00 sec)
强制password
不区分大小写的比较:
mysql> SELECT * FROM `users` WHERE `password` COLLATE latin1_general_ci = 'passwd1';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | John Doe | passwd1 |
| 2 | john doe | PASSWD1 |
+----+----------+----------+
2 rows in set (0.00 sec)
让情况正常化。
$query = mysql_query(
"
SELECT *
FROM login
WHERE
password='$password'
AND LOWER(username)=LOWER('$username')
",
$connection
) or die(mysql_error());
很简单,您可以使用LIKE
,例如:
$query = mysql_query("select * from login where password='$password' AND username LIKE '$username'", $connection) or die(mysql_error());
或者您可以像这样做部分匹配,但这不是您需要的:
$query = mysql_query("select * from login where password='$password' AND username LIKE '%$username%'", $connection) or die(mysql_error());