SQLite中阿拉伯文近似匹配搜索实现（无需REPLACE函数）

核心需求 #

实现阿拉伯语词汇的近似匹配：

• 搜索فاطمه时返回包含فاطمة的记录（匹配ة和ه）
• 搜索احمد时返回包含أحمد的记录（匹配ا和أ）
• 禁止使用REPLACE函数

方案一：自定义整理器（推荐） #

SQLite支持自定义整理规则，通过创建专门处理阿拉伯语变体字符的整理器，可让字符串比较时自动忽略差异，无需手动替换字符，且支持索引优化。

示例（Python环境下实现） #

使用Python的sqlite3模块注册自定义整理器：

import sqlite3

def arabic_collate(a, b):
    # 定义阿拉伯语变体字符映射：将变体转换为统一基准字符
    normalize_map = {
        ord('ة'): ord('ه'),
        ord('أ'): ord('ا'),
        ord('إ'): ord('ا'),
        ord('آ'): ord('ا'),
        # 可按需添加更多变体映射，比如ى和ي等
    }
    
    # 字符串归一化处理
    def normalize(s):
        return s.translate(normalize_map)
    
    # 比较归一化后的字符串
    norm_a = normalize(a)
    norm_b = normalize(b)
    if norm_a < norm_b:
        return -1
    elif norm_a > norm_b:
        return 1
    else:
        return 0

# 连接数据库
conn = sqlite3.connect(':memory:')
# 注册自定义整理器
conn.create_collation('ARABIC_CI', arabic_collate)

# 创建测试表并插入数据
conn.execute('''
CREATE TEMP TABLE Names (
    ID INTEGER,
    Name_AR TEXT
);
''')
data = [
    (1, 'فا طمة 1'),
    (2, 'أحمد 1'),
    (3, 'خديجة 1'),
    (4, 'فا طمة 2'),
    (5, 'أحمد 2'),
    (6, 'خديجة 2'),
    (7, 'فاطمه'),
    (8, 'احمد'),
    (9, 'خديجه'),
]
conn.executemany('INSERT INTO Names (ID, Name_AR) VALUES (?, ?)', data)

# 使用自定义整理器执行模糊搜索
# 搜索"فاطمه"，匹配含"فاطمة"的记录
cursor = conn.execute('SELECT * FROM Names WHERE Name_AR LIKE ? COLLATE ARABIC_CI', ('%فاطمه%',))
print("搜索'فاطمه'的结果：")
for row in cursor:
    print(row)

# 搜索"احمد"，匹配含"أحمد"的记录
cursor = conn.execute('SELECT * FROM Names WHERE Name_AR LIKE ? COLLATE ARABIC_CI', ('%احمد%',))
print("\n搜索'احمد'的结果：")
for row in cursor:
    print(row)

conn.close()

优势 #

1. 无需在查询中编写复杂的REPLACE逻辑，代码更简洁
2. 可为Name_AR列创建基于该整理器的索引，大幅提升搜索效率：

   CREATE INDEX idx_names_ar ON Names(Name_AR COLLATE ARABIC_CI);
   

3. 扩展性强，可随时补充更多阿拉伯语变体字符的映射规则

方案二：自定义归一化函数 #

若无法使用自定义整理器（如受限环境），可创建自定义SQL函数完成字符归一化，替代批量REPLACE操作：

示例（Python环境下实现） #

import sqlite3

def arabic_normalize(s):
    normalize_map = {
        'ة': 'ه',
        'أ': 'ا',
        'إ': 'ا',
        'آ': 'ا',
        'ى': 'ي',
    }
    for orig, repl in normalize_map.items():
        s = s.replace(orig, repl)
    return s

conn = sqlite3.connect(':memory:')
# 注册自定义函数
conn.create_function('AR_NORMALIZE', 1, arabic_normalize)

# 创建表和插入数据（同方案一）
# ...

# 使用自定义函数执行搜索
cursor = conn.execute('SELECT * FROM Names WHERE AR_NORMALIZE(Name_AR) LIKE AR_NORMALIZE(?)', ('%فاطمه%',))
print("搜索结果：")
for row in cursor:
    print(row)

说明 #

• 该函数封装了批量替换逻辑，无需在查询中重复编写REPLACE
• 若需优化性能，可预先将归一化后的字符串存储在单独列并创建索引

注意事项 #

• 阿拉伯语变体字符较多，需根据实际业务需求补充映射规则（如ؤ和و等）
• 自定义整理器和函数的实现需根据使用的编程语言调整（如C、Java等均有对应SQLite扩展方式）

内容的提问来源于stack exchange，提问作者Djemoui