Comments on: Generating Numeric Sequences in MySQL

By: Yura Sorokin

Yura Sorokin — Tue, 01 Dec 2020 14:24:06 +0000

R, you are right SEQUENCE_TABLE() is completely different from SEQUENCE objects defined in SQL standard (which MySQL, btw, still lacks). Their primary purpose is being a virtual source of data that does not occupy any disk space and can be used here and now inside SQL statements without defining/populating any extra tables.

By: R

Thu, 26 Nov 2020 01:46:51 +0000

just adding to my last comment.. i suppose my use-case is different. When i use sequences.. i want to use things like the traditional .NEXT_VAL and just get a new unique number each time i want one… not just list out a bunch of numbers on demand. I suppose that is a different use case that i’ve just never needed.. nor thought of as a ‘sequence_number’

By: R

Thu, 26 Nov 2020 01:44:05 +0000

Sorta similar to one of your examples.. but surprised you don’t mention an easy one: Create a table with one column. Make that column an AUTO_INCREMENT column.
Done. you want the next sequence, add a row and take the value.

Obvious downfall, you have a table.. But on the other hand, its named clearly hopefully, and gives you access anytime to see the current max, and very easy to get your next sequence number.

By: Yura Sorokin

Yura Sorokin — Tue, 11 Aug 2020 19:55:21 +0000

In reply to Justin Swanhart. Justin, thanks for the feedback. To be honest we also considered 2- and 3-argument versions of this function during the design stage which was definitely not that hard to implement. The main problem then was in inconsistency between unary version and binary/ternary. As a developer and/or a DBA with some programming skills I would expect SEQUENCE_TABLE(N) to return a zero-based sequence of numbers with values less then N - 0 to N not inclusive, in other words (like in a number of programming languages when you specify an upper bound for a generator). In contrast, when I specify SEQUENCE_TABLE(N, M) I would subconsciously expect this function to return a sequence from N to M inclusive. So, the decision was made to implement only the simplest (1-argument) version to avoid confusion with this inclusive/non-inclusive rules. But who knows, there is always a chance that we will change this behavior in future versions of the Percona Server. BTW, for the example you mentioned there is a much simpler solution that does not require filtering SELECT value + 10000 FROM SEQUENCE_TABLE(11) AS tt

By: Justin Swanhart

Justin Swanhart — Mon, 10 Aug 2020 00:29:12 +0000

It would be nice if it was sequence_value(end, start, increment) where start defaults to zero and increment defaults to 1. I assume the values are generated by a for loop, and starting a sequence at a high value (like I need 10000 – 10010) requires filtering out a lot of rows with the where clause. I put end first so that it would be compatible with your current function given a single input parameter.

Just a thought.