MySQLで、複数DBに対して動的SQLを実行する

MySQLで、複数DBに対して動的SQLを実行する
Twitter LINEで送る Facebook はてなブログ

Database構造で、アカウント毎にスキーマーを持つ構成を作ることがあります。 その場合、特定のテーブルをアカウントを跨いで集計したい場合がありますがなるべく簡素でSQLで実行したい場合があります。

SQLでなるべく労力なくできないかを試してみてみたので 今後のためにまとめてみました。

検証用のSQLデータ

CREATE DATABASE common;
CREATE DATABASE user1;
CREATE DATABASE user2;
CREATE DATABASE user3;
CREATE DATABASE user4;
CREATE TABLE common.user(
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    deleted INT DEFAULT 0,
    created_at DATETIME DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW()
);

CREATE TABLE user1.data_task(
    id SERIAL PRIMARY KEY,
    status VARCHAR(64),
    data VARCHAR(64),
    created_at DATETIME DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW()
);

CREATE TABLE user2.data_task(
    id SERIAL PRIMARY KEY,
    status VARCHAR(64),
    data VARCHAR(64),
    created_at DATETIME DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW()
);

CREATE TABLE user3.data_task(
    id SERIAL PRIMARY KEY,
    status VARCHAR(64),
    data VARCHAR(64),
    created_at DATETIME DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW()
);

CREATE TABLE user4.data_task(
    id SERIAL PRIMARY KEY,
    status VARCHAR(64),
    data VARCHAR(64),
    created_at DATETIME DEFAULT NOW(),
    updated_at DATETIME DEFAULT NOW()
);
-- 有効なユーザを登録
INSERT INTO common.user(id, name, deleted) VALUES(1, 'user1', 0),(2, 'user2', 0),(3, 'user3', 0),(4, 'user4', 0),(5, 'user5', 5);
-- ユーザ毎のデータを挿入
INSERT INTO user1.data_task(status, data) VALUES('RUN', 'value'),('WAIT', 'value2'),('RUN', 'value3'),('FINISH', 'value4');
INSERT INTO user2.data_task(status, data) VALUES('RUN', 'value'),('WAIT', 'value2'),('RUN', 'value3'),('FINISH', 'value4');
INSERT INTO user3.data_task(status, data) VALUES('RUN', 'value'),('WAIT', 'value2'),('RUN', 'value3'),('FINISH', 'value4');
INSERT INTO user4.data_task(status, data) VALUES('RUN', 'value'),('WAIT', 'value2'),('RUN', 'value3'),('FINISH', 'value4');

DBを跨いでstatusの件数別にまとめる

アカウントを跨いで、現在のステータスをカウントとります。 ベースとなるSQLを作りUNION ALLを利用して纏めてそれを最終的に GROUP BYすることで算出を行なっています。

SET GLOBAL group_concat_max_len = 100000;
SET @users = (SELECT GROUP_CONCAT(q1 SEPARATOR ' UNION ALL ') FROM  (SELECT CONCAT('SELECT status FROM user', id, '.data_task') AS q1 FROM common.user WHERE deleted = 0) AS T1) ;
SET @query = CONCAT("SELECT status, COUNT(*) AS count FROM (", @users, ') AS T2 GROUP BY status');
PREPARE executeQuery FROM @query;
EXECUTE executeQuery;

実行結果

+--------+-------+
| status | count |
+--------+-------+
| RUN    |    16 |
| WAIT   |     8 |
| FINISH |     8 |
+--------+-------+
3 rows in set (0.00 sec)

今回の場合は意味がないですが、GROUP_CONCATをする際に出力文字上限があり

DBを跨いでアカウント毎のstatusの件数別にまとめる

先ほどのSQLを少し拡張してユーザIDを表示してGROUP BYしました。 監視作業とかでアカウント毎の進捗を利用するなどできるかと思います。

SET GLOBAL group_concat_max_len = 100000;
SET @users = (SELECT GROUP_CONCAT(q1 SEPARATOR ' UNION ALL ') FROM  (SELECT CONCAT('SELECT ''user', id, ''' AS user_id, status FROM user', id, '.data_task') AS q1 FROM common.user WHERE deleted = 0) AS T1) ;
SET @query = CONCAT("SELECT user_id, status, COUNT(*) AS count FROM (", @users, ') AS T2 GROUP BY user_id, status ORDER BY user_id, COUNT(*)');
PREPARE executeQuery FROM @query;
EXECUTE executeQuery;

実行結果

mysql> EXECUTE executeQuery;
+---------+--------+-------+
| user_id | status | count |
+---------+--------+-------+
| user1   | WAIT   |     2 |
| user1   | FINISH |     2 |
| user1   | RUN    |     4 |
| user2   | WAIT   |     2 |
| user2   | FINISH |     2 |
| user2   | RUN    |     4 |
| user3   | WAIT   |     2 |
| user3   | FINISH |     2 |
| user3   | RUN    |     4 |
| user4   | WAIT   |     2 |
| user4   | FINISH |     2 |
| user4   | RUN    |     4 |
+---------+--------+-------+
12 rows in set (0.00 sec)

まとめ

利用用途は、限定的かと思いますがSQLのみでできるため 応用性はあるかと思います。