CODEWITHSUNDEEP
mysqlsqlduplicates
Krishna M
Krishna M

Reputation: 1195

Prevent auto increment on duplicated entry?

I have Table called url_info and the structure of the table is:

url_info:

url_id ( auto_increment, primary key )
url ( unique,varchar(500) )

When I insert into table like this:

INSERT INTO url_info(url) VALUES('Tom');
INSERT INTO url_info(url) VALUES('Jerry');

The output is:

1 Tom
2 Jerry

When I insert like this 

INSERT INTO url_info(url) VALUES('Tom');
INSERT INTO url_info(url) VALUES('Tom');
INSERT INTO url_info(url) VALUES('Jerry');

The output is 

1 Tom
3 Jerry

The auto-increment id is incremented when I try to insert to duplicate entry. I have also tried Insert Ignore

How to prevent it from incrementing when I try to insert a duplicate entry?

Upvotes: 1

Views: 3563

Answers (4)

Joe Harper
Joe Harper

Reputation: 470

It's probably worth creating a stored procedure to insert what you want into the table. But, in the stored procedure check what items you have already in the table. If these match what you're trying to insert, then the query should not even attempt the insert.

Ie. The procedure needs to contain something like this:

IF NOT EXISTS(SELECT TOP 1 url_id FROM url_info WHERE url = 'Tom')
INSERT INTO url_info(url) VALUES('Tom')

So, in your stored procedure, it would look like this (assuming the arguments/variables have been declared)

IF NOT EXISTS(SELECT TOP 1 url_id FROM url_info WHERE url = @newUrl)
INSERT INTO url_info(url) VALUES(@newUrl)

Upvotes: 2

Emil Vikström
Emil Vikström

Reputation: 91912

This is expected behaviour in InnoDB. The reason is that they want to let go of the auto_increment lock as fast as possible to improve concurrency. Unfortunately this means they increment the AUTO_INCREMENT value before resolving any constraints, such as UNIQUE.

You can read more about the idea in the manual on AUTO_INCREMENT Handling in InnoDB, but the manual is also unfortunately buggy and doesn't tell why your simple insert will give non-consecutive values.

If this is a real problem for you and you really need consecutive numbers, consider setting the innodb_autoinc_lock_mode option to 0 in your server, but this is not recommended as it will have severe effects on your database (you cannot do any inserts concurrently).

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

Auto_increment is performed updated by the engine. This is done before hand of checking a value is unique or not. And we can't roll back the operation to get back to former value of auto_increment.

Hence NO to start from where you last read on auto_increment.

And it is not an issue in loosing some intermediate values on auto_increment field.
The MAX value you can store into a SIGNED INT field is 2^31-1 equal to 2,147,483,647. If you read it loud, it sounds 2 billion+.

And I don't think it is small and won't suite your requirement.

Upvotes: 0

Duikboot
Duikboot

Reputation: 1101

CREATE TABLE `url_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;

When I execute:

INSERT INTO url_info(url) VALUES('Tom');
INSERT INTO url_info(url) VALUES('Tom');
INSERT INTO url_info(url) VALUES('Jerry');

I get:

enter image description here

Make sure you ID column is UNIQUE too.

As the manual says:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

Upvotes: -1

Related Questions