Mysql autoincrement column that resets every month

By | October 22, 2011

There are situations when we need an autoincrement field to reset every month or so.
Example :

Invoice Numbers :

Month of october


Month of november


and so on.

Groupwise autoincrement
P1 , P2 , Q1 , Q2 , Q3 , Q4 , P3 , R1 , P4 ...

Myisam table 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.


  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`type`,`id`)

In the above table type and id columns form the primary key. If the following insert is made :

INSERT INTO `vt_sequences` (`type`) VALUES

The result should be :

A 1 2011-10-22 15:29:04
A 2 2011-10-22 15:29:04
B 1 2011-10-22 15:29:04
B 2 2011-10-22 15:29:04
A 3 2011-10-22 15:29:04

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().

References :

Last Updated On : 22nd October 2011

Related Post

One thought on “Mysql autoincrement column that resets every month

Leave a Reply

Your email address will not be published. Required fields are marked *