MySQLで文字列を一括置換！REPLACE関数と正規表現による実用テクニック・完全ガイド│MySQL & MariaDBの世界

1 はじめに
2 MySQLでの文字列置換の基本（REPLACE関数）
3 基本的な使い方・実用例
4 よくある用途別サンプル集
5 応用テクニック・トラブル回避
6 正規表現での文字列置換【MySQL8.0以降限定】
7 他の文字列操作関数との比較・補足
8 パフォーマンスと注意点
9 ケーススタディ：実務での活用例
- 9.1 ケース1：商品説明欄のURL一括修正
- 9.2 ケース2：顧客データのフォーマット統一
10 まとめ・作業チェックリスト
- 10.1 まとめポイント
- 10.2 作業チェックリスト
11 FAQ（よくある質問）
12 関連リンク・参考資料

はじめに

MySQLを使っていると、「テーブルの特定の文字列だけをまとめて置き換えたい」「間違ったデータを一括修正したい」といった場面は、決して珍しくありません。たとえば、ECサイトで商品説明文に使っているURLが変わったときや、過去の入力ミスをまとめて修正したいとき、あるいはフォーマットの統一（例：ハイフンをスラッシュに変換したい等）など、さまざまな用途で「文字列の置換」が必要になります。

本記事では、MySQLで文字列を置換するための具体的な方法やテクニックを、基本から応用まで徹底的に解説します。よく使われる「REPLACE関数」の基本的な使い方はもちろん、実務で役立つ活用例や、複数の置換を効率良く行う方法、正規表現による高度な置換なども詳しく紹介します。

また、MySQLのバージョンによって利用できる関数が異なる点や、誤操作によるデータ消失を防ぐための注意点、パフォーマンス面で気をつけたいポイントなど、現場で本当に役立つ情報を網羅しています。

この記事を読めば、以下のような疑問や課題をすべて解決できます。

MySQLで特定の文字列を簡単に置換する方法は？
複数のパターンを一括で置き換えたい場合はどうすればいい？
正規表現による柔軟な置換は可能？
大量データを一気に更新するときのリスクや注意点は？
失敗を防ぐためのバックアップ方法は？

初心者から実務担当者まで、MySQLで「文字列置換」を確実にマスターしたい方に最適な内容です。

MySQLでの文字列置換の基本（REPLACE関数）

MySQLで文字列を置換したい場合、最もよく使われるのがREPLACE関数です。REPLACE関数は、指定した文字列やカラム内の文字列から特定のパターンを見つけ出し、別の文字列へと一括で置き換えることができます。
この関数は日常的なデータ修正や、データベースの大規模な一括変換作業など、さまざまな場面で活躍します。

REPLACE関数の基本構文

REPLACE(元の文字列, 検索文字列, 置換文字列)

元の文字列：置換対象となる文字列、またはテーブルのカラム名。
検索文字列：置換したい部分。
置換文字列：新しく挿入する文字列。

たとえば、次のように使います。

SELECT REPLACE('I love MySQL!', 'MySQL', 'PostgreSQL');

このクエリは「I love MySQL!」という文字列から「MySQL」を探して「PostgreSQL」に置き換え、
結果は「I love PostgreSQL!」となります。

大文字・小文字の区別について

REPLACE関数は、大文字と小文字を区別して検索・置換します。たとえば「mysql」と「MySQL」は別物として扱われます。
目的の文字列が見つからない場合は、元の文字列がそのまま返ります。

対応するデータ型

REPLACE関数は、CHAR型、VARCHAR型、TEXT型など、基本的な文字列型カラムで利用できます。ただし、BLOB型など特殊なデータ型では意図しない動作となることがあるため注意が必要です。

このように、REPLACE関数はシンプルな書式で直感的に使えるのが大きな魅力です。
次の章からは、このREPLACE関数を使った実際のSQL例や、テーブルデータへの適用方法について解説します。

基本的な使い方・実用例

REPLACE関数の使い方はとてもシンプルですが、実務では「データベース内のカラムに対して一括で文字列を置換したい」というケースが多くなります。ここでは、REPLACE関数を使った基本的な操作方法や、テーブルデータの一括修正方法を具体的なSQL例とともに解説します。

単純なSELECTでの文字列置換例

