creating primary key based on date

I am trying to create a unique ID for each record based on the date the record is created. Example, if the record is created today, I want the key to be 20101130XX where XX would be a sequential list of numbers such as 01, 02, 03 etc... in case more than one person creates a record today.

If 3 people created records yesterday their unique IDs would be

2011032700 2011032701 2011032702

and then midnight comes and someone creates a new record it would be

2011032800

The purpose of this is to give each record a unique ID that will be used to reference the ticket.

We are already using the date format, but currently users are having to manually enter the id and that can create errors such as 2012032700 when it s开发者_Python百科hould have been 2011032700 so instead of putting 2011 they put 2012

I am just not sure how to create a trigger to generate this type of unique ID and would appreciate any assistance

From dev.mysql.com: example-auto-increment.html

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

So, make 2 columns on the table, one dateEntered and one (auto_incremented) id, like this:

CREATE TABLE yourTable (
    dateEntered DATE NOT NULL,
    id INT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (dateEntered, id)
) ENGINE=MyISAM;

If you don't use ISAM but InnoDB, I think you'll have to write your own trigger that implements this behaviour.

This approach simply won't work because @Charles is true. You can't rely on a timestamp because there's a chance of duplicates (collision).

Create a simple autoincrement id then save the timestamp too and you'll be happy.

What will you do if the number of entries per day exceeds 99? If you strongly want to use "20101130XX" index, I would recommend you use a compound key:

CREATE TABLE `test` (
  `date` date NOT NULL,
  `id` tinyint(4) NOT NULL,
  `other_fields` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`date`,`id`)
);

Just use microtime() to be sure that there are no duplicate entries.

function getUniqueID() {
  $mt = explode(' ', microtime());
  return $mt[1] . substr($mt[0], 2, 7);
  }

P.S. You can always use lower precision then 7 digits.