CODEWITHSUNDEEP
mysqlauto-increment
Niet the Dark Absol
Niet the Dark Absol

Reputation: 324620

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?

Upvotes: 4

Views: 146

Answers (2)

Robbert
Robbert

Reputation: 6582

Probably the only way to do this is to determine what the last ID is (using LAST_INSERT_ID). Then determine if the next number would be an undesirable number, do a shim insert and then delete that entry immediately.

Upvotes: 0

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

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.

Upvotes: 3

Related Questions