MySQL连续顺序行字段，即使在删除和插入时也是如此 [英] MySQL Contiguous Sequential Rows Field even on delete and insert

问题描述

我需要为表中的行提供一个连续的数字序列，并且我需要确保它始终是连续的，在insert上没有间隙，删除时我可以保留行间隙，但是在插入时我必须用新行.这样做的原因是，不同的系统必须将行记录一对一地排列.但是db可以由sql端的其他人以及通过应用程序来操作;我认为触发器将使我能够完成某些已更改的部分-但是如何实际确定我是否有缺口并执行此序列号的插入-即使我必须在单独的表中维护已删除的序列并进行管理也可以-无论表格如何操作，我都必须与另一个系统一一对应.

I need a sequential number sequence for the rows in a table and I need to ensure that it is always sequential with no gaps on insert , when deleted I can leave the row gap, but on insert I must fill the gaps with the new rows. The reason for this is a different system must line up one for one with the row records. Yet the db can be manipulated by others in both the sql end and also via an application ; I am thinking a trigger will allow me to accomplish the something changed part - but how to actually determine if I have gaps and perform the insert of this sequence number - even if I have to maintain the deleted sequences in a separate table and manage is fine - I am required to line up one for one with this other system no matter how the table gets manipulated .

随着行被删除，自动增量"字段将不起作用，下一个插入将是最后一个自动增量值.我需要在..处插入，或者保留该行并添加一个字段IsDeleted并将表强制为只读或不再有插入/删除操作..但是，该怎么做呢? 也许当插入行时，我可以将序列号设置在空白处(如果找到)或结束时(如果没有).

Auto Increment field will not work as a row gets deleted the next insert will be the last auto Increment value. I would need an insert at .. or perhaps keep the row and add a field IsDeleted and force the table as read only or no more inserts / deletes ..but how to do that? Perhaps when row is inserted I could set sequence number at gap if found or at end if not.

有人有做这种事情的经验吗?

Does somebody have experience doing this kind of thing ?

推荐答案

我知道这里有很多东西.我试图在代码中以及此处和那里很好地记录它.它使用存储过程.您自然可以拉出代码，而不使用该方法.它使用一个主表来容纳下一个可用的增量器.它使用安全的INNODB意向锁进行并发.它有一个重用表和存储的过程来支持它.

I know there is a lot here. I tried to document it rather well inside the code and here and there. It uses Stored Procedures. You can naturally pull the code out and not use that method. It uses a main table that houses next available incrementors. It uses safe INNODB Intention Locks for concurrency. It has a reuse table and stored procs to support it.

它始终不使用表myTable.此处显示的内容供您根据自己在问题下的评论进行想象.总结是，您知道在DELETE上将会有空白.您需要某种有序的方式来重用那些插槽，那些序列号.因此，当您DELETE行时，请相应地使用存储的proc来添加该数字.自然地，有一个存储的proc来获取下一个序列号以供重用和其他用途.

It does not in anyway use the table myTable. It is shown there for your own imagination based on comments under your question. The summary of that is that you know that you will have gaps upon DELETE. You want some orderly fashion to reuse those slots, those sequence numbers. So, when you DELETE a row, use the stored procs accordingly to add that number. Naturally there is a stored proc to get the next sequence number for reuse and other things.

出于测试目的，您的sectionType ='devices'

For the purposes of testing, your sectionType = 'devices'

最棒的是，它已经过测试！

And best of all it is tested!

模式:

create table myTable
(   -- your main table, the one you cherish
    `id` int auto_increment primary key, -- ignore this
    `seqNum` int not null, -- FOCUS ON THIS
    `others` varchar(100) not null
) ENGINE=InnoDB;

create table reuseMe
(   -- table for sequence numbers to reuse
    `seqNum` int not null primary key, -- FOCUS ON THIS
    `reused` int not null -- 0 upon entry, 1 when used up (reused)
    -- the primary key enforces uniqueness
) ENGINE=InnoDB;;

CREATE TABLE `sequences` (
    -- table of sequence numbers system-wide
    -- this is the table that allocates the incrementors to you
    `id` int NOT NULL AUTO_INCREMENT,
    `sectionType` varchar(200) NOT NULL,
    `nextSequence` int NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `sectionType` (`sectionType`)
) ENGINE=InnoDB;
INSERT sequences(sectionType,nextSequence) values ('devices',1); -- this is the focus
INSERT sequences(sectionType,nextSequence) values ('plutoSerialNum',1); -- not this
INSERT sequences(sectionType,nextSequence) values ('nextOtherThing',1); -- not this
-- the other ones are conceptuals for multi-use of a sequence table

存储过程:uspGetNextSequence

Stored Proc: uspGetNextSequence

DROP PROCEDURE IF EXISTS uspGetNextSequence;
DELIMITER $$
CREATE PROCEDURE uspGetNextSequence(p_sectionType varchar(200))
BEGIN
    -- a stored proc to manage next sequence numbers handed to you.
    -- driven by the simple concept of a name. So we call it a section type.
    -- uses SAFE INNODB Intention Locks to support concurrency
    DECLARE valToUse INT;

    START TRANSACTION;
    SELECT nextSequence into valToUse from sequences where sectionType=p_sectionType FOR UPDATE;
    IF valToUse is null THEN
        SET valToUse=-1;
    END IF;
    UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType;
    COMMIT; -- get it and release INTENTION LOCK ASAP
    SELECT valToUse as yourSeqNum; -- return as a 1 column, 1 row resultset
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspGetNextSequence('devices'); -- your section is 'devices'

