MySQLでランダムなｎ文字を入れたいのですが、良いコードはございませんでしょうか？

Question

実現したいこと

MySQLでランダムな値をカラムに入れたいです。

前提

必須の条件
・ランダムな6文字
・文字種は「アルファベット大文字、小文字、0-9の数字」の３つ

もしできればな条件
・重複させたくない

発生している問題・エラーメッセージ

エラーはありません。しかしざっと見てみるお、なんとなくランダムにならないような印象があります。

該当のソースコード

以下です。実行すると、ランダムというか、似通った値になってしまいます。（012345などという値も入り、ランダム性が疑わしいです。）

update tbl set rand_str = 
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 6);

試したこと

以下試すと上記よりはランダムっぽくなりましたが、どうにも冗長な印象があり改善したいです。

update tbl set rand_str =
CONCAT(
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1),
SUBSTRING('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 62) + 1, 1)
)

確認したこと

上述のように、できれば「重複させたくない」と思っております。

まぁこの条件は達成できなくとも、UPDATE後に重複をSELECTし、重複レコードに対し再度UPDATEすればいいと思っておりましたので、いったん「試したこと」の実行後に、以下で重複をSELECTしてみました。

SELECT rand_str, COUNT(*) FROM tbl GROUP BY rand_str HAVING COUNT(*) > 1;

すると全レコード約6万件に対し、570件の重複がSELECTされ驚きました。

といいますのは、「アルファベット大文字、小文字、0-9の数字」では 56,800,235,584 通りになるはずなのに、6万件のうち570件も重複する可能性は低いはず。一体なぜ…？という驚きです。

このことから、「試したこと」ではランダム性がやや低いのか、とも疑っております。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[退会済みユーザー]

回答じゃないのでこちらで。
つ「 Function 」

[退会済みユーザー]

uuid は選択肢に入りませんか？

[m.ts10806]

SQLだけでやりきらなくても良いのでは

[Zuishin]

SQL でも頑張ればできるのかもしれませんが、汎用プログラミング言語でデータを作成してインポートするのが簡単だと思います。

56,800,235,584 は 64 ビットに収まるので、0 から 56,800,235,583 までの乱数を生成することは容易です。
生成した数値をハッシュセットなどに格納し、ハッシュセットの要素数が欲しい数になるまでそれを繰り返します。
それが終わったら、ハッシュセットから一つずつ取り出しながら、それを 0 から 9 までの数字とアルファベットの大文字小文字で表せる 62 進数文字列に変換します。
変換の仕方は「N 進数 変換」でググるとすぐに見つかります。
変換後の文字列が求める文字列になるので、これを CSV ファイルに出力し、データベースにインポートします。

[sazi]

MySQLのパスワード生成機能では駄目なのですか？

[origa3]

みなさまアドバイスありがとうございます。
いろいろな方法があるようで、参考にさせていただきます。

[Zuishin]

前提が書かれていない上に聞いても返事をしないためにみんなで当てずっぽうを書いてたまたま当ったのがあれば解決という質問が増えてきましたね。

[Zuishin]

前提を消した。

Answer 1

56,800,235,584通りを想定しているということは同じ文字が2度でてもよいという認識ですかね？(56,800,235,584=62の6乗)
まずファンクションを作って

drop function if exists rnd;
delimiter //
create function rnd(n int)
returns varchar(255) DETERMINISTIC
begin 
if n>255 then 
set @a=null;
else
set @a="";
set @b=1;
while @b<=n
do
set @a:=concat(@a,substring('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',ceil(rand() * 62),1));
set @b:=@b+1;
end while;
end if;
return @a;
end
//

こんな感じで指定

select rnd(6);

調整

ちょっとランダムの精度を上げてみました。
同じコードを次に引き渡していくとランダム性はあがるかも

create table tbl (id int primary key auto_increment,val varchar(6));

drop procedure if exists set_blank;
delimiter //
create procedure set_blank(in n int)
begin
set @sql:=concat('insert into tbl(val) values(\'\')',repeat(',(\'\')',n-1));
prepare stmt from @sql;
execute stmt;
end
//
delimiter ;

