MySQLで連番をINSERTと同時に挿入する方法

Question

###実現したいこと
レコードのINSERT時に、「グループごとのカラムの最大値+1」を自動で入れたいのですが、そんな都合のいい方法ございますか？

###テーブル構成
以下はコメントのテーブルで、order_numberでコメントの並び順が指定されています。
尚、order_numberはユーザーが任意に変更できるようになっています。

CREATE TABLE p_comments
    (`ID` int, `thread_ID` int, `order_number` int)
;

INSERT INTO p_comments
    (`ID`, `thread_ID`, `order_number`)
VALUES
    (100, 11, 1),
    (101, 12, 1),
    (102, 13, 1),
    (103, 11, 2),
    (104, 13, 2),
    (105, 12, 2), # thread_ID = 12 の最大
    (106, 11, 3), 
    (107, 13, 3), # thread_ID = 13 の最大
    (108, 11, 4)  # thread_ID = 11 の最大
;

例えばスレッドthread_ID = 12にコメントすると以下実行しますが、このとき同一スレッドにおけるorder_numberを自動で最大としてorder_number = 3を入れたいということです。
（現状のthread_ID = 12の最大のorder_numberは2であるためです。）

INSERT INTO p_comments
    (`ID`, `thread_ID`, `order_number`)
VALUES
    (109, 12, 3)
;

###試している方法
クエリを2回発行すれば、目的は達成できることがわかりました。
つまりまず1回目に以下を実行して最大のorder_numerである2を得ます。
続いてPHPでその2に+1して、2回目のクエリで上記を実行するという方法です。
ですがこの2回のクエリにやや冗長さを覚えましたために、もし1回で済ませることができればと思い質問させて頂きました。

SELECT `order_number`
FROM `p_comments`
WHERE thread_ID = 12
ORDER BY `order_number`
DESC LIMIT 1;

また、検索しますと以下リンクを見つけまして、

[MySQL] 連番をINSERTと同時に挿入する方法

https://b.0218.jp/20121112133354.html

この方法に従って次のSQLを実行したのですが、実現には至りませんでした。

INSERT INTO p_comments( `order_number`, `ID`, `thread_ID` )
SELECT COALESCE(MAX(`order_number`)+1,1), ?, ?
FROM p_comments;

###発生している問題
上記「実現には至りませんでした」について詳細補足します。

エラーはないのですが、order_numberが想定より大きな数字になってしまいます。

例えば現状でthread_ID = 12には2件のレコードがあったとして、order_numberは最大2ということですが、そこに上記実行しますとorder_numberに227が入るといった感じです。

この227が何の数字なのかわかりません。p_commentsテーブルのレコードは2121件ですし、思い当たるフシがないです。

さらにその上でもう一度実行すると228が入りこれは227の次なので正常ですが、そもそも227でなく3を入れたかったので困っています。

###バージョン
MySQLは5.7ですが、もし上位のバージョンでのみ可能であればアップデートも視野に入れています。
宜しくお願い致します。

Comments

[maisumakun]

「実現には至りませんでした。」とありますが、具体的にどのような状況になりました？

エラーが出るなら、そのエラーの内容を書いてください。

[ikatako]

失礼いたしました。エラーはないのですが、order_numberが想定より大きな数字になってしまいます。その旨「発生している問題」へ追記いたしましたので、ご確認頂けましたら幸いです。

[CHERRY]

試したことのSQLでは、select 文に条件をつけずに order_number を MAX しているので、どこかのthread_IDに 226 が存在しているから 227 になるとおもいます。
select 文を thread_ID で絞り込むとどうなりますか？

[ikatako]

すみません、コメントに今気づきました。
> どこかのthread_IDに 226 が存在している
とのことで以下実行しましたが、結果は０件でした。
SELECT * FROM `p_comments` WHERE `thread_ID` = 226

[Yith]

質問に書かれているSQLは
> SELECT COALESCE(MAX(`order_number`)+1,1), ?, ?
なので、これだとorder_numberの最大値＋１を取得してますが、ikatakoさんの返答にあるクエリ
> SELECT * FROM `p_comments` WHERE `thread_ID` = 226
だと、thread_IDが226のレコードを取得しようとしてます。
226の数字を出どころを求めるなら、
SELECT * FROM p_comments WHERE order_number = 226 では？

Answer 1

こうでしょうか。

insert into p_comments
    (`ID`, `thread_ID`, `order_number`)
select null, 12, ifnull(
   (select max(`order_number`) + 1 from p_comments where thread_ID = 12), 1
);

IDにはAUTO_INCREMENTが指定されていて、
thread_IDをアプリケーションからバインドする場合
の想定です。

Comments

[ikatako]

どうもありがとうございます。おかげ様で楽に実装できました。

ちなみに次の結果によれば、IDにAUTO_INCREMENTがなくても、アプリケーションからバインドすれば（26行目で109を指定すれば）できるみたいです。
http://sqlfiddle.com/#!9/a23474/1

Answer 2

functionで処理すれば不可能ではないですが、抜け番が出たりすると
結局番号のフリ直しが必要になったりするので、
逐次ランク処理をするのが賢明です

Comments

[ikatako]

いつもありがとうございます。検索しているうちにyambejp様のご回答を見つけました。
https://teratail.com/questions/161776

これを参考に以下を書きましたら実現出来たようで今喜んでいるところなのですが、以下ですと何か不安が残りますでしょうか。たとえばどこかの値が保証されなかったりなど？

```
$ID = 109;
$thread_id = 12;
$sql = "
INSERT INTO p_comments( ID, thread_ID, order_number )
SELECT *
FROM(
SELECT {$ID}, {$thread_id}, COALESCE(( # 初めてに備えて COALESCE を使う
SELECT order_number +1
FROM p_comments AS comments1 # テーブルロックで複数の同名が使えないためエイリアス付与
WHERE thread_ID = {$thread_id}
ORDER BY order_number
DESC LIMIT 1
),1) # AS order_number
FROM p_comments AS comments2 # 同じくエイリアス付与
) AS sub
ON DUPLICATE KEY UPDATE order_number = VALUES(order_number)
";
```

[yambejp]

/* functionを使ったサンプル */
drop function if exists next_number;
delimiter //
create function next_number(thread int) RETURNS int DETERMINISTIC
begin
select coalesce(max(order_number),0)+1 into @a from p_comments where p_comments.thread_ID=thread;
return @a;
end
//
delimiter ;

[yambejp]

/* insert処理 */
INSERT INTO p_comments
(`ID`, `thread_ID`, `order_number`)
VALUES
(100,@a:=11,next_number(@a)),
(101,@a:=12,next_number(@a)),
(102,@a:=13,next_number(@a)),
(103,@a:=11,next_number(@a)),
(104,@a:=13,next_number(@a)),
(105,@a:=12,next_number(@a)),
(106,@a:=11,next_number(@a)),
(107,@a:=13,next_number(@a)),
(108,@a:=11,next_number(@a));
;

[yambejp]

この変数の使い方は保証外なので、ちゃんとやるなら変数ではなく
実数を入れる必要があります

[ikatako]

ご返信ありがとうございます。そちらのfunctionですが、2020/11/04 10:53 に記載いたしましたコード（http://sqlfiddle.com/#!9/2c6e8a/1）よりも、おすすめな理由があるのでしょうか？
どちらもyambejp様のもので、どちらがなぜ良いのか、知りたいです。

[ikatako]

あ、わかりました。2020/11/04 10:53 に記載いたしましたコードでは、レコードが0件のときにinsertされないというわけですね。いまTRUNCATE TABLEをしてそのことに気が付きました。どうもありがとうございました。