Implementing Fuzzy Search on Encrypted Sensitive Fields in MySQL with Spring Boot

When sensitive fields like phone numbers, ID numbers, or addresses are stored encrypted in a database, traditional SQL LIKE queries cannot be used because the stored ciphertext does not match the plaintext search term.

The article presents a personnel‑management scenario where fields real_name , phone , and id_number need fuzzy search while remaining encrypted.

Solution approaches

Decrypt all data in memory and filter – simple but memory‑intensive.

Plain‑text mapping table – defeats encryption purpose.

Database‑side decryption in the query, e.g. SELECT * FROM sys_person WHERE AES_DECRYPT(phone,'key') LIKE '%0537' – works but cannot use indexes.

Tokenized ciphertext mapping table – the recommended method.

The tokenized approach creates a separate mapping table that stores encrypted substrings (tokens) of the sensitive value together with the primary key of the original record. During registration, the plaintext value is split into fixed‑length substrings (e.g., four‑character tokens), each token is encrypted, and the concatenated ciphertext is saved in the mapping table.

Example table definition:

create table if not exists sys_person_phone_encrypt (
    id bigint auto_increment comment '主键' primary key,
    person_id int not null comment '关联人员信息表主键',
    phone_key varchar(500) not null comment '手机号码分词密文'
) comment '人员的手机号码分词密文映射表';

Key Java code snippets (Spring Boot, MyBatis, Hutool) illustrate the registration flow, token generation, encryption/decryption utilities, and the fuzzy‑search query.

public Person registe(Person person) {
    this.personDao.insert(person);
    String phone = this.decrypt(person.getPhoneNumber());
    String phoneKeywords = this.phoneKeywords(phone);
    this.personDao.insertPhoneKeyworkds(person.getId(), phoneKeywords);
    return person;
}

private String phoneKeywords(String phone) {
    return this.keywords(phone, 4);
}

private String keywords(String word, int len) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < word.length(); i++) {
        int start = i;
        int end = i + len;
        String sub1 = word.substring(start, end);
        sb.append(this.encrypt(sub1));
        if (end == word.length()) {
            break;
        }
    }
    return sb.toString();
}

public String encrypt(String val) {
    byte[] key = SecureUtil.generateKey(SymmetricAlgorithm.DES.getValue(), "fanfu-csdn".getBytes()).getEncoded();
    SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.DES, key);
    return aes.encryptBase64(val);
}

public String decrypt(String val) {
    byte[] key = SecureUtil.generateKey(SymmetricAlgorithm.DES.getValue(), "fanfu-csdn".getBytes()).getEncoded();
    SymmetricCrypto aes = new SymmetricCrypto(SymmetricAlgorithm.DES, key);
    return aes.decryptStr(val);
}

Fuzzy query implementation encrypts the search keyword, looks up matching rows in the token mapping table, retrieves the corresponding primary keys, and finally queries the main table:

public List
getPersonList(String phoneVal) {
    if (phoneVal != null) {
        return this.personDao.queryByPhoneEncrypt(this.encrypt(phoneVal));
    }
    return this.personDao.queryList(phoneVal);
}
SELECT * FROM sys_person WHERE id IN (
        SELECT person_id FROM sys_person_phone_encrypt
        WHERE phone_key LIKE concat('%', #{phoneVal}, '%')
    )

The article also lists environment configuration (JDK 1.8, Spring Boot 2.3.9, MyBatis), Maven dependencies for Spring AOP and Hutool, and provides links to reference implementations from major Chinese e‑commerce platforms.