0

Is there a way in mysql to auto increment an ID when the date is changed?

I have a table like this and the id must be auto increment when the day is changed.

ID Name Date Time
1 Adi 2022-06-29 17:00:00
1 Adi 2022-06-29 17:00:00
1 Adi 2022-06-29 17:00:00
2 Adi 2022-06-30 17:00:00
2 Adi 2022-06-30 17:00:00
3 Adi 2022-07-01 17:00:00
3 Adi 2022-07-01 17:00:00
4 Adi 2022-07-02 17:00:00
4 Adi 2022-07-02 17:00:00
Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Vrinceanu Andrei
  • 1
  • 2
  • Why not auto-generate the `id` column? `id AS datediff(date_time, curdate())`. – The Impaler Jun 29 '22 at 12:56
  • 1
    Im a bit confused. Why do some IDs exist multiple times? – MF714 Jun 29 '22 at 13:04
  • 1
    Do not store, calculate in the query using DENSE_RANK(). – Akina Jun 29 '22 at 13:10
  • 1
    @MF714 It seems that for some reason the OP wants to add redundancy to the table. And also, the column name `ID` is poorly chosen, since it implies uniqueness. – The Impaler Jun 29 '22 at 13:18
  • Adding a group-wise auto increment field to an innodb table is not straightforward. You can use triggers to accomplish the task, but you are better positioned to calculate it on the fly as @Akina has suggested. – Shadow Jun 29 '22 at 13:26

0 Answers0