blocks|key|311020|text|如何计算连续整数的MD5+(或其他)散列，然后获取前8个字符。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311021|i.e|311022|MD5(1)+=+c4ca4238a0b923820dcc509a6f75849b+=>+c4ca4238
MD5(2)+=+c81e728d9d4c2f636f067f89cc14862c+=>+c81e728d
MD5(3)+=+eccbc87e4b5ce2fe28308fd9f2a7baf3+=>+eccbc87e|code-block|syntax|javascript|311023|等。|311024|警告:我不知道在冲突之前你可以分配多少(但它将是一个已知的常量值)。|311025|编辑:这是一个旧的答案，但随着时间的推移，我又看到了它，所以，从观察来看…|311026|所有数字的概率=+2.35%25|311027|所有字母的概率=+0.05%25|311028|当MD5(82945)+=+"7b763dcb...“时第一次冲突(结果与MD5(25302)相同)|311029|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

What about calculating the MD5 (or other) hash of sequential integers, then taking the first 8 characters.

i.e 

<pre><code>MD5(1) = c4ca4238a0b923820dcc509a6f75849b =&gt; c4ca4238
MD5(2) = c81e728d9d4c2f636f067f89cc14862c =&gt; c81e728d
MD5(3) = eccbc87e4b5ce2fe28308fd9f2a7baf3 =&gt; eccbc87e
</code></pre>

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))

blocks|key|94129|text|以下是一种使用字母数字作为有效字符的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94130|select+concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1),
++++++++++++++substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',+rand()*36%2B1,+1)
+++++++++++++)+as+LicensePlaceNumber;|code-block|syntax|javascript|94131|注意:不能保证唯一性。你得单独检查一下。|94132|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is one way, using alpha numerics as valid characters:

<pre><code>select concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1)
 ) as LicensePlaceNumber;
</code></pre>

Note there is no guarantee of uniqueness. You'll have to check for that separately.

blocks|key|94161|text|您可以使用MySQL的rand()和char()函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|94162|select+concat(+
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97),
++++char(round(rand()*25)%2B97)
)+as+name;|code-block|syntax|javascript|94163|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html#function_rand|1|http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_char^0|B|6|0|I|6|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

You may use MySQL's <a href="http://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html#function_rand">rand()</a> and <a href="http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_char">char()</a> function:

<pre><code>select concat( 
 char(round(rand()*25)+97),
 char(round(rand()*25)+97),
 char(round(rand()*25)+97),
 char(round(rand()*25)+97),
 char(round(rand()*25)+97),
 char(round(rand()*25)+97),
 char(round(rand()*25)+97),
 char(round(rand()*25)+97)
) as name;
</code></pre>

blocks|key|94235|text|考虑到所需的字符总数，生成两个完全相同的车牌的可能性非常小。因此，您可能不需要在LUA中生成数字。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94236|你有36%5E8个不同的唯一车牌(2,821,109,907,456，这是很多)，即使你已经有一百万个车牌，你也只有很小的机会生成一个你已经有的车牌，大约0.000035%25|94237|当然，这完全取决于你最终会创建多少个车牌。|94238|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|94319|text|我不关心碰撞的可能性。只需生成一个随机字符串并检查它是否存在。如果是这样，再试一次，除非你已经分配了大量的车牌，否则你不需要做更多的次数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94320|用纯(我的)SQL生成8字符长的伪随机字符串的另一种解决方案：|94321|SELECT+LEFT(UUID(),+8);|code-block|syntax|javascript|94322|您可以尝试执行以下操作(伪代码)：|94323|DO+
++++SELECT+LEFT(UUID(),+8)+INTO+@plate;
++++INSERT+INTO+plates+(@plate);
WHILE+there_is_a_unique_constraint_violation
--+@plate+is+your+newly+assigned+plate+number|94324|94325|由于这篇文章受到了意想不到的关注，让我强调一下ADTC's+comment：上面的代码非常愚蠢，并产生了连续的数字。|offset|length|94326|对于稍微不那么愚蠢的随机性，可以试着这样做：|94327|SELECT+LEFT(MD5(RAND()),+8)|94328|对于真正的(加密安全的)随机性，使用RANDOM_BYTES()而不是RAND()+(但是我会考虑将此逻辑上移到应用层)。|style|CODE|94329|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql/16738332#comment69315008_16738332^0|0|0|0|0|0|0|N|E|0|0|0|0|I|E|Z|6|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|18|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|19|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1A|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1B|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1D|8|@]|9|@$P|1E|Q|1F|1|1G]]|A|$]]|$1|R|3|S|5|6|7|1H|8|@]|9|@]|A|$]]|$1|T|3|U|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|V|3|W|5|6|7|1J|8|@$P|1K|Q|1L|X|Y]|$P|1M|Q|1N|X|Y]]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|10|$11|$5|12|13|14|A|$15|16]]]]

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:
<pre><code>SELECT LEFT(UUID(), 8);
</code></pre>
You can try the following (pseudo-code):
<pre><code>DO 
 SELECT LEFT(UUID(), 8) INTO @plate;
 INSERT INTO plates (@plate);
