以下を前提に、回答させていただきます。
- wp_cf7dbplugin_submits テーブルの全てのレコードの form_name カラムは、同じ値
- wp_cf7dbplugin_submits テーブルの全てのレコードの field_name カラムの値は、
field_1
, field_2
, field_3
の 3種類のみ
- wp_cf7dbplugin_submits テーブルの全てのレコードの form_name カラム, field_name カラムには、非NULL値が格納されている
- wp_cf7dbplugin_submits テーブルには、「変換済みFLG」として is_normalized カラムを追加する(0 => 未変換, 1 => 変換済み)
- 「フィールド名とその値を横持ち」するテーブルの定義は、以下の通り
sql
1CREATE TABLE normalized_wp_cf7dbplugin_submits (
2 submit_time decimal(16,4) NOT NULL,
3 form_name varchar(127),
4 field_value_1 longtext,
5 field_order_1 int(11),
6 file_1 longblob,
7 field_value_2 longtext,
8 field_order_2 int(11),
9 file_2 longblob,
10 field_value_3 longtext,
11 field_order_3 int(11),
12 file_3 longblob,
13
14 PRIMARY KEY (submit_time, form_name)
15);
回答
sql
1BEGIN;
2
3INSERT INTO normalized_wp_cf7dbplugin_submits
4SELECT
5 t1.submit_time,
6 t1.form_name,
7 t1.field_value,
8 t1.field_order,
9 t1.file,
10 t2.field_value,
11 t2.field_order,
12 t2.file,
13 t3.field_value,
14 t3.field_order,
15 t3.file
16FROM wp_cf7dbplugin_submits AS t1
17INNER JOIN wp_cf7dbplugin_submits AS t2
18 ON t1.submit_time = t2.submit_time AND t1.form_name = t2.form_name
19INNER JOIN wp_cf7dbplugin_submits AS t3
20 ON t1.submit_time = t3.submit_time AND t1.form_name = t3.form_name
21WHERE t1.field_name = 'field_1' AND t1.is_normalized = 0
22 AND t2.field_name = 'field_2' AND t2.is_normalized = 0
23 AND t3.field_name = 'field_3' AND t3.is_normalized = 0
24FOR UPDATE;
25
26UPDATE wp_cf7dbplugin_submits SET is_normalized = 1 WHERE is_normalized = 0;
27
28COMMIT;
実行結果
sql
1mysql> CREATE TABLE wp_cf7dbplugin_submits (
2 -> submit_time decimal(16,4) NOT NULL,
3 -> form_name varchar(127),
4 -> field_name varchar(127),
5 -> field_value longtext,
6 -> field_order int(11),
7 -> file longblob,
8 ->
9 -> PRIMARY KEY (submit_time, form_name, field_name)
10 -> );
11Query OK, 0 rows affected (0.02 sec)
12
13mysql> INSERT INTO wp_cf7dbplugin_submits VALUES
14 -> (0.0, 'form', 'field_1', 'value_1_1', 1, 'file_1_1'),
15 -> (0.0, 'form', 'field_2', 'value_1_2', 2, 'file_1_2'),
16 -> (0.0, 'form', 'field_3', 'value_1_3', 3, 'file_1_3'),
17 -> (1.0, 'form', 'field_1', 'value_2_1', 1, 'file_2_1'),
18 -> (1.0, 'form', 'field_2', 'value_2_2', 2, 'file_2_2'),
19 -> (1.0, 'form', 'field_3', 'value_2_3', 3, 'file_2_3'),
20 -> (2.0, 'form', 'field_1', 'value_3_1', 1, 'file_3_1'),
21 -> (2.0, 'form', 'field_2', 'value_3_2', 2, 'file_3_2'),
22 -> (2.0, 'form', 'field_3', 'value_3_3', 3, 'file_3_3');
23Query OK, 9 rows affected (0.00 sec)
24Records: 9 Duplicates: 0 Warnings: 0
25
26mysql> ALTER TABLE wp_cf7dbplugin_submits
27 -> ADD COLUMN is_normalized TINYINT UNSIGNED NOT NULL DEFAULT 0,
28 -> ADD INDEX (is_normalized);
29Query OK, 0 rows affected (0.06 sec)
30Records: 0 Duplicates: 0 Warnings: 0
31
32mysql> SELECT * FROM wp_cf7dbplugin_submits;
33+-------------+-----------+------------+-------------+-------------+----------+---------------+
34| submit_time | form_name | field_name | field_value | field_order | file | is_normalized |
35+-------------+-----------+------------+-------------+-------------+----------+---------------+
36| 0.0000 | form | field_1 | value_1_1 | 1 | file_1_1 | 0 |
37| 0.0000 | form | field_2 | value_1_2 | 2 | file_1_2 | 0 |
38| 0.0000 | form | field_3 | value_1_3 | 3 | file_1_3 | 0 |
39| 1.0000 | form | field_1 | value_2_1 | 1 | file_2_1 | 0 |
40| 1.0000 | form | field_2 | value_2_2 | 2 | file_2_2 | 0 |
41| 1.0000 | form | field_3 | value_2_3 | 3 | file_2_3 | 0 |
42| 2.0000 | form | field_1 | value_3_1 | 1 | file_3_1 | 0 |
43| 2.0000 | form | field_2 | value_3_2 | 2 | file_3_2 | 0 |
44| 2.0000 | form | field_3 | value_3_3 | 3 | file_3_3 | 0 |
45+-------------+-----------+------------+-------------+-------------+----------+---------------+
469 rows in set (0.00 sec)
47
48mysql> CREATE TABLE normalized_wp_cf7dbplugin_submits (
49 -> submit_time decimal(16,4) NOT NULL,
50 -> form_name varchar(127),
51 -> field_value_1 longtext,
52 -> field_order_1 int(11),
53 -> file_1 longblob,
54 -> field_value_2 longtext,
55 -> field_order_2 int(11),
56 -> file_2 longblob,
57 -> field_value_3 longtext,
58 -> field_order_3 int(11),
59 -> file_3 longblob,
60 ->
61 -> PRIMARY KEY (submit_time, form_name)
62 -> );
63Query OK, 0 rows affected (0.02 sec)
64
65mysql> BEGIN;
66Query OK, 0 rows affected (0.00 sec)
67
68mysql> INSERT INTO normalized_wp_cf7dbplugin_submits
69 -> SELECT
70 -> t1.submit_time,
71 -> t1.form_name,
72 -> t1.field_value,
73 -> t1.field_order,
74 -> t1.file,
75 -> t2.field_value,
76 -> t2.field_order,
77 -> t2.file,
78 -> t3.field_value,
79 -> t3.field_order,
80 -> t3.file
81 -> FROM wp_cf7dbplugin_submits AS t1
82 -> INNER JOIN wp_cf7dbplugin_submits AS t2
83 -> ON t1.submit_time = t2.submit_time AND t1.form_name = t2.form_name
84 -> INNER JOIN wp_cf7dbplugin_submits AS t3
85 -> ON t1.submit_time = t3.submit_time AND t1.form_name = t3.form_name
86 -> WHERE t1.field_name = 'field_1' AND t1.is_normalized = 0
87 -> AND t2.field_name = 'field_2' AND t2.is_normalized = 0
88 -> AND t3.field_name = 'field_3' AND t3.is_normalized = 0
89 -> FOR UPDATE;
90Query OK, 3 rows affected (0.00 sec)
91Records: 3 Duplicates: 0 Warnings: 0
92
93mysql> UPDATE wp_cf7dbplugin_submits SET is_normalized = 1 WHERE is_normalized = 0;
94Query OK, 9 rows affected (0.00 sec)
95Rows matched: 9 Changed: 9 Warnings: 0
96
97mysql> COMMIT;
98Query OK, 0 rows affected (0.00 sec)
99
100mysql> SELECT * FROM wp_cf7dbplugin_submits;
101+-------------+-----------+------------+-------------+-------------+----------+---------------+
102| submit_time | form_name | field_name | field_value | field_order | file | is_normalized |
103+-------------+-----------+------------+-------------+-------------+----------+---------------+
104| 0.0000 | form | field_1 | value_1_1 | 1 | file_1_1 | 1 |
105| 0.0000 | form | field_2 | value_1_2 | 2 | file_1_2 | 1 |
106| 0.0000 | form | field_3 | value_1_3 | 3 | file_1_3 | 1 |
107| 1.0000 | form | field_1 | value_2_1 | 1 | file_2_1 | 1 |
108| 1.0000 | form | field_2 | value_2_2 | 2 | file_2_2 | 1 |
109| 1.0000 | form | field_3 | value_2_3 | 3 | file_2_3 | 1 |
110| 2.0000 | form | field_1 | value_3_1 | 1 | file_3_1 | 1 |
111| 2.0000 | form | field_2 | value_3_2 | 2 | file_3_2 | 1 |
112| 2.0000 | form | field_3 | value_3_3 | 3 | file_3_3 | 1 |
113+-------------+-----------+------------+-------------+-------------+----------+---------------+
1149 rows in set (0.00 sec)
115
116mysql> SELECT * FROM normalized_wp_cf7dbplugin_submits;
117+-------------+-----------+---------------+---------------+----------+---------------+---------------+----------+---------------+---------------+----------+
118| submit_time | form_name | field_value_1 | field_order_1 | file_1 | field_value_2 | field_order_2 | file_2 | field_value_3 | field_order_3 | file_3 |
119+-------------+-----------+---------------+---------------+----------+---------------+---------------+----------+---------------+---------------+----------+
120| 0.0000 | form | value_1_1 | 1 | file_1_1 | value_1_2 | 2 | file_1_2 | value_1_3 | 3 | file_1_3 |
121| 1.0000 | form | value_2_1 | 1 | file_2_1 | value_2_2 | 2 | file_2_2 | value_2_3 | 3 | file_2_3 |
122| 2.0000 | form | value_3_1 | 1 | file_3_1 | value_3_2 | 2 | file_3_2 | value_3_3 | 3 | file_3_3 |
123+-------------+-----------+---------------+---------------+----------+---------------+---------------+----------+---------------+---------------+----------+
1243 rows in set (0.00 sec)