Go to Qiita Advent Calendar 2024 Top
LoginSignup
7
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@mimitaro

MySQLで連続した値を扱う方法

Posted at

はじめに

最近、MySQLを使う機会が増えています。
その中でテストデータの生成や、連続する日付生成など連番が欲しくなる場面がちょいちょいありました。

連番はPostgreSQLならばgenerate_series()関数が、OracleならLEVEL疑似列があります。
しかしMySQLにはちょうどいいのがないので、色々工夫する必要がありました。

なので今回はMySQLで連番を生成する"工夫"について、まとめたいと思います。

既存テーブルへの連番追加

例えば次のような果物とその値段が入力されたテーブルがあるとします。

果物テーブル
SELECT * FROM sample;

+-----------+-------+
| name      | price |
+-----------+-------+
| りんご    |   100 |
| みかん    |    80 |
| バナナ    |   120 |
+-----------+-------+

これを値段の安い順に番号を付け並べるには以下のようにします。

果物テーブルに連番を付与
SET @num=0;
SELECT
  @num:=@num+1 AS num,
  name,
  price
FROM sample
ORDER BY price;

+------+-----------+-------+
| num  | name      | price |
+------+-----------+-------+
|    1 | みかん    |    80 |
|    2 | りんご    |   100 |
|    3 | バナナ    |   120 |
+------+-----------+-------+

「@num」変数を定義することで上述のように簡単に連番を付与できます。
ここで注意することは必ず「SET @num=0;」を忘れないことです。
「@num」変数の変動は継続するため、再び上述のSELECT文を実行すると次のような結果となります。

@numを初期化せずにSELECT文を再度実行
SELECT
  @num:=@num+1 AS num,
  name,
  price
FROM sample
ORDER BY price;

+------+-----------+-------+
| num  | name      | price |
+------+-----------+-------+
|    4 | みかん    |    80 |
|    5 | りんご    |   100 |
|    6 | バナナ    |   120 |
+------+-----------+-------+

前回で「@num」が3となったため、さらに増加しています。
そのため「SET @num=0;」による初期化が必要です。

テストデータのための連番生成

DBの連番生成機能のよいところは、テストデータが簡単に生成できることです。
しかしMySQLだと丁度よい関数がないため、工夫が必要です。

単純な方法

先程説明した通り、既存のテーブルには簡単に連番が付与できます。
そのためMySQLがデフォルトで持っており、どのユーザでも簡単にアクセス可能なテーブルがあれば、連番のみのテーブルが作成可能です。
※行数さえ豊富にあれば中身のデータは参照しないので何でもよいです。

多くの方はそんな都合のいいテーブルとして「information_schema.COLUMNS」を使っているようです。
例えば10行の連番を持つテストテーブルを作成するには次のようにします。

10行のテストデータ生成
SET @num=0;
SELECT
  @num:=@num+1 AS num,
  MD5(@num) AS txt
FROM information_schema.COLUMNS
LIMIT 10;

+------+----------------------------------+
| num  | txt                              |
+------+----------------------------------+
|    1 | c4ca4238a0b923820dcc509a6f75849b |
|    2 | c81e728d9d4c2f636f067f89cc14862c |
|    3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
|    4 | a87ff679a2f3e71d9181a67b7542122c |
|    5 | e4da3b7fbbce2345d7772b0674a318d5 |
|    6 | 1679091c5a880faf6fb5e6087eb1b2dc |
|    7 | 8f14e45fceea167a5a36dedd4bea2543 |
|    8 | c9f0f895fb98ab9159f51fd0297e236d |
|    9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
|   10 | d3d9446802a44259755d38e6d163e820 |
+------+----------------------------------+

※MD5関数を使うことで文字列も生成しています。

ただしこの方法で生成できるテーブルの行数は「information_schema.COLUMNS」の行数に依存しています。
これを超えるテストデータ生成には、UNIONを上手く使うか、自前で行数が非常に大きいテーブルを作る必要があります。

プログラムを使った方法

UNIONでテーブルを連結することで、次のように任意の行数のテーブルが生成可能です。

ただし手動でこれを何千行分も生成するのは現実的ではありません。
MySQLへ何かしらのプログラミング言語で接続してループ処理するのが簡単です。

今回はmysql-connector-pythonを用いてpythonから接続して試してみます。

mysql_test.py
# coding:utf-8
import mysql.connector

# 接続情報辞書
config = {
    'user': 'root',
    'password': 'password',
    'host': '123.45.67.890',
    'database': 'test'
}

# 接続
conn = mysql.connector.connect(**config)

# ------------------------------
# テストデータSELECT文生成
# ------------------------------
start_num = 0
end_num = 10
query_list = ["(SELECT {start_num} AS s".format(start_num=start_num)]
# 「UNION ALL SELECT {num}」を追加
for i in range(start_num + 1, end_num):
    query_list.append(" UNION ALL SELECT {num}".format(num=i))