WHILE there_is_a_unique_constraint_violation
-- @plate is your newly assigned plate number
</code></pre>
<hr />
Since this post has received a unexpected level of attention, let me highlight <a href="https://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql/16738332#comment69315008_16738332">ADTC's comment</a> : the above piece of code is quite dumb and produces sequential digits.
For slightly less stupid randomness try something like this instead :
<pre><code>SELECT LEFT(MD5(RAND()), 8)
</code></pre>
And for true (cryptograpically secure) randomness, use <code>RANDOM_BYTES()</code> rather than <code>RAND()</code> (but then I would consider moving this logic up to the application layer).

blocks|key|311299|text|如果你对“随机”但完全可预测的车牌没有意见，你可以使用linear-feedback+shift+register来选择下一个车牌号码-它保证在重复之前检查每个数字。但是，如果没有一些复杂的数学运算，您将无法遍历每个8个字符的字母数字字符串(您将获得36%5E8+(78%25)个可能牌照中的2%5E41个)。为了更好地填充您的空间，您可以从车牌中排除一个字母(可能是O)，得到97%25。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|311300|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/LFSR^0|R|U|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

If you're OK with "random" but entirely predictable license plates, you can use a <a href="http://en.wikipedia.org/wiki/LFSR" rel="nofollow">linear-feedback shift register</a> 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%.

blocks|key|311333|text|您可以使用以下命令生成随机字母数字字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311334|lpad(conv(floor(rand()*pow(36,8)),+10,+36),+8,+0);|code-block|syntax|javascript|311335|您可以在BEFORE+INSERT触发器中使用它，并在while循环中检查重复项：|offset|length|style|CODE|311336|CREATE+TABLE+`vehicles`+(
++++`plate`+CHAR(8)+NULL+DEFAULT+NULL,
++++`data`+VARCHAR(50)+NOT+NULL,
++++UNIQUE+INDEX+`plate`+(`plate`)
);

DELIMITER+//
CREATE+TRIGGER+`vehicles_before_insert`+BEFORE+INSERT+ON+`vehicles`
FOR+EACH+ROW+BEGIN

++++declare+str_len+int+default+8;
++++declare+ready+int+default+0;
++++declare+rnd_str+text;
++++while+not+ready+do
++++++++set+rnd_str+:=+lpad(conv(floor(rand()*pow(36,str_len)),+10,+36),+str_len,+0);
++++++++if+not+exists+(select+*+from+vehicles+where+plate+=+rnd_str)+then
++++++++++++set+new.plate+=+rnd_str;
++++++++++++set+ready+:=+1;
++++++++end+if;
++++end+while;

END//
DELIMITER+;|311337|现在只需像这样插入您的数据|311338|insert+into+vehicles(col1,+col2)+values+('value1',+'value2');|311339|触发器将为plate列生成一个值。|311340|(sqlfiddle+demo)|311341|如果该列允许空值，则可以这样工作。如果希望它不为空，则需要定义一个缺省值|311342|`plate`+CHAR(8)+NOT+NULL+DEFAULT+'default',|311343|如果大写字母数字不是您想要的，您也可以在触发器中使用任何其他随机字符串生成算法。但是触发器将照顾到唯一性。|311344|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!9/2c15f7/1^0|0|0|4|D|0|0|0|0|5|5|0|1|E|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@$I|1D|J|1E|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|1F|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|1H|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|1I|8|@$I|1J|J|1K|K|L]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1L|8|@]|9|@$I|1M|J|1N|1|1O]]|A|$]]|$1|W|3|X|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|D|7|1Q|8|@]|9|@]|A|$E|F]]|$1|10|3|11|5|6|7|1R|8|@]|9|@]|A|$]]|$1|12|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|19]]]]

