MYSQL カラムに更新日時を追加していき、MAXを超えると古いものを消去して更新されていくようにする方法

MYSQLで、
あるカラムに、
更新日時を入れ、カンマで区切って追加していくとします。

それが100個を超えると、
古い日時から順に消して更新されていくようにしたいのですが、
どんな実装方法が考えられるでしょうか？

行動規範の内容に同意します

回答3件

直接的な回答ではありません。

あるカラムに、更新日時を入れ、カンマで区切って追加していくとします

テーブルの構造変更をお勧めします。

1カラム内にカンマ区切りで複数の値を持たせるような実装はお勧めしません。
設計として、1フィールドには1値。

元テーブルとの関連付けフィールド:IDをもち、2つ目に連番をいれて、この２つで主キーとし、
ID:A 連番:1 日付:2016/11/21
ID:A 連番:2 日付:2016/11/22
ID:A 連番:3 日付:2016/11/25

というテーブル構造にすれば、すっきり解決します。
元テーブルとの関連付けはIDと、最大の連番。

投稿2016/11/28 05:22

akio221

総合スコア716

KY6

2016/11/28 05:43

なるほど。どうしても既存の仕様の都合上、 1カラムに日付を連ねていく必要があるのですが、参考にさせていただきます。

行動規範の内容に同意します

SQLだとこんなかんじかなあ

set TheColName = CONCAT_WS(',',SUBSTRING(TheColName,INSTR(TheColName,',')+1) ,TheDate）

ホスト言語がPHPならPHPで文字列操作したほうがスマートだと思うけど。

投稿2016/11/28 06:09

katsuya141

総合スコア367

カンマ区切りで保存するのはDBとしてはありえないと思いますが
どうしてもそういう仕様がしたいというのであれば
更新日時は定形長でしょうから101個おさまる領域を用意しておいて
投入後100個分になるよう更新すればよいでしょう

#修正
よくよく考えたら101個収まる領域は不要ですね
100個になるとvarcharの範囲を超えるのでtextで処理します。
確保する領域もtextなので無視してよいでしょう

100個も例示できないのでとりあえず3個を上限とした場合
日時の標準的なフォーマットを以下とすると
2016-11-28 00:00:00・・・文字数=19、
これにセパレータをカンマとすると3個データ保持に必要なバイト数が
20*3-1=59

以下の例を参照してください

SQL
1create table tbl(id int not null primary key,datetimes text(59));
2insert into tbl values(1,(@a:='2016-11-28 00:00:00')) on duplicate key update datetimes=substring(concat(@a,',',datetimes),1,59);
3insert into tbl values(1,(@a:='2016-11-28 01:00:00')) on duplicate key update datetimes=substring(concat(@a,',',datetimes),1,59);
4insert into tbl values(1,(@a:='2016-11-28 02:00:00')) on duplicate key update datetimes=substring(concat(@a,',',datetimes),1,59);
5insert into tbl values(1,(@a:='2016-11-28 03:00:00')) on duplicate key update datetimes=substring(concat(@a,',',datetimes),1,59);
6insert into tbl values(1,(@a:='2016-11-28 04:00:00')) on duplicate key update datetimes=substring(concat(@a,',',datetimes),1,59);