drop function if exists str_shuffle;
delimiter //
create function str_shuffle(t text)
returns text
begin
set @txt=t;
set @ret='';
set @pos=0;
set @u=length(t);
WHILE @u > 0
do
set @pos=1 + floor(rand() * @u);
set @ret = concat(@ret,mid(@txt,@pos,1));
set @txt = concat(left(@txt,@pos-1),mid(@txt,@pos+1,@u));
set @u=@u-1;
end while;
return @ret;
end
//
delimiter ;

call set_blank(60000);
set @a:='abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
update tbl set val=substr(@a:=str_shuffle(@a),1,6);

それでもいくつかはだぶりますけど

select val,count(*) as cnt from tbl group by val
order by cnt desc

Comments

[sazi]

一括処理を考慮して、NOT DETERMINISTIC　の方が良くないですか？

[yambejp]

TO:saziさん
私も最初NOT DETERMINISTIC（デフォルト）にしようと思ったのですが、私の環境がわるいのかエラーがでるので仕方なくDETERMINISTICにしてあります、どうやら
SET GLOBAL log_bin_trust_function_creators = 1;
を設定しておけばNOT DETERMINISTICが指定できるようですね・・・

[origa3]

すごい！そのような関数を作っておくと後からいくらでも呼べるのですね。
どうもありがとうございます。勉強になりました。

ただし、下記に記すように質問と同じ現象が起こりました。

現状 tbl には約6万件のレコードがあるので、それに対し

UPDATE tbl SET rand_str = rnd(6);
を実行し、

SELECT rand_str, COUNT(*) FROM tbl GROUP BY rand_str HAVING COUNT(*) > 1;
を実行して重複を探してみますと、

やはり質問と同様に600件弱の重複が見つかりました。

56,800,235,584通りの組み合わせに対して6万回の試行をしても、重複する可能性は相当低いはずですが、なぜ600件もの重複が起こるのか…。このランダム性の低さは何に起因するのかがやはり疑問です。

[yambejp]

ランダム処理はしょせん疑似処理ですからねぇ、一応文字列をシャッフルする方式にするとランダム性はあがると思います

[退会済みユーザー]

まあネタではあるが インデックスを求める計算式は
（乱数ｘ最大値）
より
（乱数ｘ係数＋乱数ｘ桁番号）％ 最大値
のような余で算出する方がすきｗ

[origa3]

ご調整実行させて頂きました。ほぼ（1, 2件しか）ダブらなくなります！
「重複レコードに対し再度UPDATEすればいい」とは申したものの内心面倒に感じていたところでしたが、その手間が大きく減りました。どうもありがとうございます。

Answer 2

「アルファベット大文字、小文字、0-9の数字」では 56,800,235,584 通りになるはずなのに、6万件のうち570件も重複する可能性は低いはず。一体なぜ…？という驚きです。

この部分についてですが、文字コードの照合順位がutf8_general_ciだったりすると、WHEREやGROUP BYにおいてアルファベットの大文字と小文字の区別がありません。

下記SQLで結果が変わったりしないでしょうか？

SELECT rand_str, COUNT(*) FROM tbl GROUP BY BINARY rand_str HAVING COUNT(*) > 1;

Comments

[origa3]

> WHEREやGROUP BYにおいてアルファベットの大文字と小文字の区別がありません。
アドバイスありがとうございます。確認方法に問題がありえたとは気が付きませんでした。

> 文字コードの照合順位がutf8_general_ciだったりすると
こちらの確認はわかりませんでした。「SHOW COLLATION」とやらを実行するとずらっと出ますがどれがその照合順位なのか…

> 下記SQLで結果が変わったりしないでしょうか？
こちらは１件も変化なしでした。（ただしONLY_FULL_GROUP_BYがあるとエラーになるので次のようにONLY_FULL_GROUP_BYは解除しましたが。）

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT rand_str, COUNT(*) FROM tbl GROUP BY BINARY rand_str HAVING COUNT(*) > 1;