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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

Q&A

解決済

3回答

9123閲覧

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

ocms

総合スコア53

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Python

Pythonは、コードの読みやすさが特徴的なプログラミング言語の1つです。 強い型付け、動的型付けに対応しており、後方互換性がないバージョン2系とバージョン3系が使用されています。 商用製品の開発にも無料で使用でき、OSだけでなく仮想環境にも対応。Unicodeによる文字列操作をサポートしているため、日本語処理も標準で可能です。

0グッド

0クリップ

投稿2017/04/11 00:32

編集2017/04/11 02:14

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

よろしくお願いします。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

以下、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の意味も勉強になりました。 実践がまだのため、でき次第再度ご報告させていただきます。 取り急ぎお礼までとなります。
guest

0

ベストアンサー

命題の表題と内容が異なっていますが大丈夫でしょうか?
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

総合スコア114585

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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でカウントすると、2重カウントされてしまいます。 1件更新したいが2件カウントされる。(どうカウントされたかはわからない。) 下記を参考にしておりました。 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 で更新件数を得る (ただし更新件数は半分にしないといけないかも?) という2段階で処理すればいいと思います
yambejp

2017/04/11 04:52

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

2017/04/12 04:56

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

0

  • おそらくバルクインサートしたいと思われるので、こんなやり方もあるよ的な
  • 日時のカラム(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)

投稿2017/04/11 08:14

編集2017/04/11 12:38
yoseemetoo

総合スコア12

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問