2026.10.07
- 技術 💻
テストデータの作り方｜生成SQLとマスキング手法一覧・匿名化の手順
テストデータの作り方は、「SQLで生成する」が基本で、本番データを加工して使うのは、生成では再現しにくい性能やデータの偏りを確かめたいときに限ります。 本番データを使う場合は、項目ごとにマスキングの方法を決めてから取り出します。
この記事では、100万件を生成するSQL、項目別のマスキングSQL、加工後に元の値が残っていないことを確かめる検証SQLを、PostgreSQL と MySQL で実際に流した結果つきで載せています。検証SQLを流したところ、日付ずらしで元と同じ日付の行が残っていることが見つかったので、その直し方も書きました。
確認環境：PostgreSQL 16.15（Ubuntu 24.04、pgcrypto 拡張）、MySQL 8.0.46。確認日は 2026-10-05。所要時間はこの環境での参考値です。例に出てくる氏名・メールアドレス・電話番号・郵便番号はすべて架空の値で、「本番データ」にあたる表も生成したものです。
この記事は技術的な方法の説明です。個人情報の取り扱いは、自社の規程と法務の確認に従ってください。
この記事の結論
- まず生成データで足りるかを考える。 機能の確認・境界値・異常系は、生成データと手で作る数件で足ります。
- 本番データの利用は、性能試験やデータの偏りの再現が必要な場合に限る。
- 使うなら、項目ごとにマスキング方法を決めた表を先に作り、加工してから取り出す。
- 加工後は検証SQLで「元と同じ値が残っている件数」を数える。目で見るだけでは見つからない残りがある。
- ハッシュ化は鍵つき（HMAC）にする。鍵なしのハッシュは、候補の値を同じ関数に通すだけで元の値と突き合わせられる。
テストデータの作り方 早見表
| 作り方 | 向いている試験 | 件数の目安 | 準備の手間 | 気を付ける点 |
|---|---|---|---|---|
| 手で作る（INSERT 文・CSV） | 単体、境界値、異常系 | 数件〜数十件 | 小 | 1件ごとに「何を確かめる行か」を設計表に書く |
| SQLで生成する | 結合、一覧・検索、性能の一次確認 | 数千〜数百万件 | 小〜中 | 一様な乱数だけだと偏りが出ない。分布を式で作る |
| 本番データを加工して使う | 性能試験、データ移行のリハーサル、偏りの再現 | 本番と同じ | 大 | 項目ごとにマスキング方法を決め、検証してから取り出す |
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
（記載例）出典：株式会社RJC「テストデータの作り方｜生成SQLとマスキング手法一覧・匿名化の手順」
どれを選ぶかの判断基準
| 条件 | 選ぶもの |
|---|---|
| 入力チェックや計算の正しさを確かめたい | 手で作る（境界値・NULL・最大長・重複を設計表で洗い出す） |
| 一覧やページ送り、検索の動きを件数つきで確かめたい | SQLで生成する |
| 索引が効くか、応答時間が目標に入るかを確かめたい | SQLで生成する（分布を式で作る）。それで足りなければ本番データの加工 |
| 「一部の会員だけ注文が極端に多い」など、偏りそのものが試験の対象 | 本番データを加工して使う |
| 旧システムの不ぞろいなデータ（書式ゆれ、想定外の値）を確かめたい | 本番データを加工して使う |
この記事が扱うのは、データセット全体をどう設計し、どう作り、どう加工するかです。入力値1つずつの境界の選び方（同値分割・境界値分析）や、generate_series の構文そのものは扱いません。
マスキング・仮名化・匿名化の違いと選び方
| 用語 | この記事での意味 | 元の人にたどれるか | 行どうしの対応 | テストでの使いどころ |
|---|---|---|---|---|
| マスキング | 値の全部または一部を別の値や記号に置き換える加工の総称 | 方法による | 方法による | 画面や帳票に実在の値を出さない |
| 仮名化 | 氏名や会員番号などを別の符号に置き換え、対応表や鍵が無ければ本人にたどれなくする | 対応表・鍵があればたどれる | 残る（同じ人は同じ符号） | 結合や集計を崩さずに試験したい |
| 匿名化 | 丸めや削除を重ね、ほかの情報と組み合わせても本人にたどれない状態にする | たどれない | 残さない、または粗くする | 外部へ渡す統計、公開用のサンプル |
法律上の用語としては、個人情報保護委員会のページで、仮名加工情報は「他の情報と照合しない限り特定の個人を識別することができないように個人情報を加工して得られる個人に関する情報」、匿名加工情報は「特定の個人を識別することができないように個人情報を加工し、当該個人情報を復元できないようにした情報」と説明されています。上の表はテストデータ作りのための技術的な整理で、加工したデータが法律上どれにあたるかは、自社の規程と法務の確認に従って判断します。
| 条件 | 選ぶもの |
|---|---|
| テスト環境の中だけで使い、表どうしの結合を保ちたい | 仮名化（鍵つきハッシュ、対応表による置換） |
| 画面の表示確認だけで、値の中身は問わない | 置換・部分マスク |
| 年齢層や地域ごとの集計の傾向は残したい | 丸め、日付ずらし |
| 組織の外に渡す | 匿名化まで行う。渡してよいかは法務の確認に従う |
生成SQL：PostgreSQL の generate_series で100万件
会員100万件と、注文100万件を作ります。注文は「少数の会員に集中する」形にするため、乱数を3乗して小さい番号に寄せています。
CREATE TABLE member (
member_id integer PRIMARY KEY,
member_code text NOT NULL UNIQUE,
full_name text NOT NULL,
email text NOT NULL,
phone text,
birth_date date NOT NULL,
postal_code text NOT NULL,
points integer NOT NULL,
created_at timestamp NOT NULL
);
SELECT setseed(0.42); -- 乱数の種を固定して、同じデータを作り直せるようにする
INSERT INTO member
SELECT n,
'M' || lpad(n::text, 7, '0'),
(ARRAY['見本','例示','架空','試験','仮名'])[1 + n % 5] || ' ' ||
(ARRAY['一郎','花子','次郎','さくら','三郎','もみじ','四郎'])[1 + n % 7],
'user' || n || '@example.com',
CASE WHEN n % 20 = 0 THEN NULL -- 20件に1件は電話番号なし
ELSE '999-' || lpad((n % 10000)::text, 4, '0') || '-' || lpad((n * 7 % 10000)::text, 4, '0') END,
DATE '1960-01-01' + (random() * 16000)::int,
lpad((n * 13 % 1000)::text, 3, '0') || '-' || lpad((n * 17 % 10000)::text, 4, '0'),
(random() * 5000)::int,
TIMESTAMP '2024-01-01' + random() * INTERVAL '640 days'
FROM generate_series(1, 1000000) AS n;
CREATE TABLE orders (
order_id integer PRIMARY KEY,
member_id integer NOT NULL REFERENCES member (member_id),
amount integer NOT NULL,
ordered_at timestamp NOT NULL
);
INSERT INTO orders
SELECT n,
1 + floor(1000000 * power(random(), 3))::int, -- 3乗して小さい会員番号に寄せる
100 * (1 + floor(random() * 300))::int,
TIMESTAMP '2025-01-01' + random() * INTERVAL '270 days'
FROM generate_series(1, 1000000) AS n;
INSERT 0 1000000
Time: 7256.441 ms (00:07.256)
INSERT 0 1000000
Time: 8534.711 ms (00:08.535)
できたデータを確かめます。
SELECT count(*) AS members, count(phone) AS with_phone, min(birth_date), max(birth_date) FROM member;
SELECT member_id, member_code, full_name, email, phone, birth_date, postal_code, points
FROM member WHERE member_id IN (1, 2, 20) ORDER BY 1;
members | with_phone | min | max
---------+------------+------------+------------
1000000 | 950000 | 1960-01-01 | 2003-10-22
member_id | member_code | full_name | email | phone | birth_date | postal_code | points
-----------+-------------+-----------+--------------------+---------------+------------+-------------+--------
1 | M0000001 | 例示 花子 | user1@example.com | 999-0001-0007 | 1992-07-18 | 013-0017 | 1812
2 | M0000002 | 架空 次郎 | user2@example.com | 999-0002-0014 | 1962-10-26 | 026-0034 | 471
20 | M0000020 | 見本 四郎 | user20@example.com | | 1982-01-24 | 260-0340 | 4524
会員ごとの注文件数の分布も見ておきます。
SELECT bucket, count(*) AS members, sum(cnt) AS orders
FROM (SELECT CASE WHEN cnt = 0 THEN '0件' WHEN cnt <= 2 THEN '1〜2件'
WHEN cnt <= 9 THEN '3〜9件' ELSE '10件以上' END AS bucket, cnt
FROM (SELECT m.member_id, count(o.order_id) AS cnt
FROM member m LEFT JOIN orders o USING (member_id) GROUP BY m.member_id) s) t
GROUP BY bucket ORDER BY min(cnt);
bucket | members | orders
----------+---------+--------
0件 | 521860 | 0
1〜2件 | 398132 | 503721
3〜9件 | 72573 | 295032
10件以上 | 7435 | 201247
全体の1%に満たない7,435人が、注文の約2割（201,247件）を持つデータになりました。1 + floor(1000000 * random()) のように一様な乱数だけで作ると、どの会員もほぼ同じ件数になり、「注文が多い会員の画面だけ遅い」といった現象が再現できません。偏りは式で作るのがポイントです。テーブルの大きさは member が 170 MB、orders が 71 MB（索引込み）でした。
生成SQL：MySQL の再帰CTEで100万件
MySQL 8.0 には generate_series が無いので、再帰CTE（WITH RECURSIVE）で連番を作ります。そのまま100万まで回そうとすると、次のエラーで止まります。
INSERT INTO member
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000)
SELECT n, CONCAT('M', LPAD(n, 7, '0')), 'x', 'x', NULL, '2000-01-01', '000-0000', 0 FROM seq;
ERROR 3636 (HY000) at line 12: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
MySQL のリファレンスマニュアルにあるとおり、cte_max_recursion_depth の既定値は 1000 です。セッションの値を上げてから実行します。
CREATE TABLE member (
member_id INT PRIMARY KEY,
member_code VARCHAR(8) NOT NULL UNIQUE,
full_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(13),
birth_date DATE NOT NULL,
postal_code CHAR(8) NOT NULL,
points INT NOT NULL
);
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO member
WITH RECURSIVE seq (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000)
SELECT n,
CONCAT('M', LPAD(n, 7, '0')),
CONCAT(ELT(1 + n % 5, '見本','例示','架空','試験','仮名'), ' ',
ELT(1 + n % 7, '一郎','花子','次郎','さくら','三郎','もみじ','四郎')),
CONCAT('user', n, '@example.com'),
IF(n % 20 = 0, NULL, CONCAT('999-', LPAD(n % 10000, 4, '0'), '-', LPAD(n * 7 % 10000, 4, '0'))),
DATE '1960-01-01' + INTERVAL FLOOR(RAND(n) * 16000) DAY,
CONCAT(LPAD(n * 13 % 1000, 3, '0'), '-', LPAD(n * 17 % 10000, 4, '0')),
FLOOR(RAND(n + 1) * 5000)
FROM seq;
SELECT COUNT(*) AS members, COUNT(phone) AS with_phone, MIN(birth_date), MAX(birth_date) FROM member;
Query OK, 1000000 rows affected (12.00 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
+---------+------------+-----------------+-----------------+
| members | with_phone | MIN(birth_date) | MAX(birth_date) |
+---------+------------+-----------------+-----------------+
| 1000000 | 950000 | 1960-01-01 | 2003-10-21 |
+---------+------------+-----------------+-----------------+
RAND(n) のように引数を渡すと、同じ n から同じ値が返るので、作り直しても同じデータになります。
| やりたいこと | PostgreSQL 16 | MySQL 8.0 |
|---|---|---|
| 連番を作る | generate_series(1, 1000000) |
WITH RECURSIVE と SET SESSION cte_max_recursion_depth |
| 候補から順に選ぶ | (ARRAY['a','b'])[1 + n % 2] |
ELT(1 + n % 2, 'a', 'b') |
| 乱数を再現できるようにする | SELECT setseed(0.42); |
RAND(n) |
| ゼロ埋め | lpad(n::text, 7, '0') |
LPAD(n, 7, '0') |
| 日付に日数を足す | DATE '1960-01-01' + 整数 |
DATE '1960-01-01' + INTERVAL 整数 DAY |
境界値・NULL・最大長・重複は、生成データに数件足す
大量に生成したデータには、端の値がまず入りません。確かめたい行は、設計表で決めてから手で足します。
テストデータ設計表のテンプレート
| No | テーブル | 項目 | 観点 | 入れる値 | 確かめること | 対応するテストケース | 作り方 |
|---|---|---|---|---|---|---|---|
| 1 | 最小値 | 手で作る | |||||
| 2 | 最大値 | 手で作る | |||||
| 3 | NULL | 手で作る | |||||
| 4 | 最大長 | 手で作る | |||||
| 5 | 重複 | 手で作る | |||||
| 6 | 件数・分布 | SQLで生成する |
表計算ソフトに貼れるタブ区切り版です（記入例つき）。
No テーブル 項目 観点 入れる値 確かめること 対応するテストケース 作り方
1 member points 最小値 0 ポイント0の会員が一覧に出る TC-012 手で作る
2 member points 最大値 2147483647 integerの上限で表示が崩れない TC-013 手で作る
3 member phone NULL NULL 電話番号なしでも詳細画面が開く TC-020 手で作る
4 member full_name 最大長 「あ」を50文字 50文字の氏名が一覧で折り返される TC-021 手で作る
5 member email 最大長 255文字のアドレス 255文字でも登録・表示できる TC-022 手で作る
6 member email 重複 user1@example.com を2件 同じアドレスの2件目の扱いが仕様どおり TC-030 手で作る
7 member birth_date 最小値 1900-01-01 年齢計算が3桁でも表示できる TC-040 手で作る
8 member birth_date 最大値 試験日当日 0歳として扱われる TC-041 手で作る
9 orders member_id 件数・分布 上位1%未満の会員に注文の約2割 注文が多い会員の履歴画面の応答時間 PT-003 SQLで生成する
10 orders member_id 件数・分布 注文0件の会員が約半数 注文なしの会員で空の一覧が出る TC-050 SQLで生成する
この表のうち、No.1〜8 を実際に入れた結果です。
INSERT INTO member VALUES
(1000001, 'M9999991', repeat('あ', 50), 'max' || repeat('a', 240) || '@example.com', NULL,
DATE '1900-01-01', '000-0000', 0, '2024-01-01 00:00:00'),
(1000002, 'M9999992', '見本 一郎', 'user1@example.com', '999-0000-0000',
DATE '2026-10-05', '999-9999', 2147483647, '2026-10-05 23:59:59');
SELECT member_id, length(full_name) AS name_len, length(email) AS email_len,
phone IS NULL AS phone_null, birth_date, points
FROM member WHERE member_id > 1000000 ORDER BY 1;
SELECT email, count(*) FROM member GROUP BY email HAVING count(*) > 1;
member_id | name_len | email_len | phone_null | birth_date | points
-----------+----------+-----------+------------+------------+------------
1000001 | 50 | 255 | t | 1900-01-01 | 0
1000002 | 5 | 17 | f | 2026-10-05 | 2147483647
email | count
-------------------+-------
user1@example.com | 2
（このあとのマスキングの節では、この2行を削除した100万件を使っています。）
マスキング手法一覧
| 手法 | やること | 向いている項目 | 行どうしの対応 | 集計の傾向 | 元に戻せるか |
|---|---|---|---|---|---|
| 置換 | 用意した別の値に入れ替える | 氏名、住所、自由記述 | 残らない | 残らない | 戻せない |
| 部分マスク | 一部を記号にする | 電話番号、会員番号の表示 | 一部残る | 残らない | 戻せない（残した桁は見える） |
| 日付ずらし | 人ごとに決まった日数だけ前後させる | 生年月日、登録日 | 残る | ほぼ残る | 鍵とずらし方が分かれば戻せる |
| 丸め | 粒度を粗くする | 郵便番号、金額、年齢 | 残らない | 粗い粒度で残る | 戻せない |
| ハッシュ化（鍵つき） | 鍵と値から決まる符号にする | メールアドレス、結合に使う番号 | 残る（同じ値は同じ符号） | 件数・重複は残る | 鍵が無ければ戻せない |
| 削除（NULL・空にする） | 値を持たない | 試験で使わない項目 | 残らない | 残らない | 戻せない |
試験で使わない項目は削除がいちばん確かです。残す項目だけ、上の表から方法を選びます。
項目別マスキングSQLと実行結果（PostgreSQL）
鍵つきハッシュには pgcrypto 拡張の hmac() を使います。鍵は psql の変数に入れています（記事用の値です。鍵そのものはテスト環境へ持ち込みません）。
CREATE EXTENSION IF NOT EXISTS pgcrypto;
\set key '''k3y-for-test-2026'''
置換：氏名を用意した別の値に入れ替える
SELECT member_id, full_name,
(ARRAY['テスト 太郎','テスト 花子','テスト 次郎','テスト 桜'])[1 + member_id % 4] AS name_replaced
FROM member WHERE member_id IN (1, 2, 20) ORDER BY 1;
member_id | full_name | name_replaced
-----------+-----------+---------------
1 | 例示 花子 | テスト 花子
2 | 架空 次郎 | テスト 次郎
20 | 見本 四郎 | テスト 太郎
部分マスク：電話番号は下4桁、メールは先頭1文字とドメインを残す
SELECT member_id, phone,
regexp_replace(phone, '\d(?=.*\d{4})', '*', 'g') AS phone_masked,
email,
regexp_replace(email, '^(.).*(@.*)$', '\1***\2') AS email_masked
FROM member WHERE member_id IN (1, 2, 20) ORDER BY 1;
member_id | phone | phone_masked | email | email_masked
-----------+---------------+---------------+--------------------+--------------------
1 | 999-0001-0007 | ***-****-0007 | user1@example.com | u***@example.com
2 | 999-0002-0014 | ***-****-0014 | user2@example.com | u***@example.com
20 | | | user20@example.com | u***@example.com
\d(?=.*\d{4}) は「後ろに数字があと4つ以上ある数字」という意味で、下4桁より前の数字だけが記号になります。NULL は NULL のまま残ります。メールの部分マスクは同じ値に集まりやすいので、一意制約がある列には次のハッシュ化を使います。
日付ずらし・丸め
SELECT member_id, birth_date,
birth_date + (('x' || substr(encode(hmac(member_code, :key, 'sha256'), 'hex'), 1, 6))::bit(24)::int % 31 - 15) AS birth_shifted,
date_trunc('year', birth_date)::date AS birth_rounded,
postal_code, left(postal_code, 3) || '-0000' AS postal_rounded,
points, round(points, -2) AS points_rounded
FROM member WHERE member_id IN (1, 2, 20) ORDER BY 1;
member_id | birth_date | birth_shifted | birth_rounded | postal_code | postal_rounded | points | points_rounded
-----------+------------+---------------+---------------+-------------+----------------+--------+----------------
1 | 1992-07-18 | 1992-07-16 | 1992-01-01 | 013-0017 | 013-0000 | 1812 | 1800
2 | 1962-10-26 | 1962-10-13 | 1962-01-01 | 026-0034 | 026-0000 | 471 | 500
20 | 1982-01-24 | 1982-02-06 | 1982-01-01 | 260-0340 | 260-0000 | 4524 | 4500
日付ずらしは、会員コードと鍵から −15〜+15 日の値を決めています。乱数ではなく会員ごとに決まる値にすると、同じ会員の複数の日付（生年月日と登録日など）を同じ日数だけ動かせるので、前後関係や間隔が保てます。ただし、この式には見落としがあり、後の検証で見つかります。
ハッシュ化：鍵つき（HMAC）にする
SELECT member_id, email,
md5(email) AS md5_plain,
left(encode(hmac(email, :key, 'sha256'), 'hex'), 16) AS hmac_16
FROM member WHERE member_id IN (1, 2) ORDER BY 1;
member_id | email | md5_plain | hmac_16
-----------+-------------------+----------------------------------+------------------
1 | user1@example.com | 111d68d06e2d317b5a59c2c6c5bad808 | 882f65a32e5d1e83
2 | user2@example.com | ab53a2911ddf9b4817ac01ddcd3d975f | 24881d0f31aa75b0
鍵なしの md5 と鍵つきの hmac を並べました。見た目はどちらも意味のない文字列ですが、鍵なしのほうは次の方法で元の値と突き合わせられます。
-- 鍵なしの md5 で加工した表
CREATE TABLE masked_md5 AS SELECT member_id, md5(email) AS email_hash FROM member;
-- 候補のアドレスを同じ関数に通して結合する
SELECT count(*) AS reidentified
FROM masked_md5 h
JOIN (SELECT 'user' || n || '@example.com' AS guess FROM generate_series(1, 1000000) n) g
ON h.email_hash = md5(g.guess);
reidentified
--------------
1000000
候補の一覧さえあれば、100万件すべてが元のアドレスと結び付きました。鍵つきで加工した表（次の節の member_masked）に、鍵を知らない立場で同じことをすると、一致は0件でした。
SELECT count(*) AS reidentified
FROM member_masked h
JOIN (SELECT 'user' || n || '@example.com' AS guess FROM generate_series(1, 1000000) n) g
ON h.email = 'u' || left(md5(g.guess), 16) || '@example.invalid';
reidentified
--------------
0
まとめて加工した表を作る
CREATE TABLE member_masked AS
SELECT member_id,
member_code,
(ARRAY['テスト 太郎','テスト 花子','テスト 次郎','テスト 桜'])[1 + member_id % 4] AS full_name,
'u' || left(encode(hmac(email, :key, 'sha256'), 'hex'), 16) || '@example.invalid' AS email,
regexp_replace(phone, '\d(?=.*\d{4})', '*', 'g') AS phone,
birth_date + (('x' || substr(encode(hmac(member_code, :key, 'sha256'), 'hex'), 1, 6))::bit(24)::int % 31 - 15) AS birth_date,
left(postal_code, 3) || '-0000' AS postal_code,
round(points, -2)::int AS points,
created_at
FROM member;
SELECT 1000000
Time: 12861.580 ms (00:12.862)
member_id | full_name | email | phone | birth_date | postal_code | points
-----------+-------------+-----------------------------------+---------------+------------+-------------+--------
1 | テスト 花子 | u882f65a32e5d1e83@example.invalid | ***-****-0007 | 1992-07-16 | 013-0000 | 1800
2 | テスト 次郎 | u24881d0f31aa75b0@example.invalid | ***-****-0014 | 1962-10-13 | 026-0000 | 500
20 | テスト 太郎 | u068050f8e071a20e@example.invalid | | 1982-02-06 | 260-0000 | 4500
元の表を UPDATE で書き換えるのではなく、加工済みの別の表を作り、それだけを取り出す形にしています。メールのドメインを example.invalid にしているのは、試験中に誤ってメールが外へ送られないようにするためです。
MySQL 8.0 での書き方
同じ加工を MySQL 8.0.46 で実行した結果です（MySQL 側の生成データは乱数が違うため、生年月日とポイントの元の値は PostgreSQL と異なります）。
SET @k = 'k3y-for-test-2026';
SELECT member_id,
ELT(1 + member_id % 4, 'テスト 太郎','テスト 花子','テスト 次郎','テスト 桜') AS name_replaced,
REGEXP_REPLACE(phone, '[0-9](?=.*[0-9]{4})', '*') AS phone_masked,
CONCAT('u', LEFT(SHA2(CONCAT(@k, email), 256), 16), '@example.invalid') AS email_hashed,
birth_date + INTERVAL (CONV(LEFT(SHA2(CONCAT(@k, member_code), 256), 6), 16, 10) % 31 - 15) DAY AS birth_shifted,
CONCAT(LEFT(postal_code, 3), '-0000') AS postal_rounded,
ROUND(points, -2) AS points_rounded
FROM member WHERE member_id IN (1, 2, 20);
+-----------+---------------+---------------+-----------------------------------+---------------+----------------+----------------+
| member_id | name_replaced | phone_masked | email_hashed | birth_shifted | postal_rounded | points_rounded |
+-----------+---------------+---------------+-----------------------------------+---------------+----------------+----------------+
| 1 | テスト 花子 | ***-****-0007 | uc8d357879db2c823@example.invalid | 1977-10-18 | 013-0000 | 3300 |
| 2 | テスト 次郎 | ***-****-0014 | u63385eab8f9ff9aa@example.invalid | 1988-09-18 | 026-0000 | 4500 |
| 20 | テスト 太郎 | NULL | u5dd552fa1565ed54@example.invalid | 1966-12-02 | 260-0000 | 2000 |
+-----------+---------------+---------------+-----------------------------------+---------------+----------------+----------------+
MySQL には hmac() にあたる組み込み関数が無いため、ここでは鍵を値の前につなげてから SHA2() に通しています。PostgreSQL の hmac() とは計算方法が違うので、同じ鍵でも結果の文字列は一致しません。両方のデータベースで同じ符号が必要なら、アプリ側で HMAC を計算してから取り込みます。
加工後に元の値が残っていないことを確かめる検証SQL
加工した表と元の表を主キーで結合し、項目ごとに「元と同じ値の件数」を数えます。
SELECT count(*) FILTER (WHERE m.full_name = o.full_name) AS same_name,
count(*) FILTER (WHERE m.email = o.email) AS same_email,
count(*) FILTER (WHERE m.phone = o.phone) AS same_phone,
count(*) FILTER (WHERE m.birth_date = o.birth_date) AS same_birth,
count(*) FILTER (WHERE m.postal_code = o.postal_code) AS same_postal,
count(*) AS total
FROM member_masked m JOIN member o USING (member_id);
same_name | same_email | same_phone | same_birth | same_postal | total
-----------+------------+------------+------------+-------------+---------
0 | 0 | 0 | 32540 | 100 | 1000000
氏名・メール・電話は0件でしたが、生年月日が 32,540 件、郵便番号が 100 件、元と同じ値のままでした。
- 生年月日：ずらす日数を −15〜+15 の31通りにしたため、31分の1の確率で「0日ずらし」になっていました（100万 ÷ 31 ≒ 32,258 に近い値です）。
- 郵便番号：元の値がもともと下4桁 0000 だった行です。丸めた結果が元と同じになります。
生年月日は、0日を含まない式に直します。ずらす幅を 1〜15 日にし、別の桁で前後を決めます。
SELECT count(*) FILTER (WHERE d = 0) AS zero_shift, min(d), max(d), count(*) AS total
FROM (SELECT (h % 15 + 1) * (CASE WHEN (h / 15) % 2 = 0 THEN 1 ELSE -1 END) AS d
FROM (SELECT ('x' || substr(encode(hmac(member_code, :key, 'sha256'), 'hex'), 1, 6))::bit(24)::int AS h
FROM member) s) t;
zero_shift | min | max | total
------------+-----+-----+---------
0 | -15 | 15 | 1000000
郵便番号の100件は、丸めという方法の性質上残るものです。「上3桁までは残してよい」と決めた項目であれば問題になりませんが、こうした件数が出たときに残してよいかを項目ごとに判断して記録することが大切です。
書式・件数・分布の確認
-- 書式：加工後の形になっていない行を数える
SELECT count(*) FILTER (WHERE email NOT LIKE '%@example.invalid') AS bad_email,
count(*) FILTER (WHERE phone !~ '^\*{3}-\*{4}-\d{4}$') AS bad_phone,
count(*) FILTER (WHERE postal_code !~ '^\d{3}-0000$') AS bad_postal
FROM member_masked;
-- 件数・NULLの数・値の種類の数が変わっていないか
SELECT (SELECT count(*) FROM member) AS src_rows, (SELECT count(*) FROM member_masked) AS dst_rows,
(SELECT count(phone) FROM member) AS src_phone, (SELECT count(phone) FROM member_masked) AS dst_phone,
(SELECT count(DISTINCT email) FROM member) AS src_email_kinds,
(SELECT count(DISTINCT email) FROM member_masked) AS dst_email_kinds;
-- 日付ずらしの幅と、平均年齢が変わっていないか
SELECT min(o.birth_date - m.birth_date) AS min_diff, max(o.birth_date - m.birth_date) AS max_diff,
round(avg(extract(year FROM age(DATE '2026-10-05', o.birth_date))), 2) AS src_avg_age,
round(avg(extract(year FROM age(DATE '2026-10-05', m.birth_date))), 2) AS dst_avg_age
FROM member_masked m JOIN member o USING (member_id);
bad_email | bad_phone | bad_postal
-----------+-----------+------------
0 | 0 | 0
src_rows | dst_rows | src_phone | dst_phone | src_email_kinds | dst_email_kinds
----------+----------+-----------+-----------+-----------------+-----------------
1000000 | 1000000 | 950000 | 950000 | 1000000 | 1000000
min_diff | max_diff | src_avg_age | dst_avg_age
----------+----------+-------------+-------------
-15 | 15 | 44.35 | 44.35
件数、NULL の数、メールアドレスの種類の数、平均年齢がそのまま保たれています。ハッシュを16桁に切り詰めても、100万件で衝突は起きませんでした（種類の数が同じ）。
組み合わせると1人に絞れてしまう行を数える
1つずつの項目は加工してあっても、複数の項目を組み合わせると1人に絞れることがあります。組み合わせごとの人数を数えます。
-- 加工前：生年月日 × 郵便番号
SELECT count(*) AS groups, count(*) FILTER (WHERE c = 1) AS groups_of_one, min(c) AS min_size
FROM (SELECT birth_date, postal_code, count(*) AS c FROM member GROUP BY 1, 2) s;
-- 加工後：生まれ年 × 郵便番号の上3桁
SELECT count(*) AS groups, count(*) FILTER (WHERE c = 1) AS groups_of_one, min(c) AS min_size
FROM (SELECT extract(year FROM birth_date) AS y, left(postal_code, 3) AS p, count(*) AS c
FROM member_masked GROUP BY 1, 2) s;
groups | groups_of_one | min_size
--------+---------------+----------
996907 | 993820 | 1
groups | groups_of_one | min_size
--------+---------------+----------
44187 | 166 | 1
加工前は 993,820 通りの組み合わせが1人だけでした。年と上3桁まで丸めると 166 通りまで減りますが、0にはなっていません。外へ渡すデータであれば、さらに粗くする（年を10年刻みにする、該当行を除く）といった追加の加工を検討します。
外部キーの整合を保ったまま置換する手順
会員コードのように、ほかの表から参照されている値を置き換えるときは、親と子を同じ対応表で同時に書き換えます。ここではクーポン利用の表が会員コードを参照しているとします。
CREATE TABLE coupon_use (
use_id integer PRIMARY KEY,
member_code text NOT NULL,
used_on date NOT NULL,
CONSTRAINT coupon_use_member_fk FOREIGN KEY (member_code) REFERENCES member (member_code)
);
INSERT INTO coupon_use
SELECT n, 'M' || lpad((1 + n * 37 % 1000)::text, 7, '0'), DATE '2026-01-01' + n % 200
FROM generate_series(1, 5000) n;
手順1：対応表を作る。 新しいコードは、元の並びと関係のない順番で振ります。
CREATE TABLE code_map AS
SELECT member_code AS old_code,
'T' || lpad((row_number() OVER (ORDER BY gen_random_uuid()))::text, 7, '0') AS new_code
FROM member;
ALTER TABLE code_map ADD PRIMARY KEY (old_code);
ALTER TABLE code_map ADD UNIQUE (new_code);
親だけを先に書き換えようとすると、外部キーが止めます。
UPDATE member m SET member_code = c.new_code
FROM code_map c WHERE m.member_code = c.old_code AND m.member_id <= 1000;
ERROR: update or delete on table "member" violates foreign key constraint "coupon_use_member_fk" on table "coupon_use"
DETAIL: Key (member_code)=(M0000215) is still referenced from table "coupon_use".
手順2：子の参照列に索引を作る。（理由は下の計測結果のとおりです。）
CREATE INDEX coupon_use_member_code_idx ON coupon_use (member_code);
手順3：外部キーの確認をコミット時まで遅らせ、親と子を同じトランザクションで書き換える。
ALTER TABLE coupon_use ALTER CONSTRAINT coupon_use_member_fk DEFERRABLE INITIALLY IMMEDIATE;
BEGIN;
SET CONSTRAINTS coupon_use_member_fk DEFERRED;
UPDATE member m SET member_code = c.new_code FROM code_map c WHERE m.member_code = c.old_code;
UPDATE coupon_use u SET member_code = c.new_code FROM code_map c WHERE u.member_code = c.old_code;
COMMIT;
UPDATE 1000000
Time: 13762.913 ms (00:13.763)
UPDATE 5000
Time: 54.864 ms
COMMIT
Time: 10297.743 ms (00:10.298)
手順4：整合を確かめる。
-- 親のいない子が無いこと
SELECT count(*) AS orphans
FROM coupon_use u LEFT JOIN member m USING (member_code) WHERE m.member_code IS NULL;
-- 件数・会員の数が変わらず、以前のコード（M始まり）が残っていないこと
SELECT (SELECT count(*) FROM coupon_use) AS rows_after,
(SELECT count(DISTINCT member_code) FROM coupon_use) AS members_after,
(SELECT count(*) FROM coupon_use WHERE member_code LIKE 'M%') AS old_code_left,
(SELECT count(*) FROM member WHERE member_code LIKE 'M%') AS old_code_left_parent;
orphans
---------
0
rows_after | members_after | old_code_left | old_code_left_parent
------------+---------------+---------------+----------------------
5000 | 1000 | 0 | 0
手順5：対応表を削除する。 対応表は元のコードにたどる手がかりそのものなので、テスト環境へは持ち込みません。
DROP TABLE code_map;
子の参照列に索引が無いと、確認に6分かかった
手順2の索引を作らずに同じ書き換えを行い、外部キーの確認にかかる時間を測りました（確認だけを行わせるために SET CONSTRAINTS ... IMMEDIATE を実行し、そのあと ROLLBACK しています）。
| 子の member_code の索引 | 親100万件の UPDATE | 外部キーの確認 |
|---|---|---|
| なし | 14.9 秒 | 6分7秒（367,132 ms） |
| あり | 13.8 秒 | 10.3 秒（COMMIT の時間） |
親のキーを書き換えると、PostgreSQL は1行ごとに「このキーを参照している子がいないか」を確かめます。子の参照列に索引が無いと、そのたびに子の表を端から読むことになります。子は5,000件しかありませんでしたが、親が100万件あるため、合計で6分かかりました。
本番データを使うときの手順（項目ごとに方法を決めてから取り出す）
| 順番 | やること | 残すもの |
|---|---|---|
| 1 | 使う表と項目を一覧にし、試験で使わない項目は「削除」と決める | 項目別マスキング方針表 |
| 2 | 残す項目ごとに手法（置換・部分マスク・日付ずらし・丸め・ハッシュ化）を決める | 同上 |
| 3 | 参照されているキーを洗い出し、対応表で親子を同時に置き換える | 置換の手順とSQL |
| 4 | 本番と同じ管理区域の中で加工済みの表を作る | 加工SQL |
| 5 | 検証SQLを流し、同じ値の件数・書式・件数・組み合わせを確かめる | 検証結果 |
| 6 | 残った件数を項目ごとに判断し、必要なら加工をやり直す | 判断の記録 |
| 7 | 加工済みの表だけを取り出す。鍵と対応表は持ち出さない | 取り出しの記録 |
項目別マスキング方針表のテンプレートです（タブ区切り、記入例つき）。
テーブル 項目 種別 試験での用途 手法 加工後の形 検証の観点
member full_name 氏名 画面表示 置換 テスト 太郎 など4種 元と同じ値が0件
member email 連絡先 一意の確認、ログイン ハッシュ化（鍵つき） u＋16桁＠example.invalid 元と同じ値が0件、種類の数が同じ
member phone 連絡先 画面表示 部分マスク 下4桁以外を記号に 書式どおり、NULLの数が同じ
member birth_date 属性 年齢層の集計 日付ずらし 前後1〜15日 0日ずらしが0件、平均年齢が同じ
member postal_code 住所 地域別の集計 丸め 上3桁＋0000 書式どおり
member points 数値 並べ替え、集計 丸め 100単位 合計の差が許容内
member member_code キー 結合 対応表で置換 T＋7桁 親のいない子が0件、以前のコードが0件
PostgreSQL では、マスキングの規則を列に宣言しておける拡張「PostgreSQL Anonymizer」もあります。公式ドキュメントによると、SECURITY LABEL でマスキング規則を宣言でき、Anonymous Dumps・Static Masking・Dynamic Masking などの方式が用意されています。この記事のSQLは拡張なし（pgcrypto のみ）で動く形にしています。
確かめて分かったこと
- 日付ずらしの幅に0日が含まれていて、32,540 件が元の生年月日のままだった。 サンプルを数行見るだけでは気づけず、全件を結合して数える検証SQLで見つかりました。
- 丸めは、元の値がすでに丸めた形だと同じ値が残る。 郵便番号で100件ありました。残してよいかを項目ごとに判断します。
- 鍵なしのハッシュは、候補を同じ関数に通すだけで100万件すべてが元の値と結び付いた。 鍵つき（HMAC）では0件でした。
- 丸めたあとも、組み合わせで1人に絞れる行が 166 通り残った。 項目単体の確認に加えて、組み合わせの人数も数えます。
- 子の参照列に索引が無いと、親100万件のキー置換で外部キーの確認に6分7秒かかった。 索引を作ると 10.3 秒でした。
- MySQL の再帰CTEは、既定のままでは1001回目で止まる。
SET SESSION cte_max_recursion_depthで上げてから実行します。 - 一様な乱数では偏りが出ない。 乱数を3乗するだけで、1%未満の会員に注文の約2割が集まるデータになりました。
よくある質問
Q. テストデータとは何ですか？ A. テストを実行するためにシステムへ入れておくデータです。入力する値だけでなく、あらかじめデータベースに入っているマスターや取引のデータも含みます。
Q. マスキングと匿名化の違いは？ A. マスキングは値を別の値や記号に置き換える加工の総称で、匿名化は本人にたどれない状態にすることを指します。マスキングをしただけで匿名化になるとは限らず、組み合わせで1人に絞れないかまで確かめる必要があります。
Q. 本番データをテストに使ってもよいですか？ A. 技術的には加工すれば使えますが、使ってよいかは自社の規程と法務の確認に従います。この記事の立場は「まず生成データで足りるかを考え、足りない場合に限って、項目ごとに加工方法を決めてから使う」です。
Q. 100万件のテストデータをSQLで作るには？ A. PostgreSQL は generate_series(1, 1000000) を FROM に置いて INSERT ... SELECT します。MySQL 8.0 は WITH RECURSIVE で連番を作り、先に cte_max_recursion_depth を上げておきます。今回の環境では PostgreSQL が約7秒、MySQL が約12秒でした。
Q. マスキングしたのに外部キーエラーになるときは？ A. 親と子を別々に書き換えているのが原因です。対応表を作り、外部キーの確認をコミット時まで遅らせて、同じトランザクションで両方を書き換えます。
まとめ
- まず生成データ。本番データの加工は、性能試験や偏りの再現が必要な場合に限る。
- 100万件は generate_series（PostgreSQL）／再帰CTE（MySQL） で作れる。偏りは式で作り、境界値・NULL・最大長・重複は設計表で決めて手で足す。
- マスキングは項目ごとに手法を決める。使わない項目は削除、結合に使う項目は鍵つきハッシュか対応表で置換。
- 加工後は検証SQLで「元と同じ値の件数」を全件で数える。今回は日付ずらしの0日が見つかった。
- 参照されているキーは、対応表で親子を同時に置換し、親のいない子が0件であることを確かめる。子の参照列には索引を作る。
- 鍵と対応表はテスト環境へ持ち込まない。
参考・出典
- MySQL 8.0 Reference Manual「15.2.20 WITH (Common Table Expressions)」 https://dev.mysql.com/doc/refman/8.0/en/with.html
- 個人情報保護委員会「個人情報の保護に関する法律についてのガイドライン（仮名加工情報・匿名加工情報編）」 https://www.ppc.go.jp/personalinfo/legal/guidelines_anonymous/
- 個人情報保護委員会「匿名加工情報」 https://www.ppc.go.jp/personalinfo/tokumeikakouInfo/
- PostgreSQL Anonymizer ドキュメント https://postgresql-anonymizer.readthedocs.io/en/stable/
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
（記載例）出典：株式会社RJC「テストデータの作り方｜生成SQLとマスキング手法一覧・匿名化の手順」
株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。
WE ARE HIRING
RJCで一緒に開発しながら、
成長を楽しみませんか？
RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください！
- 127日年間休日
- 12時間平均残業時間
- 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ！
ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨（休暇 or 金一封）、11期連続の黒字決算。