まずは、特定の文字列に対して置換を行う最も基本的な使い方です。

SELECT REPLACE('Hello, mysql user!', 'mysql', 'MySQL');

このクエリでは、「mysql」を「MySQL」に置き換えた結果、「Hello, MySQL user!」が返されます。

テーブルのカラム値を一括で置換するUPDATE文

実務で頻繁に使うのが、テーブルの特定カラム内の文字列をまとめて置き換える方法です。
たとえば、商品説明文に記載されている旧ドメイン「oldsite.com」を新ドメイン「newsite.com」に全件まとめて修正したい場合は、以下のようなSQLを実行します。

UPDATE products
SET description = REPLACE(description, 'oldsite.com', 'newsite.com');

このSQLは、productsテーブルのdescriptionカラム内にある「oldsite.com」をすべて「newsite.com」に置換します。

実行時の注意点

REPLACEによるUPDATEは、すべてのレコードに対して実行されるため、想定外の部分まで書き換えてしまうリスクがあります。
本番環境で実行する前に、必ずバックアップを取得し、テスト環境で動作確認を行いましょう。

WHERE句で影響範囲を限定する方法

もし一部のデータだけを修正したい場合は、WHERE句を活用します。たとえば、2024年以降に追加された商品だけを対象にする場合は、以下のように書きます。

UPDATE products
SET description = REPLACE(description, 'oldsite.com', 'newsite.com')
WHERE created_at >= '2024-01-01';

このようにすることで、不要なデータの書き換えを防ぐことができます。

REPLACE関数を用いた基本的な使い方を理解することで、日常の運用やデータクリーニング作業が格段に効率化します。

よくある用途別サンプル集

REPLACE関数は、さまざまな現場で「これって置換できないかな？」という場面に重宝します。ここでは、実際によくある用途ごとに具体的なサンプルを紹介します。

1. 誤字・タイプミスの修正

たとえば、データ入力時のよくあるミスを一括で直したい場合に便利です。

UPDATE users
SET profile = REPLACE(profile, 'htto://', 'http://');

このSQLでは、誤って「htto://」となっていた部分を「http://」へ一括修正しています。

2. URLやドメインの一括置換

ウェブサイトのリニューアルや移転などで、古いドメインを新しいものへまとめて置き換えたい場合に活躍します。

UPDATE blog_posts
SET content = REPLACE(content, 'old-domain.jp', 'new-domain.jp');

3. 不要な空白・改行・記号の削除

意図しないスペースや改行コードがデータに紛れ込んでしまった場合も、REPLACEでまとめて削除可能です。

UPDATE addresses
SET zipcode = REPLACE(zipcode, ' ', '');

上記は郵便番号から空白をすべて除去する例です。改行の場合は「’\n’」や「’\r’」を指定します。

4. ハイフン→スラッシュ、全角→半角などの形式統一

データのフォーマット統一も、REPLACEで簡単にできます。

UPDATE products
SET code = REPLACE(code, '-', '/');

全角を半角に一括変換したい場合は、REPLACE関数を何度もネストして使うこともできます。

5. 複数パターンの同時置換

複数パターンを一度に置換したい場合は、REPLACE関数を入れ子（ネスト）にします。

UPDATE contacts
SET note = REPLACE(REPLACE(note, '株式会社', '（株）'), '有限会社', '（有）');

このように書くことで、「株式会社」と「有限会社」を一括して省略表記へ変換できます。

REPLACE関数は「大量の修正が必要」「人手では大変」といった作業を効率化できる、非常に頼もしい機能です。

応用テクニック・トラブル回避

REPLACE関数は非常に便利ですが、使い方次第で思わぬトラブルが起きたり、もっと効率的な運用ができたりします。ここでは、実践的な応用テクニックやトラブルを防ぐためのポイントを解説します。

1. NULL値への対応

REPLACE関数は、置換対象のカラムがNULLの場合、結果もNULLになります。そのため、想定外に置換できていないデータが残ることがあります。NULL値も含めて置換を確実に行いたい場合は、IFNULL関数と組み合わせるのが有効です。

UPDATE users
SET comment = REPLACE(IFNULL(comment, ''), 'NGワード', '***');