query_list.append(")")
query = "\n".join(query_list)
# ------------------------------

# SELECT文の実行
cursor = conn.cursor()
cursor.execute(query)

# SELECT結果の取得
for get_row in cursor:
    print(get_row[0])

# 接続終了
conn.close()

結果、以下のようなSELECT文が生成され、連番を持ったテーブルが取得できます。

生成されたSQL
(SELECT 0 AS s
 UNION ALL SELECT 1
 UNION ALL SELECT 2
 UNION ALL SELECT 3
 UNION ALL SELECT 4
 UNION ALL SELECT 5
 UNION ALL SELECT 6
 UNION ALL SELECT 7
 UNION ALL SELECT 8
 UNION ALL SELECT 9
)

連番の参照は、次のように連番箇所を副問合せにすることで可能です。

mysql_test2.py
# coding:utf-8
import mysql.connector

# 接続情報辞書
config = {
    'user': 'root',
    'password': 'password',
    'host': '123.45.67.890',
    'database': 'test'
}

# 接続
conn = mysql.connector.connect(**config)

# ------------------------------
# テストデータSELECT文生成
# ------------------------------
start_num = 0
end_num = 10
query_list = ["SELECT serial_num.s, md5(serial_num.s) FROM", "(SELECT {start_num} AS s".format(start_num=start_num)]
# 「UNION ALL SELECT {num}」を追加
for i in range(start_num + 1, end_num):
    query_list.append(" UNION ALL SELECT {num}".format(num=i))
query_list.append(") AS serial_num")
query = "\n".join(query_list)
# ------------------------------

# SELECT文の実行
cursor = conn.cursor()
cursor.execute(query)

# SELECT結果の取得
for get_row in cursor:
    print(get_row[0], get_row[1])

# 接続終了
conn.close()
実行結果
0 cfcd208495d565ef66e7dff9f98764da
1 c4ca4238a0b923820dcc509a6f75849b
2 c81e728d9d4c2f636f067f89cc14862c
3 eccbc87e4b5ce2fe28308fd9f2a7baf3
4 a87ff679a2f3e71d9181a67b7542122c
5 e4da3b7fbbce2345d7772b0674a318d5
6 1679091c5a880faf6fb5e6087eb1b2dc
7 8f14e45fceea167a5a36dedd4bea2543
8 c9f0f895fb98ab9159f51fd0297e236d
9 45c48cce2e2d7fbdea1afc51c7c6ad26
生成されたSQL
SELECT serial_num.s, md5(serial_num.s) FROM
(SELECT 0 AS s
 UNION ALL SELECT 1
 UNION ALL SELECT 2
 UNION ALL SELECT 3
 UNION ALL SELECT 4
 UNION ALL SELECT 5
 UNION ALL SELECT 6
 UNION ALL SELECT 7
 UNION ALL SELECT 8
 UNION ALL SELECT 9
) AS serial_num

テストデータ生成用のプロシージャを作成する

プログラムを使わずとも、プロシージャを使えばMySQLだけで巨大なテーブルを生成可能です。
以下は、引数に渡した数字行のgenerate_series_tblテーブルを生成するプロシージャです。
先頭でDROP TABLEをしているため、実行するたびに新しくテーブルが生成されます。

テストデータ生成用プロシージャ
DELIMITER //
CREATE PROCEDURE generate_series (IN max INT)
BEGIN
  DECLARE i INT;
  SET i = 0;
  DROP TABLE IF EXISTS generate_series_tbl;
  CREATE TABLE generate_series_tbl (num INT);
  WHILE i < max DO
    INSERT INTO generate_series_tbl VALUES (i);
    SET i = i + 1;
  END WHILE;
END
//
DELIMITER ;
実行例
CALL generate_series(100);

Query OK, 1 row affected (1.61 sec)


SELECT count(*) FROM generate_series_tbl;

+----------+
| count(*) |
+----------+
|      100 |
+----------+


SELECT * FROM generate_series_tbl;
+------+
| num  |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
…(中略)…
|   97 |
|   98 |
|   99 |
+------+

上記の通り、0～99までの連番を持つテーブルが生成されました。
当然、実データをINSERTしているため、テーブルが巨大になるほど生成に時間がかかります。
またその巨大テーブルを放置することとなるため、一時テーブルとして生成するのもよいかもしれません。

参考リンク

連番生成について

  • MySQLで連番の仮想表を作る
    https://blog.toshimaru.net/mysql-virtual-table/
    通常の連番生成や、値を倍加させる方法など基本から応用まで丁寧にまとめてあります。
    本記事作成において、こちらのサイトを大変参考にさせていただきました。

  • 裏MySQLクエリー入門(15) 応用編3 MySQLで連番の仮想表を作成
    https://it7c.hatenadiary.org/entry/20100713/1278950305
    PHPによる連番生成方法が紹介されています。
    プログラムによる連番生成の参考にさせていただきました。

mysql-connector-pythonについて

プロシージャについて

7
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?