Kriss Jessop About Me Projects Personal Projects RPGs

Unique Strings – SQL

Posted on February 6th, 2018

On occasion a unique string with a database entry can be a very useful way of indexing a table that constrains to either a character limit, or hides the number of entries in the table. Here’s a couple of ways this can easily be done.

UUID()

The likelihood of repetition with a UUID (or GUID) is so infinitesimally small, and so easily available using SQL that if all you need is a unique identifier then this is what you want. The trade-off is it’s length. A UUID is a 32-digit hex-number, so it’s not always useful when trying to create something like a URL shortener or have non-incrementing identifiers.

[code language=”SQL”]

123e4567-e89b-12d3-a456-426655440000

[/code]

RAND()

Cue the RAND() function. The below SQL can be created as a stored procedure, or added as a trigger to the table in question, to produce a seeded-random 4-character alpha-numeric string. Assuming each row has an ID column with AUTO INCREMENT enabled, we can do the following:

[code language=”SQL”]

UPDATE MyDbTable SET Hash=concat(
substring(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, rand(@seed:=round(rand(id)))*62+1, 1),
substring(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, rand(@seed:=round(rand(@seed)))*62+1, 1),
substring(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, rand(@seed:=round(rand(@seed)))*62+1, 1),
substring(‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, rand(@seed)*62+1, 1)) WHERE id = LAST_INSERT_ID()

[/code]

Because we’re specifying a seed ourselves we can guarantee that the result is reproducible, so long as we know the id of the row. There are a few trade-offs however:

  • There’s a much smaller selection of possible unique strings compared to a UUID.
  • It’s technically possible to get duplicate strings, which will need accounting for.
  • A case-sensitive schema’s required to really utilise it.