通过hash计算,代码如下:
1 定义基本sql、常量
/**
* shard 4 databases. cir_0, cir_1, cir_2, cir_3
*/
private static final int CIRCLE_SHARD_DB_COUNT = 4;
/**
* shard 64 tables. cir_base_0~15
*/
private static final int CIRCLE_SHARD_TB_COUNT = 16;
private static final int CIRCLE_SHARD_COUNT = CIRCLE_SHARD_DB_COUNT * CIRCLE_SHARD_TB_COUNT;
private static final String COLUMN_CID = "column_cid";
private static final String COLUMN_UID = "column_uid";
private static final String CIRCLE_DB_HASH = "$dbhash";
private static final String CIRCLE_TB_HASH = "$tbhash";
private static final String CIRCLE_SCHEMA = "db_cir_" + CIRCLE_DB_HASH + "tb.cir_base_" + CIRCLE_TB_HASH;
private static final String QUERY_LATEST_CIRCLE_IDS_BY_UID
= "SELECT " + COLUMN_CID + " FROM " + CIRCLE_SCHEMA + " WHERE " + COLUMN_UID + " = ? AND " +
COLUMN_PRIVACY + " = 1" +
" ORDER BY " + COLUMN_CID + " DESC LIMIT ?";
2 根据基本sql,获取分库分表后的sql
public String getSqlString(String baseSql, long ownerid){
int hash = HashUtil.hash(ownerid, CIRCLE_SHARD_COUNT);
int dbhash = hash / CIRCLE_SHARD_TB_COUNT;
int tbhash = hash % CIRCLE_SHARD_TB_COUNT;
String sql = baseSql.replace(CIRCLE_DB_HASH, String.valueOf(dbhash));
sql = sql.replace(CIRCLE_TB_HASH, String.valueOf(tbhash));
LOGGER.debug("sql={}", sql);
return sql;
}
3 工具类
package com.mico.util.misc;
import com.mico.util.codec.Encoder;
import java.util.zip.CRC32;
/**
*
*/
public final class HashUtil {
private static final int DEFAULT_NUM = 8;
private static final ThreadLocal<CRC32> CRC32_HOLDER = new ThreadLocal<CRC32>() {
@Override
protected CRC32 initialValue() {
return new CRC32();
}
};
public static final long crc32(String id) {
CRC32 crc = CRC32_HOLDER.get();
crc.reset();
crc.update(Encoder.encodeString(id));
return crc.getValue();
}
public static final int hash(long id) {
return hash(String.valueOf(id));
}
public static final int hash(long id, int num) {
return hash(String.valueOf(id), num);
}
public static final int hash(String id) {
return hash(id, DEFAULT_NUM);
}
public static final int hash(String id, int num) {
long h = crc32(id);
h = Math.abs(h);
int hash = (int) (h / num % num);
return hash;
}
}
4 main方法
public static void main(String[] args) {
int hash = HashUtil.hash("1539425379524502548", CIRCLE_SHARD_COUNT);
int dbhash = hash / CIRCLE_SHARD_TB_COUNT;
int tbhash = hash % CIRCLE_SHARD_TB_COUNT;
System.out.println(dbhash);
System.out.println(tbhash);
}