在项目中用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函数的方式。