Re: Re: auto_increment every year

[Paul DuBois]

sql,query,queries,smallint


>   > HI all
>>   I'm searching in how to generate a auto_increment number starting form 1
>>   every year
>>   I mean
>>
>>   ID DATE
>>   1  2003
>>   2  2003
>>   ....
>>   1  2004
>>   2  2004
>>   etc
>   > maybe possible or I've to use some c code to make it possible ?

Yes, this is possible.  Use a MyISAM table and set up a composite
index on the two columns, with the AUTO_INCREMENT column as the
second column in the index.

CREATE TABLE t
(
      yr   YEAR NOT NULL,
      id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
      PRIMARY KEY(yr,id)
) TYPE = MyISAM;

This will generate an independent sequence of numbers for each distinct
yr value.

INSERT INTO t (yr) VALUES(2003),(2003),(2004),(2005),(2005);
SELECT * FROM t;

+------+----+
| yr   | id |
+------+----+
| 2003 |  1 |
| 2003 |  2 |
| 2004 |  1 |
| 2005 |  1 |
| 2005 |  2 |
+------+----+