0

I need a database design (mysql 8.0+) to support a cyclic number series from 1 to a specific max number, such as 1 to 3, then would be get 1,2,3,1,2,3,... as query result respectively and cyclically. My version has been worked successfully but hope seeking for maybe better, native version. Many thanks.

My scripts are here,

CREATE TABLE IF NOT EXISTS `cyclic_series_number` (
  `category` VARCHAR(100) NOT NULL,
  `sn` int NOT NULL,
  `max` int NOT NULL,
  PRIMARY KEY (`category`)
);

Afterwards, insert 2 records. The 1st record will be the one to test.

REPLACE INTO `cyclic_series_number` (`category`, `sn`, `max`) 
VALUES ('testing', 1, 3), ('ticket', 1, 999);

SELECT * FROM `cyclic_series_number`;
+--------------------------+
|   cyclic_series_number   |
+---+-----------+----+-----+
| # |  category | sn | max |
+---+-----------+----+-----+
| 1 | 'testing' | 1  |  3  |
+---+-----------+----+-----+
| 2 |  'ticket' | 1  | 999 |
+---+-----------+----+-----+

The last, offering a stored procedure.

The idea is to update (sn=sn+1) and get that number as well as a necessary check sn+1 to see if exceeds the max number.

All above logics run at the same time.

DROP PROCEDURE IF EXISTS `get_new_sn`;
DELIMITER //
CREATE PROCEDURE get_new_sn(IN input_category varchar(100))
BEGIN
    SET @latest_sn = -1;
    UPDATE `cyclic_series_number`
    SET `sn` = (@latest_sn := case `sn` when `max` then 1 else `sn` + 1 end)
    WHERE `category` = @input_category;

    SELECT @latest_sn;
END //
DELIMITER ;

The testing result shows the stored procedure works.

CALL get_new_sn('testing'); -- 2
CALL get_new_sn('testing'); -- 3
CALL get_new_sn('testing'); -- 1
CALL get_new_sn('testing'); -- 2
CALL get_new_sn('testing'); -- 3
CALL get_new_sn('testing'); -- 1
-- ...

References

Community
  • 1
  • 1
nwpie
  • 665
  • 11
  • 24
  • Is it mysql or SQL Server (the product from Microsoft)? If the latter, there are first-class sequence objects that do what you want natively (with the cycling). If that's what you're working with, I'll write it up. – Ben Thul May 19 '20 at 04:43
  • @BenThul **mysql** please, many thanks ~ – nwpie May 19 '20 at 04:48
  • I suggest you simplify the problem; so numbers run from 1 to 5, say. Then see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry May 19 '20 at 07:23
  • @Strawberry totally agree, so actually I've simplified that from 999,999 to 99. guess it's clear already, isn't it ? – nwpie May 19 '20 at 11:12
  • No. See above . – Strawberry May 19 '20 at 12:49
  • @BenThul Hi, I've updated and simplified the question. May I have any hint for the way more natively ? Thanks. – nwpie May 26 '20 at 09:15

2 Answers2

0
UPDATE sourcetable
SET sourcetable.num = subquery.num
FROM ( SELECT id, 1 + (ROW_NUMBER() OVER (ORDER BY id) - 1) % 99 num
       FROM sourcetable ) subquery
WHERE sourcetable.id = subquery.id;

where 99 is upper limit.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • I struggle to imagine a situation where this would be useful – Strawberry May 19 '20 at 07:24
  • @Akina thanks, but do you have the version for **mysql** ? And why update from a subquery ? Looks that must be careful if `sourcetable` is not a small table. – nwpie May 19 '20 at 11:17
  • @nwpie You have not specified MySQL version - so I may assume that you use the most recent version. *And why update from a subquery ?* This is simple - both in logic and in SQL text. You may use any another technique, no problems. *if sourcetable is not a small table.* I hope there is primary key (`id` in my code) or at least unique index in the table? – Akina May 19 '20 at 11:22
  • @Akina Earlier post I should not have put double tags for `mysql` and `sql-server`, Now removed `sql-server` already. Sorry that's my mistake. – nwpie May 19 '20 at 12:14
  • What version of mysql are you using? The features in this answer require 8 or later. – Honeyboy Wilson May 19 '20 at 17:41
  • Yes mysql 8.0+ . – nwpie May 20 '20 at 01:23
0

keeping your stored procedure...

change line that starts: SET sn = (@latest_sn := case sn when ... .. ...

to something like: SET sn = (sn + 1) % max;

The modulo operator returns remainder after division... so if sn+1 is less than max then the remainder is sn+1. Once sn+1 = max, remainder = 0 and it starts over... This means too, that max needs to be 1 higher than highest allowed value... so if sn can be 99 but not 100, then max = 100.

DynasticSponge
  • 1,416
  • 2
  • 9
  • 13
  • Thanks, `MOD` operator is more clear. Though we don't allow sn is zero, this formula you mentioned would change to `sn = (sn % max) + 1`. Thus guarantee no zero returned. – nwpie May 21 '20 at 02:16