[MySQL] サブクエリで挿入しようとすると、エラー（索引'PRIMARY'で重複しています）がでてしまう。

Question

mst_staff.staffIdとリレーションのあるstaffIdと日付を主キーにしたmst_attendanceにselect文を使って、insert実行しようとしたら掲題のえらーがでました。
主キーが重複しているということだと思いますが、select文を確認しても日付とIdのどちらとも重複している行はありませんでした。
何が原因でどうすればよいのでしょうか。

テーブル作成時のSQL

CREATE TABLE `mst_attendance` (
 `date` date NOT NULL,
 `staffId` int(11) NOT NULL,
 `attend_time` time DEFAULT NULL,
 `leave_time` time DEFAULT NULL,
 `working_time` float DEFAULT NULL,
 `resting_time` float DEFAULT NULL,
 `overwork_time` float DEFAULT NULL,
 PRIMARY KEY (`date`,`staffId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `mst_staff` (
 `staffId` varchar(6) NOT NULL,
 `password` varchar(20) NOT NULL,
 `staffName` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `authorityId` int(11) NOT NULL,
 `delete_flg` int(11) NOT NULL,
 PRIMARY KEY (`staffId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

実行したSQL

INSERT INTO mst_attendance(
    date,
    staffId
)
SELECT CURRENT_DATE , ms.staffId
from mst_staff ms

発生したエラー

#1062 - '2019-05-20-0' は索引 'PRIMARY' で重複しています。

Comments

[coco_bauer]

「select文を確認しても」というのは、select文を実行して得られた結果と、`mst_attendance` テーブルの内容を、比較チェックしたという事ですか？
エラーメッセージに出てくる '2019-05-20-0' という書式は Dateなんですか？　Dateだと'2019-05-20'(yyyy-mm-dd形式)のような表現が普通だと思うのですが。

[maisumakun]

> エラーメッセージに出てくる '2019-05-20-0' という書式は Dateなんですか？

複合インデックスをかけていると、`1つ目の値-2つ目の値`のような表記になりますので、これ自体は問題ないかと思います（2つ目が0なのが気にはなりますが）。

Answer 1

そもそもstaffIdはvarchar型でした。どうやらvarchar型をint型に入れようとしたために0へ自動変換されてそれが衝突したようです。
エラー文の'2019-05-20-0'はdateとstaffIdをつなげたものらしいです

Answer 2

そのINSERTのSQLだと同じ日に複数回実行すると重複しますね。
存在するかどうかの有無をチェックするようにしてはどうでしょうか。

INSERT INTO mst_attendance(
 date, staffId
)
SELECT CURRENT_DATE , ms.staffId
from mst_staff ms
where not exists(
        select 1 from mst_attendance where date=CURRENT_DATE and staffId=ms.staffId
      )
;

Answer 3

mst_staffのstaffIdカラムと当日=CURRENT_DATEの組み合わせがすでに
mst_attendanceに登録ずみのため競合しています

考え方として

• insert ignore intoで新規データのときだけ挿入する
• insert on duplicate key update構文で競合したときに上書きする

のどちらかの処理が必要です。
命題を見る限り投入するのが(date,staffId)の組み合わせだけなので
前者で十分でしょう

Answer 4

主キーが重複しているということだと思いますが、select文を確認しても日付とIdのどちらとも重複している行はありませんでした。

すでにmst_attendanceにある行と、mst_staffから挿入しようとした行が衝突した、ということではないかと思います。