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

I tested the above trigger idea with 10 concurrent threads doing inserts and I got over 1000 cases of 2 or 3 duplicates after ~25k inserted.

DROP TABLE IF EXISTS test_table CASCADE;

CREATE TABLE `test_table` (
  `id`             INT         NOT NULL  AUTO_INCREMENT,
  `update_me`      VARCHAR(36),
  `otherdata`      VARCHAR(36) NOT NULL,

  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COMMENT 'test table for trigger testing';

delimiter $$

DROP TRIGGER IF EXISTS setnum_test_table;
$$
CREATE TRIGGER setnum_test_table
BEFORE INSERT ON test_table FOR EACH ROW
-- SET OLD.update_me = CONCAT(NEW.id, 'xyz');

BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table' LOCK IN SHARE MODE );
--    SET NEW.update_me = CONCAT(next_id, 'qrst');
    SET NEW.update_me = next_id;
END

$$

delimiter ;

-- SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table'

INSERT INTO test_table (otherdata) VALUES ('hi mom2');

SELECT count(*) FROM test_table;
SELECT * FROM test_table;

-- select count(*) from (
select * from (
SELECT count(*) as cnt ,update_me FROM test_table group by update_me) q1
where cnt > 1
order by cnt desc

I used 10 of:

while true ; do echo "INSERT INTO test_table (otherdata) VALUES ('hi mom2');" | mysql --user xyz testdb ; done &

And ran the last query to watch for duplicates

example output: '3', '4217' '3', '13491' '2', '10037' '2', '14658' '2', '5080' '2', '14201' ...

Note 'LOCK IN SHARE MODE' didn't change anything. With and without gave duplicates at about the same rate. It seems that MySQL AUTO_INCREMENT doesn't work like Postgres' next_val() and is NOT concurrency safe.

Answer 2

You can do this reliably using a simple subquery:

INSERT INTO users ('username','default_assignment')
    SELECT 'barry', Auto_increment FROM information_schema.tables WHERE TABLE_NAME='users'

Answer 3

I know this post is from 2010, but I couldn't find a good solution. I've solved this by creating a separate table that holds the counters. When I need to generate an unique identifier for a column I just call a Stored proc:

CREATE DEFINER=`root`@`localhost` PROCEDURE `IncrementCounter`(in id varchar(255))
BEGIN
declare x int;
-- begin;
start transaction;
-- Get the last counter (=teller) and mark record for update.
select Counter+1 from tabel.Counter where CounterId=id into x for update;
-- check if given counter exists and increment value, otherwise create it.
if x is null then
    set x = 1;
    insert into tabel.Counters(CounterId, Counter) values(id, x);
else
    update tabel.Counters set Counter = x where CounterId = id;
end if;
-- select the new value and commit the transaction
select x;
commit;
END

The 'for update' statement locks the row in the counters table. This avoids duplicates being made by multiple threads.

Answer 4

there's no need to create another table, and max() will have problems acording to the auto_increment value of the table, do this:

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
   SET NEW.field=next_id;
END

I declare the next_id variable because usually it will be used in some other way(*), but you could do straight new.field=(select ...)

(*) To auto-name an image:
SET NEW.field = CONCAT('image_', next_id, '.gif');
(*) To create a hash:
SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );

Answer 5

$ret = $mysqli->query("SELECT Auto_increment FROM information_schema.tables WHERE table_schema = DATABASE() ");l
while ($row = mysqli_fetch_array($ret)) {
    $user_id=$row['Auto_increment'];
}

Answer 6

The only I found that would solve this problem without an extra table would be to calculate self the next number and put that in the fields required.

CREATE TABLE `Temp` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(255) ,
  PRIMARY KEY  (`idsequence`)
) ENGINE=InnoDB;


CREATE TRIGGER temp_before_insert BEFORE INSERT ON `Temp`
FOR EACH ROW 
BEGIN
    DECLARE m INT;

    SELECT IFNULL(MAX(id), 0) + 1 INTO m FROM Temp;
    SET NEW.value = m;
    -- NOT NEEDED but to be save that no other record can be inserted in the meanwhile
    SET NEW.id = m;   
END;