MySQL5.7で複数の文字列を同時に置換するSQL

文字列を同時に置換するSQL

MySQL8.0ではREGEXP_REPLACE()を利用して簡単に複数の文字列を同時に置換することができるが、MySQL5.7では利用できなかったため、以下で実施しました。

UPDATE 
    accounts
SET 
    description = REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(description, 'テスト禁止語句', '〇'), 
                'サンプル', 
                '〇'
            ), 
            'テキスト', 
            '〇'
        ), 
        'テスト', 
        '〇'
    ) 
WHERE 
    description REGEXP 'テスト|サンプル|テキスト|テスト禁止語句';

実際は対象の文字列が多かったため、REPLACE(を追加していった。手動で行うのは大変ですが、ChatGPTの利用（「テスト|サンプル|テキスト|テスト禁止語句|文字列A|文字列B|・・・バージョン作成して」と指示を出すこと）で問題なく作成できました。

指定する順番

注意点として長い文字列から順に指定する必要があります。以下の場合、「テスト」が最初に「〇」に置き換えられてしまうため、「テスト禁止語句」は「〇禁止語句」となってしまいます。

UPDATE 
    accounts 
SET 
    description = REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(description, 'テスト', '〇'), 
                'サンプル', 
                '〇'
            ), 
            'テキスト', 
            '〇'
        ), 
        'テスト禁止語句', 
        '〇'
    ) 
WHERE 
    description REGEXP 'テスト|サンプル|テキスト|テスト禁止語句';

実行時間

REPLACEが増えても実行時間への影響はなく、以下を実行するのとほぼ同じでした。

SELECT *
FROM accounts
WHERE description REGEXP 'テスト|サンプル|テキスト|テスト禁止語句';

レコード数が多くてもREPLACEが見るのは、1つのdescription文字列内であるため、description文字列が非常に長い場合でなければ特に影響はありませんでした。ただし、レコード数が多い場合はWHEREで絞る箇所に時間がかかると考えられるので、DBをロックする場合は複数に分割して上記のUPDATE文を実行する方が良さそうです。