Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
5
Help us understand the problem. What is going on with this article?
@pinkumohikan

MySQLで任意の文字種を使ったランダムな文字列を作る

More than 3 years have passed since last update.

この記事で分かること

  • MySQLで任意の文字種を使って任意の長さの(そこそこ)ランダムな文字列を作る方法

概要

MySQLで任意の文字種を使った任意長のある程度ランダムな文字列が生成したくなったので調べてみたら、組み込み関数でペロっとできるものではなさそうだったのでfunctionを書いた。

モノ

delimiter //

create function generate_random_string (permitted_chars text, string_length int) returns text
begin
    declare i int default 0;
    declare generated_string text default '';

    while i < string_length do
        set generated_string = concat(
            generated_string,
            substring(permitted_chars, ceil(rand() * char_length(permitted_chars)), 1)
        );
        set i = i + 1;
    end while;

    return generated_string;
end//

delimiter ;

使わなくなったら drop function generate_random_string で葬ってあげる。

実行例

mysql> select generate_random_string('a', 1);
+--------------------------------+
| generate_random_string('a', 1) |
+--------------------------------+
| a                              |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select generate_random_string('abc', 1);
+----------------------------------+
| generate_random_string('abc', 1) |
+----------------------------------+
| c                                |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select generate_random_string('abc', 1);
+----------------------------------+
| generate_random_string('abc', 1) |
+----------------------------------+
| b                                |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select generate_random_string('0123456789', 32);
+------------------------------------------+
| generate_random_string('0123456789', 32) |
+------------------------------------------+
| 46034298664266382588833453036192         |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select generate_random_string('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 32);
+----------------------------------------------------------------------------------------------+
| generate_random_string('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 32) |
+----------------------------------------------------------------------------------------------+
| Ki5pAAZ39ppFZKENU0aCq8eDggp50CUy                                                             |
+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

キーワード

独り言

任意の文字種を使う、っていう要件がなければ、 sha1 で生成した文字列をsubstringでカットしてあげる方がシンプルで好きです(この場合、英小文字 + 数値の文字列が生成される)。

もっとスマートにかける方法があれば、是非教えてください(_ _)

参考文献

  1. "[MySQL] ランダム文字列の生成", http://readit.l8r.in/post-382/, 2016/11/22参照.
5
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
pinkumohikan
プログラマー風林火山で言う「風のエンジニア」になりたい(比較的)若者Webエンジニア。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
5
Help us understand the problem. What is going on with this article?