mysql - 使用MySQL，我如何遍历更新触发器中的字段以比较变更日志的新旧值？

我试图在日志记录打开时使用MySQL中的触发器使用表更改来更新日志表。UPDATE触发器需要遍历表的所有字段，比较数据更改（OLD.fieldname NEW.fieldname），然后将字段名称和数据写入日志表。我编写的代码必须明确命名每个字段。有些表有很多字段，我不希望手动输入所有字段名称。有没有办法以编程方式遍历表的字段，以便我可以创建要调用的存储过程？我的代码：CREATE DEFINER=`root`@`localhost` TRIGGER `trgUserUpd` AFTER UPDATE ON `user`FOR EACH ROW BEGINDECLARE rChanges TEXT DEFAULT '';IF BINARY(OLD.userName) <> BINARY(NEW.userName) THEN SET rChanges := CONCAT('userName: ', OLD.userName, ' => ', NEW.userName);END IF;IF BINARY(OLD.fullName) <> BINARY(NEW.fullName) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, 'fullName: ', OLD.fullName, ' => ', NEW.fullName);END IF;IF BINARY(OLD.initials) <> BINARY(NEW.initials) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, 'initials: ', OLD.initials, ' => ', NEW.initials);END IF;IF BINARY(OLD.password) <> BINARY(NEW.password) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, 'password changed');END IF;IF BINARY(OLD.salt) <> BINARY(NEW.salt) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, 'salt: ', OLD.salt, ' => ', NEW.salt);END IF;IF BINARY(OLD.administrator) <> BINARY(NEW.administrator) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, 'administrator: ', OLD.administrator, ' => ', NEW.administrator);END IF;IF BINARY(OLD.active) <> BINARY(NEW.active) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, 'active: ', OLD.active, ' => ', NEW.active);END IF;CALL ChangeNotify('user', OLD.id, rChanges, 'update');END;准备好的语句似乎也不起作用。我只是试过：BEGINDECLARE rChanges TEXT DEFAULT '';DECLARE eof INT DEFAULT FALSE;DECLARE field CHAR(50) DEFAULT '';DECLARE query, stmt CHAR(200) DEFAULT '';DECLARE oldValue, newValue TEXT DEFAULT '';DECLARE fields CURSOR FORSELECT column_nameFROM INFORMATION_SCHEMA.COLUMNSWHERE table_name = 'user'ORDER BY ordinal_position;DECLARE CONTINUE HANDLER FOR NOT FOUND SET eof = TRUE;OPEN fields;REPEAT FETCH fields INTO field; IF NOT eof THEN SET query = CONCAT('SELECT OLD.', field, ', NEW.', field, ' INTO oldValue, newValue'); PREPARE stmt FROM query; EXECUTE stmt; IF BINARY(oldValue) <> BINARY(newValue) THEN IF rChanges <> '' THEN SET rChanges := CONCAT(rChanges, ''); END IF; SET rChanges := CONCAT(rChanges, field, ': ', oldValue, ' => ', newValue); END IF; END IF;UNTIL eof = TRUE;END REPEAT;CLOSE fields;CALL ChangeNotify('user', OLD.id, rChanges, 'update');END甚至无法保存（除非我有问题）。 (adsbygoogle = window.adsbygoogle || []).push({}); 最佳答案 使用数据库和表尝试以下代码，可能会有所帮助。让我知道它是否对您有帮助。  调用ATESTE（'tablename'，'dbname'） CREATE PROCEDURE `ATESTE`(tablename varchar(100),schemaname varchar(100)) BEGIN DECLARE rChanges MEDIUMTEXT; DECLARE field VARCHAR(50); DECLARE cmdquery MEDIUMTEXT; DECLARE oldValue, newValue VARCHAR(50); DECLARE myfields MEDIUMTEXT; DECLARE mPos integer; set myfields = (SELECT group_concat(column_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = tablename and table_schema= schemaname ORDER BY ordinal_position); set rChanges = ''; set mPos = Position("," in myfields); myloop: While(mPos > 0) Do set field = trim(substring(myfields,1,mPos-1)); set myfields = trim(substring(myfields,mPos+1)); set mPos = Position("," in myfields); set oldValue = CONCAT('old.',field); set NewValue = CONCAT('new.',field); IF rChanges <> '' THEN SET rChanges = CONCAT(rChanges, '\r\n'); END IF; SET rChanges = CONCAT(rChanges, field, ' : ', oldValue, ' => ', newValue); End While; -- select rChanges; CALL ChangeNotify('user', OLD.id, rChanges, 'update'); END关于mysql - 使用MySQL，我如何遍历更新触发器中的字段以比较变更日志的新旧值？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59055077/ (adsbygoogle = window.adsbygoogle || []).push({});