Início / dba / Perguntas / 337781
Accepted
Max Muster
Asked: 2024-03-18 00:11:20 +0800 CST

Como AUTO_INCREMENT somente no INSERT?

  • 772

Eu tenho uma dessas situações:

CREATE TABLE table (
    `col1` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `col2` VARCHAR(512) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`col1`) USING BTREE,
    UNIQUE INDEX `col2` (`col2`) USING BTREE,
[...]

e então eu tenho essa declaração:

INSERT INTO table (col2, col3) VALUES ( val2, val3) ON DUPLICATE KEY UPDATE (col2=val2, col3=val3)

se houver uma chave duplicada em col2 UPDATE irá incrementar automaticamentecol1

Pergunta existe uma maneira de fazer um incremento automático de tabela apenas em INSERT ?

mysql

2 respostas

  1. Você executa INSERT INTO .. ON DUPLICATE KEY UPDATE.

    Em primeiro lugar INSERT INTOé executado. Sua parte de valores não contém o valor da coluna de chave primária que é definida como incrementada automaticamente. Portanto, um novo valor incrementado automaticamente é gerado para esta linha recém-inserida.

    Se os valores fornecidos na consulta não causarem violação duplicada, a linha com o valor de incremento automático recém-gerado será inserida.

    Se os valores fornecidos na consulta causarem violação duplicada, a ação UPDATE especificada na parte ON DUPLICATE KEY UPDATE da consulta será executada.

    Mas em ambos os casos um novo valor autoincrementado já foi gerado . Portanto, em ambos os casos, a próxima consulta gerará o próximo valor incrementado automaticamente. Você não pode "não gerar se ON DUPLICATE KEY UPDATE foi acionado".

    • 2
  2. Best Answer

    No modo de bloqueio padrão do InnoDB, a resposta é não. Os valores de aumento automático são gerados rapidamente, antes que as verificações de chaves duplicadas sejam feitas. Uma vez liberado o bloqueio de auto-inc da tabela, não há "desfazer" e os valores gerados não serão reutilizados.

    No entanto, você pode usar um modo de bloqueio diferente, era assim que o InnoDB funcionava anos atrás. O bloqueio automático de inclusão da tabela é mantido até o final da instrução INSERT, incluindo quaisquer verificações de chaves duplicadas e atualizações subsequentes. Se os novos valores de inclusão automática não foram usados, eles poderão ser preservados para uso em alguma outra sessão.

    Demonstração:

    Alterei o modo de bloqueio /etc/my.cnfe reiniciei meu processo do servidor MySQL. Esta opção é global, somente leitura e só pode ser alterada no arquivo de opções.

    [mysqld]
...
innodb_autoinc_lock_mode=0

    Então eu testei:

    mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          0 |
+----------------------------+

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `col1` int unsigned NOT NULL AUTO_INCREMENT,
  `col2` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `col3` int DEFAULT NULL,
  PRIMARY KEY (`col1`) USING BTREE,
  UNIQUE KEY `col2` (`col2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> insert into mytable set col2='col2', col3=5 
  on duplicate key update col2=values(col2), col3=values(col3);
Query OK, 2 rows affected, 2 warnings (0.01 sec)

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `col1` int unsigned NOT NULL AUTO_INCREMENT,
  `col2` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `col3` int DEFAULT NULL,
  PRIMARY KEY (`col1`) USING BTREE,
  UNIQUE KEY `col2` (`col2`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

    Observe que a tabela AUTO_INCREMENTnão avançou, como aconteceria se innodb_autoinc_lock_modeo valor padrão fosse 2.

    Em geral, o modo de bloqueio padrão é preferido, porque significa que o bloqueio da tabela é mantido mais brevemente, e isso tem sido importante quando múltiplas sessões são inseridas na mesma tabela. Ele permite maior rendimento em paralelo.

    Leia mais sobre os modos de bloqueio do InnoDB aqui: https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

    • 2

relate perguntas