【php】mysql全局ID生成方案，phpmysql全局id

CREATE TABLE `SEQUENCE` (
    `name` varchar(30) NOT NULL COMMENT '分表的表名',
    `gid` bigint(20) NOT NULL COMMENT '最大全局id',
    PRIMARY KEY (`name`)
) ENGINE=innodb

update sequence set gid = 203 where name = 'users' and gid < 203;

//$name 表名
function next_id_db($name){
    //获取数据库全局sequence对象
    $seq_dao = Wk_Sequence_Dao_Sequence::getInstance();
    $threshold = 100; //最大尝试次数
    for($i = 0; $i < $threshold; $i++){
        $last_id = $seq_dao->get_seq_id($name);//从数据库获取全局id
        $id = $last_id +1;
        $ret = $seq_dao->set_seq_id($name, $id);
        if($ret){
            return $id;
            break;
        }
    }
    return false;
}

//使用redis实现
//$name 为 逻辑表名
function next_id_redis($name){
    $redis = Wk_Redis_Util::getRedis();//获取redis对象
    $seq_dao = Wk_Sequence_Dao_Sequence::getInstance();//获取存储全局id数据表对象
    if(!is_object($redis)){
        throw new Exception("fail to create redis object");
    }
    $max_times = 10; //最大执行次数 避免redis不可用的时候 进入死循环
    while(1){
        $i++;
        //检测key是否存在，相当于检测锁是否存在
        $ret = $redis->setnx("sequence_{$name}_flag",time());
        if($ret){
            break;
        }
        if($i > $max_times){
            break;
        }
        $time = $redis->get("sequence_{$name}_flag");
        if(is_numeric($time) && time() - $time > 1){//如果循环等待时间大于1秒，则不再等待。
            break;
        }
    }
    $id = $redis->incr("sequence_{$name}");
    //如果操作失败，则从sequence表中获取全局id并加载到redis
    if (intval($id) === 1 or $id === false) {
        $last_id = $seq_dao->get_seq_id($name);//从数据库获取全局id
        if(!is_numeric($last_id)){
            throw new Exception("fail to get id from db");
        }
        $ret = $redis->set("sequence_{$name}",$last_id);
        if($ret == false){
            throw new Exception("fail to set redis key [ sequence_{$name} ]");
        }
        $id = $redis->incr("sequence_{$name}");
        if(!is_numeric($id)){
            throw new Exception("fail to incr redis key [ sequence_{$name} ]");
        }
    }
    $seq_dao->set_seq_id($name, $id);//把生成的全局id写入数据表sequence
    $redis->delete("sequence_{$name}_flag");//删除key，相当于释放锁
    $db = null;
    return $id;
}

function next_id($name){
    try{
        return $this->next_id_redis($name);
    }
    catch(Exception $e){
        return $this->next_id_db($name);
    }
}

CREATE TABLE Tickets64 (
            id bigint(20) unsigned NOT NULL auto_increment,
            stub char(1) NOT NULL default '',
            PRIMARY KEY  (id),
            UNIQUE KEY stub (stub)
    ) ENGINE=MyISAM

+-------------------+------+
| id                | stub |
+-------------------+------+
| 72157623227190423 |    a |
+-------------------+------+

REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();

TicketServer1:
auto-increment-increment = 2
auto-increment-offset = 1

TicketServer2:
auto-increment-increment = 2
auto-increment-offset = 2