调用uspGetNextSequence()之后，您有责任确保该序列#

After you call uspGetNextSequence() it is your RESPONSIBILITY to ensure that that sequence #

添加到myTable中(通过确认)，或者如果失败，则将其插入

is either added into myTable (by confirming it), or that if it fails, you insert it into

重用表，其中包含对uspAddToReuseList()的调用.并非所有插入都能成功.专注于这一部分.

the reuse table with a call to uspAddToReuseList(). Not all inserts succeed. Focus on this part.

由于此代码，由于

并发性，其他用户以及已通过的范围.因此，简单地说，如果插入失败，

concurrency, other users, and the range already passed by. So, simply, if the insert fails,

通过uspAddToReuseList()将数字放入reuseMe

put the number into reuseMe via uspAddToReuseList()

. .

存储过程:uspAddToReuseList:

Stored Proc: uspAddToReuseList:

DROP PROCEDURE IF EXISTS uspAddToReuseList;
DELIMITER $$
CREATE PROCEDURE uspAddToReuseList(p_reuseNum INT)
BEGIN
    -- a stored proc to insert a sequence num into the reuse list
    -- marks it available for reuse (a status column called `reused`)
    INSERT reuseMe(seqNum,reused) SELECT p_reuseNum,0; -- 0 means it is avail, 1 not
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspAddToReuseList(701); -- 701 needs to be reused

存储过程:uspGetOneToReuse:

Stored Proc: uspGetOneToReuse:

DROP PROCEDURE IF EXISTS uspGetOneToReuse;
DELIMITER $$
CREATE PROCEDURE uspGetOneToReuse()
BEGIN
    -- a stored proc to get an available sequence num for reuse
    -- a return of -1 means there aren't any
    -- the slot will be marked as reused, the row will remain
    DECLARE retNum int; -- the seq number to return, to reuse, -1 means there isn't one

    START TRANSACTION;

    -- it is important that 0 or 1 rows hit the following condition
    -- also note that FOR UPDATE is the innodb Intention Lock
    -- The lock is for concurrency (multiple users at once)
    SELECT seqNum INTO retNum 
    FROM reuseMe WHERE reused=0 ORDER BY seqNum LIMIT 1 FOR UPDATE;

    IF retNum is null THEN
        SET retNum=-1;
    ELSE 
        UPDATE reuseMe SET reused=1 WHERE seqNum=retNum; -- slot used
    END IF;
    COMMIT; -- release INTENTION LOCK ASAP

    SELECT retNum as yoursToReuse; -- >0 or -1 means there is none
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspGetOneToReuse();

存储过程:uspCleanReuseList:

Stored Proc: uspCleanReuseList:

DROP PROCEDURE IF EXISTS uspCleanReuseList;
DELIMITER $$
CREATE PROCEDURE uspCleanReuseList()
BEGIN
    -- a stored proc to remove rows that have been successfully reused
    DELETE FROM reuseMe where reused=1;
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspCleanReuseList();

存储过程:uspOoopsResetToAvail:

Stored Proc: uspOoopsResetToAvail:

DROP PROCEDURE IF EXISTS uspOoopsResetToAvail;
DELIMITER $$
CREATE PROCEDURE uspOoopsResetToAvail(p_reuseNum INT)
BEGIN
    -- a stored proc to deal with a reuse attempt (sent back to you)
    -- that you need to reset the number as still available, 
    -- perhaps because of a failed INSERT when trying to reuse it
    UPDATE reuseMe SET reused=0 WHERE seqNum=p_reuseNum;
END$$
DELIMITER ;
-- ****************************************************************************************
-- test:
call uspOoopsResetToAvail(701);

工作流程提示:

让 GNS 表示对uspGetNextSequence()的调用.

通过调用uspGetOneToReuse()

如果需要新的INSERT，请致电 RS :

When a new INSERTis desired, call RS:

A.如果 RS 返回-1，则没有任何可重用的内容，因此调用 GNS 返回N.如果您可以通过myTable.seqNum=N成功地INSERT进行确认，就可以完成.如果无法成功INSERT，请致电uspAddToReuseList(N).

A. If RS returns -1 then nothing is to be reused so call GNS which returns N. If you can successfully INSERT with myTable.seqNum=N with a confirm, you are done. If you cannot successfully INSERT it, then call uspAddToReuseList(N).

B.如果 RS 返回> 0，请注意您的插槽中有reuseMe.reused=1，这是一件值得记住的好事.因此，假定它处于成功重用的过程中.让我们将该序列号称为N.如果您可以成功地INSERT和myTable.seqNum=N进行确认，就可以完成.如果无法成功INSERT，请致电uspOoopsResetToAvail(N).

B. If RS returns > 0, note in your head that slot has reuseMe.reused=1, a good thing to remember. So it is assumed to be in the process of being successfully reused. Let's call that sequence number N. If you can successfully INSERT with myTable.seqNum=N with a confirm, you are done. If you cannot successfully INSERT it, then call uspOoopsResetToAvail(N).

当您认为可以安全地致电uspCleanReuseList()时，请这样做.在reuseMe表中添加DATETIME可能是一个好主意，表示从myTable开始的行何时被删除并导致reuseMe行获得其原始INSERT.

When you deem it safe to call uspCleanReuseList() do so. Adding a DATETIME to the reuseMe table might be a good idea, denoting when a row from myTable was orignally deleting and causing the reuseMe row to get its original INSERT.

这篇关于MySQL连续顺序行字段，即使在删除和插入时也是如此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！