High Case sensitivity and performance of mysql queries
Mysql queries are case-insensitive by default, for example:
1 2 3 4 5 6 7 |
<A href = "/tags. php/select/" target = "_ blank"> select </a> * from table_name where a like 'a %' Select * from table_name where a like 'a %' Select * from table_name where a like 'a %' Select * from table_name where a like 'a %' |
The results are the same.
To make mysql queries case sensitive, you can:
1 2 3 4 5 6 7 |
Select * from table_name where binary a like 'a %' Select * from table_name where binary a like 'a %' Select * from table_name where binary a like 'a %' Select * from table_name where binary a like 'a %' |
You can also identify a table when creating it.
1 2 3 4 5 6 7 8 9 10 11 |
Create table table_name ( A varchar (20) binary ) Create table table_name ( A varchar (20) binary ) |
Test 30 W data
1 |
SELECT * FROM 'tablename' WHERE (BINARY weixin = 'value') LIMIT 1; |
Indexing is not supported. It is not recommended to consider the query efficiency. The preceding SQL statements do not have any problems at first glance, but when there is more data in the table, the problem will be highlighted. If no index is used, the query efficiency will be very low.
Supports indexing and high query efficiency (recommended)
1 |
SELECT * FROM 'tablename' WHERE weixin = 'value' COLLATE utf8_bin LIMIT 1; |
Query analysis:
The above description clearly shows the fundamental difference between the two SQL statements in efficiency. Use binary with caution.