How to Enable Fuzzy Search on Encrypted Phone Numbers in MySQL

Introduction

When designing systems that store personal privacy data such as passwords, ID numbers, bank cards, or phone numbers, encryption is essential to prevent leakage. Common symmetric algorithms include AES, SM4, ChaCha20, 3DES, DES, Blowfish, IDEA, RC5, RC6, and Camellia, with AES and SM4 being the most widely used internationally and domestically.

Because encrypted strings differ significantly from their plaintext, direct fuzzy matching with SQL LIKE is impossible, prompting the need for specialized solutions.

1 Load All Data into Memory

One naive approach is to load all encrypted personal data into memory, decrypt it, and perform fuzzy matching in application code. This is simple and low‑cost but can cause OOM errors and data‑consistency issues when the dataset grows or when multiple server nodes are deployed.

2 Use Database Functions

If the encrypted strings are stored in the database, MySQL's encryption functions can be used directly:

SELECT DES_DECRYPT('U2FsdGVkX1+q7g9npbydGL1HXzaZZ6uYYtXyug83jHA=', '123');

This keeps encryption/decryption consistent at the database layer, but each fuzzy search requires decrypting every row, leading to poor performance on large datasets.

3 Segment Storage

Split a full string into smaller parts (e.g., a phone number into three‑digit groups) and store each segment in a mapping table:

CREATE TABLE `encrypt_value_mapping` (
  `id` bigint NOT NULL COMMENT 'system ID',
  `ref_id` bigint NOT NULL COMMENT 'reference ID',
  `encrypt_value` varchar(255) NOT NULL COMMENT 'encrypted segment'
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='segment encryption mapping';

The table contains id, ref_id (link to the main user table), and encrypt_value (the encrypted segment). When inserting a phone number, store each encrypted segment together with the same transaction to ensure consistency.

Fuzzy search can then be performed on the encrypt_value column, retrieving the corresponding ref_id and joining back to the user table:

SELECT s2.id, s2.name, s2.phone
FROM encrypt_value_mapping s1
INNER JOIN `user` s2 ON s1.ref_id = s2.id
WHERE s1.encrypt_value = 'U2FsdGVkX19Se8cEpSLVGTkLw/yiNhcB'
LIMIT 0, 20;

4 Other Fuzzy Search Scenarios

For additional privacy fields (ID cards, bank cards, etc.), add a type column to distinguish data categories. While this works for small tables, large volumes can cause the mapping table to explode, severely degrading query performance.

5 Add a Dedicated Fuzzy Search Field

A more scalable approach is to add an extra column (e.g., encrypt_phone) to the main user table. Store the concatenated encrypted segments, separated by commas, to prevent accidental matches across segment boundaries.

CREATE TABLE `user` (
  `id` int NOT NULL,
  `code` varchar(20) NOT NULL,
  `age` int NOT NULL DEFAULT '0',
  `name` varchar(30) NOT NULL,
  `height` int NOT NULL DEFAULT '0',
  `address` varchar(30) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `encrypt_phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='user table';

When saving a phone number, split it, encrypt each segment, and join them with commas. Then fuzzy search can be performed directly on encrypt_phone using LIKE:

SELECT id, name, phone
FROM user
WHERE encrypt_phone LIKE '%U2FsdGVkX19Se8cEpSLVGTkLw/yiNhcB%'
LIMIT 0, 20;

The comma separator ensures that unrelated segments do not inadvertently satisfy the search condition. This pattern can be applied to other encrypted privacy fields as well.

In practice, choose the solution that best fits the specific business scenario; there is no universally optimal method.