MySQL case-sensitivity solution
1. MySQL case-sensitivity control
MySQL controls case sensitivity through the lower_case_table_names parameter, which is under the [mysqld] node. The author has taken a screenshot from the official website for the specific meaning.
Note:
① About the lower_case_table_names parameter to control the case sensitivity of table names or database names .
② The default value is 0 under Unix, which means case-sensitive. The default value is 1 under Windows, which means insensitive. The default value is 2 on macOS, which means case-sensitive storage but converted to lowercase when querying.
③ For case-insensitive systems (Windows and macOS), this field cannot be set to 0.
2. Problems
#1. Linux defaults to lower_case_table_names=0, which is case sensitive. Note: The author’s data version is 5.7.22.
Note: lower_case_file_system: OFF means case-sensitive, ON means case-insensitive. lower_case_table_names: 0 means case-sensitive, 1 means case-insensitive.
#2. Specific demonstration.
Note: Create a test table and insert a record.
Case 1:
analyze:
By comparing the two sets of queries, we can see that under Linux, MySQL is case-sensitive to table names by default.
Case 2:
analyze:
Get Harries’s stories in your inbox
Join Medium for free to get updates from this writer.
The same results are obtained by searching with ‘Aa’ and ‘aa’, which indicates that the field content is converted (uppercase to lowercase) during the query, resulting in this phenomenon.
Case 3:
analyze:
When querying, the results can also be queried for the field ‘Name’, indicating that the query is not case sensitive to the field.
Combining the above cases, we can draw the following conclusions:
① By default, Linux is case-sensitive to database and table names because the Linux system itself is case-sensitive.
②The field content is case insensitive.
③Column names are case insensitive.
The above-mentioned problems still have corresponding impacts on actual business, so corresponding solutions should be found.
3. Solutions for case-insensitive field content and column names
① When creating a table, mark the field as binary . Binary fields are case sensitive.
② Add binary before the field in the query condition, but this is not recommended because it will invalidate the field index .
③ Set the collation rules ( collate ) when creating the table .
collate rules:
*_bin : indicates binary case sensitive collation, which means it is case sensitive .
*_ci: case insensitive collation, which is not case sensitive .
④ If the proofreading rule does not use *_bin when creating the table, you can directly modify the properties of the field .
alter table test3 modify name varchar ( 20 ) collate utf8_bin;
IV. Conclusion
As long as you specify collate as utf8_bin when creating a table , case sensitivity can be achieved. If it is not specified when creating the table, you can modify the collation rules of the field to achieve case sensitivity.