このようにすることで、NULLの場合も空文字列として扱い、置換処理が適用されます。

2. 大文字・小文字を区別しない置換

REPLACE関数はデフォルトで大文字・小文字を区別します。同じ内容で大小どちらも置換したい場合は、LOWER関数やUPPER関数と組み合わせて判定し、必要に応じて2回に分けて置換するのが一般的です。

UPDATE articles
SET title = REPLACE(REPLACE(title, 'MySQL', 'MariaDB'), 'mysql', 'MariaDB');

3. 複数語の多段置換（REPLACE関数のネスト）

複数の異なるパターンを一度に置換したい場合は、REPLACE関数をネストして使います。

UPDATE logs
SET message = REPLACE(REPLACE(message, 'error', 'warning'), 'fail', 'caution');

4. UPDATEのWHERE条件で影響範囲を限定する

全データに対して一括更新するのではなく、必要な行だけに限定したい場合はWHERE句を活用しましょう。

UPDATE customers
SET email = REPLACE(email, '@oldmail.com', '@newmail.com')
WHERE registered_at >= '2023-01-01';

5. 必ずバックアップとテスト環境での確認を

REPLACE関数による更新は「元に戻す」ことが困難なケースが多いため、本番環境で実行する前に、必ずバックアップを取りましょう。また、サンプルデータやテスト環境で十分に動作検証を行うことで、リスクを大きく減らすことができます。

REPLACE関数を正しく応用すれば、より安全かつ効率的な文字列置換作業が可能です。

正規表現での文字列置換【MySQL8.0以降限定】

MySQL 8.0以降では、「REPLACE関数」だけでなくREGEXP_REPLACE関数を使って、正規表現による高度な置換が可能になりました。これにより、より柔軟なパターンマッチや複雑なデータクレンジングが効率的に実現できます。

REGEXP_REPLACE関数の基本構文

REGEXP_REPLACE(元の文字列, 正規表現パターン, 置換文字列)

元の文字列：置換対象となる文字列やカラム名。
正規表現パターン：マッチさせたいパターン（例：「[0-9]{3}-[0-9]{4}」など）。
置換文字列：新しく挿入する文字列。

利用例1：電話番号のハイフン除去

電話番号などでハイフンをすべて削除したい場合、次のように書けます。

UPDATE users
SET tel = REGEXP_REPLACE(tel, '-', '');

利用例2：郵便番号の統一フォーマット化

さまざまなフォーマットの郵便番号（例：「123-4567」や「1234567」）を統一したい場合も、正規表現が便利です。

UPDATE addresses
SET zipcode = REGEXP_REPLACE(zipcode, '([0-9]{3})-?([0-9]{4})', '\1-\2');

このSQLでは、「1234567」「123-4567」の両方を「123-4567」形式へ統一します。

利用例3：英数字以外の文字を削除

データの中から英数字以外の文字をまとめて除去したい場合にも使えます。

UPDATE records
SET code = REGEXP_REPLACE(code, '[^a-zA-Z0-9]', '');

このように書くと、codeカラム内の英数字以外のすべての文字が削除されます。

バージョン確認方法

REGEXP_REPLACE関数はMySQL 8.0以降でのみ利用できます。現在使っているMySQLのバージョンは、次のクエリで確認できます。

SELECT VERSION();

5系など古いバージョンをお使いの場合、REGEXP_REPLACEは利用できないため、REPLACE関数やアプリケーション側での置換をご検討ください。

正規表現による置換は、データのパターンが多様な場合や複雑な変換が必要なときに非常に強力です。

他の文字列操作関数との比較・補足

MySQLには文字列の操作に便利な関数が複数用意されています。それぞれ用途や特徴が異なるため、置換や編集作業を行う際には最適な関数を選ぶことが大切です。ここでは「REPLACE」「REGEXP_REPLACE」「INSERT」「CONCAT」など、よく使う関数を比較しながら解説します。

1. REPLACE

用途：指定した文字列やカラムの中から“完全一致”する部分を、別の文字列で置換する。
特徴：大文字小文字を区別し、単純な置換には最も手軽。
例：

  SELECT REPLACE('cat and dog', 'cat', 'fox');
  -- → "fox and dog"

2. REGEXP_REPLACE（MySQL8.0以降）

