mysql中连续插入行_MySQL连续顺序行字段,即使在删除和插入时也...

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

它无论如何都不使用表myTable.此处显示的内容是您根据自己的疑问根据自己的想象而提出的.总结是,您知道DELETE上会有空白.您希望以某种有序的方式重用那些插槽,那些序列号.因此,当您删除一行时,请相应地使用存储的proc来添加该数字.自然地,有一个存储的proc来获取下一个序列号以供重用和其他用途.

为了进行测试,您的sectionType =’devices’

最好的是经过测试！

架构：

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

存储的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()之后,您有责任确保该序列#

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

重用表,并带有对uspAddToReuseList()的调用.并非所有插入都能成功.专注于这一部分.

因为使用此代码,您不能将其“放回”到序列表中,原因是

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

通过uspAddToReuseList()将数字放入reuseMe

.

.

.

存储过程：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：

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：

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：

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(),让RS表示重用序列.

需要新的INSERT时,请致电RS：

答：如果RS返回-1,则不会再使用任何东西,因此请调用GNS,它返回N.如果可以成功通过myTable.seqNum = N INSERT进行确认,则操作完成.如果无法成功插入,请调用uspAddToReuseList(N).

B.如果RS返回> 0,在您的脑海中请注意插槽已使用了复用Me.reused = 1,这是一件值得记住的好事.因此,假定它处于成功重用的过程中.让我们将该序列号称为N.如果您可以成功地使用myTable.seqNum = N进行确认并成功插入,则操作完成.如果无法成功插入,请调用uspOoopsResetToAvail(N).

如果您认为可以安全地调用uspCleanReuseList(),则可以这样做.向DreuseMe表中添加一个DATETIME可能是一个好主意,它表示何时将myTable中的行原始删除,并使REUSEMe行获得其原始INSERT.