Force auto_increment to skip specific values?

Question

As part of my current project, the url might look like this:

http://example.com/summary/ab123 

If the last part is an integer identifier after starting through what is effective base_convert(~,10,64) with a custom alphabet.

However, letters write things. For example, when the identifier 1 950 434 appears, the URL would look like this:

 http://example.com/summary/shit 

So ... yes. Is there a way to "reserve" identifiers that form unwanted words and skip the AUTO_INCREMENT field by them?

Answer 1

For now, you can implement something in the database by creating a trigger that checks the automatically generated identifier, and if this identifier is included in your exclusion list, delete the line and try again.

But it seems hacked to me ... and makes the database take care of things that should be outside of it - identifiers that translate into dirty words.

Instead, I propose a solution completely outside the database. For example, can you change your own alphabet to exclude all vowels? Thus, regardless of the identifier generated, it will never say anything wrong in your URL.

I could come up with a lot of words that you do not need in your url. If you try to blacklist unwanted words, I would say that the chances are pretty good that a more exotic term will slip through ... so I think an approach that does not require a blacklist would be ideal for that reason.

Answer 2

Probably the only way to do this is to determine what the last identifier is (using LAST_INSERT_ID). Then determine if the next number will be an unwanted number, insert a pad and immediately delete this entry.