用途：正規表現パターンにマッチする部分を、まとめて置換できる。
特徴：複雑なパターンマッチ、複数パターンの同時置換、部分的な抽出や編集にも強い。
例：

  SELECT REGEXP_REPLACE('a123b456c', '[a-z]', '');
  -- → "123456"

3. INSERT

用途：指定した位置から、指定した長さ分の文字列を別の文字列で“挿入”する。
特徴：部分的な差し替えやデータ挿入に向くが、置換というより挿入・上書き向き。
例：

  SELECT INSERT('abcdef', 2, 3, 'XYZ');
  -- → "aXYZef"

4. CONCAT

用途：複数の文字列やカラムの内容を“結合”する。
特徴：置換や編集ではなく、文字列同士をつなげたい場合に使用。
例：

  SELECT CONCAT('abc', '123');
  -- → "abc123"

5. SUBSTRING / LEFT / RIGHT

用途：文字列の一部を抽出する。
特徴：部分取り出しやデータ切り出しに最適。
例：

  SELECT SUBSTRING('abcdef', 2, 3);
  -- → "bcd"

簡易比較表

機能	置換	正規表現置換	挿入・上書き	結合	部分抽出
関数	REPLACE	REGEXP_REPLACE	INSERT	CONCAT	SUBSTRING等
パターン対応	×（完全一致のみ）	○（正規表現対応）	×	×	×
バージョン	すべて	8.0以上	すべて	すべて	すべて

このように、用途やバージョンによって使い分けることで、より効率的かつ安全にデータを操作できます。

パフォーマンスと注意点

MySQLで文字列を一括置換する場合、データ量が多いテーブルや本番環境での操作では、思わぬトラブルやパフォーマンス低下が起きることがあります。ここでは、安全に効率良く作業を行うための注意点とパフォーマンスに関するポイントを解説します。

1. 大量データの一括更新は慎重に

REPLACEやREGEXP_REPLACEを使ったUPDATE文は、対象テーブルの全レコードをスキャンして書き換えるため、データ量が多いと実行時間が長くなり、サーバーへの負荷も高くなります。特に数万件～数百万件規模のテーブルでは、実行中に他のクエリのレスポンスが低下したり、最悪の場合ロックやタイムアウトが発生することもあります。

2. インデックスへの影響

UPDATE文によってインデックス付きカラム（例：email、codeなど）の値が変化すると、インデックスの再構築が発生します。これにより、パフォーマンスに影響を与える場合があるので注意しましょう。不要な書き換えを防ぐためにも、必ずWHERE句で対象データを限定することが重要です。

3. トランザクションの活用とロールバック

大量データを一度に書き換える場合は、トランザクションを使うことで、もしエラーや想定外の置換が発生した場合に元に戻す（ロールバック）ことができます。

START TRANSACTION;
UPDATE users SET comment = REPLACE(comment, 'A', 'B') WHERE ...;
-- 問題なければ
COMMIT;
-- 何かあれば
ROLLBACK;

このようにしておけば、安心して作業ができます。

4. 本番環境では必ずバックアップを

大規模な一括更新を行う場合は、必ず事前にバックアップを取得しましょう。万が一、予期せぬデータの破損や消失が起きても、バックアップから元に戻すことができます。

5. バッチ処理や分割実行も有効

処理件数が非常に多い場合、一度に全件を更新するのではなく、LIMIT句を使って分割しながら処理したり、夜間などサーバー負荷の低い時間帯に実行することもおすすめです。

UPDATE logs
SET message = REPLACE(message, 'error', 'info')
WHERE id BETWEEN 1 AND 10000;

このようにして段階的に作業を進めれば、サーバーの負担を分散できます。

パフォーマンスや安全性に配慮しながら文字列置換を行うことで、業務の効率化とトラブル防止の両立が可能です。

ケーススタディ：実務での活用例

ここでは、実際の業務で役立つ「文字列置換」の具体的な活用例を2つ紹介します。どちらも、日々の運用やデータメンテナンスで頻繁に発生するシーンです。実際のクエリとともに、作業の流れや注意点もあわせて解説します。

ケース1：商品説明欄のURL一括修正

あるECサイトで、サイトのリニューアルに伴い全商品説明欄に記載された旧URL（old-shop.com）を新URL（new-shop.jp）へ一括で置換したいケースです。

