MySQL LOAD DATA INFILEを使ってフィールド内のテキストを置換する

2024-04-06

MySQLでフィールド内のテキストを検索置換する方法

REPLACE関数を使う

概要

REPLACE関数は、文字列中の指定された部分文字列を別の文字列に置換します。

構文

REPLACE(str, old_str, new_str)

str: 置換対象の文字列

例

-- テーブル名：`users`
-- フィールド名：`name`

-- "田中" を "佐藤" に置換
UPDATE users SET name = REPLACE(name, '田中', '佐藤');

-- "太郎" を "二郎" に置換
UPDATE users SET name = REPLACE(name, '太郎', '二郎') WHERE name LIKE '%太郎%';

注意点

REPLACE関数は、大文字と小文字を区別します。
ワイルドカード文字（%、_）を使用できます。
WHERE句で条件を指定して、特定のレコードのみを置換できます。

UPDATE文とCASE式を使う

CASE式は、条件式に基づいて異なる値を返す式です。UPDATE文と組み合わせて、フィールド内のテキストを置換できます。

UPDATE table_name
SET field_name = CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE value3
END
WHERE condition;

-- テーブル名：`users`
-- フィールド名：`gender`

-- 男性を "男"、女性を "女" に置換
UPDATE users
SET gender = CASE
    WHEN gender = 'male' THEN '男'
    WHEN gender = 'female' THEN '女'
    ELSE gender
END;

CASE式は、複数の条件を処理できます。
ELSE句は、いずれの条件にも合致しない場合の値を指定します。

MySQLでフィールド内のテキストを検索置換するには、REPLACE関数またはUPDATE文とCASE式を使用できます。それぞれの方法の特徴を理解して、目的に合った方法を選択してください。

上記の例は基本的なものです。実際の使用例は、状況によって異なります。
テスト環境で動作確認してから、本番環境で実行してください。

REPLACE関数を使う

-- テーブル名：`users`
-- フィールド名：`name`

-- "田中" を "佐藤" に置換
UPDATE users SET name = REPLACE(name, '田中', '佐藤');

-- "太郎" を "二郎" に置換
UPDATE users SET name = REPLACE(name, '太郎', '二郎') WHERE name LIKE '%太郎%';

-- 全ての "a" を "A" に置換
UPDATE users SET name = REPLACE(name, 'a', 'A');

UPDATE文とCASE式を使う

-- テーブル名：`users`
-- フィールド名：`gender`

-- 男性を "男"、女性を "女" に置換
UPDATE users
SET gender = CASE
    WHEN gender = 'male' THEN '男'
    WHEN gender = 'female' THEN '女'
    ELSE gender
END;

-- 役職を "役職名" に置換
UPDATE users
SET role = CASE
    WHEN role = 'admin' THEN '管理者'
    WHEN role = 'editor' THEN '編集者'
    ELSE role
END;

上記のサンプルコードは、MySQL 5.7.32で動作確認しています。
テーブルやフィールド名は、実際の環境に合わせて変更してください。

MySQLでフィールド内のテキストを検索置換するその他の方法

REGEXP_REPLACE関数を使う

REGEXP_REPLACE関数は、正規表現を使用して文字列を置換します。

REGEXP_REPLACE(str, pattern, replacement)

pattern: 置換対象のパターン
replacement: 置換後の文字列

-- テーブル名：`users`
-- フィールド名：`email`

-- 末尾の数字を "00" に置換
UPDATE users SET email = REGEXP_REPLACE(email, '[0-9]+$', '00');

-- "http://" を "https://" に置換
UPDATE users SET email = REGEXP_REPLACE(email, '^http://', 'https://');

複雑なパターンを使用すると、処理速度が遅くなる可能性があります。

Perl互換正規表現を使う

MySQL 8.0以降では、Perl互換正規表現を使用できます。

REGEXP_REPLACE(str, pattern, replacement, flags)

flags: 正規表現オプション

-- テーブル名：`users`
-- フィールド名：`name`

-- 大文字と小文字を区別せずに "田中" を "佐藤" に置換
UPDATE users SET name = REGEXP_REPLACE(name, '田中', '佐藤', 'i');

-- 全ての "a" を "A" に置換
UPDATE users SET name = REGEXP_REPLACE(name, 'a', 'A', 'g');

Perl互換正規表現は、従来の正規表現とは異なる構文を使用します。

LOAD DATA INFILEを使う

LOAD DATA INFILEを使用して、CSVファイルからデータを読み込み、フィールド内のテキストを置換できます。

手順

置換後のテキストを含むCSVファイルを作成します。
LOAD DATA INFILEを使用して、CSVファイルをMySQLデータベースに読み込みます。

-- テーブル名：`users`

-- CSVファイル：`users.csv`

-- name,email
-- 佐藤,[email protected]
-- 山田,[email protected]

LOAD DATA INFILE 'users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;