Handling Case Sensitivity in MySQL Brand Table to Prevent Duplicate Entries

Preface – When you first see the title you might think it’s a mistake, but the article intentionally explores an unexpected online issue that resonates with many developers.

A user reported that a custom brand named yoyo could not be saved through the product‑creation page. The front‑end provides a searchable dropdown; if the brand is not found, the user can type a new one, which is then sent to the back‑end for insertion.

1. Reproducing the Issue

The problem: the brand yoyo entered by the user is not persisted in the database, even though the UI accepts it.

2. Analyzing the Issue

Running the query select * from brand where `name`='yoyo'; returns no row for the lowercase yoyo, but unexpectedly returns a row for the uppercase YOYO.

The table definition is:

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_general_ci COMMENT='品牌表';

The table uses the InnoDB engine, utf8mb4 charset, and the collation utf8mb4_general_ci. The collation determines how string comparisons are performed.

MySQL collations fall into three groups:

Suffix _ci – case‑insensitive (ignores letter case).

Suffix _cs – case‑sensitive.

Suffix _bin – binary comparison, also case‑sensitive.

Because utf8mb4_general_ci is case‑insensitive, the query for 'yoyo' matches the stored value 'YOYO', which explains why the lowercase brand appears to exist even though it is not stored.

3. How to Solve the Problem

Changing the table collation to utf8mb4_bin would make the comparison case‑sensitive and stop the false match, but that would also allow both yoyo and YOYO to coexist as separate brands, which is undesirable for a reference table.

Instead, the solution focuses on the business logic:

Option 1 – Perform case‑insensitive matching on the front‑end when searching brands (suitable for small datasets).

Option 2 – Replace the front‑end dropdown with a paginated, server‑side search that returns results case‑insensitively (suitable for large datasets).

Given the growing number of brands, the article chooses Option 2: implement a back‑end pagination API that supports case‑insensitive fuzzy search and add a unique index on the name column to enforce uniqueness.

With this change, when a user types yoyo and the database already contains YOYO, the dropdown will display the existing YOYO entry for selection, providing a smoother user experience while preventing duplicate records.

The same case‑insensitivity issue can appear in other attribute‑value tables, so developers should audit similar logic throughout the system.

Additional recommended reading:

Transformers.js: Bringing Front‑End to a New Domain

Why Most Developers Don't Do Independent Projects

Jinshan C++ Interview: Detailed Answers