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

よろしくお願いします。

行動規範の内容に同意します

回答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 プロパティの値を確認すれば

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

を取得できます。

■テーブル定義

sql
1mysql> CREATE TABLE test (
2    ->   id INT PRIMARY KEY AUTO_INCREMENT,
3    ->   value VARCHAR(4)
4    -> );
5Query OK, 0 rows affected (0.03 sec)
6
7mysql> INSERT INTO test VALUES
8    -> (null, 'a'),
9    -> (null, 'b'),
10    -> (null, 'c');
11Query OK, 3 rows affected (0.00 sec)
12Records: 3  Duplicates: 0  Warnings: 0
13
14mysql> select * from test;
15+----+-------+
16| id | value |
17+----+-------+
18|  1 | a     |
19|  2 | b     |
20|  3 | c     |
21+----+-------+
223 rows in set (0.00 sec)

■ソースコード

python
1import mysql.connector
2
3cnx = mysql.connector.connect(user='XXXX', password='XXXX',
4                              host='XXXX',
5                              database='XXXX')
6
7cursor = cnx.cursor()
8
9skipcount = 0
10insertcount = 0
11updatecount = 0
12
13sql = ("INSERT INTO test VALUES (%s, %s)"
14        "ON DUPLICATE KEY UPDATE value = VALUES(value)")
15parameters = [
16    (1, 'a'), # skip
17    (2, 'd'), # update
18    (3, 'e'), # update
19    (4, 'f'), # insert
20    (5, 'g'), # insert
21    (6, 'h'), # insert
22]
23
24for parameter in parameters:
25    cursor.execute(sql, parameter)
26
27    rowcount = cursor.rowcount
28    if rowcount == 0:
29        skipcount += 1
30    elif rowcount == 1:
31        insertcount += 1
32    elif rowcount == 2:
33        updatecount += 1
34    else:
35        print "Something is wrong!"
36
37cnx.commit()
38cnx.close()
39
40print skipcount
41print insertcount
42print updatecount

■実行結果

$ python 72041.py 
1
3
2

sql
1mysql> select * from test;
2+----+-------+
3| id | value |
4+----+-------+
5|  1 | a     |
6|  2 | d     |
7|  3 | e     |
8|  4 | f     |
9|  5 | g     |
10|  6 | h     |
11+----+-------+
126 rows in set (0.00 sec)

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

sql
1mysql> SELECT * FROM test;
2+----+-------+
3| id | value |
4+----+-------+
5|  1 | a     |
6|  2 | d     |
7|  3 | e     |
8|  4 | f     |
9|  5 | g     |
10|  6 | h     |
11+----+-------+
126 rows in set (0.01 sec)
13
14mysql> BEGIN;
15Query OK, 0 rows affected (0.00 sec)
16
17mysql> INSERT INTO test VALUES (1, 'a'), (2, 'i'), (7, 'j') ON DUPLICATE KEY UPDATE value = VALUES(value);
18Query OK, 3 rows affected (0.00 sec)
19Records: 3  Duplicates: 1  Warnings: 0
20
21mysql> SELECT * FROM test;
22+----+-------+
23| id | value |
24+----+-------+
25|  1 | a     |
26|  2 | i     |
27|  3 | e     |
28|  4 | f     |
29|  5 | g     |
30|  6 | h     |
31|  7 | j     |
32+----+-------+
337 rows in set (0.00 sec)
34
35mysql> ROLLBACK;
36Query OK, 0 rows affected (0.00 sec)
37
38mysql> BEGIN;
39Query OK, 0 rows affected (0.00 sec)
40
41mysql> INSERT IGNORE INTO test VALUES (1, 'a'), (2, 'i'), (7, 'j') ON DUPLICATE KEY UPDATE value = VALUES(value);
42Query OK, 3 rows affected (0.00 sec)
43Records: 3  Duplicates: 1  Warnings: 0
44
45mysql> SELECT * FROM test;
46+----+-------+
47| id | value |
48+----+-------+
49|  1 | a     |
50|  2 | i     |
51|  3 | e     |
52|  4 | f     |
53|  5 | g     |
54|  6 | h     |
55|  7 | j     |
56+----+-------+
577 rows in set (0.00 sec)
58
59mysql> ROLLBACK;
60Query OK, 0 rows affected (0.00 sec)

