質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

4回答

2470閲覧

sql:複数レコードを塊として、IDを後から振りたい

TomoKubota

総合スコア53

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

1クリップ

投稿2017/02/03 02:12

編集2017/02/06 00:35

*WordPressのContactFormDBというプラグインで使用されるテーブルを前提として質問させていただきます。

テーブル構成は以下の通りです。
mysql> DESC wp_cf7dbplugin_submits;
+------------- +--------------- +------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------- +--------------- +------+-----+---------+-------+
| submit_time | decimal(16,4) | NO | MUL | NULL | |
| form_name | varchar(127) | YES | MUL | NULL | |
| field_name | varchar(127) | YES | MUL | NULL | |
| field_value | longtext | YES | | NULL | |
| field_order | int(11) | YES | | NULL | |
| file | longblob | YES | | NULL | |
+------------- +--------------- +------+-----+---------+-------+

WordPressのフォームを送信すると、
1.送信時刻
2.フォーム名
3.フィールド名
をキーとして、各フィールドごとにレコードが作成されます。
単純に説明すると、3つのフィールドを持つフォームで送信を行うと、
3つのレコードが作成されることになります。

このテーブルに新たに「ID」というフィールドを追加したとして、
1回の送信ごとに(上記の例で言えば3レコードを一つの塊として)、
ユニークな数値を振っていきたいと思っています。

このような処理を、バッチ処理で1日一回行おうとした場合に、
どのようなSQLを実行することで可能になりますでしょうか。

DBはmysql、
OSはCentos6.5です。

よろしくお願いいたします。

追記
この質問を記入させていただく時点では、
ストアドファンクション(テーブルをロックしたくないので)を作成し、
IDがnullのレコードを上記3つのキーごとにグループ化してcountし、
whileを使ってループするような感じで行けるのかな、
くらいの認識ではおります。

ただ、ストアドファンクションは書いたことがなく、
もしこれを使用する場合には、この点も考慮していただくと大変ありがたいです。

追記2 show indexの結果です
mysql> SHOW INDEX FROM wp_cf7dbplugin_submits;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_cf7dbplugin_submits | 0 | PRIMARY | 1 | submit_time |A | 173 | NULL | NULL | | BTREE | | |
| wp_cf7dbplugin_submits | 0 | PRIMARY | 2 | form_name |A | 173 | NULL | NULL | | BTREE | | |
| wp_cf7dbplugin_submits | 0 | PRIMARY | 3 | field_name |A | 1038 | NULL | NULL | | BTREE | | |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+

追記3 質問の一部を修正させていただきます。
すみません、やりたいことが明確になっておらず、質問の内容を変えさせていただくことになってしまいました。

上記テーブル構造で、
現状、投稿時間、フォーム名、フィールド名をキーとしており、
WordPressのフォームで投稿を行うと、そのフォームのフィールド数分、
レコードが作成されることとなります。
もし別にテーブルを一つ用意し、
フィールド名とその値を横持ち(RowではなくClmとして)するようにInsertしていくようなSQLを書きたいと考えています。

現在のテーブルに変換済みFLGのようなフィールドを持たせ、
上記インサート処理が終了したら、FLGをtrueにするようにして、
変換処理が完了しているかを確認できるようにしておきたいです。

ですのでやはりやりたいこととしては、
FLGが立っていないデータを塊ごとにselectし、
これをもとに一意のレコードを作成し、
別テーブルにinsertする、
というような流れになるかと思います。

上記内容をストアドファンクションで作成することになるのかな、
と思っています

回答への御礼
みなさま、ご回答に御礼申し上げます。
また返信が遅くなってしまった方については、申し訳ありません。
ベストアンサーについては大変迷ったのですが、
データを横持ちしたい、という当方の要望を正確にテストまで実行いただいた方とさせていただきました。

この度の質問では大変勉強になり、
お時間を割いてくださった方には、繰り返しになりますが、
本当にありがとうございました。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

KiyoshiMotoki

2017/02/03 02:32

