Hi!
I am doing an experiment, to randomize the id’s from a table, because I will need this in future for security purposes.
For example, If I have records from id=1 to id=10. Now I want that their serial id numbers will get randomize, like id=1 become 6 and id=2 become 1, etc. in a randomize manner.
Can anyone tell me any idea or mysql command for this?
Thanks.
don’t do that, you are asking for a world of hurt
what is the real issue you are trying to solve?
Hi Rudy Sir.
I am doing this experiment for learning how to do this.
That’s only for learning, I know this will not be implemented in the real world, because many problems will arise, if we implement it.
But just for my experiment, and learning purpose, I wanted to know that.
The values in the ids have nothing whatever to do with security and so randomising them will not affect security at all and just make database access more complicated.
The only meaning of the ids is to provide a unique identifier for the associated data and sequential numbers serve that purpose better than random numbers would.
spot on 
perhaps if his re-numbering scheme failed, or created dupes, i would agree
but why is sequential better than random?
what if the database had a builtin AUTO_RANDOM function for this purpose (similar to mysql’s AUTO_INCREMENT sequentil)
Because with AUTO_INCREMENT you can be sure you can use last_number + 1, with AUTO_RANDOM you’d have to check in the database each time if the number you picked doesn’t already exist in the database, which obviously takes time.
[QUOTE=ScallioXTX;4945400with AUTO_RANDOM you’d have to check in the database each time if the number you picked doesn’t already exist in the database, which obviously takes time.[/QUOTE]
Exactly what I was thinking when I suggested that sequential numbers would work better than random.
no, not if the database system does it for you, just like with AUTO_INCREMENT you don’t have to do the +1 yourself
it was a ~hypothetical~ AUTO_RANDOM
microsoft access actually has this, but mysql doesn’t
Okay, but then the database has to check for duplicates before it inserts doesn’t it?
While this is true, sequential numbers may reveal information about your database you would rather keep secret. If you use sequential id in public URLs then people know (more or less) how many items you have in the database. For example, you register on a web page and you see you have been given user_id=1000 so you already know the database is holding 1000 users (or a little less if some were deleted but that will not skew the guess by much).
Why does youtube include some random garbage IDs in their video URLs instead of a neat sequential number? I don’t know for sure but it’s very likely they don’t want people to know how much their database has grown.
yes, but it’s a lot faster when the database system does it than if you did that yourself
just like it’s a lot faster for the database system to grab MAX(id) and then add +1 to it than if you did that yourself
There are a couple options for “hiding” the sequential id.
Use a bigger base (a-zA-Z0-9 = 36 base) and use a hashing function: Create short IDs with PHP - Like Youtube or TinyURL
Create a random string with something like uuid: Universally unique identifier - Wikipedia, the free encyclopedia or uniqid(): [url=http://php.net/manual/en/function.uniqid.php]PHP: uniqid - Manual
Use a user-generated “slug” (super pretty, but much more room for collisions): Glossary « WordPress Codex
In any case, this is typically generated outside of MySQL, but stored in with the record.
Not true - you can start the sequential count from any number and restart it from any bigger number at any time. If you start it from 1004356 and someone gets allocated id 1004357 then they will have no idea that they have the second id that was allocated.
Not displaying sequential values in URLs does relate to security but has nothing to do with databases. The best way to handle passing information like that is to create a session. That generates a session hash which is a long string of random characters that can be passed between pages. The id of the record in the database can then be passed in the session data while all the person sees is the random characters either in the URL or in a cookie.