0
CREATE TABLE `contacts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) DEFAULT NULL,
`email` varchar(60) DEFAULT NULL,
`password` varchar(60) DEFAULT NULL,
`hash` varchar(60) DEFAULT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

I created the previous table. When I try to insert with the following code duplicated data, the instruction is ignored and not inserted as expected. However, the ID is auto incremented, producing gaps among the correct insertions of data. For example, inserting through PHP:

$sql = "INSERT IGNORE INTO `contacts`(`ID`, `name`, `email`, `password`, `date`) VALUES (NULL, '$nombre', '$email', '$password', NOW())";

I would like to prevent the ID from being auto incremented when a duplicated data is tried to be inserted, so I can keep the ordered enumeration in my DB. I am asking this only because I didn´t find this specific question been asked with the aforementioned target and using INSERT IGNORE INTO...

Armando Andres Flores
  • 139
  • 2
  • 9
  • 1
    (The duplicate is discussing this behavior with regards to transactions, not individual statements, but the principle is the same -- autoincrement values are not guaranteed to be sequential. In fact, some database clustering solutions _depend_ on leaving gaps.) –  Dec 03 '18 at 22:19
  • Even with gaps you will have an ordered list of auto increment values. Just you will happen to have gaps between certain values. – Shadow Dec 03 '18 at 22:56

0 Answers0