MySQLで禁止・NGワード検索

MySQLを使ってNGワードテーブルに登録されている単語で検索してみた。

NGワードテーブル準備

CREATE DATABASE `test` /*!40100 COLLATE 'utf8mb4_general_ci' */;
USE `test`;
CREATE TABLE `ng` ( `word` VARCHAR(50) NULL DEFAULT NULL ) COMMENT='NGワードテーブル' ENGINE=InnoDB;
INSERT INTO `ng` (`word`) VALUES ('ばか'),('あほ'),('どじ'),('まぬけ');

NGワードテーブル

word
ばか
あほ
どじ
まぬけ

単語検索

SELECT ng.word 
FROM (SELECT 'ばか' AS `value` ) target
INNER JOIN ng ON target.value REGEXP ng.word;
-- INNER JOIN ng ON target.value LIKE CONCAT('%', ng.word ,'%');

カウント用

SELECT COUNT(*) 
FROM (SELECT 'あほどじ' AS `value` ) target
INNER JOIN ng ON target.value REGEXP ng.word;

検証用

-- 取得件数の有無で判定
SELECT ng.word 
FROM (SELECT 'まぬけ' AS `value` ) target
INNER JOIN ng ON target.value REGEXP ng.word LIMIT 1

-- ngflagカラムの0,1で判定
SELECT COUNT(*) AS `ngflag` FROM 
(
    SELECT ng.word 
    FROM (SELECT 'ほげほげ' AS `value` ) target
    INNER JOIN ng ON target.value REGEXP ng.word LIMIT 1
) tmp;

NGワード連結正規表現パターンマッチング編

SELECT (SELECT ':targetValue' REGEXP (SELECT GROUP_CONCAT(word SEPARATOR '|') FROM ng)) AS `ngflag`;

相関サブクエリ編

SELECT COUNT(*) AS `ngflag`
FROM (SELECT ':targetValue' AS `value`) target
WHERE EXISTS (
    SELECT *
    FROM ng 
    WHERE target.value LIKE CONCAT ('%', ng.word, '%')
);

文献をざっと探したところなさそうだったので、できそうだなーと思いやってみたら動いたのでアップしました。
用途としてはユーザ名・記事のタイトル・本文とかをテーブルに格納する前にクエリ1回流せば検証できるので、サーバサイドのプログラムでゴリゴリ書くよりは楽かと思います。