mysql实现自增序列

在项目中用mysql作数据库。id策略用mysql的自增长。但是有些表优化后进行了分区。导致无法设置主键自增长，因此自实现了一个id生成的序列。

1.建了一张表存储表名和当前序列

2.三种实现方式

1）java同步

    public synchronized int nextInt(String tableKey) {
        SequenceExample example = new SequenceExample();
        example.createCriteria().andTableKeyEqualTo(tableKey);
        int id = 1;
        Sequence sequence = null;
        List<Sequence> list = sequenceMapper.selectByExample(example);
        if(list != null && list.size() > 0) {
            sequence = list.get(0);
            id = sequence.getCuid() + 1;
            sequence.setCuid(id);
            sequenceMapper.updateByExample(sequence, example);
        }else {
            sequence = new Sequence();
            sequence.setCuid(id);
            sequence.setTableKey(tableKey);
            sequenceMapper.insert(sequence);
        }

        return id;
    }

缺点是对所有操作都同步，效率有浪费。如果不是相同的表，那么他们的序列更新不存在竞争关系，不需要同步。

2）mysql行锁

public int nextInt(String tableKey) {
        // 获取mysql的行级锁
        sequenceMapper.getRowLock(tableKey);
        // 操作
        SequenceExample example = new SequenceExample();
        example.createCriteria().andTableKeyEqualTo(tableKey);
        int id = 1;
        Sequence sequence = null;
        List<Sequence> list = sequenceMapper.selectByExample(example);
        if(list != null && list.size() > 0) {
            sequence = list.get(0);
            id = sequence.getCuid() + 1;
            sequence.setCuid(id);
            sequenceMapper.updateByExample(sequence, example);
        }else {
            sequence = new Sequence();
            sequence.setCuid(id);
            sequence.setTableKey(tableKey);
            sequenceMapper.insert(sequence);
        }
        return id;
    }

可行，但是效率还是不够

3）mysql函数

BEGIN
DECLARE id INT DEFAULT 0;
SELECT cuid + 1 INTO id from uc_gen_number where table_key = tableKey for update;
IF id = 0 THEN 
SET id = 1;
INSERT INTO uc_gen_number (table_key, cuid) VALUES (tableKey, id);
ELSE UPDATE uc_gen_number SET cuid = id where table_key = tableKey;
END IF;
RETURN id;
END

可行，效率最好
最终选择了mysql函数的方式。