6
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

データの一意性を保証するとき、データベース (MySQL) で Unique 制約をつけた方が良さそうな件

みなさんは、どんなときに MySQL で Unique 制約をつけるでしょうか。

「必要ならつける!」が答えなのですが、どんな時に必要でしょうか。

今回は、「MySQL で Unique 制約をつける場面はどんなときか」というのを考えてみたいと思います。

Unique 制約とは

あるテーブル内のカラムのデータが1つであることを保証します。(一意性)

Web アプリケーションの場合で考えてみる

Web アプリケーション (Rails) でデータの一意性を保証したい場合

  1. Web アプリケーション側 (サーバー側) で、データ保存前に検証する
  2. 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">

処理のおおまかな流れはこんな感じです。

  1. MySQL の transaction 開始
  2. データの一意性検証
  3. 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 アプリケーション側 (サーバー側) の実装漏れがあった場合も対処できます。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
6
Help us understand the problem. What are the problem?