Author: Tamesuu Source: https://qiita.com/tamesuu/items/d9475a35709ec0d49763
When are you going to limit Unique to MySQL? “When necessary when,” you might say. So when is it necessary?
In this article, author Tamesuu will talk about cases that need a unique limit
What is the unique limit?
Ensuring that the data of a column in a table is unique
URL ref: https://dev.mysql.com/doc/refman/5.6/ja/constraint-primary-key.html
When thinking about the case of Web application When you want to ensure data specificity in Web application (Rails)
On the web application (server) side, check before saving data. I will set the Unique limit on the My SQL side
So the answer to the question “When do I need to set a Unique limit in MySQL?” at the top of the post will be
- When testing before saving data on the Web app side (server side)
- Attach a unique limit to MySQL
Try to see which cases to attach:
Test environment
|
1 2 3 4 5 6 |
$ rails --version Rails 5.2.3 $ mysql --version mysql Ver 14.14 Distrib 5.6.43, for osx10.13 (x86_64) using EditLine wrapper |
Test Preparation:
MySQL
|
1 2 3 4 5 6 7 8 9 10 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
On the Web application (Server) side 、 both uniq_test1 and uniq_test2 test before saving data
This time, the model will be created in the rails console
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ 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"> |
The process is roughly as follows
- Start transaction of MySQL
- Check the data for specificity
- Finish the transaction of MySQL (Here will create data)
The problem is: processing is done almost at the same time
Without limiting unique to MySQL (Bad pattern)
Console1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$ 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"> |
Console2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$ 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"> |
The Web app (Server) will leak data ！！！
Attach a unique key limit to MySQL (Good pattern) Console1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$ 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"> |
Console2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$ 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')) |
On the web app (server) side, there is a problem, when saved in MySQL, the MySQL side will detect and give an error
Fasteners: To ensure data specificity, a unique key must be attached to MySQL. The second effect is coping in case the web app (server) is missing.