mysql - 具有不是键的 auto_increment 字段的架构和表设计

Question

我有一个发票号码，这个号码与一个特殊文件相关联。这个文档有一个id，由一个递增的数字和当前的年份组成，像这样

222-2011
223-2011
224-2011
...

每年累进数字从1重新开始

1-2012
2-2012
3-2012

首先，我想用 invoice_n、prog_n、year 制作一张表。 prog_n 是 AUTO_INCREMENT，每年我都会重置它。但是您不能使用不是键的 AUTO_INCREMENT 字段。无论如何，我要重置计数器，这不是那么值得推荐...

我无法更改 id 格式，我必须使用该规则。我能否以某种方式进行高效设计？

环境是经典的LAMP

非常感谢!

Answer 1

扩展@Marius 的回答，我会使用触发器让 MySQL 像这样自动设置发票编号:

DELIMITER $$

CREATE TRIGGER bi_invoices_each BEFORE INSERT ON invoices FOR EACH ROW
BEGIN
  DECLARE lastest_invoice_number VARCHAR(20);
  DECLARE numberpart INTEGER;

  -- find lastest invoicenumber in the current year.
  SELECT COALESCE(max(invoicenumber),0) INTO lastest_invoice_number 
  FROM invoice 
  WHERE invoice_date >= MAKEDATE(YEAR(NEW.invoice_date)  ,1) 
    AND invoice_date <  MAKEDATE(YEAR(NEW.invoice_date)+1,1);

  -- extract the part before the '-'
  SET numberpart = SUBSTRING_INDEX(lastest_invoice_number,'-',1)

  SET NEW.invoicenumber = CONCAT(numberpart+1,'-',YEAR(NEW.invoice_date));
END $$

DELIMITER ;

请注意，您不能在 before 触发器中访问自动递增的 id；
您只能在 after 触发器中执行此操作，但您无法更改任何值，因此需要一些技巧。
我们使用了更多技巧来确保我们像在选择查询中一样使用 invoice_date，以便可以使用该字段的索引。

参见:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_makedate