stop Auto increment with duplicates value in Mysql

user_14040567 0
Viewed 193

I have a table called comment in which there is a text column with unique key and a priamry key id column with a auto increment if a duplicate text is inserted it gives an error but it also increment my id column. is there a way to stop auto increment while duplicates value occurs and only increment if a record is inserted? I have tried innodb_autoinc_lock_mode=0 but still not working and also tried insert ignore but still counter goes up. I am using Mysql 5.7. Thanks

mysql-error-1064 mysql-workbench mysql
1 Answers

You can set the innodb_autoinc_lock_mode in your /etc/my.cnf. In this moe it works fine for you. see also https://mariadb.com/kb/en/auto_increment-handling-in-innodb/

Add this lines in the configuration in the mysqld section and restart the mysql service

[mysqld]
innodb_autoinc_lock_mode       = 0

See which Mode is set

MariaDB [(none)]> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 0     |
+--------------------------+-------+
1 row in set (0.00 sec)

Sample

MariaDB [bernd]> CREATE TABLE IF NOT EXISTS `docs` (
    ->   `id` int(6) unsigned NOT NULL AUTO_INCREMENT ,
    ->   `rev` int(3) unsigned NOT NULL,
    ->   `content` varchar(200) NOT NULL,
    ->   PRIMARY KEY (`id`,`rev`),
    ->   unique KEY (content)
    -> ) DEFAULT CHARSET=utf8;
/*INSERT INTO `docs` Query OK, 0 rows affected (0.01 sec)

MariaDB [bernd]> /*INSERT INTO `docs` ( `rev`, `content`) VALUES
   /*>   ( '1', 'The earth is flat'),
   /*>   ( '1', 'One hundred angels can dance on the head of a pin'),
   /*>   ( '2', 'The earth is flat and rests on a bull\'s horn'),
   /*>   ( '3', 'The earth is like a ball.');*/
MariaDB [bernd]>
MariaDB [bernd]> INSERT IGNORE INTO `docs` ( `rev`, `content`) VALUES
    ->   ('1', 'The earth is flat'),
    ->   ('1', 'One hundred angels can dance on the head of a pin'),
    ->   ('2', 'The earth is flat and rests on a bull\'s horn'),
    ->   ('2', 'The earth is flat and rests on a bull\'s horn'),
    ->   ('3', 'X The earth is like a ball.');
  INSQuery OK, 4 rows affected, 1 warning (0.01 sec)
Records: 5  Duplicates: 1  Warnings: 1

MariaDB [bernd]>   INSERT IGNORE INTO `docs` ( `rev`, `content`) VALUES
    ->   ('5', 'The earth is flat type');
Query OK, 1 row affected (0.00 sec)

MariaDB [bernd]> SELECT * from DOCS ORDER by id;
ERROR 1146 (42S02): Table 'bernd.DOCS' doesn't exist
MariaDB [bernd]> SELECT * from docs ORDER by id;
+----+-----+---------------------------------------------------+
| id | rev | content                                           |
+----+-----+---------------------------------------------------+
|  1 |   1 | The earth is flat                                 |
|  2 |   1 | One hundred angels can dance on the head of a pin |
|  3 |   2 | The earth is flat and rests on a bull's horn      |
|  4 |   3 | X The earth is like a ball.                       |
|  5 |   5 | The earth is flat type                            |
+----+-----+---------------------------------------------------+
5 rows in set (0.00 sec)

MariaDB [bernd]>
user_52472790
Related