All geek questions in one place

How to insert value based on current date and generated sequence number in MySQL?

I have this MySQL table:

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

And now I want to be able to manually insert a unique integer for id_interessand automatically generate it id_billso that it consists of the current date and an integer (the integer is reset to the new year using a trigger) as follows:

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 |

If anyone has a direct solution using only one request , I would be very happy! I just came up with a solution that uses three queries , but I still get some errors ...

My newbie attempt: I created an extra column id_bill_tmpthat contains the integer part id_billas follows:

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 reset 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 |

, , , , , :

1- :

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. , :

"",

, :

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, .

+4
6

โ„–1 -

Demo

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 , . , .

โ„–2 -

Demo

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).

  • CONCAT(...) , .
  • DATE_FORMAT(CURDATE(),'%Y%m%d') yyyymmdd (, 20170923).
  • IF(..., <x>, <y>) , , , : , , reset 1.
  • LEFT(<date>, 4) - 4 id_bill.
  • SELECT MAX(...) AS id_bill FROM bills + id_bill id_bill. (. , (b1), SELECT). , , MAX .
  • CONCAT(LEFT(id_bill, 8), ...) , , , . . 201709230000000001.
  • LPAD(SUBSTR(id_bill, 10), 10, 0) (, 0000000001, MAX (. , , - , , 10 9, 1).
  • DATE_FORMAT(CURDATE(),'%Y') (, 2017) IF, (3) .
  • IFNULL(<x>, <y>) , , NULL. 1.
  • SELECT SUBSTR(MAX(...), 9) AS id_bill_tmp FROM bills + id_bill ( ), , 9 . , (b2) SELECT.
  • + 1 . ( , , 0 , , 1).
+2

, , , :

DROP FUNCTION fcn46309431;
DELIMITER //
CREATE FUNCTION fcn46309431 (_max VARCHAR(22))
    RETURNS VARCHAR(22)
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    RETURN
        CONCAT(DATE_FORMAT(CURDATE(), "%Y%m%d"), '-',
            IF( LEFT(_max, 4) = YEAR(CURDATE()),
                  SUBSTRING_INDEX(_max, '-', -1) + 1,
                  1 ) );
END                  
//
DELIMITER ;

INSERT INTO se46309431 (id_interess, id_bill)
    SELECT 149, fcn46309431(MAX(id_bill)) FROM se46309431;

SELECT * FROM se46309431;

( , MAX(..) .)

+2

: https://www.percona.com/blog/2008/04/02/stored-function-to-generate-sequences/

, , :

delimiter // create function seq(seq_name char (20)) returns int begin update seq set val=last_insert_id(val+1) where name=seq_name; return last_insert_id(); end // delimiter ; CREATE TABLE `seq` ( `name` varchar(20) NOT NULL, `val` int(10) unsigned NOT NULL, PRIMARY KEY (`name`) )

, : insert into seq values('2017',1); insert into seq values('2018',1); insert into seq values('2019',1); ... ( )

, : insert into bills (id_interess, id_bill) select 123, concat(date_format(now(), '%Y%m%d-'), seq(date_format(now(), '%Y')));

123 // , .

+1

, , . :

id_interess |id_counter    |id_bill    |  
------------+--------------+-----------+  
1           |1             |20170912   |
2           |2             |20171231   |
3           |1             |20180101   |

"20170912-1", id_counter id_bill SQL-Query , (, ).

SQL- .

, . id_counter ( SQL-Query).

id_counter auto_increment reset , . .

+1

, , - , - . id_bill no., , .

CREATE TABLE IF NOT EXISTS `bill` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `bill_date` date NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Query

select a.id,concat(DATE_FORMAT(a.bill_date,"%Y%m%d"),'-',a.no) id_bill
from(    
    select b.*,count(b2.bill_date) no
    from bill b
    join bill b2 ON (EXTRACT(YEAR FROM b.bill_date) = EXTRACT(YEAR FROM b2.bill_date) 
      and b.bill_date >= b2.bill_date)
    group by b.id
    order by b.bill_date,no    
) a

, ,

DEMO

1 , id, auto_increment

select a.id,concat(DATE_FORMAT(a.bill_date,"%Y%m%d"),'-',a.no) id_bill
from(
    select b.*,count(b2.bill_date) no
    from bill b
    join bill b2 ON (EXTRACT(YEAR FROM b.bill_date) = EXTRACT(YEAR FROM b2.bill_date) 
      and b.id >= b2.id)
    group by b.id
    order by b.bill_date,no
) a

+1

() ( MySQL 5.7 MariaDB).

CREATE TABLE bills (
    id_interess   INT UNSIGNED    NOT NULL,
    bill_dt       DATETIME DEFAULT CURRENT_TIMESTAMP,
    bill_year     YEAR AS (year(bill_dt)),
    year_position INT UNSIGNED    NULL,
    id_bill       VARCHAR(30) AS (concat(date_format(bill_dt, '%Y%m%d-'), year_position)),
    PRIMARY KEY (id_interess),
    INDEX (bill_year, year_position)
) ENGINE=InnoDB;

bill_year id_bill . . - bill_year , , ( ).

:

insert into bills(id_interess, year_position) 
    select 1, coalesce(max(year_position), 0) + 1
    from bills
    where bill_year = year(now());

:

insert into bills(id_interess, bill_dt, year_position) 
    select 10, '2016-01-01', coalesce(max(year_position), 0) + 1
    from bills
    where bill_year = year('2016-01-01')

: https://www.db-fiddle.com/f/8pFKQb93LqNPNaD5UhzVwu/0

, , year_postion:

CREATE TRIGGER bills_after_insert BEFORE INSERT ON bills FOR EACH ROW
    SET new.year_position = (
        SELECT coalesce(max(year_position), 0) + 1
        FROM bills
        WHERE bill_year = year(coalesce(new.bill_dt, now()))
    );

insert :

insert into bills(id_interess) values (1);


insert into bills(id_interess, bill_dt) values (11, '2016-02-02');

select:

select id_interess, id_bill
from bills
order by id_bill;

: https://www.db-fiddle.com/f/55yqMh4E1tVxbpt9HXnBaS/0

Update

, insert:

insert into bills(id_interess, id_bill)
    select
        @id_interess, 
        concat(
            date_format(@date, '%Y%m%d-'),
            coalesce(max(substr(id_bill, 10) + 1), 1)
        )
    from bills 
    where id_bill like concat(year(@date), '%');

@id_interess @date . @date CURDATE(), , . . 2016 , 2017 .

: http://rextester.com/BXK47791

LIKE WHERE id_bill ( ), . . , MAX. insert, .

+1

Source: https://habr.com/ru/post/1685990/

More articles:


All Articles