如何通过PDO预处理语句在SQL结果中直接高亮搜索关键词？

补充说明：我的问题并非“如何高亮搜索结果”的重复问题，因为我希望直接通过查询而非处理查询结果来实现高亮。之前尝试过在结果里处理，但遇到了大小写不敏感和重音相关的问题。所以想了解是否可通过MySQL结合PDO预处理语句实现，类似带LIKE的查询方式，请勿关闭此问题！

我的数据库mydb中有如下数据：

id | name
---------
1  | abcdef
2  | bcdefg
3  | cdefgh
4  | defghi
5  | efghij

我使用如下PDO预处理查询：

$q = "SELECT * FROM `mydb` WHERE name LIKE :search ;";
$res = $cnx->prepare($q);
$res->bindValue(':search', '%'.$_POST['search'].'%');
$res->execute();

当$_POST['search'] = 'defgh'时，查询结果如下：

id | name
---------
3  | cdefgh
4  | defghi

我想了解是否可通过MySQL和PDO，在查询结果中直接插入包裹搜索关键词的HTML代码？期望得到如下结果：

id | name   | html
----------------
3  | cdefgh | c<mark>defgh</mark>
4  | defghi | <mark>defgh</mark>i

完全可以通过MySQL的字符串处理函数结合PDO实现，以下分两种场景给出方案：

1. 基础场景（区分大小写与重音） #

如果不需要处理大小写和重音匹配，直接使用REPLACE函数即可完成关键词替换：

SQL查询语句 #

SELECT 
    id,
    name,
    REPLACE(name, :search_term, CONCAT('<mark>', :search_term, '</mark>')) AS html
FROM `mydb` 
WHERE name LIKE :search_pattern;

对应PDO代码 #

$searchTerm = $_POST['search'];
$searchPattern = '%' . $searchTerm . '%';

$stmt = $cnx->prepare("
    SELECT 
        id,
        name,
        REPLACE(name, :search_term, CONCAT('<mark>', :search_term, '</mark>')) AS html
    FROM `mydb` 
    WHERE name LIKE :search_pattern
");
$stmt->bindValue(':search_term', $searchTerm);
$stmt->bindValue(':search_pattern', $searchPattern);
$stmt->execute();

2. 进阶场景（忽略大小写与重音） #

针对你提到的大小写不敏感和重音问题，使用REGEXP_REPLACE（MySQL 8.0+支持）配合排序规则实现模糊匹配与高亮：

SQL查询语句 #

SELECT 
    id,
    name,
    REGEXP_REPLACE(
        name,
        :search_regex,
        '<mark>$0</mark>',
        1,
        0,
        'i' -- 开启不区分大小写匹配
    ) COLLATE utf8mb4_unicode_ci AS html -- 忽略重音差异
FROM `mydb` 
WHERE name LIKE :search_pattern COLLATE utf8mb4_unicode_ci;

对应PDO代码 #

$searchTerm = $_POST['search'];
// 转义正则特殊字符，避免匹配出错或注入风险
$escapedSearch = preg_quote($searchTerm, '/');
$searchPattern = '%' . $searchTerm . '%';

$stmt = $cnx->prepare("
    SELECT 
        id,
        name,
        REGEXP_REPLACE(
            name,
            :search_regex,
            '<mark>$0</mark>',
            1,
            0,
            'i'
        ) COLLATE utf8mb4_unicode_ci AS html
    FROM `mydb` 
    WHERE name LIKE :search_pattern COLLATE utf8mb4_unicode_ci
");
$stmt->bindValue(':search_regex', $escapedSearch);
$stmt->bindValue(':search_pattern', $searchPattern);
$stmt->execute();

关键说明 #

• REGEXP_REPLACE的'i'参数开启大小写不敏感匹配，COLLATE utf8mb4_unicode_ci用于忽略重音差异（比如匹配Défgh和defgh）。
• 必须转义正则表达式中的特殊字符（如.、*等），防止匹配逻辑出错或SQL注入。
• 若使用MySQL 5.x版本（不支持REGEXP_REPLACE），建议升级数据库版本，或在应用层处理，但数据库层实现更贴合你的需求。

内容的提问来源于stack exchange，提问作者Aurélien Grimpard