CODEWITHSUNDEEP
mysqldatabase
Miguel Stevens
Miguel Stevens

Reputation: 9220

MySQL increment field on insert

I have a lists table that has an order field.

When I insert a new record, is it possible to find the order of the previous row and increment the new row?

Or should I go about it myself in PHP by doing an OrderBy('order') query and getting the max() value of that?

Upvotes: 1

Views: 1368

Answers (1)

Veve
Veve

Reputation: 6758

When you declare a table with MySQL you can use an auto-increment id so you won't have to deal about its incrementation:

CREATE TABLE people (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

As explained in the documentation,

An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1.

I suggest you to ommit the field completly when inserting new records.

You can then retrieve the last id inserted with LAST_INSERT_ID() SQL function (or the mysqli_insert_id function of PHP languagefor example).

But since it's not what you wanted, probably because of one of the reasons quoted from MarioZ's comment:

If you are already using auto-increment for the ID you can use it for the order (that can be one reason). For auto-increment the column must be set as primary and unique, can't be repeated values. The auto-increment is from the number in the record, if you inserted 10 rows and you delete 2, the next insert with auto-increment will be 11(if the last now is 8 you'd want it to be 9). Those are posible reasons not to use it for what @Notflip wants :P

... You'll have to use PHP, with LOCK TABLE and UNLOCK TABLE SQL instructions before and after the retrieving of the last order then the updating of the new order, to avoid having simultaneous records with the same "order".

Upvotes: 1

Related Questions