CREATE TABLE test (
id INT AUTO_INCREMENT PRIMARY KEY,
val INT
);
CREATE TRIGGER tr_ins -- save id of the inserted row
AFTER INSERT ON test
FOR EACH ROW
SET @inserted := CONCAT_WS(',', @inserted, NEW.id);
CREATE TRIGGER tr_upd -- save id of the updated row
AFTER UPDATE ON test
FOR EACH ROW
SET @updated := CONCAT_WS(',', @updated, NEW.id);
x
-- clear user-defined variables which will collect the list of effected rows
SET @inserted := NULL;
SET @updated := NULL;
INSERT INTO test VALUES
(1,1), -- explicit insert
(NULL,2), -- autogenerated insert
(NULL,3), -- autogenerated insert
(2,4), -- update
(1,5), -- update
(2,6), -- update
(NULL,7), -- autogenerated insert
(5,8), -- explicit insert
(3,9) -- update
ON DUPLICATE KEY UPDATE val = VALUES(val);
SELECT * FROM test;
-- see the lists of inserted/updated row's id values
SELECT @inserted, @updated;
1条答案
按热度按时间vlf7wbxs1#
您可以使用用户定义的变量和触发器获取所需的数据。
示例代码:
x
的数据
| ID|瓦尔|
| --|--|
| 1 | 5 |
| 2 | 6 |
| 3 | 9 |
| 4 | 7 |
| 5 | 8 |
| @插入|@更新|
| --|--|
| 一二三四五|二、一、二、三|
fiddle