How to query upper and lower case in MySQL

The problem with capitalization in MySQL

The problem with capitalization in MySQL can be traced back to the design of the database. MySQL is case-sensitive for identifiers, including table names, column names, variable names, etc. This can cause us a lot of trouble in some cases.

For example, when we create a table, we specify a table name student. If we later use the SELECT statement to query the table, we cannot use Student or STUDENT and other similar case forms, otherwise the MySQL database will return error message.

How to perform case query in MySQL

1. Use BINARY keyword

In progress When querying, use the BINARY keyword to make MySQL case-insensitive. For example, we can use the following statement to query the student table:

SELECT * FROM student WHERE BINARY name = 'Tom';

In this way, MySQL will return the correct result regardless of the case of the name. Although this approach can solve the problem, using the BINARY keyword in a relatively large data set may affect the performance of the query, so the solution to the problem is not elegant.

2. Use the LOWER function

To solve the case problem, you can use the LOWER function in MySQL to query. The LOWER function converts a string to lowercase letters. For example, we can use the following statement to query the student table:

SELECT * FROM student WHERE LOWER(name) = 'tom';

In this way, MySQL will convert all strings in the name field into lowercase, and then compare them with 'tom'. Using this approach, we can avoid the query performance impact of using the BINARY keyword.

The above is the detailed content of How to query upper and lower case in MySQL. For more information, please follow other related articles on the PHP Chinese website!