CODEWITHSUNDEEP
mysqlinsertauto-increment
T.Poe
T.Poe

Reputation: 2079

Use auto increment only when needed

I have a column in my db "id" int(11) NOT NULL AUTO_INCREMENT, and I want multiple rows in this table with the same id value. So when inserting to the table I'd like to tell whether it should increment or the value remains the sasme. Is there any easy way how to do that?

Upvotes: 1

Views: 3514

Answers (3)

Ayyappa P
Ayyappa P

Reputation: 27

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;


Which returns:     
**grep   id name**     
fish   1  lax     
mammal 1  dog     
mammal 2  cat     
mammal 3  whale     
bird   1  penguin     
bird   2  ostrich

If the AUTO_INCREMENT column is part of multiple indexes, MySQL generates sequence values using the index that begins with the AUTO_INCREMENT column, if there is one. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value

Upvotes: 0

Shadow
Shadow

Reputation: 34231

As MySQL documentation on auto_increment says (highlighting is mine):

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers. When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.

This means, if you determine before the insert the current maximum of the auto_increment field and you explicitly insert that value in the insert statement, then you can have duplicate values in the auto_increment field.

There a couple things that you need to pay attention to:

  1. If you can have parallel inserts into the table, then you may have to lock the table for reading, so another process does not insert a new record triggering the increment of the field.

  2. You cannot use primary / unique index constraint on the auto_increment field.

The alternative is to have a separate table just for the auto_increment and do not use auto_increment in the main table. If you need a new id, then just insert a record into the auto_ncrement table a get the incremented id and use that to insert a record into the main table. Otherwise, just fetch the id value from the main table and use it in the insert.

Upvotes: 1

sagi
sagi

Reputation: 40481

An auto_increment column makes sure that the values in it are unique ! So, you can't do it this way.

I'd suggest a trigger instead , combining the needed logic.

Upvotes: 0

Related Questions