mysql - 不小心从具有主键的表中删除了行

Question

如果你“不小心”(温柔一点，今天早上真辛苦)从一个有主键的表中删除一行，是否可以用它的前一个键重新插入记录，即使表自动递增主键？

Answer 1

TSQL 我有一个非常奇怪的环境 - 在某些方面非常受限，而在其他方面很遗憾地开放。不允许插入已使用的 PK 值，必须重新创建整个表...

/****
create protoTable w/ same structure as your mainTable that has the data you are trying to fix in this example the fieldname of the primary key is FldPK
assumption here is that your primary key is getting auto incremented
get a list of the fields in the mainTable that are NOT NULL.
in this example those fields are <not null fields>
get a list of all fields in the mainTable
in this example <all fields>, rather than spell out the fields. DO NOT INCLUDE the primary key field name
***/
declare @x int, @y int, @iLast int
select @iLast = (select MAX( FldPK ) from mainTable)
set @x = 1
while @x <= @iLast
begin
  select @y = (select COUNT(*) from mainTable where FldPK = @x)
  if @y = 1
    begin
      insert into protoTable(<all fields>)
        select <all fields> from mainTable where FldPK = @x
    end
  else
    begin
      insert into protoTable (<not null fields> )values('N','xyz'+convert(varchar,@x)) /*or whatever values are valid to fulfill not null*/
      /* this is where you keep one or more of the missing rows to update later with the lost data */
      if @x <> 126
        begin
          delete protoTable where FldPK = @x
        end
    end
  set @x=@x+1
end

然后将mainTable重命名为backup，将protoTable重命名为mainTable

第一个