mysql触发器更新新添加行的一个字段

Question

我有一个包含以下字段的“用户”表:-user_id(int,自动递增PK)加密的用户 ID (varchar 50)用户名(varchar 50)用户位置(varchar 50)

我想要做的是创建一个触发器，以便当将值插入到用户表的 user_name 和 user_location 中时，我想使用 user_id 中的 AES_ENCRYPTED 值填充加密的_userid 字段 - 例如AES_ENCRYPT(user_id,'MYAESKEY') 但仅适用于新插入的行

这在 MySQL 中可以通过某种触发器实现吗？

提前致谢。

Answer 1

So is there a solution to my problem that will not fail etc? using a trigger - all other solutions i tried from reading other sources just didn't work.

所有的解决方案都围绕 LAST_INSERT_ID() 展开因为它是获取自动生成 ID 的唯一多用户安全方式。

<小时/>

第一种可能的方法，如果您非常喜欢触发器，就是为自动生成的序列建立一个单独的表。您的架构将如下所示

CREATE TABLE users_seq (user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE users
(
  user_id INT NOT NULL PRIMARY KEY DEFAULT 1, 
  encrypted_userid varchar(50), 
  user_name varchar(50), 
  user_location varchar(50),
  FOREIGN KEY user_id_fk (user_id) REFERENCES users_seq (user_id)
);

还有触发器

DELIMITER //
CREATE TRIGGER useridinserttrigger
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_seq() VALUES();
  SET NEW.user_id = LAST_INSERT_ID(),
      NEW.encrypted_userid = AES_ENCRYPT(LAST_INSERT_ID(), 'MYAESKEY');
END//
DELIMITER ;

<小时/>

第二种方法是利用现有架构但使用存储过程

DELIMITER //
CREATE PROCEDURE insert_user(IN _name VARCHAR(50), IN _location VARCHAR(50))
BEGIN
  DECLARE _id INT;

  START TRANSACTION;
  INSERT INTO users (user_name, user_location) VALUES(_name, _location);
  SET _id = LAST_INSERT_ID();
  UPDATE users 
     SET encrypted_userid = AES_ENCRYPT(_id, 'MYAESKEY')
   WHERE user_id = _id;
  COMMIT;
END//
DELIMITER ;

使用示例:

CALL insert_user('johndoe', null);