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