mysql - 如何根据当前日期和 MySQL 中生成的序列号插入一个值？

Question

我有这个 MySQL 表:

CREATE TABLE bills
(
    id_interess     INT UNSIGNED    NOT NULL,
    id_bill         VARCHAR(30)     NULL,
    PRIMARY KEY (id_interess)
) ENGINE=InnoDB;

现在我希望能够为 id_interess 手动插入唯一整数并自动生成 id_bill 以便它由当前日期和整数组成(整数重置为新的一年使用触发器)像这样:

id_interess |id_bill    |
------------+-----------+
1           |20170912-1 |
2           |20171030-2 |
6           |20171125-3 |
10          |20171231-4 |
200         |20180101-1 |
3           |20180101-2 |
8           |20180102-3 | 

如果有人只使用一个查询就可以直接解决这个问题，我会很高兴!我只提出了一个使用三个查询的解决方案，但我仍然遇到一些错误...

我的新手尝试:我创建了一个附加列 id_bill_tmp，它包含 id_bill 的整数部分，如下所示:

CREATE TABLE bill
(
    id_interess     INT UNSIGNED    NOT NULL,   
    id_bill_tmp     INT UNSIGNED    NULL,
    id_bill         VARCHAR(30)     NULL,
    PRIMARY KEY (id_interess)
) ENGINE=InnoDB;

上面的表格在这种情况下看起来像这样(注意在新的一年 id_bill_tmp 被重置为 1 因此我不能使用 AUTO_INCREMENT 只能用于键，键需要在列中具有唯一值):

id_interess |id_bill_tmp   |id_bill    |
------------+--------------+-----------+
1           |1             |20170912-1 |
2           |2             |20171030-2 |
6           |3             |20171125-3 |
10          |4             |20171231-4 |
200         |1             |20180101-1 |
3           |2             |20180101-2 |
6           |3             |20180102-3 | 

例如，要从上表插入第一行，表必须为空，我会在三个查询中插入一个值，如下所示:

第一个查询:

INSERT INTO racuni (id_interess) VALUES (1);

我首先这样做是因为我不知道如何为 id_bill_tmp 增加一个不存在的值，这帮助我首先获得了 id_bill_tmp = NULL:

id_interess |id_bill_tmp   |id_bill    |
------------+--------------+-----------+
1           |[NULL]        |[NULL]     |

第二次查询

现在我尝试增​​加 id_bill_tmp 成为 1 - 我尝试了两个查询都失败了:

table is specified twice both as a target for 'update' and as a separate source for data

这是我尝试过的查询:

UPDATE bills
SET id_bill_tmp = (SELECT IFNULL(id_bill_tmp, 0)+1 AS id_bill_tmp FROM bills)
WHERE id_interess = 1;

UPDATE bills
SET id_bill_tmp = (SELECT max(id_bill_tmp)+1 FROM bills)
WHERE id_interess = 1;

第三个查询:

最后一步是重用 id_bill_tmp 作为 id_bill 的整数部分，如下所示:

UPDATE bills
SET id_bill = concat(curdate()+0,'-',id_bill_tmp)
WHERE id_interess = 1;

这样我终于得到了

id_interess |id_bill_tmp   |id_bill    |
------------+--------------+-----------+
1           |1             |20170912-1 |

因此，如果有人可以帮助我处理第二个查询，甚至可以通过单个查询甚至不使用 id_bill_tmp 列来提供解决方案，那就太好了。

Answer 1

解决方案 #1 - 使用额外的列

演示

http://rextester.com/GOTPA70741

SQL