どのようなインデックスが張られているかも確認したいため、SHOW CREATE TABLE wp_cf7dbplugin_submits; という SQL文の実行結果も、追記願います。
KiyoshiMotoki

2017/02/03 02:35

あと、差し支えなければ、何の目的で ID を振ろうとしているのかも、教えてください。
KiyoshiMotoki

2017/02/03 03:59

インデックス情報の追記、ありがとうございます。以下、"追記3"について、確認させてください。
KiyoshiMotoki

2017/02/03 03:59 編集

元の wp_cf7dbplugin_submits では、データの「塊」ごとのフィールドの数(すなわち、Row の数)は不定となります。しかし、MySQL ではカラム数(Clm の数)が不定のテーブルは作成できません。その点は、どのように対処する想定ですか?
KiyoshiMotoki

2017/02/03 04:04

もしくは、「データの塊ごとのフィールド数は一定」という前提で、構いませんか?
TomoKubota

2017/02/03 08:22

フィールドの数は一定ですので、カラム数が変わることはございません。
KiyoshiMotoki

2017/02/03 12:40

情報の追記、ありがとうございます。回答をご確認ください。
guest

回答4

0

ベストアンサー

以下を前提に、回答させていただきます。

  • 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)

投稿2017/02/03 12:39

KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

TomoKubota

2017/02/06 00:31

KiyoshiMotokiさま 返信が週をまたいでしまい失礼をお詫びいたします。 私の環境で実行したところ、意図通りに結果を得ることができました。 データを横持ちにするためにjoinとwhereをこのように使えることには思い至りませんでした。 シンプルかつわかりやすく、大変勉強になりました。 ありがとうございました。 私のつたない質問に対して、辛抱強くコメントで内容を確認してくださったことについても、 深く御礼申し上げます。
guest

0

※手元にMYSQLが無いのでSQLをよく使う身として回答します。
※回答文のSQLはここで書いただけで動作確認がとれてません

もしもデータの保存処理を少しでも書き換えられるなら、ID列の型をGUIDにして、適当なGUIDを生成して一緒に代入してしまうのが一番手っ取り早いです。MySQLにはGUID型が無いので、ユニークな文字列として登録します。

しかし、それができない場合はID発行用のテーブルで別途管理していくプロシージャで処理するかな、と思います。以下はそのサンプルです。


最新より古いsubmit_timeが登録されるようなことがあるとコレはちょっとダメになってしまいますが、最後にID列を振ったところの続きからID列を振りなおすように配慮しています。

1. ID列発行用のテーブルを作る

通常、このテーブルは以下のように正規化されると思います。

submit

  1. id
  2. submit_time
  3. form_name

submit_detail

  1. id
  2. field_name
  3. field_value
  4. field_order
  5. file

なので、ID列発行用にid, submit_time, form_nameでテーブルを作ってしまいます。

SQL

1CREATE TABLE wp_cf7dbplugin_submits_identities( 2 id MEDIUMINT NOT NULL AUTO_INCREMENT, 3 submit_time decimal(16,4) NOT NULL, 4 form_name varchar(127));

2. ID列発行用のテーブルの最大のsubmit_timeをとってくる

バッチの最初にこのテーブルを検索して最大のsubmit_timeを取得します。
submit_timeが入れ替わることが無い場合は基本的にはこれで済みます。

SQL

1SELECT MAX(submit_time) FROM wp_cf7dbplugin_submits_identities;

必要そうならsubmit_timeにインデックスを張ったり、MAX(id)を調べてそのsubmit_timeを取ってくるとかなんかやってください…。


3. submit_time以降のレコードをgroup byしID列発行テーブルに挿入する

SQL

1INSERT INTO wp_cf7dbplugin_submits_identities(submit_time, form_name) 2SELECT submit_time, form_name FROM wp_cf7dbplugin_submits 3GROUP BY submit_time, form_name WHERE submit_time > ?/*最大時間*/

4. ID列発行テーブルから元のテーブルにID列をコピーする

SQL

