XF 2.1 mysql auto +1

[Robert9]

Example:

We have a table

id (auto increment)
test (int unique)

When i do an INSERT the id will count+1 from the last AUTO_INCREMENT

INSERT INTO x id, test ... values null, 1

How can i have test also +1 from the last max value?

Do i need to catch it first?
select test from x where 1=1 order by test DESC LIMIT 1
new_test=result+1
insert null, new_test

or is there a function to tell mysql:

""Hey, i need a new value in this field, do just +1 to the highest value you have till now!""

?

 

[Jeremy P]

MySQL only supports one auto-increment column per table, and I'm not sure what the use case for having another would possibly be. I would think over what you're trying to accomplish and see if you can come up with a better schema. I'd be surprised if this was really necessary.

If it is strictly necessary for whatever reason, I suppose you could accomplish this manually by reading the last value before inserting, but you'd have to lock the table to prevent race conditions when two rows are being inserted at nearly the same time.

 

[Robert9]

I found something
insert into table set field=(select max(h1.fielt)+1 from table h1);

but anyway i have solved it with a finder() for the highest value + 1
Thank you.

This function exists for the admin only, but to learn how to lock a table is a good idea anyway.