You can generate a random alphanumeric string with:

<pre><code>lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0);
</code></pre>

You can use it in a <code>BEFORE INSERT</code> trigger and check for a duplicate in a while loop:

<pre><code>CREATE TABLE `vehicles` (
 `plate` CHAR(8) NULL DEFAULT NULL,
 `data` VARCHAR(50) NOT NULL,
 UNIQUE INDEX `plate` (`plate`)
);

DELIMITER //
CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `vehicles`
FOR EACH ROW BEGIN

 declare str_len int default 8;
 declare ready int default 0;
 declare rnd_str text;
 while not ready do
 set rnd_str := lpad(conv(floor(rand()*pow(36,str_len)), 10, 36), str_len, 0);
 if not exists (select * from vehicles where plate = rnd_str) then
 set new.plate = rnd_str;
 set ready := 1;
 end if;
 end while;

END//
DELIMITER ;
</code></pre>

Now just insert your data like

<pre><code>insert into vehicles(col1, col2) values ('value1', 'value2');
</code></pre>

And the trigger will generate a value for the <code>plate</code> column.

(<a href="http://sqlfiddle.com/#!9/2c15f7/1" rel="noreferrer">sqlfiddle demo</a>)

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

<pre><code>`plate` CHAR(8) NOT NULL DEFAULT 'default',
</code></pre>

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.

blocks|key|94433|text|我使用另一列中的数据来生成"hash“或唯一字符串|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94434|UPDATE+table_name+SET+column_name+=+Right(+MD5(another_column_with_data),+8+)|code-block|syntax|javascript|94435|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I Use data from another column to generate a "hash" or unique string

<pre><code>UPDATE table_name SET column_name = Right( MD5(another_column_with_data), 8 )
</code></pre>

blocks|key|94457|text|字母表中的8个字母-全部大写：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94458|UPDATE+`tablename`+SET+`tablename`.`randomstring`=+concat(CHAR(FLOOR(65+%2B+(RAND()+*+25))),CHAR(FLOOR(65+%2B+(RAND()+*+25))),CHAR(FLOOR(65+%2B+(RAND()+*+25))),CHAR(FLOOR(65+%2B+(RAND()+*+25)))CHAR(FLOOR(65+%2B+(RAND()+*+25))),CHAR(FLOOR(65+%2B+(RAND()+*+25))),CHAR(FLOOR(65+%2B+(RAND()+*+25))),CHAR(FLOOR(65+%2B+(RAND()+*+25))));|code-block|syntax|javascript|94459|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

8 letters from the alphabet - All caps:

<pre><code>UPDATE `tablename` SET `tablename`.`randomstring`= concat(CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25)))CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))));
</code></pre>

blocks|key|94479|text|如果您没有id或种子，就像它在insert中的值列表一样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94480|REPLACE(RAND(),+'.',+'')|code-block|syntax|javascript|94481|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you dont have a id or seed, like its its for a values list in insert:

<pre><code>REPLACE(RAND(), '.', '')
</code></pre>

blocks|key|311496|text|下面是生成随机字符串的另一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311497|SELECT+SUBSTRING(MD5(RAND())+FROM+1+FOR+8)+AS+myrandomstring|offset|length|style|CODE|311498|entityMap^0|0|0|1O|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Here's another method for generating a random string:

<code>SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8) AS myrandomstring</code>

blocks|key|311508|text|DELIMITER+$$

USE+`temp`+$$

DROP+PROCEDURE+IF+EXISTS+`GenerateUniqueValue`$$

