7

I'm quite new to setting up tables in MySQL and there is something I'd like to do which is a bit more advance than I'm able to do.

I have two columns as part of a composite primary key, one is a Date and an ID I would like to be an auto increment integer. For each date, I would like to reset the auto integer to 0, so something like this:

|-----------------|
|Date       | ID  |
|-----------------|
|2012-06-18 | 1   |
|2012-06-18 | 2   |
|2012-06-18 | 3   |
|2012-06-19 | 1   |
|2012-06-19 | 2   |
|2012-06-20 | 1   |
|-----------------|

Thanks

AdmiralJonB
  • 2,038
  • 3
  • 23
  • 27

4 Answers4

6

Here this should work.

CREATE TABLE  `answer`(
  `dates` DATE NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`dates`,`id`)
) ENGINE=MyISAM;

It is known to cause problems with innoDB. Hope this helps you.

EDIT: RESULTS

2012-06-19  1
2012-06-19  2
2012-06-19  3
2012-07-19  1
2012-07-19  2
2012-08-19  1

On php myadmin.

ppsreejith
  • 3,318
  • 2
  • 25
  • 27
5

Well, for me mysql does what you want automatically.

mysql> CREATE TABLE TestData(Date date not null, ID int unsigned not null auto_increment, PRIMARY KEY(Date, ID));

mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-19";
mysql> INSERT INTO TestData SET Date = "2012-06-19";
mysql> INSERT INTO TestData SET Date = "2012-06-20";

mysql> select * from TestData;
+------------+----+
| Date       | ID |
+------------+----+
| 2012-06-18 |  1 |
| 2012-06-18 |  2 |
| 2012-06-18 |  3 |
| 2012-06-19 |  1 |
| 2012-06-19 |  2 |
| 2012-06-20 |  1 |
+------------+----+

No magic involved.

Johan Soderberg
  • 2,650
  • 1
  • 15
  • 12
  • That CREATE results in: `Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key.` – Richard Jun 19 '12 at 12:11
  • @RichardKing: Oh? Not for me, what version are you using? Tested on 5.1.51-log, have used it on way older mysql versions too (since mysql 3). Table is MyISAM. – Johan Soderberg Jun 19 '12 at 12:24
  • Thank you. Using the `ENGINE=MyISAM` solved my problem. – Killer Oct 10 '17 at 16:16
  • @JohanSoderberg that's a perfect way to solve the problem, but how to do this with innoDB engine? can we ? – Andy Su Dec 07 '21 at 03:28
3

You can create a before insert trigger.

DELIMITER $$
CREATE TRIGGER `composite_auto_increment` BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
    DECLARE max_id INT(11); -- add the appropriate column length from your table definition
    SELECT ID FROM `your_table` WHERE `Date` = DATE(NOW()) INTO max_id;
    SET NEW.ID = IF(ISNULL(max_id), 1, max_id + 1);
END$$

This way, if and ID already existed for the day, it gets incremented. If it didn't, it gets set to 1. Note that in this scenario, ID isn't AUTO_INCREMENT in the table definition. It just gets done by the trigger.

cypher
  • 6,822
  • 4
  • 31
  • 48
  • 2
    This is wrong, you need to lock the table before getting the max value. If you don't, 2 triggers that run at the same time will get the same identifier – mavroprovato Jun 19 '12 at 11:39
  • Well, in MyISAM, inserts don't run concurrently. In InnoDB, you can lock. Good point though. – cypher Jun 19 '12 at 11:48
0

In trigger: SELECT ID FROM your_table WHERE Date = DATE(NOW()) INTO max_id; must be: SELECT max(ID) FROM your_table WHERE Date = NEW.key_field INTO max_id;

but better is lock by key. this is better for concurrent inserts on innodb.

huskye
  • 1
  • 1