手順例：

作業前にproductsテーブルのバックアップを取得
置換対象データをWHERE句などで絞り込む（テスト実行も推奨）
UPDATE文を実行して一括修正

実際のSQL例：

UPDATE products
SET description = REPLACE(description, 'old-shop.com', 'new-shop.jp');

注意点：

本番反映前に必ずバックアップとテスト環境での検証を行うこと
URLの前後にスペースや改行が含まれている場合も想定して、正規表現置換（REGEXP_REPLACE）も検討する

ケース2：顧客データのフォーマット統一

顧客データベースで、電話番号や郵便番号などのフォーマットを統一したい場合にも文字列置換は便利です。例えば、すべての電話番号からハイフンを除去して連続した数字にしたい場合は、次のようなクエリを使います。

実際のSQL例：

UPDATE customers
SET tel = REPLACE(tel, '-', '');

また、MySQL 8.0以降なら正規表現でより柔軟なフォーマット修正も可能です。

正規表現の利用例（郵便番号を「123-4567」形式に統一）：

UPDATE customers
SET zipcode = REGEXP_REPLACE(zipcode, '([0-9]{3})-?([0-9]{4})', '\1-\2');

注意点：

置換結果が想定通りかどうか、十分に検証した上で本番データへ反映
影響範囲が大きい場合は、WHERE句で更新対象を段階的に絞る

これらのケーススタディのように、大量データのメンテナンスや定期的なデータクリーニングにもMySQLの文字列置換は大いに活用できます。作業のたびにバックアップや事前検証を徹底することで、失敗やトラブルを未然に防ぎましょう。

まとめ・作業チェックリスト

ここまで、MySQLで文字列を置換する方法について、基本から応用、実務例まで詳しく解説してきました。最後に、記事のポイントを整理し、実際に作業を進める際に役立つチェックリストをまとめます。

まとめポイント

REPLACE関数は、単純な文字列の一括置換に最適。大文字・小文字を区別し、完全一致でのみ動作する。
REGEXP_REPLACE関数（MySQL 8.0以降）は、正規表現を活用した高度なパターン置換ができる。
UPDATE文での一括置換は便利だが、バックアップやテスト環境での動作確認が不可欠。
複数パターンの同時置換にはREPLACE関数のネストが有効。
パフォーマンスやインデックスへの影響、大量データ処理時のサーバー負荷にも十分注意すること。

作業チェックリスト

□ 置換したいパターンと対象カラムを正確に把握できているか？
□ WHERE句などで必要なデータだけを更新するよう絞り込みを行っているか？
□ 本番環境に適用する前に必ずバックアップを取得しているか？
□ テスト環境やダミーデータで実際のクエリの動作確認を済ませているか？
□ データ量やサーバー負荷に応じて、バッチ処理やLIMIT句を活用する計画があるか？
□ 作業後、置換結果が想定通りになっているか十分に確認したか？
□ 使っているMySQLのバージョンに応じて適切な関数を選んでいるか？

このチェックリストに沿って作業を進めることで、安全かつ確実にMySQLの文字列置換を行うことができます。
文字列置換はちょっとした操作ミスが大きなトラブルにつながりやすいため、必ず一つずつ丁寧に確認してから本番反映を行いましょう。

FAQ（よくある質問）

ここでは、「MySQL 文字列置換」に関して多くの人が疑問に思う点や、現場でよく寄せられる質問についてまとめました。作業時の迷いや不安の解消にご活用ください。

Q1. REPLACE()は大文字小文字を区別しますか？

A1: はい、REPLACE関数は大文字と小文字を区別します（ケースセンシティブ）。たとえば「mysql」と「MySQL」は別の文字列として扱われます。同時に両方を置換したい場合は、REPLACE関数を2回ネストするなど工夫が必要です。

Q2. NULL値があるカラムに使うとどうなりますか？

A2: 置換対象のカラムがNULLの場合、REPLACEの結果もNULLとなり、何も変化しません。NULL値も置換したい場合は、IFNULL関数と組み合わせて使用しましょう。

Q3. 複数パターンの置換を一度にできますか？

