質問: 2ちゃんねるのようにスレッドごとに一意の連番が付与されるような機能を実現したいと思っているのですが、 良い方法が思いつきません。 書き込みテーブル | ID(主キー、自動連番) | スレッドID | 書き込み番号 | 内容 | | --- | --- | --- | --- | | 1 | 1 | 1 | hogehoge | | 2 | 1 | 2 | fugafuga | | 3 | 1 | 3 | piyopiyo | | 4 | 2 | 1 | foofoo | | 5 | 2 | 2 | barbar | | 6 | 3 | 1 | fizfiz | | 7 | 3 | 2 | bazbaz | | 8 | 3 | 3 | dardar | パッと思いついたのは ``` INSERT INTO 書き込み(スレッドID, 書き込み番号, 内容) VALUES (1, (SELECT MAX(書き込み番号) + 1 FROM 書き込みテーブル WHERE スレッドID=1), 'nyaaa'); ``` こんな感じにSQL中でグループの書き込み番号MAX値+1を取得する方法ですが、 これだけでは場合によっては重複などの不整合を起こす可能性があるように思えます。 ユニーク制約で弾くことはできそうですが、できればエラー自体発生させたくありません。 良い感じに実現する方法はあるでしょうか・・・ お知恵を貸していただけると幸いです。
回答: ### 序 最近はWEBプログラムもSQLも書いていないのでちょっとしたリハビリです。間違っていたらすいません。 気付いた人いたら、どんどん訂正してください。 ### レス番管理テーブルを作ったらどうでしょう? 商業アプリとかでは割とよくやる手法ですが、レス番だけのテーブルを作ります。 | スレッドID(PRIMARY KEY AUTO_INCREMENT) | レス番(INT NOT NULL) | | --- | --- | | 12345 | 1 | | 12346 | 5 | | : | : | ### 排他処理をしながらレス番を取得 該当スレッドに書き込みを行いたい時は、まずトランザクションを開始した後、レス番管理テーブルに対してスレッドID指定でSELECTをかけてレス番を取得しますが、その際、該当レコードをロックして悲観的排他処理を行います。 ``` START TRANSACTION; SELECT `レス番` AS MY_NUM FROM `レス番管理テーブル` WHERE `スレッドID` = '該当スレID' FOR UPDATE; ``` これで、別の接続が同じスレッドIDのレス番を取得しようとしても、上記の**トランザクションがコミットされるまで読み取りが行えず、別の接続は待機**します。 ### 書き込みテーブルに書き込みを行った後、レス番管理テーブルをUPDATEしてコミット ``` INSERT INTO `書き込みテーブル` (`スレッドID`, `レス番`, `内容`) VALUES ('該当スレID', MY_NUM + 1, 'あいうえお'); UPDATE `レス番管理テーブル` SET `レス番` = MY_NUM + 1 WHERE `スレッドID` = '該当スレID'; COMMIT; ``` トランザクションがコミットされると、待機していた別の接続の試みが自動で実行されるはずです(タイムアウトしていない場合)。 ### 終わりに なお、トランザクションが必須になるので、InnoDBなどのストレージエンジンを利用してください。 以上