みなさんは、どんなときに MySQL で Unique 制約をつけるでしょうか。
「必要ならつける！」が答えなのですが、どんな時に必要でしょうか。
今回は、「MySQL で Unique 制約をつける場面はどんなときか」というのを考えてみたいと思います。
Unique 制約とは
あるテーブル内のカラムのデータが1つであることを保証します。(一意性)
Web アプリケーションの場合で考えてみる
Web アプリケーション (Rails) でデータの一意性を保証したい場合
- Web アプリケーション側 (サーバー側) で、データ保存前に検証する
- MySQL 側に Unique 制約をつける
で実現すると思います。
さて最初の問いである「MySQL で Unique 制約をつける場面はどんなときか」の答えとして、
1. Web アプリケーション側 (サーバー側) で、データ保存前に検証する
さえ満たしていれば、
2. MySQL 側に Unique 制約をつける
は不要でしょうか。いや必要です。
必要になるケースについて見ていきましょう。
確認環境
$ rails --version
Rails 5.2.3
$ mysql --version
mysql Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using EditLine wrapper
検証
準備
MySQL
CREATE TABLE `hoges` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`uniq_test1` int(11) DEFAULT NULL,
`uniq_test2` int(11) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_test2` (`uniq_test2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
INSERT INTO hoges VALUES (null, 11111, 22222, NOW(), NOW());
app/models/hoge.rb
class Hoge < ApplicationRecord
validates :uniq_test1, uniqueness: true
validates :uniq_test2, uniqueness: true
validate :hoge
def hoge
sleep(5)
end
end
Web アプリケーション側 (サーバー側)では、uniq_test1、uniq_test2 ともにデータ保存前に検証します。
今回は、rails console でモデルを create します。
$ rails c
Running via Spring preloader in process 12528
Loading development environment (Rails 5.2.3)
irb(main):002:0> ActiveRecord::Base.transaction do
irb(main):003:1* Hoge.create(uniq_test1: 5, uniq_test2: nil)
irb(main):004:1> end
(1.9ms) BEGIN
Hoge Exists (6.3ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1
Hoge Exists (9.9ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1
Hoge Create (4.0ms) INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:42:39', '2020-03-09 16:42:39')
(6.9ms) COMMIT
=> #<Hoge id: 6, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:42:39", updated_at: "2020-03-09 16:42:39">
処理のおおまかな流れはこんな感じです。
- MySQL の transaction 開始
- データの一意性検証
- MySQL の transaction 終了 (ここでデータが作られる)
問題になるのはほぼ同時に処理が実行されたときです。
MySQL に Unique キー制約なし (悪いパターン)
コンソール1
$ rails c
Running via Spring preloader in process 12742
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: 5, uniq_test2: nil)
irb(main):003:1> end
(2.2ms) SET NAMES utf8mb4, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
(1.1ms) BEGIN
Hoge Exists (3.2ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1
Hoge Exists (1.4ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1
Hoge Create (4.3ms) INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:55:12', '2020-03-09 16:55:12')
(7.9ms) COMMIT
=> #<Hoge id: 7, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:55:12", updated_at: "2020-03-09 16:55:12">
コンソール2
$ rails c
Running via Spring preloader in process 12757
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: 5, uniq_test2: nil)
irb(main):003:1> end
(1.8ms) SET NAMES utf8mb4, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
(1.5ms) BEGIN
Hoge Exists (2.0ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` = 5 LIMIT 1
Hoge Exists (5.1ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` IS NULL LIMIT 1
Hoge Create (5.1ms) INSERT INTO `hoges` (`uniq_test1`, `created_at`, `updated_at`) VALUES (5, '2020-03-09 16:55:13', '2020-03-09 16:55:13')
(49.3ms) COMMIT
=> #<Hoge id: 8, uniq_test1: 5, uniq_test2: nil, created_at: "2020-03-09 16:55:13", updated_at: "2020-03-09 16:55:13">
Web アプリケーション側 (サーバー側) をすり抜けてしまいました！！！
MySQL に Unique キー制約あり (良いパターン)
コンソール1
$ rails c
Running via Spring preloader in process 12742
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: nil, uniq_test2: 77777)
irb(main):003:1> end
(3.7ms) BEGIN
Hoge Exists (1.4ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` IS NULL LIMIT 1
Hoge Exists (2.8ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` = 77777 LIMIT 1
Hoge Create (3.5ms) INSERT INTO `hoges` (`uniq_test2`, `created_at`, `updated_at`) VALUES (77777, '2020-03-09 16:57:01', '2020-03-09 16:57:01')
(18.2ms) COMMIT
=> #<Hoge id: 9, uniq_test1: nil, uniq_test2: 77777, created_at: "2020-03-09 16:57:01", updated_at: "2020-03-09 16:57:01">
コンソール2
$ rails c
Running via Spring preloader in process 12757
Loading development environment (Rails 5.2.3)
irb(main):001:0> ActiveRecord::Base.transaction do
irb(main):002:1* Hoge.create(uniq_test1: nil, uniq_test2: 77777)
irb(main):003:1> end
(7.9ms) BEGIN
Hoge Exists (3.7ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test1` IS NULL LIMIT 1
Hoge Exists (1.8ms) SELECT 1 AS one FROM `hoges` WHERE `hoges`.`uniq_test2` = 77777 LIMIT 1
Hoge Create (5.4ms) INSERT INTO `hoges` (`uniq_test2`, `created_at`, `updated_at`) VALUES (77777, '2020-03-09 16:57:04', '2020-03-09 16:57:04')
(19.1ms) ROLLBACK
Traceback (most recent call last):
2: from (irb):4
1: from (irb):5:in `block in irb_binding'
ActiveRecord::RecordNotUnique (Mysql2::Error: Duplicate entry '77777' for key 'uniq_test2': INSERT INTO `hoges` (`uniq_test2`, `created_at`, `updated_at`) VALUES (77777, '2020-03-09 16:57:04', '2020-03-09 16:57:04'))
Web アプリケーション側 (サーバー側) をすり抜けたとしても、MySQL に保存したときに MySQL 側のエラーで弾くことができます。
まとめ
データの一意性を保証する必要があるなら、MySQL に Unique キーをつけましょう！
副次的なメリットとして、Web アプリケーション側 (サーバー側) の実装漏れがあった場合も対処できます。