A3: はい、REPLACE関数をネスト（入れ子）にして複数パターンを同時に置換できます。ただし、置換の順序によって結果が変わることもあるため、意図した通りになるか事前に検証しましょう。

Q4. 正規表現で置換したい場合はどうすればいいですか？

A4: MySQL 8.0以降で利用できるREGEXP_REPLACE関数を使えば、正規表現パターンにマッチした部分を置換できます。バージョンが古い場合は、アプリケーション側での置換や他の方法を検討しましょう。

Q5. REPLACE INTOとは何が違いますか？

A5: REPLACE関数は「文字列の置換」を行うものですが、REPLACE INTOは「既存レコードを削除して新しいレコードを挿入する」INSERT系のSQL文です。まったく異なる用途なので混同しないよう注意してください。

Q6. 置換後に元データを戻せますか？（リカバリ方法）

A6: 一度置換を実行したデータは、元に戻すのが難しい場合がほとんどです。必ず作業前にバックアップを取りましょう。万一失敗した場合は、バックアップからリストア（復元）してください。

Q7. MySQLのバージョン確認方法は？

A7: 次のクエリを実行することで、MySQLのバージョンを確認できます。

SELECT VERSION();

REGEXP_REPLACEなどの関数が使えるかどうか、あらかじめバージョンをチェックしておきましょう。

これらのFAQを参考に、安心して文字列置換作業を進めてください。

MySQLで文字列を一括置換！REPLACE関数と正規表現による実用テクニック・完全ガイド

はじめに

MySQLでの文字列置換の基本（REPLACE関数）

REPLACE関数の基本構文

大文字・小文字の区別について

対応するデータ型

基本的な使い方・実用例

単純なSELECTでの文字列置換例

テーブルのカラム値を一括で置換するUPDATE文

実行時の注意点

WHERE句で影響範囲を限定する方法

よくある用途別サンプル集

1. 誤字・タイプミスの修正

2. URLやドメインの一括置換

3. 不要な空白・改行・記号の削除

4. ハイフン→スラッシュ、全角→半角などの形式統一

5. 複数パターンの同時置換

応用テクニック・トラブル回避

1. NULL値への対応

2. 大文字・小文字を区別しない置換

3. 複数語の多段置換（REPLACE関数のネスト）

4. UPDATEのWHERE条件で影響範囲を限定する

5. 必ずバックアップとテスト環境での確認を

正規表現での文字列置換【MySQL8.0以降限定】

REGEXP_REPLACE関数の基本構文

利用例1：電話番号のハイフン除去

利用例2：郵便番号の統一フォーマット化

利用例3：英数字以外の文字を削除

バージョン確認方法

他の文字列操作関数との比較・補足

1. REPLACE

2. REGEXP_REPLACE（MySQL8.0以降）

3. INSERT

4. CONCAT

5. SUBSTRING / LEFT / RIGHT

簡易比較表

パフォーマンスと注意点

1. 大量データの一括更新は慎重に

2. インデックスへの影響

3. トランザクションの活用とロールバック

4. 本番環境では必ずバックアップを

5. バッチ処理や分割実行も有効

ケーススタディ：実務での活用例

ケース1：商品説明欄のURL一括修正

手順例：

実際のSQL例：

注意点：

ケース2：顧客データのフォーマット統一

実際のSQL例：

正規表現の利用例（郵便番号を「123-4567」形式に統一）：

注意点：

まとめ・作業チェックリスト

まとめポイント

作業チェックリスト

FAQ（よくある質問）

Q1. REPLACE()は大文字小文字を区別しますか？

Q2. NULL値があるカラムに使うとどうなりますか？

Q3. 複数パターンの置換を一度にできますか？

Q4. 正規表現で置換したい場合はどうすればいいですか？

Q5. REPLACE INTOとは何が違いますか？

Q6. 置換後に元データを戻せますか？（リカバリ方法）

Q7. MySQLのバージョン確認方法は？

関連リンク・参考資料

1. MySQL公式ドキュメント

2. REGEXP_REPLACE関数の実践例

4. SQL基礎から学びたい場合

MySQLで文字列と数値を相互変換する方法まとめ｜CAST・CONVERTの使い方と注意点

MySQLで重複データを抽出・削除する方法｜基本から応用まで実践SQLガイド