投稿2017/04/11 04:49

KiyoshiMotoki

総合スコア4791

ocms

2017/04/12 04:59 編集

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

行動規範の内容に同意します

ベストアンサー

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

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

ロジック

元データ

SQL
1create table t1(id int not null unique key,data int);
2insert into t1 values(1,10),(2,20),(3,30),(4,40),(5,50);

新規データ

新規データを投入する際に別テーブルを用意する

SQL
1create table t2(id int not null unique key,data int);
2insert into t2 values(3,300),(4,400),(5,50),(6,600),(7,700),(8,800);

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

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

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

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

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

投稿2017/04/11 01:09

編集2017/04/11 04:51

yambejp

総合スコア117229

ocms

2017/04/11 01:35

yambejpさん大変失礼いたしました。本文にignoreを失念しておりました。自分の質問は正しくは下記となります。 INSERT IGNORE INTO table (a, b, c) VALUES (1, 12, 100), (2, 22, 100) ON DUPLICATE KEY UPDATE b = b sql自体は追加も更新もされます。 cursor.rowcountでカウントすると、２重カウントされてしまいます。 1件更新したいが２件カウントされる。（どうカウントされたかはわからない。）下記を参考にしておりました。 http://www.databasepronews.com/insert-ignore-on-duplicate-key-update/ 表題のようなカウントをしたい場合は、sqlを分けたほうが良いでしょうか。

yambejp

2017/04/11 01:58

ごめんなさい、私の理解が間違っているのでしょうか？ ignore intoとon duplicate key updateは意味合いとして競合しています前者はユニークキーを無視してデータを投入後者はユニークキーがあれば更新処理的にはignoreはついていなくても問題ないはずです。また例示いただいたb=bもちょっと意味がわかりません。 bをbで上書きしようとしても同じ値なので更新はされません結果として例示のSQLでは新規でデータが投入されるだけです。

ocms

2017/04/11 02:13

何度も申し訳ありません。以下のようなデータとなります。 insert ignore into test (customer_id,customer_name,email) values ('1', '12tanakあ', '@email.com'), ('2', '1yoshida', 'email2@email.com'), ('3', 'ABC3', 'email3@email.com') ON DUPLICATE KEY UPDATE customer_name = VALUES(customer_name) >また例示いただいたb=bもちょっと意味がわかりません。私の方の不備で大変失礼いたしました。

yambejp

2017/04/11 03:07

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

yambejp

2017/04/11 04:52

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

ocms

2017/04/12 04:56

サンプルまでご紹介いただきまして大変感謝の次第です。ありがとうございます。サンプルの方を作ってみまして無事通過しました。 ignoreの件ですが、warning メッセージを最後の行まで取得したいと言うことがあり、付与していました。（ignoreを外すとwarningがあった時点で処理が終了してしまうため）もう少し、実際に組んでみて再度こちらにご報告させていただきます。

行動規範の内容に同意します

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

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

skipは取得できないです、更新としてカウントされます

