Why MySQL LIKE Pagination Misses Exact Matches and How to Fix It

Background

When a brand selection component uses a MySQL LIKE fuzzy query together with pagination, an exact‑match brand can end up on a later page. This leads the front‑end to think the brand does not exist and automatically create a duplicate custom brand.

Incident Example

In the example, the brand name 苏三 ("Su San") was already stored in the brand table. The query used was:

select * from brand where name like '%苏三%' order by edit_date desc limit 5;

The first page returned rows that did not contain the exact string "苏三"; the exact match appeared on the second page:

select * from brand where name like '%苏三%' order by edit_date desc limit 5,5;

Because the results were ordered by edit_date descending, newer records appeared first, and the exact‑match record (with an older edit time) fell to page 2.

Why It Happens

The pagination logic assumes that any matching row will appear on the first page. When the ordering column does not prioritize exact matches, the pagination splits the result set, causing the front‑end to miss the exact match.

Possible Solutions

Increase pageSize : Setting a larger page size (e.g., 200) can bring the exact match onto the first page, but it is not a sustainable fix for all queries.

Separate Exact and Fuzzy Queries : First query with name='苏三' to fetch the exact match; if none, fall back to LIKE '%苏三%'. Merge the results manually, placing the exact match at the front. This approach adds code coupling.

Change Ordering Column : Order by id or name instead of edit_date. Ordering by id still may not guarantee the exact match appears first.

Order by String Length : Use CHAR_LENGTH(name) ASC to prioritize shorter names. Example:

select * from brand where name like '%苏三%' order by char_length(name) asc limit 5;

Works for the first page but not for subsequent pages.

Combine Length and Position : Use LOCATE (or INSTR / POSITION) to order by both length and the position of the keyword. Final query:

select * from brand where name like '%苏三%' order by char_length(name) asc, locate('苏三', name) asc limit 5,5;

This places the exact match at the top of the result set, even on later pages.

Final Recommendation

For fuzzy search combined with pagination, customize the ORDER BY clause using MySQL functions such as CHAR_LENGTH and LOCATE (or INSTR, POSITION) to ensure exact matches appear on the first page. This approach avoids changing page size or adding heavy application‑side logic while keeping query performance acceptable.

Images illustrating the query results are omitted for brevity.