MySQL自定义主键生成器 [英] MySQL custom primary key generator

问题描述

我已经为我们的预订系统编写了一个发票模块.

I have written an invoice module for our reservation system.

因此，当我创建新发票时，我会通过MySQL自动生成一个主键.

So when I create a new invoice, I automatically generate a primary key through MySQL.

但是目前这只是一个简单的整数.

However for the moment this is just a simple integer counting up.

问题是我们必须以#year#id"的形式获得发票编号，其中#year是例如2013和#id是每年从1开始的ID.

The problem is that we are obligated to have an invoice number in the form of "#year#id" where #year is e.g. 2013 and #id is an id that start every year back at 1.

例如20131、20132、20133，2014年则是20141、20142.

So e.g. 20131, 20132, 20133 and in 2014 it wil be 20141, 20142.

如何使用自定义主键生成器解决此问题?

How can I fix this with a custom primary key generator?

推荐答案

您可以在年份和ID中输入两个字段.

You can make two fields in your, year and id.

在两个字段上都做一个主键，给id一个auto_increment选项.对于年份的每个唯一值，id都会递增.例如:

Make one primary key on both fields, giving id the auto_increment option. For each unique value of year, id will be counting up. For example:

2012    1
2012    2
2012    3
2013    1

您可以在选择以下内容时将其合并:SELECT CONCAT(year，id)AS primary FROM table

And you can concat them when selecting: SELECT CONCAT(year,id) AS primary FROM table

插入将是:

INSERT INTO表SET year = YEAR(NOW())

INSERT INTO table SET year = YEAR(NOW())

您不必指定ID.

这篇关于MySQL自定义主键生成器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！