CODEWITHSUNDEEP
mysqldatabase
leo leone
leo leone

Reputation: 63

Mysql reset number daily

I want to reset number daily,

Table : 

CREATE TABLE tickets(
    ticketId varchar(10),
    moviesname text,
    date DATE,
    seatnumber INT(5),
    PRIMARY KEY(ticketId));

is there anything SQL Syntax to reset the id daily ?

Upvotes: 2

Views: 2709

Answers (1)

Shadow
Shadow

Reputation: 34231

I see 2 possible approaches:

1. Use myisam table's special auto_increment feature:

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.

  • Make sure that tickets table is myisam.
  • tickeid column must be auto_increment
  • change the primary key to be date, tickeid. The order of the columns is paramount!!!!

2. Spice-up your current max() query to determine the next value

  • Change the primary key to be date, tickeid. Order of the fields is not important. You cannot leave ticketid to be the pk alone, since you want to restart the numbering daily, which would lead to duplicate index value error message.

  • Use the following query to get the next ticketid:

    select coalesce(max(ticketid),1) from tickets where date=curdate()

The where criteria ensures that every day the ticketid values would restart. The coalesce() is there to assign the number 1 as the first id every day, since max() returns null if there are no records to satisfy the where criteria.

However, the risk with this solution is that multiple statements executed at the same time may yield the same id. Either you handle this situation with proper error handling within your application code (e.g. attempt to insert again), or you serialise the inserts into the tickets table by using explicit locks.

Upvotes: 1

Related Questions