Hướng dẫn mysql random string
I woudn't bother with the likelihood of collision. Just generate a random string and check if it exists. If it does, try again and you shouldn't need to do it more that a couple of times unless you have a huge number of plates already assigned. Another solution for generating an 8-character long pseudo-random string in pure (My)SQL:
You can try the following (pseudo-code):
Since this post has received a unexpected level of attention, let me highlight ADTC's comment : the above piece of code is quite dumb and produces sequential digits. For slightly less stupid randomness try something like this instead :
And for true (cryptograpically secure) randomness, use answered May 24, 2013 at 15:16
RandomSeedRandomSeed 28.8k6 gold badges49 silver badges86 bronze badges 6 This problem consists of two very different sub-problems:
While randomness
is quite easily achieved, the uniqueness without a retry loop is not. This brings us to concentrate on the uniqueness first. Non-random uniqueness can trivially be achieved with
A sequence of random numbers created by the same seed is guaranteed to be
So we use @AndreyVolk's or @GordonLinoff's approach, but with a seeded e.g. Assumin
Yves M. 29.1k22 gold badges105 silver badges138 bronze badges answered May 24, 2013 at 15:21
Eugen RieckEugen Rieck 63k10 gold badges69 silver badges92 bronze badges 12 What about calculating the MD5 (or other) hash of sequential integers, then taking the first 8 characters. i.e
etc. caveat: I have no idea how many you could allocate before a collision (but it would be a known and constant value). edit: This is now an old answer, but I saw it again with time on my hands, so, from observation... Chance of all numbers = 2.35% Chance of all letters = 0.05% First collision when MD5(82945) = "7b763dcb..." (same result as MD5(25302)) answered May 24, 2013 at 15:05
paulpaul 21.4k1 gold badge51 silver badges54 bronze badges 4 Create a random string Here's a MySQL function to create a random string of a given length.
Usage You can customize the Uniqueness isn't guaranteed - as you'll see in the comments to other solutions, this just isn't possible. Instead you'll need to generate a string, check if it's already in use, and try again if it is. Check if the random string is already in use If we want to keep the collision checking code out of the app, we can create a trigger:
answered Nov 30, 2016 at 10:41
Paddy MannPaddy Mann 1,04912 silver badges16 bronze badges 2 Here is one way, using alpha numerics as valid characters:
Note there is no guarantee of uniqueness. You'll have to check for that separately. answered May 24, 2013 at 15:07
Gordon LinoffGordon Linoff 1.2m53 gold badges598 silver badges740 bronze badges 2 Here's another method for generating a random string:
answered Dec 19, 2017 at 10:10
beingalexbeingalex 2,3764 gold badges31 silver badges69 bronze badges You may use MySQL's rand() and char() function:
answered May 24, 2013 at 15:11
Andrey VolkAndrey Volk 3,4832 gold badges16 silver badges28 bronze badges You can generate a random alphanumeric string with:
You can use it in a
Now just insert your data like
And the trigger will generate a value for the (sqlfiddle demo) That works this way if the column allows NULLs. If you want it to be NOT NULL you would need to define a default value
You can also use any other random string generating algorithm in the trigger if uppercase alphanumerics isn't what you want. But the trigger will take care of uniqueness. answered Sep 10, 2016 at 0:02
Paul SpiegelPaul Spiegel 30k5 gold badges42 silver badges51 bronze badges 8 For a String consisting of 8 random numbers and upper- and lowercase letters, this is my solution:
Explained from inside out:
answered Aug 22, 2017 at 14:08
Jan UhligJan Uhlig 811 silver badge1 bronze badge 2 For generate random string, you can use:
You recieve smth like that:
answered Apr 14, 2018 at 5:26
Nikita G.Nikita G. 7201 gold badge14 silver badges20 bronze badges I Use data from another column to generate a "hash" or unique string
slfan 8,805115 gold badges65 silver badges77 bronze badges answered Oct 5, 2016 at 20:37
0 8 letters from the alphabet - All caps:
answered Oct 12, 2016 at 4:29
TV-C-1-5TV-C-1-5 6712 gold badges11 silver badges17 bronze badges If you dont have a id or seed, like its its for a values list in insert:
answered Sep 19, 2017 at 17:36
ekernerekerner 5,5231 gold badge36 silver badges31 bronze badges Taking into account the total number of characters that you require, you would have a very small chance of generating two exactly similar number plates. Thus you could probably get away with generating the numbers in LUA. You have 36^8 different unique numberplates (2,821,109,907,456, that's a lot), even if you already had a million numberplates already, you'd have a very small chance of generating one you already have, about 0.000035% Of course, it all depends on how many numberplates you will end up creating. answered May 24, 2013 at 15:12
1 This function generates a Random string based on your input length and allowed characters like this:
function code:
This code is based on shuffle string function sends by "Ross Smith II" answered May 21, 2019 at 12:52
Mahoor13Mahoor13 5,0175 gold badges22 silver badges22 bronze badges 1 To create a random 10 digit alphanumeric, excluding lookalike chars 01oOlI:
This is exactly what I needed to create a voucher code. Confusing characters are removed to reduce errors when typing it into a voucher code form. Hopes this helps somebody, based on Jan Uhlig's brilliant answer. Please see Jan's answer for a breakdown on how this code works. answered Feb 17, 2020 at 10:35
Simple and efficient solution to get a random 10 characters string with uppercase and lowercase letters and digits :
answered Mar 1, 2020 at 21:18
AntaresAntares 1771 silver badge12 bronze badges
gives you a 16-character alphanumeric string that is unique. It has some unlikely caveats, see https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short The "next" value is often predictable:
Converting to BASE64 can get the string down to 11 characters: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64
That's 12 chars, stripping off the '=' gives you 11. These may make it unsuitable for your use: The "next" plate is somewhat predictable. There can be some punctuation marks ( answered Dec 6, 2021 at 20:03
Rick JamesRick James 127k11 gold badges121 silver badges204 bronze badges If you're OK with "random" but entirely predictable license plates, you can use a linear-feedback shift register to choose the next plate number - it's guaranteed to go through every number before repeating. However, without some complex math, you won't be able to go through every 8 character alphanumeric string (you'll get 2^41 out of the 36^8 (78%) possible plates). To make this fill your space better, you could exclude a letter from the plates (maybe O), giving you 97%. answered May 24, 2013 at 15:18
τεκτεκ 2,8541 gold badge15 silver badges13 bronze badges SQL Triggers are complex and resource-intensive. Against a MySQL "Trigger"-based solutions, here is a simpler solution.
This will be lighter and more efficient on DB Server. Here's a sample (pseudo-) code in PHP:
answered Nov 27, 2021 at 13:43
AquaholicAquaholic 7687 silver badges20 bronze badges This work form me, generate 6 digit number and update in MySQL: Generate:
Update:
marc_s 713k171 gold badges1314 silver badges1433 bronze badges answered Dec 10, 2021 at 10:51
Use this stored procedure and use it everytime like
answered Apr 13, 2018 at 7:00
An easy way that generate a unique number
answered Oct 12, 2018 at 7:31
GautierGautier 9961 gold badge11 silver badges23 bronze badges I was looking for something similar and I decided to make my own version where you can also specify a different seed if wanted (list of characters) as parameter:
Can be used as:
Which would use the built-in seed of upper- and lowercase characters + digits. NULL would also be value instead of ''. But one could specify a custom seed while calling:
answered Sep 11, 2019 at 9:47
|