Can you access the auto increment value in MySQL w

Question

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

e.g.

I'd like a statement like this:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

Is there any way to achieve this please?

Thanks!

Update:

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

Update:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users`  
FOR EACH ROW BEGIN
SET NEW.default_assignment = NEW.userid;
END;

When inserting a new user however the default_assignment is always set to 0.

If I manually set the userid then the default_assignment does get set to the userid.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

Answer 1

Actually I just tried to do the same thing as was suggested above. But it seems Mysql doesent generate the inserted ID before the row actually gets commited. So NEW.userid will always return 0 in a Before insert trigger.

The above also wont work unless it is a BEFORE INSERT trigger, since you cant update values in a AFTER INSERT query.

From a Mysql Forum Post It seems the only way to handle this is using an additional table as a sequence. So that your trigger can pull in the values from an external source.

CREATE TABLE `lritstsequence` (
  `idsequence` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`idsequence`)
) ENGINE=InnoDB;

CREATE TABLE `lritst` (
`id` int(10) unsigned NOT NULL auto_increment,
`bp_nr` decimal(10,0) default '0',
`descr` varchar(128) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `dir1` (`bp_nr`)
) ENGINE=InnoDB;

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `lritst_bi_set_bp_nr`$$

CREATE TRIGGER `lritst_bi_set_bp_nr` BEFORE INSERT ON `lritst`
FOR EACH ROW
BEGIN
    DECLARE secuencia INT;
    INSERT INTO lritstsequence (idsequence) VALUES (NULL);
    SET secuencia = LAST_INSERT_ID();
    SET NEW.id = secuencia;
    SET NEW.bp_nr = secuencia;
END;$$

DELIMITER ;

INSERT INTO lritst (descr) VALUES ('test1');
INSERT INTO lritst (descr) VALUES ('test2');
INSERT INTO lritst (descr) VALUES ('test3');

SELECT * FROM lritst;

Result:

    id   bp_nr  descr 
------  ------  ------
     1       1  test1 
     2       2  test2 
     3       3  test3

This was copied from forums.mysql.com/read.php?99,186171,186241#msg-186241 but Im not allowed to post links yet.

Answer 2

try this

INSERT INTO users (default_assignment) VALUES (LAST_INSERT_ID()+1);

Answer 3

basically, the solution is like Resegue said.
But if you want it in one statement, you will use one of the below ways:

1. One long statement:

INSERT INTO `t_name`(field) VALUES((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t_name'))

or for text with number:

INSERT INTO `t_name`(field) VALUES(CONCAT('Item No. ',CONVERT((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t_name') USING utf8)))

it looks more clearly in PHP:

$pre_name='Item No. ';
$auto_inc_id_qry = "(SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$table')";
$new_name_qry = "CONCAT('$pre_name',CONVERT($auto_inc_id_qry USING utf8))";
mysql_query("INSERT INTO `$table`(title) VALUES($new_name_qry)");

2. Using function: (not tested yet)

CREATE FUNCTION next_auto_inc(table TINYTEXT) RETURNS INT
BEGIN
DECLARE next_id INT;
SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=table INTO next_id;
RETURN next_id;
END

INSERT INTO users ('username','default_assignment')
    VALUES ('barry', next_auto_inc('users'))

Answer 4

seeing that last_insert_id() wouldn't work in this case, yes, the trigger would be the only way to accomplish that.

I do ask myself though: What do you need this functionality for? Why do you store the users id twice? Personally, I don't like storing redundant data like this and I'd probably solve this in application code by making that ominous default_assignment column NULL and using the user id in my application code if default_assignment was NULL.