Fuzzy Company Name Matching Using MySQL Regular Expressions

This article presents a business scenario involving a company approval workflow with two roles: business and administrator. The core requirement is to prevent duplicate company entries by matching company names using fuzzy matching techniques.

The technical implementation focuses on three key steps: extracting company name key information, performing word segmentation, and matching against existing database records. Due to the small system scale, the solution avoids introducing Elasticsearch and instead leverages MySQL's built-in capabilities.

MySQL provides three fuzzy search methods: LIKE matching (requires exact field match), RegExp regular expression matching (requires pattern presence), and Fulltext indexing (requires specific column types). For this use case, RegExp matching is chosen due to its flexibility in pattern matching, despite slightly lower performance compared to full-text indexing.

The implementation includes preprocessing company names by removing administrative regions, parentheses, and company-related keywords like "Group", "Shares", "Limited", etc. A comprehensive address utility class handles Chinese administrative divisions. The IKAnalyzer is integrated for Chinese word segmentation, with configuration in pom.xml and supporting classes.

The matching logic uses regular expressions to find similar company names and sorts results by match frequency using a custom SQL query that calculates the ratio of matched keywords to total name length. This ensures companies with more matching keywords appear first in the results.

Core code demonstrates preprocessing, word segmentation, and matching implementation, with detailed SQL queries showing how to achieve fuzzy matching and sorting by relevance within MySQL.