根据日期创建主键 [英] creating primary key based on date

问题描述

我正在尝试根据创建记录的日期为每个记录创建唯一的ID.例如，如果今天创建记录，我希望键为20101130XX，其中XX是数字的顺序列表，例如01、02、03等...以防今天有多个人创建记录.

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.

如果3个人昨天创建了记录，那么他们的唯一ID将为

If 3 people created records yesterday their unique IDs would be

2011032700 2011032701 2011032702

2011032700 2011032701 2011032702

然后午夜到了，有人创建了一个新记录

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

2011032800

2011032800

这样做的目的是给每个记录一个唯一的ID，该ID将用于引用票证.

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

我们已经在使用日期格式，但是当前用户必须手动输入ID，否则可能会产生错误，例如2012032700(应为2011032700)，因此他们将2012放到了2011年而不是放了

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 should have been 2011032700 so instead of putting 2011 they put 2012

我只是不确定如何创建触发器来生成这种类型的唯一ID，因此不胜感激

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

推荐答案

来自dev.mysql.com:

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

对于 MyISAM和BDB表，您可以在多列索引的第二列中指定 AUTO_INCREMENT .在这种情况下，将为AUTO_INCREMENT列生成的值计算为MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.当您要将数据放入有序组中时，这很有用.

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.

因此，在表上添加2列，一个dateEntered和一个(auto_incremented)id，如下所示:

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;

如果您不使用ISAM而是InnoDB，我认为您必须编写自己的触发器来实现此行为.

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

这篇关于根据日期创建主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！