mysql 敏感字符_如何在MySQL上對SQL大小寫敏感的字符串進行比較?

18

Answer posted Craig White, has big performance penalty

回答發布克雷格懷特，有很大的表現懲罰

SELECT * FROM `table` WHERE BINARY `column` = 'value'

because it don't use indexes. So, either you need to change the table collation like mention here https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html.

因為它不使用索引。因此，您需要更改表排序，比如這里提到的https://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html。

OR

或

Easiest fix, you should use a BINARY of value.

最簡單的方法，應該使用二進制的值。

SELECT * FROM `table` WHERE `column` = BINARY 'value'

Eg.

如。

mysql> EXPLAIN SELECT * FROM temp1 WHERE BINARY col1 = "ABC" AND col2 = "DEF" ;

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | temp1 | ALL | NULL | NULL | NULL | NULL | 190543 | Using where |

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

VS

VS

mysql> EXPLAIN SELECT * FROM temp1 WHERE col1 = BINARY "ABC" AND col2 = "DEF" ;

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+

| 1 | SIMPLE | temp1 | range | col1_2e9e898e | col1_2e9e898e | 93 | NULL | 2 | Using index condition; Using where |

+----+-------------+-------+-------+---------------+---------------+---------+------+------+------------------------------------+

enter code here

1 row in set (0.00 sec)

一組一行(0.00秒)