RDBは、使いやすい!速い!なのですが、カラムの拡張には、どの製品であろうと弱い。。24時間365日稼働しているサービスでカラム追加なんてなんてしようものなら、もうとんでもなくめんどくさいことになる。。
というわけで、カラム拡張が容易なテーブル構成について考えてみました。
例えばこんなテーブルがあったとします。
CREATE TABLE `m_users` (
`id` bigint(20) unsigned NOT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name` varchar(50) NOT NULL DEFAULT '',
`login_id` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
なんのことはない、login_idとnameがあるテーブルです。実際こんな質素なユーザーテーブルはないとは思いますが、シナリオ的には、このテーブルで、数年運用して、1億レコードがすでに格納されたとことで改修が
はいったなんてことにしましょうか?
改修内容
メールアドレス、性別、プロフィールのカラムを新規で追加したい・・
なんていう要件にしましょうか?
この程度のテーブルであれば、カラムを追加、もしくは別テーブルを生成することで対応できるのですが、ここでは、既存のテーブル(m_users)の定義に一切変更をあたえず、上記の要件を満たすように考えてみたいと思います
カラム名と値だけの構成のテーブルを新設する
あ、そうだ、keyとvalueだけもつテーブルを作ってみようということで、そんなテーブルを作ってみました。テーブルの名前は、既存のm_usersをひっかけて、m_user_propsという名前にしてみました
CREATE TABLE `m_user_props` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) unsigned NOT NULL,
`mkey` varchar(20) NOT NULL COMMENT 'column_name',
`mval` varchar(150) NOT NULL COMMENT 'value',
PRIMARY KEY (`id`),
UNIQUE KEY `ix01_user_props` (`mkey`,`mval`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;
どう使うか?
直接上記テーブルにINSERTしてもいいのですが、なんかちょっとかっこよくしたかったのでこんなストアドプロシージャを作ってみました
DROP PROCEDURE IF EXISTS sp_put_m_user_props ;
DELIMITER $$
CREATE PROCEDURE `sp_put_m_user_props`(
IN _user_id bigint(20) unsigned,
IN _mkey varchar(20) ,
IN _mval varchar(150) ,
OUT _result int
)
BEGIN
SET _result = 0;
BEGIN
DECLARE _not_found TINYINT UNSIGNED DEFAULT 0;
DECLARE _studio_prop_id BIGINT ;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET _result = -99;
END;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;
BEGIN
SELECT id INTO _studio_prop_id FROM m_user_props
WHERE
user_id = _user_id AND
mkey = _mkey
;
IF _not_found = 1 THEN
INSERT INTO
m_user_props
(
user_id,
mkey,
mval
) VALUES (
_user_id,
_mkey,
_mval
) ;
ELSE
UPDATE
m_user_props
SET
mval = _mval
WHERE
user_id = _user_id AND
mkey = _mkey
;
END IF ;
END ;
END ;
END $$
DELIMITER ;
極力エラーは出したくなかった為、キーがあれば、更新する。キーが無かればインサートするといった構成にしてみました。
試してみる
では実際に試してみます。
m_users
+----+---------------------+---------------------+------+----------+
| id | created_at | updated_at | name | login_id |
+----+---------------------+---------------------+------+----------+
| 1 | 2016-03-02 17:40:45 | 2016-03-02 17:40:45 | hit | hitabc |
+----+---------------------+---------------------+------+----------+
m_user_props
今回依頼のあった、メールアドレス、性別、プロフィールを上記のストアドプロシージャを利用して追加してみます。
SET @user_id=1 ;
SET @mkey='mail' ;
SET @mval='hit@abcdefg.com' ;
CALL sp_put_m_user_props (@user_id,@mkey,@mval,@result) ;
SET @mkey='sex' ;
SET @mval='男' ;
CALL sp_put_m_user_props (@user_id,@mkey,@mval,@result) ;
SET @mkey='profile' ;
SET @mval='SQLの検証中です' ;
CALL sp_put_m_user_props (@user_id,@mkey,@mval,@result) ;
MySQL > SELECT * FROM m_user_props ;
+----+---------+---------+-----------------------+
| id | user_id | mkey | mval |
+----+---------+---------+-----------------------+
| 1 | 1 | mail | hit@abcdefg.com |
| 2 | 1 | sex | 男 |
| 3 | 1 | profile | SQLの検証中です |
+----+---------+---------+-----------------------+
感の良い皆様ならもうおきづきかもしませんが、key/value の構成でデータを持つことに成功しました
投稿した値を取得する
あとはもうSQLの話しなのでおわかりかと思いますが、こんな感じで今回追加したカラムが表示できるかと思います
- SQL、その1
SELECT
u.id AS user_id,
u.name AS user_name,
p.mval AS mail,
p2.mval AS sex,
p3.mval AS profile
FROM
m_users u
LEFT JOIN
m_user_props p
ON
u.id = p.user_id AND
p.mkey = 'mail'
LEFT JOIN
m_user_props p2
ON
u.id = p2.user_id AND
p2.mkey = 'sex'
LEFT JOIN
m_user_props p3
ON
u.id = p3.user_id AND
p3.mkey = 'profile'
;
+---------+-----------+-----------------+------+-----------------------+
| user_id | user_name | mail | sex | profile |
+---------+-----------+-----------------+------+-----------------------+
| 1 | hit | hit@abcdefg.com | 男 | SQLの検証中です |
+---------+-----------+-----------------+------+-----------------------+
なんかかっこ悪いですね
- SQL、その2
SELECT
X.user_id,
X.user_name,
MAX(X.mail) AS mail ,
MAX(X.sex) AS sex ,
MAX(X.profile) AS profile
FROM
(
SELECT
u.id AS user_id,
u.name AS user_name,
CASE p.mkey WHEN 'mail' THEN p.mval END AS mail,
CASE p.mkey WHEN 'sex' THEN p.mval END AS sex,
CASE p.mkey WHEN 'profile' THEN p.mval END AS profile
FROM
m_users u
LEFT JOIN
m_user_props p
ON
u.id = p.user_id
) X
GROUP BY
X.user_id
;
+---------+-----------+-----------------+------+-----------------------+
| user_id | user_name | mail | sex | profile |
+---------+-----------+-----------------+------+-----------------------+
| 1 | hit | hit@abcdefg.com | 男 | SQLの検証中です |
+---------+-----------+-----------------+------+-----------------------+
JOINの数は減ったけど、なんか複雑だなあ。。
ということで。。。
値を取得するストアドファンクションを作成する
値を取得するだけのストアドファンクションを作成してみました
DROP FUNCTION IF EXISTS sf_get_props ;
DELIMITER $$
DROP FUNCTION IF EXISTS sf_get_props ;
DELIMITER $$
CREATE FUNCTION `sf_get_props`(
_user_id bigint(20),
_mkey varchar(20)
) RETURNS varchar(150)
BEGIN
DECLARE _cur_val varchar(150) ;
DECLARE _not_found TINYINT UNSIGNED DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _not_found = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET _cur_val = '-9999-';
return _cur_val ;
END;
SELECT
mval INTO _cur_val
FROM
m_user_props
WHERE
user_id = _user_id AND
mkey = _mkey ;
RETURN _cur_val;
END $$
DELIMITER ;
さっそく使ってみる
こんな感じのSQLになります
SELECT
m.id AS user_id,
m.name AS user_name,
sf_get_props(m.id,'mail') AS mail ,
sf_get_props(m.id,'sex') AS sex,
sf_get_props(m.id,'profile') AS profile
FROM
m_users m
;
+---------+-----------+-----------------+------+-----------------------+
| user_id | user_name | mail | sex | profile |
+---------+-----------+-----------------+------+-----------------------+
| 1 | hit | hit@abcdefg.com | 男 | SQLの検証中です |
+---------+-----------+-----------------+------+-----------------------+
1 row in set (0.01 sec)
SQLもシンプルでかっこ良くないですか?(中身はゴリゴリですが。。