1UPDATE 2 wp_cf7dbplugin_submits t1, 3 wp_cf7dbplugin_submits_identities t2 4SET t1.id = t2.id 5WHERE 6 t1.submit_time = t2.submit_time 7 AND t1.form_name = t2.form_name 8 AND t2.submit_time > ? /*最大時間*/

で、これをストアドにすると以下みたいな感じじゃないかと思います。
テーブルは別に作っておきます。

  1. 複数のSQL文を実行するのでトランザクションをかけます
  2. クエリが失敗した時はエラーハンドリングでロールバックします
  3. コマンドを実行します
  4. コミットします

SQL

1CREATE TABLE wp_cf7dbplugin_submits_identities( 2 id MEDIUMINT NOT NULL AUTO_INCREMENT, 3 submit_time decimal(16,4) NOT NULL, 4 form_name varchar(127)); 5 6CREATE PROCEDURE wp_cf7dbplugin_submits_grant_id() 7BEGIN 8 START TRANSACTION; 9 10 DECLARE EXIT HANDLER FOR SQLEXCEPTION 11 BEGIN 12 ROLLBACK; 13 EXIT PROCEDURE; 14 END; 15 16 SELECT MAX(submit_time) FROM wp_cf7dbplugin_submits_identities INTO @submits; 17 18 INSERT INTO wp_cf7dbplugin_submits_identities(submit_time, form_name) 19 SELECT submit_time, form_name FROM wp_cf7dbplugin_submits 20 GROUP BY submit_time, form_name WHERE submit_time > @submits; 21 22 UPDATE 23 wp_cf7dbplugin_submits t1, 24 wp_cf7dbplugin_submits_identities t2 25 SET t1.id = t2.id 26 WHERE 27 t1.submit_time = t2.submit_time 28 AND t1.form_name = t2.form_name 29 AND t2.submit_time > @submits; 30 31 COMMIT; 32END

submit_timeで対象レコードをフィルタリングしているので全体で1回トランザクションをかけてしまっています。ID列の生成と反映を別にするならもっと細かいロックにできると思います。
また、各クエリでorder by句等は不要なはずなのでつけていませんが、MySQLの場合にも不要ということを確認しているわけではないので別途ご確認ください。

※念のため再掲:回答文のSQLはここで書いただけで動作確認がとれてません

投稿2017/02/03 04:54

haru666

総合スコア1591

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

TomoKubota

2017/02/03 08:21

haru666さま 詳細なご回答をいただき、恐れ入ります。 submit_timeが逆転してしまうことは、pluginの不具合位でしか発生しえないものと思われます。 まだテストはできていないのですが、処理の流れを見させていただき、 ほぼ私が実現したい内容に沿っているのではないかと思います。 ・ID発行用のテーブルを作る ・トランザクションをかける などの処理は、普段select位しか書かない身からすると大変参考になり、 今後の仕事でも大いに活かすことができる内容でございました。 深く御礼申し上げます。
guest

0

(1)autoincrementのIDを有する別のテーブルAを用意
(2)そのテーブルに、3つの情報を何らかの形で表現するフィールドを用意
そのまま転記するでも、ハッシュなどでも
(3)Aに対して、(2)と比較し、レコードが存在しなければレコードを追加
(4)上記(3)で付番されたIDを、元々振りたいIDに設定する

・IDが数値である必要はない場合、3つの情報から、ユニークになるような文字列にしてしまうのが手っ取り早い。数値にしようとすると、それなりに大きな数字になる
・投稿時であれば、(2)はあまり必要ではなく、投稿と同時に、テーブルAにレコードを追加し、そのIDを採用するだけ

投稿2017/02/03 02:54

t_obara

総合スコア5488

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

ちょっと状況がわからないので考え方だけ
本質的にIDというのはレコードを特定するキーになる箇所なので
ユニークなコードでNULLは指定させないものです。
なにか仕様として問題ありそうな気がしますが、
運用でカバーする感じでしょうか?

逆にIDは先に振って、投入データをIDを指定してUPDATEするとかでは
いけないのでしょうか?

投稿2017/02/03 02:40

yambejp

総合スコア114572

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問