Home > Database > Mysql Tutorial > body text

How to query upper and lower case in MySQL

王林
Release: 2023-06-03 08:04:10
forward
1471 people have browsed it

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';
Copy after login

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.

  1. 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';
Copy after login

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!

Related labels:
source:yisu.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template