0

I have an InndoDB table 

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL,
  `network_id` int(10) unsigned NOT NULL,
  `nickname` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
 UNIQUE KEY `network_id` (`network_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In php I receive (it does not matter where from) an array $network_info, which contains pairs network_id,nickname. For those network_id which are present in users I would like to update their nickname. For those, which are not present I would like to insert them into table with generated (new incremented) id.

Earlier, I have used id as AUTO_INCREMENT, but queries such as INSERT IGNORE and so on, increment it all time, even when insertion was not done,and it is not good.

Which is the best way to solve this problem?

Anton
  • 539
  • 6
  • 14
  • I didn't think that `INSERT IGNORE` would increment the auto_increment ID, but even if it does that shouldn't matter since that value should be totally transparent – Explosion Pills May 29 '13 at 14:35
  • See, http://stackoverflow.com/questions/5924762/prevent-autoincrement-on-mysql-duplicate-insert. There will be a lot of attempts. It is not good. – Anton May 29 '13 at 14:46
  • What I'm saying is that the actual value of the auto_increment ID should not matter – Explosion Pills May 29 '13 at 14:49

1 Answers1

0

Change your id line to this --

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

You could stay with int(10), but I always find it best to make it possible for id's to grow to huge sizes just in case someday it's needed.

And after CHARSET=ut8, add --

AUTO_INCREMENT=1;

That way the table knows where to start incrementing from.

Zamphatta
  • 4,634
  • 8
  • 30
  • 34
  • How to do it without auto_increment? It will be increased all time even when the attempts to insert will be failed. It will be jump out some time. – Anton May 29 '13 at 14:47