如何编写MySQL SELECT查询筛选仅含指定字符的单词

需求是编写SELECT语句，从数据库中筛选仅包含指定字符，同时每个字符的使用次数不超过给定重复次数的单词。

示例数据 #

id  word
---------
1    a
2    b
3    aa
4    ab
5    aaa
6    aba
7    abc
8    abcd
9    abac
10   abaca

不同筛选条件的预期结果 #

条件1：指定字符为a、b（各最多使用1次） #

a
b
ab

条件2：指定字符为a、b、a（a最多用2次，b最多用1次） #

a
b
aa
ab
aba

条件3：指定字符为a、b、a、a（a最多用3次，b最多用1次） #

a
b
aa
ab
aaa
aba

错误尝试说明 #

之前用如下语句无法满足需求：

SELECT * FROM `WORDS_table` WHERE `word` LIKE '%a%' AND `word` LIKE '%b%'

该语句会返回所有包含a或b的单词，既无法排除包含其他字符的项（比如abc），也不能限制字符的使用次数。

解决方案 #

1. 纯MySQL语句实现 #

核心思路是两步：先确保单词只包含指定字符，再统计每个字符的出现次数不超过限制。

以条件2（a最多2次，b最多1次）为例：

SELECT word 
FROM WORDS_table 
WHERE 
  -- 仅保留由a和b组成的单词
  word REGEXP '^[ab]+$'
  -- 统计a的出现次数，不超过2次
  AND LENGTH(word) - LENGTH(REPLACE(word, 'a', '')) <= 2
  -- 统计b的出现次数，不超过1次
  AND LENGTH(word) - LENGTH(REPLACE(word, 'b', '')) <= 1;

2. 结合PHP动态生成SQL（更灵活） #

因为需要根据输入的字符数组动态调整条件，用PHP处理更方便：

// 示例输入的指定字符数组
$allowedChars = ['a', 'b', 'a'];
// 统计每个字符的最大允许出现次数
$charLimits = array_count_values($allowedChars);

// 连接数据库（示例用mysqli，建议用PDO更安全）
$conn = mysqli_connect('localhost', 'username', 'password', 'database');

// 生成正则表达式：限制单词仅包含指定字符
$allowedCharStr = implode('', array_keys($charLimits));
$regex = "^[$allowedCharStr]+$";

// 构建WHERE条件
$conditions = [];
$conditions[] = "word REGEXP '$regex'";

foreach ($charLimits as $char => $limit) {
    // 转义字符，避免SQL注入
    $escapedChar = mysqli_real_escape_string($conn, $char);
    $conditions[] = "LENGTH(word) - LENGTH(REPLACE(word, '$escapedChar', '')) <= $limit";
}

// 拼接完整SQL语句
$sql = "SELECT word FROM WORDS_table WHERE " . implode(' AND ', $conditions);

// 执行查询并处理结果
$result = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['word'] . "\n";
}

// 关闭连接
mysqli_close($conn);

注意事项 #

• REGEXP '^[ab]+$'中的^和$确保整个单词都由指定字符组成，不会出现其他字符。
• 计算字符出现次数的公式LENGTH(word) - LENGTH(REPLACE(word, 'a', ''))：替换掉所有a后，长度差就是a的出现次数。
• 务必做好字符转义，或者使用预处理语句，防止SQL注入风险。

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