时间戳

首页 > 解决方案 > 在 Mysql 中使用重复值停止自动递增

mysql - 在 Mysql 中使用重复值停止自动递增

问题描述

我有一个名为 comment 的表，其中有一个带有唯一键的文本列和一个带有自动增量的主键 id 列，如果插入重复的文本它会给出错误，但它也会增加我的 id 列。有没有办法在出现重复值时停止自动递增，并且仅在插入记录时才递增？我已经尝试过 innodb_autoinc_lock_mode=0 但仍然无法正常工作，并且还尝试过插入忽略但仍然计数器上升。我正在使用 Mysql 5.7。谢谢

标签： mysqlmysql-workbenchmysql-error-1064

解决方案

您可以在 /etc/my.cnf 中设置innodb_autoinc_lock_mode。在这个 moe 中它很适合你。另见https://mariadb.com/kb/en/auto_increment-handling-in-innodb/

在 mysqld 部分的配置中添加此行并重新启动 mysql 服务

[mysqld]
innodb_autoinc_lock_mode       = 0

查看设置了哪种模式

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

样本

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]>

推荐阅读