MySQL如何创建一个用日期格式化的自定义id列？

Question

MySQL 当前表

CREATE TABLE document_control (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT...

从上面 - id将按以下顺序创建: 1,2,3,4,5,6,7... .

意向

我需要这样的顺序:

19-001
19-002
19-003

说明
19 - 今天的年份日期格式 (yy)
001 - 添加新值时增加 +1。

明年(2020)..

序列需要重新设置回 001但是由于是 2020 年，所以 19 变成了 20 :

20-001
20-002
20-003

问题

如何创建此自定义 ID 列？

Answer 1

您可以使用 INSERT ... SELECT 使用以下内容:

INSERT INTO document_control 
  SELECT CONCAT_WS('-', RIGHT(YEAR(CURRENT_TIMESTAMP), 2), LPAD(COUNT(*) + 1, 3, 0)) 
  FROM document_control
  WHERE LEFT(id_custom, 2) = RIGHT(YEAR(CURRENT_TIMESTAMP), 2)

Note: It can be dangerous to use such a generated custom ID as identifier for specific records since the custom ID can change after changing the data (UPDATE or DELETE) of the table. So I don't recommend to use this custom ID as foreign key on other tables.

更好的解决方案(在我看来)如下:

CREATE TABLE document_control (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  name VARCHAR(10)
);

您正在使用带有 id 列的表格使用自动增量(因此数据库自行组织 ID)和 created_at列来存储创建的日期和时间。您可以使用 CURRENT_TIMESTAMP as default value所以您不必在 INSERT 上提供值每次。使用这两列，您可以通过简单的查询获取自定义 ID:

SELECT *, CONCAT_WS('-', RIGHT(YEAR(created_at), 2), LPAD(id, 3, 0)) AS custom_id 
FROM document_control

您可以 create a VIEW 在后台生成自定义 ID。在这种情况下，您不必在每个 SELECT 上构建自定义 ID。你自己:

-- create the VIEW
CREATE VIEW v_document_control AS
  SELECT *, CONCAT_WS('-', RIGHT(YEAR(created_at), 2), LPAD(id, 3, 0)) AS custom_id 
  FROM document_control

-- use the VIEW
SELECT * FROM v_document_control

如果您需要一个没有间隙的连续数字并且每年从“1”开始，您可以使用上面的示例(相同的列)，但使用以下 SELECT使用 ROW_NUMBER (自 MySQL 8.0 起):

SELECT *, CONCAT_WS('-', RIGHT(YEAR(created_at), 2), LPAD(ROW_NUMBER() OVER (PARTITION BY YEAR(created_at) ORDER BY id), 3, 0)) AS custom_id 
FROM document_control

demo on dbfiddle.uk