MySQLのUPSERT文

Question

いつもお世話になっております。

$idと$active_datetime_strがすでに存在していればUPDATE、存在していなければINSERTしたいです。下記コードを実行しても、常にINSERTしてしまいます。ご教示いただけますようよろしくお願いいたします。

CREATE TABLE `schedule` (
  `id` varchar(20) NOT NULL,
  `datetime` datetime NOT NULL,
  `schedule` tinyint(1) NOT NULL
) 

function update_schedule($dbh, $id, $set_schedule){
$active_datetime_str = get_active_datetime_str();
$cnt = count($active_datetime_str);
for ($i=0; $i < $cnt; $i++){
$sql = <<<SQL
INSERT INTO `schedule`
VALUES (:id, :active_datetime_str, :set_schedule)
ON DUPLICATE KEY UPDATE schedule = VALUES(schedule)
SQL;
// PDO::PARAM_STR-->プリペアドステートメント
$stmt = $dbh->prepare($sql);
// SQLに変数を代入
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->bindValue(':active_datetime_str', $active_datetime_str[$i], PDO::PARAM_STR);
$stmt->bindValue(':set_schedule', $set_schedule[$i], PDO::PARAM_STR);
if(!$stmt->execute()){
return FALSE;
}
}
return TRUE;
}

Comments

[papinianus]

yambejpさんが、まず、で書かれているとおりUNIQUE制約がなければそもそも動きません。UNIQUEかPRIMARYキー指定してからお試しください

[DaisukeKusakari]

ありがとうございます。ご指摘のお通り修正し、動作しました。

Answer 1

まず、ちゃんとdatetimeにunique属性をつけてますか？
また$idが$iに付随して変化しませんが大丈夫でしょうか？
datetimeを$active_datetime_str[$i]の重複でチェックするのに
datetime = VALUES(datetime)
をしているのも何をしたいのかわかりません
そしてprepare処理がまったくおかしいです

forで1文ずつSQLを実行するのは非効率的名のでまとめて処理するのを
おすすめします。（何万件も処理する場合は調整が必要）

sample

• テーブル作成とinsert

create table `schedule` (
  `id` varchar(20) NOT NULL,
  `datetime` datetime NOT NULL,
  `schedule` tinyint(1) NOT NULL,
  unique key(`id`,`datetime`)
);

insert into `schedule` values('honda', '2018-09-08 10:00:00', 1);

• テーブル作成とinsert

insert into `schedule` values
('kagawa', '2018-09-08 10:00:00', 2),
('honda', '2018-09-09 10:00:00', 3),
('honda', '2018-09-08 10:00:00', 4)
on duplicate key update schedule=values(schedule)

• 2のデータはidが違うので挿入
• 3のデータはdatetimeが違うので挿入
• 4のデータはidもdatetimeもかぶるので指定した4で更新

Comments

[yambejp]

もし具体的なソースの提示を希望されるなら
テーブルの構成と、updateの場合、insertの場合のそれぞれの
サンプルを提示してください

[DaisukeKusakari]

申し訳ございません。ご指摘の点が多く、一つ一つがわかりません。調査中です。

INSERT INTO `schedule` (`id`, `datetime`, `schedule`) VALUES ('honda', '2018-09-08 10:00:00', '1');
UPDATE `schedule` SET `schedule` = '2' WHERE `schedule`.`datetime` = '2018-09-08 10:00:00';

[DaisukeKusakari]

テーブルの構成（クリエイト文）は質問文に追加しております。

[yambejp]

ああ、なんとなくわかりました
idとdatatimeの組み合わせで複合uniqueにするのですね
そうなると
UPDATE `schedule` SET `schedule` = '2' WHERE `id`='honda' and `datetime` = '2018-09-08 10:00:00';
です。

これを1文でやるなら
INSERT INTO `schedule` (`id`, `datetime`, `schedule`) VALUES ('honda', '2018-09-08 10:00:00', '1') ON DUPLICATE KEY UPDATE
`schedule` = '2'
とします
（命題でなぜscheduleの値が1から2に変わっているか仕様がわかりませんが）

[DaisukeKusakari]

function update_schedule($dbh, $id, $set_schedule){
$active_datetime_str = get_active_datetime_str();
$cnt = count($active_datetime_str);
for ($i=0; $i < $cnt; $i++){
$sql = <<<SQL
INSERT INTO `schedule`
VALUES (:id, :active_datetime_str, :set_schedule)
ON DUPLICATE KEY UPDATE schedule = VALUES(schedule)
SQL;
// PDO::PARAM_STR-->プリペアドステートメント
$stmt = $dbh->prepare($sql);
// SQLに変数を代入
$stmt->bindValue(':id', $id, PDO::PARAM_STR);
$stmt->bindValue(':active_datetime_str', $active_datetime_str[$i], PDO::PARAM_STR);
$stmt->bindValue(':set_schedule', $set_schedule[$i], PDO::PARAM_STR);
if(!$stmt->execute()){
return FALSE;
}
}
return TRUE;
}

おかげさまでここまででき、UPSERTされていることを確認できました。

[DaisukeKusakari]

>>forで1文ずつSQLを実行するのは非効率的名のでまとめて処理するのを
おすすめします。（何万件も処理する場合は調整が必要）
というのはバッチを使うということでしょうか。
御厄介ついでにご教示いただけると幸いです。