How Can I Perform Case-Sensitive String Comparisons in MySQL Queries?

Achieving Case-Sensitive String Comparisons in MySQL

Standard MySQL string comparisons are typically case-insensitive. This can be problematic when precise matching is required. Fortunately, MySQL provides a simple method to enforce case sensitivity in your queries.

Implementing Case-Sensitive Queries in MySQL

The BINARY keyword is the key to performing case-sensitive string comparisons. Here's the syntax:

<code class="language-sql">SELECT * FROM `table` WHERE BINARY `column` = 'value';</code>

Let's illustrate with an example:

Imagine a table structured like this:

<code class="language-sql">CREATE TABLE `test` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `test` (`name`) VALUES ('John'), ('JOHN'), ('JoHn');</code>

A query without BINARY would match all three entries:

<code class="language-sql">SELECT * FROM `test` WHERE `name` = 'john';</code>

However, by adding BINARY, the query becomes case-sensitive, returning only the exact match:

<code class="language-sql">SELECT * FROM `test` WHERE BINARY `name` = 'john';</code>

This technique ensures accurate case-sensitive comparisons, eliminating potential errors caused by case-insensitive matching.

The above is the detailed content of How Can I Perform Case-Sensitive String Comparisons in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!