SQLで日付別のIDを生成するには

お世話になります。

SQLで日付別の製造IDを生成するにはどのように記述すればよいでしょうか？

製品IDと登録日は自動的に採番登録されていて、
別途、以下のようにユニークな製造IDを付けたいと思います。

製品ID　｜　製造ID※
　 1　　｜　20221017-1
　 2　　｜　20221017-2
　 3　　｜　20221017-3
　 4　　｜　20221018-1
　 5　　｜　20221020-1
　 6　　｜　20221020-2
・・・
※ハイフンは無しで登録する予定で、DBに製品登録したら確定し
変更せず、製品IDを削除した場合はその製造ID番号も欠番する仕様です。

どの様にSQLを記述したら宜しいでしょうか？
ご教示頂けないでしょうか？<(_ _)>

質問者からの補足コメント

• ご返信ありがとうございます。説明不足でお手数をお掛けしております。
  
  サーバーの仕様は
  MySQL 5.7.x　又は MariaDB 10.5.x　です。
  
  登録日は、created_at　として　datetime型で取得予定です。
  
  ＞製造IDは、登録日毎に製品ID順に1からの通番にするのですね？
  はい。
  
  ＞通番部分は、何文字にするのですか？
  決めていなかったのですが４桁あればOKだと思います。
  
  ＞固定長で前ゼロを入れるようにしないと、大小関係が崩れるのでソートなどで問題になります。
  ソートは製品IDで行う予定でした。
  
  宜しくお願いいたします。<(_ _)>

  No.1の回答に寄せられた補足コメントです。 補足日時：2022/10/09 11:12

No.3 ベストアンサー 回答者： 忠犬 回答日時： 2022/10/10 10:51

INSERT INTO tbl

SELECT
NULL
,dttm
,CONCAT(ch_dt,'-',seq) AS 製品ID
FROM(
--
SELECT
dttm
,dt
,DATE_FORMAT(dt,'%Y%m%d') AS ch_dt
,(
SELECT
COALESCE(
MAX(CAST(RIGHT(製造ID,LENGTH(製造ID)-9) AS INT))
,0)+1
FROM tbl
WHERE DATE(create_at)=dt
) AS seq
FROM(
SELECT
NOW() AS dttm
,CURRENT_DATE AS dt
) AS x
--
) AS t1
;

この回答へのお礼

詳しくご教示賜り御礼申し上げます。
一度テストしてみて、性能も考慮して再考してまいります。
ありがとうございました。
<(_ _)>

お礼日時：2022/10/10 11:16

No.2 回答者： 忠犬 回答日時： 2022/10/10 10:49

日付と日付毎通番の列を持ち、この二つでユニークキーにした方がいいです。

この二つの列から、日付+日付毎通番をビューなどで表示する仕組みにした方がいいです。

日時(または日付)、日付毎の最大通番
↓↑
日付と日付毎通番を文字連結した値

この方式だと、この操作部分だけで冗長なSQLになり、性能も出せません。
どれくらい冗長になるか、この後、提示します。
ハイフンがある形式(長さが1バイト長い)で、MySQL 5.7.xでもMariaDB 10.5.xでも動くと思います。

No.1 回答者： 忠犬 回答日時： 2022/10/09 11:01

RDBMSは選択したカテゴリ通り、MySQLですか？

MySQLの場合、バージョンはMySQL 8ですか？5.7とかですか？

登録日のデータ型は、DATE型ですか？CHAR(8)だったりしますか？

製造IDは、登録日毎に製品ID順に1からの通番にするのですね？
通番部分は、何文字にするのですか？
固定長で前ゼロを入れるようにしないと、大小関係が崩れるのでソートなどで問題になります。

固定長にせず、前ゼロもなし
昇順
20221017-111
20221017-22
20221017-3

固定長にして、前ゼロを入れる
昇順
20221017-003
20221017-022
20221017-111