A case-sensitive mysql Solution

Today, a colleague told me that the account and password were not case sensitive when we logged on to the game. Later, we found that the account and role creation were not case sensitive. After thinking about the login process, Mysql is case insensitive.

I checked the information online. Mysql supports case-sensitive solutions. Several solutions have been found.

First, modify the database settings to make Mysql support case sensitivity. This solution is too radical and there is too much data in the database. In this way, the change may not cause any problems.

Second, modifying the table structure and character set is similar to the first solution, but the risk is lower.

Third, modify the stored procedure or SQL statement. The advantage is that the database structure is not moved. The disadvantage is that all changes are involved.

After the trade-off, we chose the third solution, which only modifies the two stored procedures for login and creation.

The method is to use the binary keyword of mysql.

BINARY is not a function, but a type conversion operator. It is used to force a string after it to be a BINARY string. It can be understood as case sensitive during string comparison.

The test is as follows:
Mysql> select binary 'abc' = 'abc' ret1, 'abc' = 'abc' ret2;
+ -------- +
| Ret1 | ret2 |
+ -------- +
| 0 | 1 |
+ -------- +
1 row in set (0.00 sec)