MySQLでINSERTする際, 特定のカラムについて重複を許さないコードの書き方

Question

やりたいこと

例えば以下のテーブルについて、

社員番号	名前	出身地
0001	佐藤太郎	北海道
0002	鈴木次郎	青森
0003	高橋三郎	岩手
0004	田中四郎	秋田
0005	伊藤五郎	宮城
0006	渡辺六郎	山形
0007	山本七郎	福島
0008	中村八郎	茨城

以下のような新情報について区分けしてINSERTしたい

INSERTする情報

社員番号	名前	出身地
0009	小林九郎	栃木

INSERTしない情報

社員番号	名前	出身地
0009	加藤十郎	北海道

すなわち同一出身地である人をデータベースにINSERTしないコードを書きたい。

ここでのPrimary Keyは社員番号

現状のコード

うまくいっていないコード

INSERT INTO items VALUES (社員番号, 名前, 出身地)
WHERE 出身地 NOT EXISTS (SELECT 出身地 FROM items)

INSERT INTO items VALUES (社員番号, 名前, 出身地)
WHERE 出身地 NOT IN (SELECT 出身地 FROM items)

このコードを改変するにはどうしたら良いでしょうか。
よろしくお願いいたします。

Comments

[m.ts10806]

コードではなくテーブル定義での対応は考えてませんか？
それにinsertに直接whereはつけられないのでは。

[sazi]

既に登録されていないものを登録しないようにするには、ユニーク制約などでいいのですが、
0009 小林九郎 栃木
0009 加藤十郎 北海道
上記のデータを同時に登録する場合という事なら、話は違っていて、どのような条件で優先するものを決めるのかのルールが必要になってきます。

Answer 1

基本的には出身地カラムにユニーク制約を付与したあと、INSERT IGNOREを使うのが良いとは思います。

ALTER TABLE items ADD UNIQUE(出身地);
INSERT IGNORE INTO items (社員番号, 名前, 出身地) VALUES ('0009', '小林九郎', '栃木');
INSERT IGNORE INTO items (社員番号, 名前, 出身地) VALUES ('0009', '加藤十郎', '北海道');

ただスキーマ定義の更新が難しい場合、以下のようなクエリでやりたいことは実現できると思います。

INSERT INTO items
SELECT *
FROM (SELECT '0009', '小林九郎', '栃木') A (社員番号, 名前, 出身地)
WHERE NOT EXISTS (SELECT * FROM items I WHERE A.出身地 = I.出身地);

INSERT INTO items
SELECT *
FROM (SELECT '0009', '加藤十郎', '北海道') A (社員番号, 名前, 出身地)
WHERE NOT EXISTS (SELECT * FROM items I WHERE A.出身地 = I.出身地);

Comments

[退会済みユーザー]

一致するのがあればSELECTの結果としてレコードなしが返ってきてINSERTされず、
一致するのがない場合だけSELECTの結果で出てくるのでINSERTされると。
COOLだ。

Answer 2

普通にやればすでに回答があるとおりunique属性ではじく案件ですが
どうしてもSQLだけでやりたいならこれでいけるかも。

INSERT INTO items select * from (
select 9 ,'小林九郎',@a:='栃木' from items having sum(birthplace=@a)=0
) as sub;
INSERT INTO items select * from (
select 9 ,'加藤十郎',@a:='北海道' from items having sum(birthplace=@a)=0
) as sub;

Answer 3

出身地、に対してunique indexのconstraintを設定したら？

alter table `テーブル名` add constraint unique index (`出身地`)

みたいな。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

でも、いきなりINSERT INTO文をぶつけて制約に違反したってエラーを発生させるよりも、
事前に重複しないかをSELECT文実行した方がいいんでない？

INSERT INTO文でできることは、せいぜい
主キーが重複したから無視するor更新クエリーに切り替える、
ってくらいかと。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5 INSERT 構文

一貫性や整合性を検査する役割は予めテーブルに対して制約constraintや
外部参照キーforeign keyなどでやるもので、
都度挿入クエリーや更新クエリーでやることじゃない。
直前に選択クエリーして判断する、ってことになるかと。