数据库数字参考表的妙用

今天的主角就是数字参考表，什么是数字参考表？一个表中，存放了从1开始 连续到很大值的数字的表，我们称为数字参考表。

DROP TABLE IF 

EXISTS ref_number;
　　CREATE TABLE `ref_number` (
　　`id` INT(11) NOT NULL AUTO_INCREMENT,
　　`status` TINYINT(3) DEFAULT 0 NOT NULL,
　　PRIMARY KEY (`id`)
　　) ENGINE=INNODB DEFAULT CHARSET=utf8

#填充数据的存储过程

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `sp_pCreateNums`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pCreateNums`( cnt INT 

UNSIGNED) 

BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
TRUNCATE TABLE ref_number;
WHILE s <= cnt DO
BEGIN
INSERT INTO ref_number(id) SELECT s;
SET s= s+1;
END;
END WHILE;
END$$
DELIMITER ;

主题就是数据库中参考表的妙用，我们分三种需求来分别 说明数据库中参考表的使用，主要针对解决数据库的“历史问题”。

一、 两个事务同时锁定一个不存在id导致的死锁

两个事务同时向数据库插入记 录，插入的id是一样的。插入前查询表中是否存在这个id，如果不存在，那么会 给这个id上锁。mysql允许多个事务给同一条不存在的记录上锁，

然后在 插入记录，这个时候就产生冲突和死锁。

解决这个问题的方法还是挺多。

方案一：有可能的话，尽量使用auto_increment。

方案二：如果 不能使用auto_increment，程序尽量维护这个id的唯一性，不要重复使用。

方案三：引入参考表。

如果业务表不能建立auto_increment，也 不方便做一个算法来维护这个唯一性的列，那么我们建立一个数字参考表，如开 始建立的ref_number表。

id列对应业务的id列，status对应这个列是否被 使用，0为未使用，1为使用，修改业务表之前先从数字参考表中获取一个未使用 的id，

然后修改参考表的状态值，在使用这个值插入到表中，这样就不会 因为两个事务锁住同一个不存在的id而产生死锁。

二、两张同时有写入的 表之间数据同步，如何处理自增长主键的冲突问题。

这个问题在特殊的情 况下才会出现，主要是前期规划和后期需求的冲突导致的。

首先描述一下 需求和环境：

一个数据库实例里有两个数据库(schema)，两个数据库内分 别有两个表。php.test和java.test。

test表大概如下：

CREATE 

TABLE `test` (
　　`id` int(11) NOT NULL AUTO_INCREMENT,
　　`status` tinyint(3) DEFAULT NULL,
　　PRIMARY KEY (`id`)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个schema中表结构大 致一样，记录也是一样的，只是字段多少而已。现在的问题是多个团队和功能会 往这两个表插入数据，还有程序需要对这两个表做双向同步，保证他们数据一致 性。

现在我们模拟插入两条数据。

insert into php.test 

values ('','mysqlops');结果 1，mysqlops
　　insert into java.test values ('','mysqlops');结果 1，

mysqlops

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索存储过程
， table
， 数字
， default
， procedure
参考
数据库参考文献、数据库设计参考文献、数据库论文参考文献、mysql数据库参考文献、oracle数据库参考文献，以便于您获取更多的相关知识。