mysql - 想要 mysql 自动增量表作为订单号- MCI-YYMMDD001 和 001 将每天从 001 开始

Question

I Used

CREATE TABLE BOOKING(ORDER_NO VARCHAR(13) NOT NULL PRIMARY KEY DEFAULT '0';
CREATE TRIGGER TG_BOOKING BEFORE INSERT ON BOOKING FOR EACH ROW BEGIN
INSERT INTO BOOKING_SEQ VALUES (NULL);

I Need every day sequence start from 001

SET NEW.ORDER_NO = CONCAT('MCI-',DATE_FORMAT(NOW(),'%y%m%d'),LPAD(LAST_INSERT_ID(),3,'0'));END;

Answer 1

这是一种方法...

首先，一些DDL...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,invoice_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,public_id VARCHAR(30) NOT NULL);

第 1 步。为订单/发票创建占位符...

INSERT INTO my_table (invoice_date) VALUES (NULL);

SELECT * FROM my_table;
+----+---------------------+--------------+
| id | invoice_date        | public_id    |
+----+---------------------+--------------+
|  1 | 2016-06-06 10:29:29 |              |
+----+---------------------+--------------+

第 2 步。更新结果...

UPDATE my_table a
  JOIN 
     ( SELECT x.*
            , COUNT(*) total
         FROM my_table x 
         JOIN my_table y 
           ON DATE(y.invoice_date) = DATE(x.invoice_date) 
          AND y.invoice_date <= x.invoice_date 
        GROUP 
           BY id
     ) b
    ON b.id = a.id
   SET a.public_id = CONCAT(DATE_FORMAT(a.invoice_date,'%Y%m%d'),'-',LPAD(b.total,3,0));

SELECT * FROM my_table;
+----+---------------------+--------------+
| id | invoice_date        | public_id    |
+----+---------------------+--------------+
|  1 | 2016-06-06 10:29:29 | 20160606-001 |
+----+---------------------+--------------+

现在，插入下一个订单/发票...

INSERT INTO my_table (id,invoice_date) VALUES (5,NULL);

SELECT * FROM my_table;
+----+---------------------+--------------+
| id | invoice_date        | public_id    |
+----+---------------------+--------------+
|  1 | 2016-06-06 10:29:29 | 20160606-001 |
|  5 | 2016-06-06 10:30:57 |              |
+----+---------------------+--------------+

...并重复...

UPDATE my_table a
  JOIN
     ( SELECT x.*
            , COUNT(*) total
         FROM my_table x
         JOIN my_table y
           ON DATE(y.invoice_date) = DATE(x.invoice_date)
          AND y.invoice_date <= x.invoice_date
        GROUP
           BY id
     ) b
    ON b.id = a.id
   SET a.public_id = CONCAT(DATE_FORMAT(a.invoice_date,'%Y%m%d'),'-',LPAD(b.total,3,0));

SELECT * FROM my_table;
+----+---------------------+--------------+
| id | invoice_date        | public_id    |
+----+---------------------+--------------+
|  1 | 2016-06-06 10:29:29 | 20160606-001 |
|  5 | 2016-06-06 10:30:57 | 20160606-002 |
+----+---------------------+--------------+

这些步骤(1 和 2)可以绑定(bind)到一个事务中，这样如果第 2 步失败，那么第 1 步也会失败。但是，我怀疑有一种方法可以将其重写为单个查询。