MySQL AUTO_INCREMENT according to year

Question

Im creating a ticketing system and this is my table structure:

CREATE TABLE tix_sip
(
tktNum INT UNSIGNED NOT NULL,
sipNum INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY( sipNum ),
FOREIGN KEY(tktNum) REFERENCES Tix (tktNum)
);

what I would like to happen with my sipNum is to be numbered according to year.

Example: 20140001, 20140002, ..20140334, 20140335....

How do I make it change the first 4 digits automatically so everytime next year comes, it will create new\another set of AUTO_INCREMENTed numbers

Example: 20150001, 20150002........ 20160001, 20160002..

btw, Im using php code for my program, in case that could help if a solution would be creating a function. Thanks

Why do you want to do this as opposed to using a composite key? — Joe Phillips, Jun 10 '14 at 01:15
please enlighten me on your idea. I dont understand composite keys that much — user3663049, Jun 10 '14 at 02:03
I hope this helps: http://stackoverflow.com/questions/14373582/how-to-auto-increment-by-2-for-a-perticular-table-in-mysql — stepozer, Jun 10 '14 at 02:20
I missed a part of your question so I don't think composite keys will help. Also, I still don't think you've provided a good reason for wanting to do this. I have a feeling there are better solutions once you're able to describe your reasoning — Joe Phillips, Jun 10 '14 at 05:23
Related: [Auto Increment including Year and Month in MySql](https://stackoverflow.com/q/14828807/2943403) and [reset auto-increment each year with new prefix](https://stackoverflow.com/q/41552237/2943403) and [MYSQL autoincrement with date day year](https://stackoverflow.com/q/64494713/2943403) and [MySQL Field AUTO_INCREMENT according to YEAR](https://stackoverflow.com/q/30727435/2943403) and [Auto-increment - automatic reset for each year](https://stackoverflow.com/q/30167587/2943403) — mickmackusa, Apr 19 '23 at 11:33

score 0 · Accepted Answer · answered Jun 10 '14 at 03:26

You can use MySQL Custom AUTO_INCREMENT values as follows: (read the article first)

Create table and trigger:

CREATE TABLE test
(
    id int not null auto_increment primary key,
    year_autonum varchar(20)
);

delimiter //
DROP TRIGGER IF EXISTS custom_autonums_bi//

CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON test
FOR each ROW
BEGIN
   SET NEW.year_autonum = getNextCustomSeq(year(now()),year(now()));
END//

delimiter ;

Insert some values:

insert into test(id) values (null);
insert into test(id) values (null);
insert into test(id) values (null);
...

Select data:

mysql> select * from test;
+----+--------------+
| id | year_autonum |
+----+--------------+
|  1 | 2014-000001  |
|  2 | 2014-000002  |
|  3 | 2014-000003  |
|  4 | 2014-000004  |
|  5 | 2014-000005  |
|  6 | 2014-000006  |
+----+--------------+
6 rows in set (0.00 sec)

You can change the procedure getNextCustomSeq to ommit the slash - sign.

MySQL AUTO_INCREMENT according to year

1 Answers1

Linked

Related