Why MySQL’s Case‑Insensitive Collation Returns Unexpected Duplicate Brands (and How to Fix It)

Introduction

When a user tried to add a custom brand named yoyo , the brand could not be saved even though the database showed no record for the lowercase name. The investigation revealed that the uppercase YOYO entry was being returned, which seemed impossible at first glance.

1. Reproducing the Issue

The product creation page allows users to select an existing brand or type a new one. If the brand appears in the dropdown, it can be chosen; otherwise the input is treated as a custom brand and sent to the backend API for insertion.

In this case, the custom brand yoyo was not persisted.

2. Analyzing the Problem

Running the query: select * from brand where `name`='yoyo'; returned no rows for yoyo but unexpectedly returned a row for YOYO. Checking the brand table definition showed:

CREATE TABLE `brand` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(30) NOT NULL COMMENT '品牌名称',
  `create_user_id` bigint NOT NULL COMMENT '创建人ID',
  `create_user_name` varchar(30) NOT NULL COMMENT '创建人名称',
  `create_time` datetime(3) DEFAULT NULL COMMENT '创建日期',
  `update_user_id` bigint DEFAULT NULL COMMENT '修改人ID',
  `update_user_name` varchar(30) DEFAULT NULL COMMENT '修改人名称',
  `update_time` datetime(3) DEFAULT NULL COMMENT '修改时间',
  `is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除 1：已删除 0：未删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='品牌表';

The table uses the InnoDB engine, utf8mb4 charset, and the collation utf8mb4_general_ci, which is case‑insensitive. This explains why a query for the lowercase string matches the uppercase entry.

Typical collations fall into three groups:

_ci (case‑insensitive)

_cs (case‑sensitive)

_bin (binary comparison)

Because the current collation is case‑insensitive, yoyo and YOYO are considered the same value.

3. How to Resolve the Issue

Changing the collation of the brand table to utf8mb4_bin would make the comparison case‑sensitive, but that would allow duplicate brand records (both yoyo and YOYO) and break existing business logic that expects brands to be case‑insensitive.

Therefore, the proper fix is to keep the case‑insensitive collation and adjust the application logic:

Make the frontend brand search case‑insensitive.

Replace the dropdown with a paginated, server‑side search that performs a case‑insensitive fuzzy match.

For large brand datasets, the second approach is preferred. Additionally, add a unique index on the name column to prevent true duplicates. With this change, when a user types yoyo and the database already contains YOYO, the existing entry will be shown in the dropdown, providing a smoother user experience.

Similar case‑sensitivity problems can occur in other attribute tables, so it is advisable to audit code for case‑insensitive comparisons wherever user‑defined values are involved.