insertによってレコードを追加しようとした際、プライマリーキーの重複などでレコードが追加できない場合はスキップして、追加可能な場合はDBにレコードを追加する方法を２つご紹介します。
- insert ignoreを使用する方法
- where not existsを使用する方法
条件
- MariaDB 10.0.36
対象テーブル
ここでは以下のようなテーブルを使用します。
CREATE TABLE IF NOT EXISTS attendance(
person_id int unsigned NOT NULL default 0,
attend_datetime datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
memo varchar(30) NOT NULL DEFAULT '',
regist_datetime datetime NOT NULL default current_timestamp(),
update_datetime datetime NOT NULL default current_timestamp(),
PRIMARY KEY(person_id, attend_datetime)
);
「person_id」と「attend_datetime」を複合プライマリーキーとしています。
MariaDB [testData]> SHOW FULL COLUMNS FROM attendance; +-----------------+------------------+-----------------+------+-----+---------------------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------------+------------------+-----------------+------+-----+---------------------+-------+---------------------------------+---------+ | person_id | int(10) unsigned | NULL | NO | PRI | 0 | | select,insert,update,references | | | attend_datetime | datetime | NULL | NO | PRI | 2001-01-01 00:00:00 | | select,insert,update,references | | | memo | varchar(30) | utf8_general_ci | NO | | | | select,insert,update,references | | | regist_datetime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | | | update_datetime | datetime | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | | +-----------------+------------------+-----------------+------+-----+---------------------+-------+---------------------------------+---------+ 5 rows in set (0.00 sec)
レコード状況
以下のように3レコードが存在する場合を考えます。
MariaDB [testData]> select * from attendance; +-----------+---------------------+------+---------------------+---------------------+ | person_id | attend_datetime | memo | regist_datetime | update_datetime | +-----------+---------------------+------+---------------------+---------------------+ | 1 | 2018-10-10 09:00:00 | | 2018-10-12 10:40:38 | 2018-10-12 10:40:38 | | 1 | 2018-10-10 10:00:00 | | 2018-10-12 10:41:05 | 2018-10-12 10:41:05 | | 1 | 2018-10-10 11:00:00 | | 2018-10-12 10:41:12 | 2018-10-12 10:41:12 | +-----------+---------------------+------+---------------------+---------------------+
レコードの追加
以下の２レコードを追加します。
+-----------+---------------------+ | person_id | attend_datetime | +-----------+---------------------+ | 1 | 2018-10-10 11:00:00 | | 1 | 2018-10-10 12:00:00 | +-----------+---------------------+
普通にinsertすると「1」と「2018-10-10 11:00:00」の組合せはすでに存在するため重複エラーになります。
MariaDB [testData]> insert into attendance (person_id, attend_datetime) values (1, "2018-10-10 11:00:00"); ERROR 1062 (23000): Duplicate entry '1-2018-10-10 11:00:00' for key 'PRIMARY'
insert ignoreを使用する
insertに「ignore」をつけることで重複エラーの出力を回避することが出来ます。
以下のように「Query OK」でレコードが追加されていないことがわかります。
MariaDB [testData]> insert ignore into attendance (person_id, attend_datetime) values (1, "2018-10-10 11:00:00"); Query OK, 0 rows affected, 1 warning (0.00 sec) MariaDB [testData]> select * from attendance; +-----------+---------------------+------+---------------------+---------------------+ | person_id | attend_datetime | memo | regist_datetime | update_datetime | +-----------+---------------------+------+---------------------+---------------------+ | 1 | 2018-10-10 09:00:00 | | 2018-10-12 10:40:38 | 2018-10-12 10:40:38 | | 1 | 2018-10-10 10:00:00 | | 2018-10-12 10:41:05 | 2018-10-12 10:41:05 | | 1 | 2018-10-10 11:00:00 | | 2018-10-12 10:41:12 | 2018-10-12 10:41:12 | +-----------+---------------------+------+---------------------+---------------------+ 3 rows in set (0.00 sec)
重複レコードが存在しない場合、通常通りレコードが追加されます。
MariaDB [testData]> insert ignore into attendance (person_id, attend_datetime) values (1, "2018-10-10 12:00:00"); Query OK, 1 row affected (0.02 sec) MariaDB [testData]> select * from attendance; +-----------+---------------------+------+---------------------+---------------------+ | person_id | attend_datetime | memo | regist_datetime | update_datetime | +-----------+---------------------+------+---------------------+---------------------+ | 1 | 2018-10-10 09:00:00 | | 2018-10-12 10:40:38 | 2018-10-12 10:40:38 | | 1 | 2018-10-10 10:00:00 | | 2018-10-12 10:41:05 | 2018-10-12 10:41:05 | | 1 | 2018-10-10 11:00:00 | | 2018-10-12 10:41:12 | 2018-10-12 10:41:12 | | 1 | 2018-10-10 12:00:00 | | 2018-10-12 10:51:43 | 2018-10-12 10:51:43 | +-----------+---------------------+------+---------------------+---------------------+ 4 rows in set (0.01 sec)
where not existsを使用する
where句に「not exists」で対象レコードが存在することを確認してからinsertするかどうかを決めます。
- 「not exists」内で指定したselect文のレコードが存在しない場合、insertする。
- 「not exists」内で指定したselect文のレコードが存在する場合、insertしない。
以下は重複レコードが存在する場合です。
対象レコードが追加されていないことがわかります。
insert into attendance (person_id, attend_datetime)
select * from (select 1 as person_id, "2018-10-10 11:00:00" as attend_datetime) as tmp
where not exists (select * from attendance where person_id = 1 and attend_datetime = "2018-10-10 11:00:00");
MariaDB [testData]> insert into attendance (person_id, attend_datetime)
-> select * from (select 1 as person_id, "2018-10-10 11:00:00" as attend_datetime) as tmp
-> where not exists (select * from attendance where person_id = 1 and attend_datetime = "2018-10-10 11:00:00");
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [testData]> select * from attendance;
+-----------+---------------------+------+---------------------+---------------------+
| person_id | attend_datetime | memo | regist_datetime | update_datetime |
+-----------+---------------------+------+---------------------+---------------------+
| 1 | 2018-10-10 09:00:00 | | 2018-10-12 10:40:38 | 2018-10-12 10:40:38 |
| 1 | 2018-10-10 10:00:00 | | 2018-10-12 10:41:05 | 2018-10-12 10:41:05 |
| 1 | 2018-10-10 11:00:00 | | 2018-10-12 10:41:12 | 2018-10-12 10:41:12 |
+-----------+---------------------+------+---------------------+---------------------+
3 rows in set (0.00 sec)
以下は重複レコードが存在しない場合です。
対象レコードが追加されています。
insert into attendance (person_id, attend_datetime)
select * from (select 1 as person_id, "2018-10-10 12:00:00" as attend_datetime) as tmp
where not exists (select * from attendance where person_id = 1 and attend_datetime = "2018-10-10 12:00:00");
MariaDB [testData]> insert into attendance (person_id, attend_datetime)
-> select * from (select 1 as person_id, "2018-10-10 12:00:00" as attend_datetime) as tmp
-> where not exists (select * from attendance where person_id = 1 and attend_datetime = "2018-10-10 12:00:00");
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [testData]> select * from attendance;
+-----------+---------------------+------+---------------------+---------------------+
| person_id | attend_datetime | memo | regist_datetime | update_datetime |
+-----------+---------------------+------+---------------------+---------------------+
| 1 | 2018-10-10 09:00:00 | | 2018-10-12 10:40:38 | 2018-10-12 10:40:38 |
| 1 | 2018-10-10 10:00:00 | | 2018-10-12 10:41:05 | 2018-10-12 10:41:05 |
| 1 | 2018-10-10 11:00:00 | | 2018-10-12 10:41:12 | 2018-10-12 10:41:12 |
| 1 | 2018-10-10 12:00:00 | | 2018-10-12 10:59:45 | 2018-10-12 10:59:45 |
+-----------+---------------------+------+---------------------+---------------------+
4 rows in set (0.00 sec)
参考
select * from (select 1 as person_id, "2018-10-10 12:00:00" as attend_datetime) as tmp;
上記のSQLは実行すると以下のようになります。
MariaDB [testData]> select * from (select 1 as person_id, "2018-10-10 12:00:00" as attend_datetime) as tmp; +-----------+---------------------+ | person_id | attend_datetime | +-----------+---------------------+ | 1 | 2018-10-10 12:00:00 | +-----------+---------------------+ 1 row in set (0.00 sec)