mysql - 使用触发器自动增量列后不连续

Question

我有这段代码，它创建表 Nf0 和 NF1 并更新 NF1，因为如果 NF0 中插入了值，则触发 NF1，但在 NF0 中插入值后，NF1 中的自动增量不是连续的，而是 1,2,3， 4 是 1,2,4,5。

有人可以告诉我如何使用触发器自动使 Ner 列连续吗？

 drop database tron;
 create database tron;
 use tron;

CREATE TABLE IF NOT EXISTS `TRON`.`NF0` (
`Numurs` varchar(45) ,
`Speletajs` VARCHAR(45) NOT NULL,
`DzimsanasDatums` date,
`Vecums` int(8) NOT NULL,
`Valsts` VARCHAR(45) not NULL,
`ValstsKods` VARCHAR(45) not NULL,
`AugumsCollas` varchar(45) not NULL,
`SvarsMarcinas` varchar(45) not null,
`Pozicija` text ,
`Klubs` varchar(45) not null,
`NospeletasSpeles` varchar(45) not null,
`PunktiAtlecosasPiespeles` text not null,
PRIMARY KEY (Speletajs,Klubs))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

create table if not exists TRON.numbers
AS  SELECT 1 n UNION ALL SELECT 2  UNION ALL 
SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
SELECT 15  UNION ALL SELECT 16  UNION ALL SELECT 17  UNION ALL
SELECT 18   UNION ALL SELECT 19  UNION ALL SELECT 20;

create table if not exists TRON.NF1 like NF0;
alter table NF1
drop Speletajs,
drop Klubs,
drop PunktiAtlecosasPiespeles,
    add Vards varchar(45) not null after Numurs,
    add Uzvards varchar(45) not null after Vards,
    add RegionsPilseta varchar(45) not null after SvarsMarcinas,
    add Simbols varchar(45) not null after RegionsPilseta,
    add Punkti varchar(45) not null ,
    add Atlecosas varchar(45) , 
    add Piespeles varchar(45) ,
    add Ner int not null primary key auto_increment first;
 UPDATE `nf1` SET Vecums = DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-   TO_DAYS(`DzimsanasDatums`)), '%Y' )+0;

 delimiter $
 CREATE TRIGGER `NF1` after insert 
ON `NF0` FOR EACH ROW BEGIN

INSERt INTO nf1 (
Ner,    
Numurs,
Vards,
Uzvards, 
DzimsanasDatums, 
Vecums, 
Valsts, 
ValstsKods, 
AugumsCollas, 
SvarsMarcinas, 
RegionsPilseta,
Simbols, 
Pozicija,
NospeletasSpeles,
Punkti,
Atlecosas,
Piespeles)
SELECT
'0',
'',
'',
'',
new.DzimsanasDatums as DzimsanasDatums,
new.Vecums as Vecums,
new.Valsts as Valsts,
new.ValstsKods as ValstsKods,
new.AugumsCollas as AugumsCollas,
new. SvarsMarcinas as SvarsMarcinas,
'',

'',
SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.Pozicija,' ',n),' ',-1) as Pozicija,
'',
'',
'',
''
FROM numbers n
WHERE LENGTH(NEW.Numurs) - LENGTH(REPLACE(NEW.Numurs,' ','')) >= n.n-1;
UPDATE `nf1` SET Vecums = DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(`DzimsanasDatums`)), '%Y' )+0;

  end$$

 delimiter ;

 use TRON;
 insert into nf0 values ('23 6','LeBron James', '1984:12:30','31','United   States of America','USA',   '80','  250','  SF PF','    Cleveland Cavaliers,Miami Heat','   76 79', '25.3 7.4 6.8 26.7 7.5 7'),
                    ('23 6','Bron James',   '1984:12:30','31','United States of America','USA', '80','  250','  SF PF','    Cleveland Cavaliers,Miami Heat','   76 79', '25.3 7.4 6.8 26.7 7.5 7');


 select * from nf1;

Answer 1

如果您绝对需要复制数据(已经是一个可疑的想法)，那么请在应用程序的业务层中执行此操作，而不是将其埋藏在会被遗忘的触发器中。

一旦该逻辑进入您的业务层，您就可以根据需要确定列的数值(只需删除 auto_increment 属性)。 auto_increment 不保证顺序数字，也从未打算这样做。