読者です 読者をやめる 読者になる 読者になる

何一つ誇るものがない

人生のアンチパターン

INSERT ON DUPLICATE KEY UPDATEの構文を使ってランキングテーブルを操作する

みなさんいかがお過しでしょうか。
MySQL Casual Advent Calendar 2013の22日目です。

今回は他の方とくらべて十分CasualなInsertについて紹介致します。



紹介するのはINSERT ... ON DUPLICATE KEY UPDATEの構文を使ってランキングのテーブルを操作を紹介します。


INSERT ... ON DUPLICATE KEY UPDATEとは、簡単に説明しますと
insert時にプライマリのレコードが存在しなかったらinsertし、
レコードが存在したらupdateする
詳しい説明はINSERT ... ON DUPLICATE KEY UPDATE 構文にあります。



簡単にランキングを保存するテーブルを用意します。

CREATE TABLE `ranking` (
   `rank_date` DATE NOT NULL,
   `rank` int unsigned NOT NULL,
   `count` int unsigned,
   `name` varchar(255),
   PRIMARY KEY (`rank_date`, `rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;


次にデータを入れてみます。

insert into ranking (`rank_date`, `rank`, `count`, `name`) value
 ('2013-09-21', 1, 30, 'yusukebe'),
 ('2013-09-21', 2, 20, 'myfinder'),
 ('2013-09-21', 3, 10, 'kazeburo');

データを確認します。

select * from ranking order by rank;
+------------+------+-------+----------+
| rank_date  | rank | count | name     |
+------------+------+-------+----------+
| 2013-09-21 |    1 |    30 | yusukebe |
| 2013-09-21 |    2 |    20 | myfinder |
| 2013-09-21 |    3 |    10 | kazeburo |
+------------+------+-------+----------+
3 rows in set (0.01 sec)

次にランキングに変更があった場合、再集計などを行ってデータの登録を変更したいですが、

バッチシステムなどの都合でupdate文が実行できない時もあります。

そのような場合はINSERT ... ON DUPLICATE KEY UPDATEを使って登録、更新を行います。
以下が変更したいデータとなります。

insert into ranking (`rank_date`, `rank`, `count`, `name`) values
('2013-09-21', 1, 40, 'myfinder'),
('2013-09-21', 2, 30, 'kazeburo'),
('2013-09-21', 3, 20, 'goccy54'),
('2013-09-21', 999, 0, 'yusukebe')
ON DUPLICATE KEY UPDATE count=VALUES(`count`), name=VALUES(`name`);

ON DUPLICATE KEY UPDATE を指定することで、プライマリキーのレコードが存在する場合、
update文を実行させます。
以降の構文は、カラム=値となり、VALUES(column_name)を使うことでinsert時に
宣言した値を利用できます。

実行結果が以下になります。

select * from ranking order by rank;
+------------+------+-------+----------+
| rank_date  | rank | count | name     |
+------------+------+-------+----------+
| 2013-09-21 |    1 |    40 | myfinder |
| 2013-09-21 |    2 |    30 | kazeburo |
| 2013-09-21 |    3 |    20 | goccy54  |
| 2013-09-21 |  999 |     0 | yusukebe |
+------------+------+-------+----------+
4 rows in set (0.00 sec)

明日

23日目 @kenjiskywalkerさんです。