INSERT INTO bills (id_interess, id_bill_tmp, id_bill) VALUES (
    1, -- (Change this value appropriately for each insert)
    IF(LEFT((SELECT id_bill FROM 
             (SELECT MAX(CONCAT(LEFT(id_bill, 8),
                                LPAD(SUBSTR(id_bill, 10), 10, 0))) AS id_bill
              FROM bills) b1), 4) = DATE_FORMAT(CURDATE(),'%Y'), 
       IFNULL(
           (SELECT id_bill_tmp
            FROM (SELECT id_bill_tmp
                  FROM bills
                  WHERE CONCAT(LEFT(id_bill, 8),
                               LPAD(SUBSTR(id_bill, 10), 10, 0)) =
                        (SELECT MAX(CONCAT(LEFT(id_bill, 8),
                                           LPAD(SUBSTR(id_bill, 10), 10, 0)))
                         FROM bills)) b2),
           0),
       0)
       + 1,
    CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'), '-' , id_bill_tmp));

注意事项

查询看起来比实际情况稍微复杂一些，因为 MySQL 不允许您直接使用要插入的同一个表中的子查询。这可以使用在其周围包装另一个子选择的方法来规避，如所述 here .

解决方案 #2 - 没有额外的列

演示

http://rextester.com/IYES40010

SQL

INSERT INTO bills (id_interess, id_bill) VALUES (
    1, -- (Change this value appropriately for each insert)
    CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),
           '-' ,
           IF(LEFT((SELECT id_bill
                    FROM (SELECT MAX(CONCAT(LEFT(id_bill, 8),
                                            LPAD(SUBSTR(id_bill, 10), 10, 0))) AS id_bill
                          FROM bills) b1), 4) = DATE_FORMAT(CURDATE(),'%Y'), 
              IFNULL(
                  (SELECT id_bill_tmp
                   FROM (SELECT SUBSTR(MAX(CONCAT(LEFT(id_bill, 8),
                                                  LPAD(SUBSTR(id_bill, 10), 10, 0))), 9)
                                AS id_bill_tmp
                         FROM bills) b2),
                  0),
              0)
              + 1));

注意事项

这与上面的行相同，但获取的数值本应在 id_bill_tmp 中通过从 id_bill 的右侧部分中提取从第 10 个字符位置开始通过 SUBSTR(id_bill, 10) .

逐步分割

1. CONCAT(...)通过将各个部分连接在一起来组装字符串。
2. DATE_FORMAT(CURDATE(),'%Y%m%d')将当前日期格式化为 yyyymmdd (例如 20170923 )。
3. IF(..., <x>, <y>)用于检查已经存在的最近日期是否为当前年份:如果是，则数字部分应通过递增序列继续，否则重置为 1。
4. LEFT(<date>, 4)从最近的日期获取年份 - 通过从 id_bill 的前 4 个字符中提取.
5. SELECT MAX(...) AS id_bill FROM bills从 id_bill 获取最近的日期 + 序列号并给它一个别名 id_bill . (请参阅上面关于为什么还需要为子查询指定一个别名 ( b1 ) 然后包装在另一个 SELECT 中的注释)。请参阅下面的两个步骤，了解如何构造一个字符串 MAX可用于订购。
6. CONCAT(LEFT(id_bill, 8), ...)正在构造一个可用于上述排序的字符串，方法是将日期部分与用零填充的序列号组合起来。例如。 201709230000000001 .
7. LPAD(SUBSTR(id_bill, 10), 10, 0)用零填充序列号(例如 0000000001，以便 MAX 可用于排序。(请参阅 Paul Spiegel 的评论以了解为什么需要这样做 - 例如，以便序列号 10在 9 之后排序，而不是在 1 之后排序。
8. DATE_FORMAT(CURDATE(),'%Y')将 2017 的当前日期格式化为年份(例如 IF )上面(3)中提到的比较。
9. IFNULL(<x>, <y>)用于第一行，因为找不到现有行，因此结果将为 NULL。在这种情况下，数字部分应从 1 开始。
10. SELECT SUBSTR(MAX(...), 9) AS id_bill_tmp FROM bills从 id_bill 中选择最近的日期 + 序列号(如上所述)然后提取其序列号，该序列号始终从字符位置 9 开始。同样，此子查询需要使用别名 ( b2 ) 并包装在另一个 SELECT 中.
11. + 1递增序列号。 (请注意，这始终是这样做的，因为在上述序列号应设置为 1 的情况下使用了 0)。