【データ修正の救世主】MySQL REPLACE関数で効率的に複数レコードの文字列を置換する方法

2024-05-15

MySQLのREPLACE関数で複数レコードの文字列を置き換える方法

基本的な構文

REPLACE(文字列, 置換対象, 置換後)

文字列: 置換対象を含む文字列
置換対象: 置き換えたい文字列
置換後: 置換対象 を置き換える文字列

例：テーブル内のすべての「AAA」を「BBB」に置き換える

UPDATE テーブル名
SET カラム名 = REPLACE(カラム名, 'AAA', 'BBB');

複数回の置換

REPLACE関数を複数回ネストさせて、複数の文字列を同時に置き換えることもできます。

REPLACE(REPLACE(文字列, 'AAA', 'BBB'), 'CCC', 'DDD');

大文字小文字の区別

REPLACE関数は、デフォルトで大文字小文字を区別します。大文字小文字を区別せずに置換するには、LOWER関数またはUPPER関数を使用して、置換対象と置換後の文字列をすべて小文字または大文字に変換してから置換する必要があります。

UPDATE テーブル名
SET カラム名 = REPLACE(LOWER(カラム名), 'aaa', 'BBB');

注意点

REPLACE関数は、元の文字列を完全に置き換えます。部分的な置換には、SUBSTRING_INDEX関数とCONCAT関数などを組み合わせて使用する必要があります。
REPLACE関数は、更新対象のレコードのみを置き換えます。既存のデータに影響を与えないように注意する必要があります。

上記以外にも、REPLACE関数と組み合わせて使用できる様々な関数があります。具体的な置換処理については、状況に合わせて適切な関数を選択してください。

テーブル users には、名前、メールアドレス、電話番号を含むレコードが格納されています。
メールアドレスと電話番号の形式が不統一で、修正する必要があります。

修正内容

メールアドレス：すべての小文字に変換し、"@example.com" をドメインに統一する
電話番号：ハイフンを削除し、先頭文字を "0" に統一する

テーブル構造

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone_number VARCHAR(255) NOT NULL
);

修正前データ

id	name	email	phone_number
1	田中太郎	[email protected]	080-1234-5678
2	佐藤花子	[メールアドレスを削除しました]	090-9876-5432
3	鈴木一郎	[メールアドレスを削除しました]	070-3333-1111

id	name	email	phone_number
1	田中太郎	[メールアドレスを削除しました]	08012345678
2	佐藤花子	[メールアドレスを削除しました]	09098765432
3	鈴木一郎	[メールアドレスを削除しました]	07033331111

修正コード

UPDATE users
SET email = LOWER(REPLACE(email, '@', '@example.com')),
    phone_number = REPLACE(REPLACE(phone_number, '-', ''), 0, 1);

コード解説

UPDATE users：修正対象のテーブルを指定します。
SET email = LOWER(REPLACE(email, '@', '@example.com'))：
- LOWER(email)：メールアドレスをすべて小文字に変換します。
- REPLACE(email, '@', '@example.com')：@ を @example.com に置き換えます。
phone_number = REPLACE(REPLACE(phone_number, '-', ''), 0, 1)：
- REPLACE(phone_number, '-', '')：電話番号のハイフンを削除します。

注意事項

このコードはあくまでもサンプルであり、実際の状況に合わせて修正する必要があります。
データのバックアップを取ってから修正作業を行うことをお勧めします。

MySQLで複数レコードの文字列を置き換えるその他の方法

SUBSTRING_INDEX関数とCONCAT関数

REPLACE関数では部分的な置換ができないという制限がありましたが、SUBSTRING_INDEX関数とCONCAT関数を使用することで、部分的な置換を実現することができます。

UPDATE テーブル名
SET カラム名 = CONCAT(SUBSTRING_INDEX(カラム名, 'AAA', 4), 'BBB', SUBSTRING(カラム名, 6));

UPDATE文とWHERE句

WHERE句を使用して、置換対象となるレコードを絞り込むことができます。

UPDATE users
SET email = '[email protected]'
WHERE name = '田中太郎';

REPLACE関数とCASE式

CASE式を使用して、置換対象となる文字列に応じて異なる置換文字列を指定することができます。

例：テーブル内の「AAA」を「BBB」、「BBB」を「CCC」に置き換える

UPDATE テーブル名
SET カラム名 = REPLACE(カラム名,
  CASE
    WHEN 'AAA' THEN 'BBB'
    WHEN 'BBB' THEN 'CCC'
  END
);

ストアドプロシージャ

複雑な置換処理の場合は、ストアドプロシージャを作成して処理をカプセル化することができます。

例：複数の置換処理をまとめて実行するストアドプロシージャ

CREATE PROCEDURE replace_strings()
BEGIN
  UPDATE users
  SET email = LOWER(REPLACE(email, '@', '@example.com')),
      phone_number = REPLACE(REPLACE(phone_number, '-', ''), 0, 1);

  UPDATE products
  SET description = REPLACE(description, '旧商品', '新商品');
END;

CALL replace_strings();