在插入触发器之前在MYSQL中使用自动增量值? [英] Using Auto-Increment value in MYSQL Before Insert Trigger?

问题描述

用户表:

CREATE TABLE `users` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  `username` varchar(16) DEFAULT NULL,
  `salt` varchar(16) DEFAULT NULL,
  `password` varchar(128) DEFAULT NULL,
  `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `joined` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `loggedin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `sessionkey` varchar(60) DEFAULT NULL,
  `verifycode` varchar(16) DEFAULT NULL,
  `verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `banned` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `locked` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ip_address` varchar(45) DEFAULT NULL,
  `failedattempts` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `unlocktime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

user_records表:

The user_records table:

CREATE TABLE `user_records` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `userid` int(8) unsigned DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

用户表上的插入前触发器:

The before insert trigger on the users table:

USE `gknet`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `before_create_user` BEFORE INSERT ON `users` FOR EACH ROW BEGIN
INSERT INTO user_records (action, userid, timestamp)
  VALUES ('CREATED', ID, NOW() );
END

基本上，这里的问题是，当我尝试输入由MySQL自动分配的用户的ID(PK，NN，自动增量)时，在触发器上，它只是将user_records表中的userid放入0 .我该怎么做，以至于它将选择SQL分配给用户的ID，并将其作为用户ID放入记录条目中(该ID在创建"之后)?

Basically, my problem here is that on the trigger when I try to put in the id of the user that's automatically assigned by MySQL (PK, NN, Auto-Increment), it just puts in 0 for userid on the user_records table. How would I do it so it would select the id that the user is being assigned by SQL, and put it in as userid on the records entry (where the ID is right after 'CREATED')?

此外，如果您看到可以在表格上进行其他优化，请随时告诉我:D

Also, if you see any other optimizations that could be made on the tables, feel free to let me know :D

推荐答案

OP的评论:
您之前会怎么做?

OP's comment:
How would I do it before, thou?

您可以找到要分配给新记录的当前auto_increment值.
并在before触发器中将其用作user_records表的父用户ID.
您必须查询information_schema.tables表以找到该值.

You can find current auto_increment value that is to be assigned to a new record.
And use the same in the before trigger as a parent user id for user_records table.
You have to query information_schema.tables table to find the value.

示例:

use `gknet`;

delimiter $$

drop trigger if exists before_create_user; $$

create definer=`root`@`localhost` trigger `before_create_user` 
       before insert on `users` 
for each row begin
  declare fk_parent_user_id int default 0;

  select auto_increment into fk_parent_user_id
    from information_schema.tables
   where table_name = 'users'
     and table_schema = database();

  insert into user_records ( action, userid, timestamp )
         values ( 'created', fk_parent_user_id, now() );
end;

$$

delimiter ;

观察:
根据上的 mysql文档last_insert_id() ，

Observations:
As per mysql documentation on last_insert_id(),

"如果您使用单个INSERT语句插入多行， LAST_INSERT_ID()返回为第一个插入生成的值 仅行."

"if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only."

因此，

因此，取决于批量插入中的last_insert_id()和auto_increment字段值似乎不可靠.

hence, depending on last_insert_id() and auto_increment field values in batch inserts seems not reliable.

这篇关于在插入触发器之前在MYSQL中使用自动增量值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！