1

I am working on cursor based pagination and need a unique column which is sequential, so it would give proper fetch result. I know this can be solved by setting column as auto_increment with datatype bigint but eventually it will hit the limit of it which is 9223372036854775807.

So I am thinking like generating a sequential UUID like below which has compareTo result is 1.

cf3ea0ca-282d-11ec-9624-a71256fa1790

cf3ea0cb-282d-11ec-9624-410ea2fdd62c

cf3ea0cc-282d-11ec-9624-3b1a2da8c7d0

cf3ea0cd-282d-11ec-9624-877d8646d279

I generated these UUIDs using https://github.com/cowtowncoder/java-uuid-generator library

UUID uuid = Generators.timeBasedGenerator().generate();

This is how UUID can be generated, but it is not giving consistent results.

It there any other way to generate UUID which is sequential in ascending order.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
pjsagar
  • 241
  • 3
  • 11
  • 2
    UUID are supposed to be in-sequential, so that someone can not predict the other value. If you need sequence then UUID is not a right choice. – Gaurav Jeswani Oct 08 '21 at 12:20
  • 1
    UUID are defined as being unique AND random. What about using BigInteger? https://www.geeksforgeeks.org/biginteger-class-in-java/ – DevWithZachary Oct 08 '21 at 12:22
  • So what should be the curser value which is both unique and sequential? – pjsagar Oct 08 '21 at 12:23
  • With this limitation you could do a `LONGTEXT` field and manually sequence the entries in your app with numbers or however you want with some sort of presave method that adds an increment to the count of rows in the table... definitely not the best option but it will be about the biggest entry you can get – ViaTech Oct 08 '21 at 12:24
  • I have already specified that it can be done with bigint but i want to know if there are any other solutions. – pjsagar Oct 08 '21 at 12:25
  • 2
    If you want something that is sequential, increments by one for every new record and is efficient then you want an integer - period. It is the best solution. GUID have other good properties (can be generated on the client, scales well) but won't fit your bill. You can certainly write something that stores an even bigger integer in a raw (as bytes), but it will be cumbersome and not as efficient. – ewramner Oct 08 '21 at 14:39
  • 1
    @DevWithZachary The word “arbitrary” might be a better fit than “random”. Only [Version 4](https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_(random)) UUIDs are actually random. – Basil Bourque Oct 08 '21 at 16:00
  • You can always manually maintain the counter and use `UUID(long mostSigBits, long leastSigBits)`, but it sounds like a bad idea to me. Then you might just as well use two BIGINT columns (or check if your database supports larger numbers, e.g. using NUMERIC/NUMBER, etc). – Mark Rotteveel Oct 08 '21 at 16:20
  • Thanks for suggestions. I have decided to go with auto_increment with bigint as data type and if need arise then change it to java BigInteger with maintaining a counter so that no limitation will be reached. – pjsagar Oct 11 '21 at 08:21

1 Answers1

6

You asked:

How to generate UUID sequentially so that difference between two consecutive uuids would be 1?

No, you cannot generate consecutive UUID values.

You’ve misunderstood the goal of a universally unique identifier (UUID).

A UUID is an identifier that can be generated without needing to coordinate through a central authority. So that rules out a sequence such as 1, 2, 3, ….

UUIDs are intended to be (a) virtually unique, and (b) arbitrary in value. The next UUID in a procession is not predictable.

Even the type of content in a UUID may vary, with several versions of UUIDs having been defined, and some new ones proposed. One version representing a point in space and time, another version is nearly entirely random, and still others may be generated using other means. But all are valid UUIDs and can be used together.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154