如何在SQLite查询前对数据库列执行Slug处理以实现匹配搜索？

你的核心问题是：输入文本已做重音替换+转小写的slug处理，但查询时数据库的genus、species、synonyms、commonEN列没有做同样处理，导致带重音的数据库内容无法匹配处理后的输入文本。下面提供两种可行方案：

方案一：查询时动态处理数据库列（无需修改表结构） #

SQLite本身没有内置的重音替换函数，你可以通过PHP给SQLite注册自定义函数，把处理重音的逻辑搬到SQL层面，查询时对目标列调用该函数，再和处理后的输入文本匹配。

修改searchscript.php的代码，在初始化PDO后添加自定义函数：

$pdo = new PDO('sqlite:db/myDatabase.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// 注册自定义SQL函数：处理重音并转小写
$pdo->sqliteCreateFunction('slugify', function($str) {
    $accented_array = array(
        'Á'=>'A', 'Â'=>'A', 'Ă'=>'A', 'É'=>'E', 'Í'=>'I', 'Î'=>'I', 'Ó'=>'O', 'Ö'=>'O', 'Ő'=>'O', 'Ú'=>'U', 'Ű'=>'U', 'Ü'=>'U', 'Ș'=>'S', 'Ț'=>'T',
        'á'=>'a', 'â'=>'a', 'ă'=>'a', 'é'=>'e', 'í'=>'i', 'î'=>'i', 'ó'=>'o', 'ö'=>'o', 'ő'=>'o', 'ú'=>'u', 'ű'=>'u', 'ü'=>'u', 'ș'=>'s', 'ț'=>'t'
    );
    $str = strtr($str, $accented_array);
    return strtolower($str);
}, 1); // 1表示该函数接受1个参数

// 修改SQL语句，对每个列调用slugify函数
$sql = 'SELECT genus, species, commonEN FROM myTable 
        WHERE slugify(genus) LIKE ? 
           OR slugify(species) LIKE ? 
           OR slugify(synonyms) LIKE ? 
           OR slugify(commonEN) LIKE ? 
        ORDER BY genus ASC, species ASC';

$stmt = $pdo->prepare($sql);
$stmt->execute(["%".$strict_search."%", "%".$strict_search."%", "%".$strict_search."%", "%".$strict_search."%"]);
$results = $stmt->fetchAll();

这种方案无需修改数据库表，但每次查询都要动态处理列值，数据量大时性能会受影响。

方案二：预先生成slug列（性能更优） #

如果数据量较大，推荐提前给表添加对应的slug列，在插入或更新数据时就处理好重音和小写，查询时直接匹配这些slug列，性能会大幅提升。

步骤1：修改数据库表结构 #

执行SQL语句添加slug列：

ALTER TABLE myTable 
ADD COLUMN genus_slug TEXT,
ADD COLUMN species_slug TEXT,
ADD COLUMN synonyms_slug TEXT,
ADD COLUMN commonEN_slug TEXT;

步骤2：批量更新现有数据的slug列 #

写一段PHP脚本一次性处理现有数据（假设表有id主键，无主键则替换为其他唯一标识字段）：

$pdo = new PDO('sqlite:db/myDatabase.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$accented_array = array(
    'Á'=>'A', 'Â'=>'A', 'Ă'=>'A', 'É'=>'E', 'Í'=>'I', 'Î'=>'I', 'Ó'=>'O', 'Ö'=>'O', 'Ő'=>'O', 'Ú'=>'U', 'Ű'=>'U', 'Ü'=>'U', 'Ș'=>'S', 'Ț'=>'T',
    'á'=>'a', 'â'=>'a', 'ă'=>'a', 'é'=>'e', 'í'=>'i', 'î'=>'i', 'ó'=>'o', 'ö'=>'o', 'ő'=>'o', 'ú'=>'u', 'ű'=>'u', 'ü'=>'u', 'ș'=>'s', 'ț'=>'t'
);

$stmt = $pdo->query('SELECT id, genus, species, synonyms, commonEN FROM myTable');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $genus_slug = strtolower(strtr($row['genus'], $accented_array));
    $species_slug = strtolower(strtr($row['species'], $accented_array));
    $synonyms_slug = strtolower(strtr($row['synonyms'], $accented_array));
    $commonEN_slug = strtolower(strtr($row['commonEN'], $accented_array));
    
    $updateStmt = $pdo->prepare('UPDATE myTable SET genus_slug=?, species_slug=?, synonyms_slug=?, commonEN_slug=? WHERE id=?');
    $updateStmt->execute([$genus_slug, $species_slug, $synonyms_slug, $commonEN_slug, $row['id']]);
}
echo "数据更新完成";

步骤3：修改搜索逻辑 #

在searchscript.php中直接查询slug列：

// 原有的输入处理逻辑不变
$accented_array = array(/* 你的重音替换数组 */);
$required_str = strtr($search_text, $accented_array);
$strict_search = strtolower($required_str);

// 修改SQL语句
$sql = 'SELECT genus, species, commonEN FROM myTable 
        WHERE genus_slug LIKE ? 
           OR species_slug LIKE ? 
           OR synonyms_slug LIKE ? 
           OR commonEN_slug LIKE ? 
        ORDER BY genus ASC, species ASC';

$stmt = $pdo->prepare($sql);
$stmt->execute(["%".$strict_search."%", "%".$strict_search."%", "%".$strict_search."%", "%".$strict_search."%"]);
$results = $stmt->fetchAll();

后续插入新数据时，也要同步生成对应的slug值存入数据库。

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