Is MySQL Case-Sensitive?

08/03/2021

Contents

In this article, you will learn if MySQL is case-sensitive.

Is MySQL Case-Sensitive?

MySQL is generally a case-sensitive database management system. This means that MySQL treats uppercase and lowercase letters as distinct from each other. However, there are some exceptions to this rule, depending on the database configuration and collation settings. Here’s a breakdown of how case-sensitivity works in MySQL:

Identifiers (table names, column names, etc.) are case-sensitive by default

In MySQL, identifiers such as table names, column names, and aliases are case-sensitive by default. This means that if you create a table with the name “my_table” in lowercase letters, you must use the same case when you reference the table in SQL statements.

For example, the following SQL statement will not work if the table name was created in lowercase:

SELECT * FROM My_Table;

String comparisons are case-sensitive by default

When you compare two strings in MySQL, the comparison is case-sensitive by default. This means that “apple” is considered different from “Apple”.

For example, the following SQL statement will not match any rows if the data in the “name” column is stored in uppercase:

SELECT * FROM my_table WHERE name = 'apple';

Collation settings can affect case-sensitivity

Collation settings determine how string comparisons and sorting are performed in MySQL. Some collation settings are case-insensitive, meaning that string comparisons and sorting ignore case distinctions. Other collation settings are case-sensitive, meaning that string comparisons and sorting consider case distinctions.

For example, the collation setting “utf8_general_ci” is case-insensitive, while “utf8_bin” is case-sensitive. If you use “utf8_general_ci” for the “name” column in the “my_table” table, the following SQL statement will match rows regardless of case:

SELECT * FROM my_table WHERE name = 'apple';

However, if you use “utf8_bin” for the “name” column, the following SQL statement will only match rows with “apple” in the exact case:

SELECT * FROM my_table WHERE name = 'apple';