Mysql autoincrement column that resets every month

The autoincrement feature in mysql allows us to create numeric fields that automatically increment by 1 for every row inserted. It does it atomically so we don't have to worry about any duplicates or collision.

However, there are situations when we need an autoincrement field to reset at regular intervals like every month or so.

For example if you are generating batch numbers, call numbers or invoice numbers that you want to track month-wise, then you need numeric ids that reset every month.

Take for example invoice Numbers:

Month of october

2011-10-01
2011-10-02
...
...
2011-10-800

Month of november

2011-11-01
2011-11-02
...
...

The autoincrement numbers are basically grouped for each month separately.

Example of Group-wise autoincrement:
P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 , R1 , P4 ...

Resetting autoincrement in mysql

There is no standard feature or specification in database systems to implement such a column that resets on certain conditions. However there is a way to create such an autoincrement field in mysql.

The trick is to use a Myisam table with a composite primary key made of a text column and an autoincrement numeric column.

The MyIsam storage engine has a feature where , if a primary key contains 2 columns where one is an autoincrement and other is a varchar , then for each value of the text column the autoincrement column will create separate sequence of autoincrement numbers.

Table Structure

CREATE TABLE IF NOT EXISTS `vt_sequences` (
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

In the above table type and id columns form a composite primary key.

Now if we insert different values in the type column repeatedly, then the id column shall have autoincrement values that are grouped by each unique value of the type column.

INSERT INTO `vt_sequences` (`type`) VALUES
('A'),
('A'),
('B'),
('B'),
('A');

The resulting values for the type and id columns should be:

A 1 
A 2 
B 1 
B 2 
A 3

So the id column has a 1 for A and B separately. So now if we want autoincrement numbers resetting every month we could insert 2011-10 and 2011-11 in the type field and the autoincrement numbers would be independant.

Another very useful thing about this technique is that if a particular entry is deleted , say A 3 is deleted , then next time when A is insert 3 would be reused , so autoincrement numbers get reused.

Helper Functions

We can write some easy to use helper functions to manage multiple groups of autoincrements very easily. The first function will help us to get the next autoincrement for a particular group.

/*
	Get a sequence id
*/
function get_sequence( $type )
{
	//Database class object
        static $db = false;
	
	if($db == false)
	{
		$db = load_class('Database');
	}
		
	if(! $db->query("INSERT INTO vt_sequences(type , id ) VALUES( '$type' , NULL )") )
	{
		die( 'Sequence Generation failed' );
	}
	
	return $db->get_last_insert_id();
}

The function get_last_insert_id is nothing but output of "SELECT LAST_INSERT_ID();" query.

The next function will delete a particular id value for a group.

/**
	Delete a sequence id
*/
function del_sequence($type , $id)
{
	static $db = false;
	
	if($db == false)
	{
		$db = load_class('Database');
	}
	
	$db->query( "DELETE FROM vt_sequences WHERE type = '$type' AND id = '$id'" );
        return true;
}

A particular id should always be deleted , if not used , so that it is reused in the next call to get_sequence.

Now lets see how the functions perform :

get_sequence('2011-10'); //1
get_sequence('2011-10'); //2
get_sequence('2011-10'); //3
get_sequence('2011-10'); //4

get_sequence('2011-11'); //1
get_sequence('2011-11'); //2
get_sequence('2011-11'); //3

get_sequence('2011-10'); //5

//Delete a sequence
del_sequence('2011-10' , 5);

//Get it back
get_sequence('2011-10'); //5 - reused again !!

The get_sequence method inserts the type and NULL for the id , and this generates the next autoincrement number.
$db->get_last_insert_id(); could be your implementation of the sql query LAST_INSERT_ID().

Conclusion

There are other ways to implement autoincrement columns that reset every month. The above shown method actually relies on a database feature that is specific to mysql only and may not be available in other database systems like Oracle or Postgresql. This could pose a portability issue.

Another way to implement similar field is by using table locking. You could have a separate table that is used to generate and store autoincrement ids only. Simply lock the table, check the highest id number for the current month and then add 1 to it and write a new row as well as use the number in application.

With this approach there is no dependency on the Myisam feature that was used above.

If you have any questions or feedback, let us know in the comments below.

References :

1. http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html