質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.13%

mysqlで更新件数、追加件数、スキップ件数を取得 insert ignore

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 5,147

score 51

mysql でinsert ignoreをした際の更新件数、追加件数、スキップ件数を出したいと考えています。

INSERT IGNORE INTO
table (a, b, c)
VALUES
(1, 12, 100),
(2, 22, 100),
ON DUPLICATE KEY UPDATE
b = values(b)

*aがユニークキー

pythonでcursor.rowcountを利用すると、更新件数が取得できるのですが、
この場合、更新件数と、追加件数の合計が取得されます。
それぞれ分けてカウントしたい場合、どのようにしたら良いでしょうか。
合計件数は事前に確認可能

よろしくお願いします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

+3

以下、Connector/Python ドライバを使用している前提で回答させていただきます。

MySQL のマニュアルの INSERT ... ON DUPLICATE KEY UPDATE 構文のページに、以下のように説明があります。
https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html

ON DUPLICATE KEY UPDATE を使用した場合、行ごとの影響を受けた行の値は、その行が新しい行として挿入された場合は 1、既存の行が更新された場合は 2、既存の行がその現在の値に設定された場合は 0 です。

私の手元の環境(※)で試した限りでは、この動作は MySQLCursor.rowcount プロパティでも同様です。
※ Mac OSX 10.5, Python 2.7.10, MySQL Connector/Python 2.2

そのため、以下のように 1行ずつ INSERT ... ON DUPLICATE KEY UPDATE し、
rowcount プロパティの値を確認すれば

更新件数、追加件数、スキップ件数

を取得できます。

■テーブル定義