CREATE+PROCEDURE+`GenerateUniqueValue`(IN+tableName+VARCHAR(255),IN+columnName+VARCHAR(255))+
BEGIN
++++DECLARE+uniqueValue+VARCHAR(8)+DEFAULT+"";
++++WHILE+LENGTH(uniqueValue)+=+0+DO
++++++++SELECT+CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1),
++++++++++++++++SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789',+RAND()*34%2B1,+1)
++++++++++++++++)+INTO+@newUniqueValue;
++++++++SET+@rcount+=+-1;
++++++++SET+@query=CONCAT('SELECT+COUNT(*)+INTO+@rcount+FROM++',tableName,'+WHERE+',columnName,'++like+''',@newUniqueValue,'''');
++++++++PREPARE+stmt+FROM++@query;
++++++++EXECUTE+stmt;
++++++++DEALLOCATE+PREPARE+stmt;
++++IF+@rcount+=+0+THEN
++++++++++++SET+uniqueValue+=+@newUniqueValue+;
++++++++END+IF+;
++++END+WHILE+;
++++SELECT+uniqueValue;
++++END$$

DELIMITER+;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|311509|使用这个存储过程，并且每次都使用它，如下所示|unstyled|311510|Call+GenerateUniqueValue('tableName','columnName')|311511|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>DELIMITER $$

USE `temp` $$

DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$

CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255)) 
BEGIN
 DECLARE uniqueValue VARCHAR(8) DEFAULT "";
 WHILE LENGTH(uniqueValue) = 0 DO
 SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
 SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1)
 ) INTO @newUniqueValue;
 SET @rcount = -1;
 SET @query=CONCAT('SELECT COUNT(*) INTO @rcount FROM ',tableName,' WHERE ',columnName,' like ''',@newUniqueValue,'''');
 PREPARE stmt FROM @query;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 IF @rcount = 0 THEN
 SET uniqueValue = @newUniqueValue ;
 END IF ;
 END WHILE ;
 SELECT uniqueValue;
 END$$

DELIMITER ;
</code></pre>

Use this stored procedure and use it everytime like

<pre><code>Call GenerateUniqueValue('tableName','columnName')
</code></pre>

blocks|key|94542|text|对于生成随机字符串，您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94543|SUBSTRING(MD5(RAND())+FROM+1+FOR+8)|offset|length|style|CODE|94544|你会收到这样的smth：|94545|353E50CC|94546|entityMap^0|0|0|Z|0|0|0|8|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

For generate random string, you can use:

<code>SUBSTRING(MD5(RAND()) FROM 1 FOR 8)</code>

You recieve smth like that:

<code>353E50CC</code>

blocks|key|94566|text|一种生成唯一数字的简单方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94567|set+@i+=+0;
update+vehicles+set+plate+=+CONCAT(@i:=@i%2B1,+ROUND(RAND()+*+1000))+
order+by+rand();|code-block|syntax|javascript|94568|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

An easy way that generate a unique number 

<pre><code>set @i = 0;
update vehicles set plate = CONCAT(@i:=@i+1, ROUND(RAND() * 1000)) 
order by rand();
</code></pre>

blocks|key|311533|text|此函数根据您的输入长度和允许的字符生成随机字符串，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311534|SELECT+str_rand(8,+'23456789abcdefghijkmnpqrstuvwxyz');|code-block|syntax|javascript|311535|功能代码：|311536|DROP+FUNCTION+IF+EXISTS+str_rand;

DELIMITER+//

CREATE+FUNCTION+str_rand(
++++u_count+INT+UNSIGNED,
++++v_chars+TEXT
)
RETURNS+TEXT
NOT+DETERMINISTIC
NO+SQL
SQL+SECURITY+INVOKER
COMMENT+''
BEGIN
++++DECLARE+v_retval+TEXT+DEFAULT+'';
++++DECLARE+u_pos++++INT+UNSIGNED;
++++DECLARE+u++++++++INT+UNSIGNED;

++++SET+u+=+LENGTH(v_chars);
++++WHILE+u_count+>+0
++++DO
++++++SET+u_pos+=+1+%2B+FLOOR(RAND()+*+u);
++++++SET+v_retval+=+CONCAT(v_retval,+MID(v_chars,+u_pos,+1));
++++++SET+u_count+=+u_count+-+1;
++++END+WHILE;

++++RETURN+v_retval;
END;
//
DELIMITER+;|311537|这个代码是基于“罗斯·史密斯二世”发送的shuffle+string+function|offset|length|311538|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/11860364/514963^0|0|0|0|0|K|N|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

This function generates a Random string based on your input length and allowed characters like this:

<pre><code>SELECT str_rand(8, '23456789abcdefghijkmnpqrstuvwxyz');
</code></pre>

function code: 

<pre><code>DROP FUNCTION IF EXISTS str_rand;

DELIMITER //

CREATE FUNCTION str_rand(
 u_count INT UNSIGNED,
 v_chars TEXT
)
RETURNS TEXT
NOT DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
 DECLARE v_retval TEXT DEFAULT '';
 DECLARE u_pos INT UNSIGNED;
 DECLARE u INT UNSIGNED;

 SET u = LENGTH(v_chars);
 WHILE u_count &gt; 0
 DO
 SET u_pos = 1 + FLOOR(RAND() * u);
 SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
 SET u_count = u_count - 1;
 END WHILE;

 RETURN v_retval;
END;
//
DELIMITER ;
</code></pre>

This code is based on <a href="https://stackoverflow.com/a/11860364/514963">shuffle string function</a> sends by "Ross Smith II"

blocks|key|311550|text|我正在寻找类似的东西，我决定制作自己的版本，如果需要，还可以指定不同的种子(字符列表)作为参数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311551|CREATE+FUNCTION+`random_string`(length+SMALLINT(3),+seed+VARCHAR(255))+RETURNS+varchar(255)+CHARSET+utf8
++++NO+SQL
BEGIN
++++SET+@output+=+'';

++++IF+seed+IS+NULL+OR+seed+=+''+THEN+SET+seed+=+'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';+END+IF;

++++SET+@rnd_multiplier+=+LENGTH(seed);

++++WHILE+LENGTH(@output)+<+length+DO
++++++++#+Select+random+character+and+add+to+output
++++++++SET+@output+=+CONCAT(@output,+SUBSTRING(seed,+RAND()+*+(@rnd_multiplier+%2B+1),+1));
++++END+WHILE;

++++RETURN+@output;
END|code-block|syntax|javascript|311552|可用作：|311553|SELECT+random_string(10,+'')|311554|它将使用大小写字符%2B数字的内置种子。NULL也可以是value而不是'‘。|311555|但可以在调用时指定自定义种子：|311556|SELECT+random_string(10,+'1234')|311557|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>CREATE FUNCTION `random_string`(length SMALLINT(3), seed VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
 NO SQL
BEGIN
 SET @output = '';

 IF seed IS NULL OR seed = '' THEN SET seed = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; END IF;

 SET @rnd_multiplier = LENGTH(seed);

 WHILE LENGTH(@output) &lt; length DO
 # Select random character and add to output
 SET @output = CONCAT(@output, SUBSTRING(seed, RAND() * (@rnd_multiplier + 1), 1));
 END WHILE;

 RETURN @output;
END
</code></pre>

Can be used as:

<pre><code>SELECT random_string(10, '')
</code></pre>

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:

<pre><code>SELECT random_string(10, '1234')
</code></pre>

blocks|key|311580|text|简单而有效的解决方案，以获取包含大小写字母和数字的随机10个字符的字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311581|select+substring(base64_encode(md5(rand()))+from+1%2Brand()*4+for+10);|code-block|syntax|javascript|311582|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Simple and efficient solution to get a random 10 characters string with uppercase and lowercase letters and digits :

<pre><code>select substring(base64_encode(md5(rand())) from 1+rand()*4 for 10);
</code></pre>

blocks|key|94646|text|UPPER(HEX(UUID_SHORT()))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|94647|为您提供唯一的16个字符的字母数字字符串。它有一些不太可能的警告，请参阅https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short|unstyled|offset|length|94648|"next“值通常是可预测的：|94649|mysql>+SELECT+UPPER(HEX(UUID_SHORT()));
%2B--------------------------%2B
%7C+UPPER(HEX(UUID_SHORT()))+%7C
%2B--------------------------%2B
%7C+161AA3FA5000006++++++++++%7C
%2B--------------------------%2B

mysql>+SELECT+UPPER(HEX(UUID_SHORT()));
%2B--------------------------%2B
%7C+UPPER(HEX(UUID_SHORT()))+%7C
%2B--------------------------%2B
%7C+161AA3FA5000007++++++++++%7C
%2B--------------------------%2B|94650|转换为BASE64可以将字符串缩小到11个字符：|94651|https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64|94652|mysql>+SELECT+TO_BASE64(UNHEX(HEX(UUID_SHORT())));
%2B-------------------------------------%2B
%7C+TO_BASE64(UNHEX(HEX(UUID_SHORT())))+%7C
%2B-------------------------------------%2B
%7C+AWGqP6UAABA=++++++++++++++++++++++++%7C
%2B-------------------------------------%2B|94653|这是12个字符，去掉'=‘就是11个字符。|94654|这些可能使它不适合您的使用：“下一个”板块在某种程度上是可以预测的。字符串中可以有一些标点符号(%2B、/)。可能会包含小写字母。|style|CODE|94655|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short|1^0|0|10|2G|0|0|0|0|0|0|28|1|0|0|0|1C|1|1E|1|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|18|8|@]|9|@$G|19|H|1A|1|1B]]|A|$]]|$1|I|3|J|5|F|7|1C|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1D|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|1E|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1F|8|@]|9|@$G|1G|H|1H|1|1I]]|A|$]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$B|C]]|$1|S|3|T|5|F|7|1K|8|@]|9|@]|A|$]]|$1|U|3|V|5|F|7|1L|8|@$G|1M|H|1N|W|X]|$G|1O|H|1P|W|X]]|9|@]|A|$]]|$1|Y|3|-4|5|F|7|1Q|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]|16|$5|11|12|13|A|$14|P]]]]

<pre><code>UPPER(HEX(UUID_SHORT()))
</code></pre>
gives you a 16-character alphanumeric string that is unique. It has some unlikely caveats, see <a href="https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short</a>
The &quot;next&quot; value is often predictable:
<pre><code>mysql&gt; SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000006 |
+--------------------------+

mysql&gt; SELECT UPPER(HEX(UUID_SHORT()));
+--------------------------+
| UPPER(HEX(UUID_SHORT())) |
+--------------------------+
| 161AA3FA5000007 |
+--------------------------+
</code></pre>
Converting to BASE64 can get the string down to 11 characters:
<a href="https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_to-base64</a>
<pre><code>mysql&gt; SELECT TO_BASE64(UNHEX(HEX(UUID_SHORT())));
+-------------------------------------+
| TO_BASE64(UNHEX(HEX(UUID_SHORT()))) |
+-------------------------------------+
| AWGqP6UAABA= |
+-------------------------------------+
</code></pre>
That's 12 chars, stripping off the '=' gives you 11.
These may make it unsuitable for your use: The &quot;next&quot; plate is somewhat predictable. There can be some punctuation marks (<code>+</code>,<code>/</code>) in the string. Lower case letters are likely to be included.

blocks|key|311654|text|这项工作由我完成，生成6位数并在MySQL中更新：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311655|生成：|311656|SELECT+SUBSTRING(MD5(RAND())+FROM+1+FOR+6)|code-block|syntax|javascript|311657|更新：|311658|UPDATE+table_name+
SET+column_name+=+SUBSTRING(MD5(RAND())+FROM+1+FOR+6)+
WHERE+id+=+x12|311659|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This work form me, generate 6 digit number and update in MySQL:
Generate:
<pre><code>SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6)
</code></pre>
Update:
<pre><code>UPDATE table_name 
SET column_name = SUBSTRING(MD5(RAND()) FROM 1 FOR 6) 
WHERE id = x12
</code></pre>

I'm working on a game which involves vehicles at some point. I have a MySQL table named "vehicles" containing the data about the vehicles, including the column "plate" which stores the License Plates for the vehicles.

Now here comes the part I'm having problems with. I need to find an unused license plate before creating a new vehicle - it should be an alphanumeric 8-char random string. How I achieved this was using a while loop in Lua, which is the language I'm programming in, to generate strings and query the DB to see if it is used. However, as the number of vehicles increases, I expect this to become even more inefficient it is right now. Therefore, I decided to try and solve this issue using a MySQL query.

The query I need should simply generate a 8-character alphanumeric string which is not already in the table. I thought of the generate&amp;check loop approach again, but I'm not limiting this question to that just in case there's a more efficient one. I've been able to generate strings by defining a string containing all the allowed chars and randomly substringing it, and nothing more. 

Any help is appreciated.

Generating a random & unique 8 character string using MySQL

MySQL

我正在开发一款游戏，在某些时候涉及到车辆。我有一个名为" vehicles“的MySQL表，其中包含有关车辆的数据，其中包括存储车辆牌照的列”vehicles“。现在我遇到问题的部分来了。我需要在创建新车之前找到一个未使用的车牌-它应该是一个由8个字符组成的字母数字随机字符串。我是通过在Lua中使用while循环来生成...

问使用MySQL生成随机且唯一的8个字符的字符串
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL生成随机且唯一的8个字符的字符串EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用MySQL生成随机且唯一的8个字符的字符串
EN