Search code examples
mysqlauto-increment

Force auto_increment to skip certain values?

As part of my current project, a URL might look like this:

http://example.com/summary/ab123

Where the last part is the integer ID after being run through what is effectively base_convert(~,10,64) with a custom alphabet.

However, letters spell things. For instance, when the ID 1,950,434 comes around, the URL will be:

http://example.com/summary/shit

So... yeah. Is there any way I can "reserve" the IDs that form undesirable words and make the AUTO_INCREMENT field skip past them?

Solution

  • While you could implement something in the database by creating a trigger that checks the ID that was auto-generated, and if that ID is included in your exclusion list - delete the row and try again.

    But, this seems kind of hacky to me... and makes the database care about things that should be out of it's scope - IDs that translate to dirty words.

    Instead, I would suggest a solution completely outside of the database. For example, can you change your custom alphabet to exclude all vowels? That way, regardless of the ID generated, it will never spell anything unsavory in your URL.

    I could think of a lot of words that you wouldn't want in your URL. If you try to blacklist the unwanted words, I'd say the odds are pretty good that a more exotic term would slip through... so I think an approach that doesn't require a blacklist would be ideal for this reason as well.