鍍金池/ 問答/數(shù)據(jù)庫/ mysql執(zhí)行插入更新語句，影響條數(shù)正常應(yīng)該是幾條？

mysql執(zhí)行插入更新語句，影響條數(shù)正常應(yīng)該是幾條？

INSERT INTO stock_spec(spec_id,unpay_num)
            (SELECT spec_id,actual_num FROM sales_trade_order 
            WHERE trade_id='194934' ) ON DUPLICATE KEY UPDATE unpay_num=unpay_num+VALUES(unpay_num);

執(zhí)行如上的插入更新語句，select的值只有一條記錄，執(zhí)行卻顯示影響兩條數(shù)據(jù)？這正常嗎？

回答
編輯回答
深記你

官方5.7參考手冊(cè)：

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

2017年9月23日 22:06