sql
1mysql> show create table t1;
2+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3| Table | Create Table                                                                                                                                                                                                   |
4+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5| t1    | CREATE TABLE `t1` (
6  `id` int(11) NOT NULL,
7  `name` varchar(255) DEFAULT NULL,
8  `created_at` datetime NOT NULL,
9  `updated_at` datetime NOT NULL,
10  PRIMARY KEY (`id`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
12+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
131 row in set (0.00 sec)
14
15mysql> 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');
16Query OK, 3 rows affected (0.00 sec)
17Records: 3  Duplicates: 0  Warnings: 0
18
19mysql> select * from t1;
20+----+------+---------------------+---------------------+
21| id | name | created_at          | updated_at          |
22+----+------+---------------------+---------------------+
23|  1 | a    | 2017-04-01 00:00:00 | 2017-04-01 00:00:00 |
24|  2 | b    | 2017-04-01 00:00:00 | 2017-04-01 00:00:00 |
25|  3 | c    | 2017-04-01 00:00:00 | 2017-04-01 00:00:00 |
26+----+------+---------------------+---------------------+
273 rows in set (0.00 sec)
28
29mysql> 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);
30Query OK, 7 rows affected (0.00 sec)
31Records: 4  Duplicates: 3  Warnings: 0
32
33mysql> select * from t1;
34+----+------+---------------------+---------------------+
35| id | name | created_at          | updated_at          |
36+----+------+---------------------+---------------------+
37|  1 | a    | 2017-04-01 00:00:00 | 2017-04-02 00:00:00 |
38|  2 | bb   | 2017-04-01 00:00:00 | 2017-04-02 00:00:00 |
39|  3 | cc   | 2017-04-01 00:00:00 | 2017-04-02 00:00:00 |
40|  4 | d    | 2017-04-02 00:00:00 | 2017-04-02 00:00:00 |
41+----+------+---------------------+---------------------+
424 rows in set (0.00 sec)
43
44mysql> 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;
45+-----------+--------------+
46| add_count | update_count |
47+-----------+--------------+
48|         1 |            3 |
49+-----------+--------------+
501 row in set (0.00 sec)
51

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

別途最新レコードを取得してupdated_atの値を取得する必要ありますが
スキップ件数 = 対象件数 - add_count - update_count

sql
1
2mysql> show create table t2;
3+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4| Table | Create Table                                                                                                                                                                                                                                                                                   |
5+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6| t2    | CREATE TABLE `t2` (
7  `id` int(11) NOT NULL,
8  `name` varchar(255) DEFAULT NULL,
9  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
10  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
11  PRIMARY KEY (`id`)
12) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
13+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
141 row in set (0.00 sec)
15
16mysql> insert into t2 (id,name)  values (1,'a'),(2,'b'),(3,'c');
17Query OK, 3 rows affected (0.00 sec)
18Records: 3  Duplicates: 0  Warnings: 0
19
20mysql> select * from t2;
21+----+------+---------------------+---------------------+
22| id | name | created_at          | updated_at          |
23+----+------+---------------------+---------------------+
24|  1 | a    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
25|  2 | b    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
26|  3 | c    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
27+----+------+---------------------+---------------------+
283 rows in set (0.00 sec)
29
30mysql> insert into t2 (id,name)  values (1,'a'),(2,'bb'),(3,'cc'),(4,'d') on duplicate key update name=values(name);
31Query OK, 5 rows affected (0.00 sec)
32Records: 4  Duplicates: 2  Warnings: 0
33
34mysql> select * from t2;
35+----+------+---------------------+---------------------+
36| id | name | created_at          | updated_at          |
37+----+------+---------------------+---------------------+
38|  1 | a    | 2017-04-11 07:50:19 | 2017-04-11 07:50:19 |
39|  2 | bb   | 2017-04-11 07:50:19 | 2017-04-11 07:51:00 |
40|  3 | cc   | 2017-04-11 07:50:19 | 2017-04-11 07:51:00 |
41|  4 | d    | 2017-04-11 07:51:00 | 2017-04-11 07:51:00 |
42+----+------+---------------------+---------------------+
434 rows in set (0.00 sec)
44
45mysql> 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;
46+-----------+--------------+
47| add_count | update_count |
48+-----------+--------------+
49|         1 |            2 |
50+-----------+--------------+
511 row in set (0.00 sec)