php - MySQL AUTO_INCREMENT according to year -

im creating ticketing system , 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 happen sipnum numbered according year.

example: 20140001, 20140002, ..20140334, 20140335....

how make change first 4 digits automatically everytime next year comes, create new\another set of auto_incremented numbers

example: 20150001, 20150002........ 20160001, 20160002..

btw, im using php code program, in case if solution creating function. thanks

you can use mysql custom auto_increment values follows: (read article first)

create table , 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 each row begin    set new.year_autonum = getnextcustomseq(year(now()),year(now())); end//  delimiter ; 

insert values:

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

select data:

mysql> select * 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 procedure getnextcustomseq ommit slash - sign.