MySQL SELECT 语句区分大小写和特殊字符

Question

我正在用 php 和 html 编写登录脚本。用户名和密码不需要加密，所以我试图将它们直接存储到数据库 (MySQL) 中。

但我的一些用户的用户名和密码包含特殊字符，如“é”，并且必须区分大小写。

我有这张表:

用户用户名:“ÉleCTeur”，密码:“ÉleCTeur”，用户名:“保罗”，密码:“保罗”

我尝试了这个查询，它对“Paul”工作正常，但对“ÉleCTeur”没有返回任何内容。

SELECT 
    username,
    password
FROM 
    user
WHERE
    BINARY username = 'ÉleCTeur' 
    AND BINARY password = 'ÉleCTeur'

我是不是错过了什么或者 BINARY 没有比较特殊字符？我可以使用什么方法来解决这个问题？

Answer 1

您的数据库和客户端程序之间的编码可能不一致？

我花了一些时间从 MySQL CLI 进行测试:

mysql> create TABLE user (username CHAR(20), password CHAR(20)) DEFAULT CHARSET=latin1 DEFAULT COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.25 sec)

mysql> insert into user value("ÉleCTeur", "ÉleCTeur");
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----------+----------+
| username | password |
+----------+----------+
| ÉleCTeur | ÉleCTeur |
+----------+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where username = "EleCTeur" and password = "EleCteur";
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where username = "ÉleCTeur" and password = "ÉleCteur";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

编辑: 好的，这是一个(太)快速和(太)肮脏的测试。正如 Sebastien 所注意到的，我在这里使用不区分大小写 排序规则。所以:

mysql> select count() from user where username = "éleCTeur"和 password = "éleCteur";+----------+|数() |+----------+| 1 |+----------+一组中的 1 行(0.01 秒)

这里真正的答案是使用区分大小写 (_cs) 排序规则。要知道哪一个可用:

mysql> show collation where collation like '%_cs';
+--------------------+---------+----+---------+----------+---------+
| Collation          | Charset | Id | Default | Compiled | Sortlen |
+--------------------+---------+----+---------+----------+---------+
| latin1_general_cs  | latin1  | 49 |         | Yes      |       1 |
| latin2_czech_cs    | latin2  |  2 |         | Yes      |       4 |
| cp1250_czech_cs    | cp1250  | 34 |         | Yes      |       2 |
| latin7_estonian_cs | latin7  | 20 |         | Yes      |       1 |
| latin7_general_cs  | latin7  | 42 |         | Yes      |       1 |
| cp1251_general_cs  | cp1251  | 52 |         | Yes      |       1 |
+--------------------+---------+----+---------+----------+---------+
6 rows in set (0.01 sec)

在我的 Debian MySQL 默认安装中它们并不多。

令人惊讶的是，没有 'utf8_cs' !

但是MySQL有_bin校对也。对订购不是很有用，但对搜索来说已经足够了:

mysql> show collation where collation like '%_bin';

+--------------+----------+----+---------+----------+---------+
| Collation    | Charset  | Id | Default | Compiled | Sortlen |
+--------------+----------+----+---------+----------+---------+
| big5_bin     | big5     | 84 |         | Yes      |       1 |
| dec8_bin     | dec8     | 69 |         | Yes      |       1 |
| cp850_bin    | cp850    | 80 |         | Yes      |       1 |
| hp8_bin      | hp8      | 72 |         | Yes      |       1 |
| koi8r_bin    | koi8r    | 74 |         | Yes      |       1 |
| latin1_bin   | latin1   | 47 |         | Yes      |       1 |
| latin2_bin   | latin2   | 77 |         | Yes      |       1 |
| swe7_bin     | swe7     | 82 |         | Yes      |       1 |
| ascii_bin    | ascii    | 65 |         | Yes      |       1 |
| ujis_bin     | ujis     | 91 |         | Yes      |       1 |
| sjis_bin     | sjis     | 88 |         | Yes      |       1 |
| hebrew_bin   | hebrew   | 71 |         | Yes      |       1 |
| tis620_bin   | tis620   | 89 |         | Yes      |       1 |
| euckr_bin    | euckr    | 85 |         | Yes      |       1 |
| koi8u_bin    | koi8u    | 75 |         | Yes      |       1 |
| gb2312_bin   | gb2312   | 86 |         | Yes      |       1 |
| greek_bin    | greek    | 70 |         | Yes      |       1 |
| cp1250_bin   | cp1250   | 66 |         | Yes      |       1 |
| gbk_bin      | gbk      | 87 |         | Yes      |       1 |
| latin5_bin   | latin5   | 78 |         | Yes      |       1 |
| armscii8_bin | armscii8 | 64 |         | Yes      |       1 |
| utf8_bin     | utf8     | 83 |         | Yes      |       1 |
| ucs2_bin     | ucs2     | 90 |         | Yes      |       1 |
| cp866_bin    | cp866    | 68 |         | Yes      |       1 |
| keybcs2_bin  | keybcs2  | 73 |         | Yes      |       1 |
| macce_bin    | macce    | 43 |         | Yes      |       1 |
| macroman_bin | macroman | 53 |         | Yes      |       1 |
| cp852_bin    | cp852    | 81 |         | Yes      |       1 |
| latin7_bin   | latin7   | 79 |         | Yes      |       1 |
| cp1251_bin   | cp1251   | 50 |         | Yes      |       1 |
| cp1256_bin   | cp1256   | 67 |         | Yes      |       1 |
| cp1257_bin   | cp1257   | 58 |         | Yes      |       1 |
| geostd8_bin  | geostd8  | 93 |         | Yes      |       1 |
| cp932_bin    | cp932    | 96 |         | Yes      |       1 |
| eucjpms_bin  | eucjpms  | 98 |         | Yes      |       1 |
+--------------+----------+----+---------+----------+---------+
35 rows in set (0.00 sec)

在下面的示例中，我使用 utf8 编码和 utf8_bin 排序规则创建了一个表。请注意，您可以改为在每列的基础上更改编码/排序规则。

mysql> create TABLE user (username CHAR(20), password CHAR(20)) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into user value("ÉleCTeur", "ÉleCTeur");
Query OK, 1 row affected (0.00 sec)


mysql> select count(*) from user where username = "ÉleCTeur" and password="ÉleCTeur";
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where username = "éleCTeur" and password="éleCTeur";
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)