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
MySQL server already provides two auto increment variables: auto_increment_increment and auto_increment_offset, which can be used to generate different auto increment values on each member.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
In MySQL, both SERIAL and AUTO_INCREMENT are used to define a sequence as a default value for a field. But they are technically different from each other. The AUTO_INCREMENT attribute is supported by all numeric data types except for BIT and DECIMAL.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With