2018年10月12日 / 最終更新日時 : 2018年10月12日 確認飛行物体 db

対象レコードが存在しない時だけinsertする方法

insertによってレコードを追加しようとした際、プライマリーキーの重複などでレコードが追加できない場合はスキップして、追加可能な場合はDBにレコードを追加する方法を２つご紹介します。

  1. insert ignoreを使用する方法
  2. 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)

 

カテゴリー
db

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

VPS

前の記事

Ubuntuでのcronの使い方
2018年10月12日
PyCharm

次の記事

PyCharmでパッケージをインストールする方法
2018年10月13日