今天的主角就是数字参考表，什么是数字参考表？一个表中，存放了从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

　　这样在做数据表的双向同步就产生了冲突。

　　我能想到的解决方案有以下几个：

　　1、值留一个表，取消另外一个表，这个是最简单最理想的方案了。不过理想和现实往往有很大差距。

　　由于两个表存在有一定差异性，不同团队的代码都对两个表有依赖关系，所以暂时还没法实施，但最终会这样（我希望最终会这样）。

　　2、取消auto_increment，程序能够产生唯一有意义的主键，现在还没有找到可用和可控制的唯一标识代替auto_increment。

　　由于是多个团队操作这两个表，程序生成自增长id也是不现实的。

　　3、使用主主复制，php和java分开在不通的master上写入，调整auto_increment的offset，来保证每个服务器的auto_increment和对方是不冲突的。

　　这也是我们现在使用的方法，不过问题在于启用了master-master的双主写入，在架构维护上有一些问题，比如没法做到HA，数据库迁移或者操作的时候限制更多。

　　4、也就是我们今天说的参考表。

　　建立一个参考表，php和java先去参考表获取一个没有使用的id，然后把参考表上这个id的状态置为已使用，然后使用获取的id插入到自己操作的表中。

　　这个方案相对修改代码较少，对数据库架构也没有影响。

　　三、在某些查询时候需要使用的参考数据

　　下面的方法主要吸收与姜老的 《mysql技术内幕 sql编程》

　　1、查询一个字段连续的值有哪些，这个需要数字参考表。

　　这个查询和数字参考表没太大关系，主要使用的是临时生成的行号，不过特别有趣，分享给大家。

create table t(a int unsigned not null primary key); 　　insert into t select 1; 　　insert into t select 2; 　　insert into t select 3; 　　insert into t select 100; 　　insert into t select 101; 　　insert into t select 103; 　　insert into t select 104; 　　insert into t select 105;

　　求出哪些是连续的，并给出连续的范围。

SELECT 　　MIN(a) start_range, 　　MAX(a) end_range 　　FROM 　　(SELECT 　　a, 　　rn, 　　a-rn AS diff 　　FROM 　　(SELECT 　　a, 　　@a := @a + 1 rn 　　FROM 　　t , 　　(SELECT 　　@a :=0) AS a) AS b) AS c 　　GROUP BY diff ;

　　2、使用数字参考表可以更快的预建立时间维度表

DELIMITER $$ 　　USE `test`$$ 　　DROP PROCEDURE IF EXISTS `sp_createTimeDim`$$ 　　CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createTimeDim`(START DATE,END DATE) 　　BEGIN 　　SELECT DATE_ADD(START,INTERVAL id-1 DAY) FROM ref_number WHERE id<=DATEDIFF(END,START)+1; 　　END$$ 　　DELIMITER ;