數據庫數字參考表的妙用

今天的主角就是數字參考表，什么是數字參考表？一個表中，存放了從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 ;</pre> 
主題就是數據庫中參考表的妙用，我們分三種需求來分別說明數據庫中參考表的使用，主要針對解決數據庫的“歷史問題”。

一、兩個事務同時鎖定一個不存在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 ;
 


原文地址: 數據庫數字參考表的妙用