mysql> CREATE TABLE test (
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   value VARCHAR(4)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test VALUES
    -> (null, 'a'),
    -> (null, 'b'),
    -> (null, 'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
+----+-------+
3 rows in set (0.00 sec)


■ソースコード

import mysql.connector

cnx = mysql.connector.connect(user='XXXX', password='XXXX',
                              host='XXXX',
                              database='XXXX')

cursor = cnx.cursor()

skipcount = 0
insertcount = 0
updatecount = 0

sql = ("INSERT INTO test VALUES (%s, %s)"
        "ON DUPLICATE KEY UPDATE value = VALUES(value)")
parameters = [
    (1, 'a'), # skip
    (2, 'd'), # update
    (3, 'e'), # update
    (4, 'f'), # insert
    (5, 'g'), # insert
    (6, 'h'), # insert
]

for parameter in parameters:
    cursor.execute(sql, parameter)

    rowcount = cursor.rowcount
    if rowcount == 0:
        skipcount += 1
    elif rowcount == 1:
        insertcount += 1
    elif rowcount == 2:
        updatecount += 1
    else:
        print "Something is wrong!"

cnx.commit()
cnx.close()

print skipcount
print insertcount
print updatecount


■実行結果

$ python 72041.py 
1
3
2
mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | d     |
|  3 | e     |
|  4 | f     |
|  5 | g     |
|  6 | h     |
+----+-------+
6 rows in set (0.00 sec)

ちなみに、yambejp様の回答の通り、INSERT ... ON DUPLICATE KEY UPDATE 構文に IGNORE キーワードを付ける必要はありません。
付けてもシンタックスエラーにはなりませんが、SQL文の実行結果は IGNORE キーワードを付けなかった場合と変わりありません。

mysql> SELECT * FROM test;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | d     |
|  3 | e     |
|  4 | f     |
|  5 | g     |
|  6 | h     |
+----+-------+
6 rows in set (0.01 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES (1, 'a'), (2, 'i'), (7, 'j') ON DUPLICATE KEY UPDATE value = VALUES(value);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM test;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | i     |
|  3 | e     |
|  4 | f     |
|  5 | g     |
|  6 | h     |
|  7 | j     |
+----+-------+
7 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT IGNORE INTO test VALUES (1, 'a'), (2, 'i'), (7, 'j') ON DUPLICATE KEY UPDATE value = VALUES(value);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> SELECT * FROM test;
+----+-------+
| id | value |
+----+-------+
|  1 | a     |
|  2 | i     |
|  3 | e     |
|  4 | f     |
|  5 | g     |
|  6 | h     |
|  7 | j     |
+----+-------+
7 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/04/12 13:57 編集

    KiyoshiMotoki様
    ご丁寧にサンプルまで記載いただきましてありがとうございます。
    rowcountの意味も勉強になりました。
    実践がまだのため、でき次第再度ご報告させていただきます。
    取り急ぎお礼までとなります。

    キャンセル

checkベストアンサー

+2

命題の表題と内容が異なっていますが大丈夫でしょうか?
ignoreでinsertすると追加処理しかされないので更新件数は0件です
本文中はon duplicate key updateしていますよね

ちなみに先に更新してあとからignore intoすれば
更新件数と追加件数が別々に拾えると思います

 ロジック

  • 元データ
create table t1(id int not null unique key,data int);
insert into t1 values(1,10),(2,20),(3,30),(4,40),(5,50);
  • 新規データ
    新規データを投入する際に別テーブルを用意する
create table t2(id int not null unique key,data int);
insert into t2 values(3,300),(4,400),(5,50),(6,600),(7,700),(8,800);


※プログラムで実行するときにはtemporary tableで作れば
セッションが終了した時点で勝手に消えてくれます

t1に対してt2を流し込んだ場合の結果

select sum(t1.data=t2.data) as `skip`
, sum(t1.data!=t2.data) as `mod`
, sum(t1.id is null) as `ins`
from t1 right join t2 on t1.id=t2.id;


skipがidが一緒でdataも一緒のため挿入も更新もされないスキップ件数
modがidが一緒でdataが異なるため更新される件数
insがt1にデータがないため挿入される件数
上記データを取った上で実際の投入

insert into t1 select * from t2 on duplicate key update data=values(data);

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/04/11 12:07

    繰り返しになりますがon duplicate key update構文をつかうならignoreは不要です
    改めて追加件数と更新件数ですが
    まず、insert ignore intoで追加して(on dup~はつけない)影響件数を得る
    その後insert into on duplicate で更新件数を得る
    (ただし更新件数は半分にしないといけないかも?)
    という2段階で処理すればいいと思います

    キャンセル

  • 2017/04/11 13:52

    別テーブルを用意しておいて検証する方法を追記しておきました

    キャンセル

  • 2017/04/12 13:56

    サンプルまでご紹介いただきまして大変感謝の次第です。
    ありがとうございます。
    サンプルの方を作ってみまして無事通過しました。

    ignoreの件ですが、warning メッセージを最後の行まで取得したいと言うことがあり、
    付与していました。
    (ignoreを外すとwarningがあった時点で処理が終了してしまうため)

    もう少し、実際に組んでみて再度こちらにご報告させていただきます。

    キャンセル

+1

  • おそらくバルクインサートしたいと思われるので、こんなやり方もあるよ的な
  • 日時のカラム(created_at,updated_at)がある前提です
  • 件数が多い場合は created_at,updated_at で複合index貼っておくよいです

 プログラム側で日付を渡す場合

  • skipは取得できないです、更新としてカウントされます
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t1 (id,name,created_at,updated_at)  values (1,'a','2017-04-01','2017-04-01'),(2,'b','2017-04-01','2017-04-01'),(3,'c','2017-04-01','2017-04-01');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+---------------------+---------------------+
| id | name | created_at          | updated_at          |
+----+------+---------------------+---------------------+
|  1 | a    | 2017-04-01 00:00:00 | 2017-04-01 00:00:00 |
|  2 | b    | 2017-04-01 00:00:00 | 2017-04-01 00:00:00 |
|  3 | c    | 2017-04-01 00:00:00 | 2017-04-01 00:00:00 |
+----+------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> insert into t1 (id,name,created_at,updated_at)  values (1,'a','2017-04-02','2017-04-02'),(2,'bb','2017-04-02','2017-04-02'),(3,'cc','2017-04-02','2017-04-02'),(4,'d','2017-04-02','2017-04-02') on duplicate key update name=values(name),updated_at=values(updated_at);
Query OK, 7 rows affected (0.00 sec)
Records: 4  Duplicates: 3  Warnings: 0

mysql> select * from t1;
+----+------+---------------------+---------------------+
| id | name | created_at          | updated_at          |
+----+------+---------------------+---------------------+
|  1 | a    | 2017-04-01 00:00:00 | 2017-04-02 00:00:00 |
|  2 | bb   | 2017-04-01 00:00:00 | 2017-04-02 00:00:00 |
|  3 | cc   | 2017-04-01 00:00:00 | 2017-04-02 00:00:00 |
|  4 | d    | 2017-04-02 00:00:00 | 2017-04-02 00:00:00 |
+----+------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select (select count(*) from t1 where created_at=updated_at and updated_at='2017-04-02') as add_count, (select count(*) from t1 where created_at<updated_at and updated_at='2017-04-02') as update_count;
+-----------+--------------+
| add_count | update_count |
+-----------+--------------+
|         1 |            3 |
+-----------+--------------+
1 row in set (0.00 sec)

 日時を自動でセットするカラムがある場合

  • 別途最新レコードを取得してupdated_atの値を取得する必要ありますが
  • スキップ件数 = 対象件数 - add_count - update_count
mysql> show create table t2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t2 (id,name)  values (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+------+---------------------+---------------------+
| id | name | created_at          | updated_at          |
+----+------+---------------------+---------------------+
|  1 | a    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
|  2 | b    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
|  3 | c    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
+----+------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> insert into t2 (id,name)  values (1,'a'),(2,'bb'),(3,'cc'),(4,'d') on duplicate key update name=values(name);
Query OK, 5 rows affected (0.00 sec)
Records: 4  Duplicates: 2  Warnings: 0

mysql> select * from t2;
+----+------+---------------------+---------------------+
| id | name | created_at          | updated_at          |
+----+------+---------------------+---------------------+
|  1 | a    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
|  2 | bb   | 2017-04-11 07:50:19 | 2017-04-11 07:51:00 |
|  3 | cc   | 2017-04-11 07:50:19 | 2017-04-11 07:51:00 |
|  4 | d    | 2017-04-11 07:51:00 | 2017-04-11 07:51:00 |
+----+------+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select (select count(*) from t2 where created_at=updated_at and updated_at='2017-04-11 07:51:00') as add_count, (select count(*) from t2 where created_at<updated_at and updated_at='2017-04-11 07:51:00') as update_count;
+-----------+--------------+
| add_count | update_count |
+-----------+--------------+
|         1 |            2 |
+-----------+--------------+
1 row in set (0.00 sec)

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.13%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る