Manual Auto Zoom

Question

There is a table with an int field - field_1.
I want to insert a new line.
The value of field_1 will be the maximum value for all records plus one.
I tried:

INSERT INTO table (field names, `field_1`) VALUES (values, '(SELECT MAX(field_1) FROM table)'); 

I get '0' in field_1.
I know that I can do this in separate requests. Is there a way to perform this action with a single request? I mean one call from php.

I have an auto-increment 'id' field and I want to add a position field. I want to be able to make changes to the position, but the new item will always have the highest position.

Answer 1

However, what you are trying to do, this will not work, because it is not guaranteed to be atomic. Thus, two instances of this request, executed in parallel, guarantee that they will interfere with each other at some random point in time, which will lead to missing numbers and duplicate numbers.

The reason that databases offer auto-increment is precisely to solve this problem, guaranteeing atomicity in the generation of these extra values.

(Finally, “Auto Increment Manually” is an oxymoron. It will either be “Auto Increment” or it will be “manual increment.” Just be a smart ass here.)

EDIT (after editing the OP)

One ineffective way to solve your problem is to leave the Position field null or NULL, and then do UPDATE table SET Position = Id WHERE Position IS NULL . (Assuming Id is the auto-typing field in your table.)

An effective but cumbersome way would be to leave the Position NULL field when you haven't changed it, and give it a value only when you decide to change it. Then, every time you want to read the Position field, use the CASE statement: if the Position field is NULL, then use the Id value; otherwise use the Position value.

EDIT2 (after reviewing the OP description in the comments)

If you have only 30 rows, I don’t understand why you are even trying to keep order in the database. Just load all the rows in the array, programmatically assign incremental values ​​to any Position fields that are considered NULL, and when the order of the rows in your array changes, just correct the Position values ​​and update all 30 rows to the database.

Answer 2

ALTER TABLE table_name AUTO_INCREMENT = 1 allows the database to reset AUTO_INCREMENT:

MAX (auto_increment_column) + 1

This does not reset equal to 1.

This prevents duplication of AUTO_INCREMENT values. In addition, since AUTO_INCREMENT Values ​​are primary / unique, duplication is never possible. The method for this is available for some reason. It will not modify any entries in the database; just an internal counter, so that it indicates the maximum available value. As someone said earlier, don't try to outsmart the database ... just let it handle it. It does a very good job at resetting AUTO_INCREMENT. See gotphp

Answer 3

Try the following:

 INSERT INTO table (some_random_field, field_to_increment) SELECT 'some_random_value', IF(MAX(field_to_increment) IS NULL, 1, MAX(field_to_increment) + 1) FROM table; 

Or that:

 INSERT `table` SET some_random_field = 'some_random_value', field_to_increment = (SELECT IF(MAX(field_to_increment) IS NULL, 1, MAX(field_to_increment) + 1) FROM table t); 

PS I know this for 4 years, but I was looking for the same answer. :)