记录:280
场景:在实际开发中,开发文档中的建表信息以表格的方式提供,包括字段名称、字段类型、字段注释、是否为空等。本例就是先把表格信息转换为约定格式的txt文件,在使用Java代码生成完整的DRDS建表语句(PolarDB-X建表语句)。
版本:Spring Boot 2.6.3
名词:
DRDS:Distributed Relational Database Service,分布式关系型数据库服务。
PolarDB-X:阿里巴巴自主设计研发的高性能云原生分布式数据库产品,为用户提供高吞吐、大存储、低延时、易扩展和超高可用的云时代数据库服务。
DRDS是PolarDB-X更名前版本。主要就是一个阿里研发的一个分布式数据库解决方案。
经历:
2018年,小北在项目中使用DRDS。感觉挺溜的,也用上了分布式数据库。用着用着感觉坑也不少。
2022年,此刻,在官网找相关资料的话,搜索PolarDB-X,才比较快捷找到相关文档。
概念:
广播表:广播表是指将这个表复制到每个分库上,在分库上通过同步机制实现数据一致,有秒级延迟。
单库单表:建一张单库单表,不做任何拆分。
分库不分表:建一张表,只分库不分表。
分库分表:建一张表,既分库又分表。
详细解释,必须移步官网:https://www.aliyun.com/,搜索:PolarDB-X。
一、业务场景
比如A公司,选型使用分布式关系型数据库,需要8个分库。分库序号从0到7共计8个库。
1.广播表
建一张广播表,那么这个表会落在0库上,且会复制一份数据到每个分库上。在写SQL时候关联这张表,实际就在每个库内部关联。没有跨库关联。一般,数据量比较小表会建立成这样表,比如常量表。
2.单库单表
建一张单库单表,此表不做任何拆分,会落在0库上。一般会存放一些数据量不是很大数据,比如计算结果数据。
3.分库不分表
建一张分库不分表的表,只分库不分表。在8个库都会有一张这个表,从DRDS路由到实际RDS中,是根据分库字段使用哈希来计算。
4.分库分表
建一张表,既分库又分表。根据分表原则,比如使用MMDD分表分成365,那么就是8个库中,每个库都有365张表。
以上,在使用时,基本上无感,如果是分库不分表,那么强烈推荐查询SQL中的条件中至少加上分库字段。如果是分库分表,那么强烈推荐查询SQL中的条件中至少加上分库字段,推荐加上分表字段。以此提高查询效率,否则数据量特别大的话,容易卡主数据库。
二、案例场景
1.广播表
1.1开发文档中以表格方式提供建表信息。
1.2.手动转换为txt文件,文件名:T_CONSTANT.txt,格式约定如下:
第一行为,表名##表名称#表类型。
第二行开始,每行:字段名称##字段类型##字段非空##字段注释##字段主键或者索引。
T_CONSTANT##常量表##广播表
ID##BIGINT(16)##Y##唯一标识##primary##N
CONSTANT_VALUE##VARCHAR(32)##Y##值##N##N
CONSTANT_NAME##VARCHAR(128)##Y##名称##N##N
1.3执行代码,自动生成sql文件,文件名:T_CONSTANT.sql,生成结果。
create table T_CONSTANT (
ID BIGINT(16) NOT NULL COMMENT '唯一标识',
CONSTANT_VALUE VARCHAR(32) NOT NULL COMMENT '值',
CONSTANT_NAME VARCHAR(128) NOT NULL COMMENT '名称',
PRIMARY KEY(ID)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '常量表' broadcast;
2.单库单表
2.1开发文档中以表格方式提供建表信息。
2.2 手动转换为txt文件,文件名:T_USER_STAT.txt,格式约定如下:
第一行为,表名##表名称#表类型。
第二行开始,每行:字段名称##字段类型##字段非空##字段注释##字段主键或者索引。
T_USER_STAT##用户统计##单库表
ID##BIGINT(16)##Y##唯一标识##primary##N
STAT_VALUE##DECIMAL(8,2)##Y##统计值##N##N
STAT_NAME##VARCHAR(64)##Y##统计名称##index##N
2.3 执行代码,自动生成sql文件,文件名:T_USER_STAT.sql,生成结果。
create table T_USER_STAT (
ID BIGINT(16) NOT NULL COMMENT '唯一标识',
STAT_VALUE DECIMAL(8,2) NOT NULL COMMENT '统计值',
STAT_NAME VARCHAR(64) NOT NULL COMMENT '统计名称',
PRIMARY KEY(ID)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '用户统计' ;
create index IDX_T_USER_STAT_STAT_NAME on T_USER_STAT (STAT_NAME);
3.分库不分表
3.1 开发文档中以表格方式提供建表信息。
3.2 手动转换为txt文件,文件名:T_USER.txt,格式约定如下:
第一行为,表名##表名称#表类型。
第二行开始,每行:字段名称##字段类型##字段非空##字段注释##字段主键或者索引。
T_USER##用户信息##分库不分表
ID##BIGINT(16)##Y##唯一标识##primary##N
USER_NO##VARCHAR(64)##Y##用户编号##index##N
REGION_NO##VARCHAR(32)##Y##区域编码,约定为分库字段##N##dbpartition
3.3 执行代码,自动生成sql文件,文件名:T_USER.sql,生成结果。
create table T_USER (
ID BIGINT(16) NOT NULL COMMENT '唯一标识',
USER_NO VARCHAR(64) NOT NULL COMMENT '用户编号',
REGION_NO VARCHAR(32) NOT NULL COMMENT '区域编码,约定为分库字段',
PRIMARY KEY(ID)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '用户信息' dbpartition BY HASH(REGION_NO);
create index IDX_T_USER_USER_NO on T_USER (USER_NO);
4.分库分表
4.1 开发文档中以表格方式提供建表信息。
4.2 手动转换为txt文件,文件名:T_DATA.txt,格式约定如下:
第一行为,表名##表名称#表类型。
第二行开始,每行:字段名称##字段类型##字段非空##字段注释##字段主键或者索引。
T_DATA##数据表##分库分表
ID##BIGINT(16)##Y##唯一标识##primary##N
USER_NO##VARCHAR(64)##Y##用户编号##index##N
ACTION_DATE##DATE##Y##数据日期,约定为分表字段##N##tbpartition
REGION_NO##VARCHAR(32)##Y##区域编码,约定为分库字段##N##dbpartition
4.3 执行代码,自动生成sql文件,文件名:T_DATA.sql,生成结果。
create table T_DATA (
ID BIGINT(16) NOT NULL COMMENT '唯一标识',
USER_NO VARCHAR(64) NOT NULL COMMENT '用户编号',
ACTION_DATE DATE NOT NULL COMMENT '数据日期,约定为分表字段',
REGION_NO VARCHAR(32) NOT NULL COMMENT '区域编码,约定为分库字段',
PRIMARY KEY(ID)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '数据表' dbpartition BY HASH (REGION_NO) tbpartition BY MMDD (ACTION_DATE) tbpartitions 365;
create index IDX_T_DATA_USER_NO on T_DATA (USER_NO);
三、使用类
1.读文件操作
java.lang.AutoCloseable,接口。
java.io.Closeable,接口,继承AutoCloseable。
java.lang.Readable,接口。
java.io.Reader,抽象类,实现Readable接口和Closeable。
java.io.BufferedReader,实现类,实现Reader抽象类。
java.io.InputStreamReader,实现类,读输入流,Reader抽象类。
java.io.FileReader,实现类,读文件,继承InputStreamReader。
new BufferedReader(new FileReader(fileName))。
2.写文件操作
java.lang.AutoCloseable,接口。
java.io.Closeable,接口,继承AutoCloseable。
java.io.Flushable,接口。
java.lang.Appendable,接口。
java.io.Writer,抽象类,实现Appendable, Closeable, Flushable。
java.io.BufferedWriter,实现类,实现Writer抽象类。
java.io.OutputStreamWriter,实现类,写输出流,Writer抽象类。
java.io.FileWriter,实现类,写文件,继承OutputStreamWriter。
new BufferedWriter(new FileWriter(fileName, true));
3.操作ArrayList
java.lang.Iterable,接口。
java.util.Collection,接口,继承Iterable。
java.util.List,接口,继承Collection。
java.util.AbstractCollection,抽象类,实现Collection。
java.util.AbstractList,抽象类,继承AbstractCollection,实现List。
java.util.ArrayList,一个Resizable-array。
4.Collections
java.util.Collections,对集合相关操作。
5.File操作
java.io.Serializable,接口。
java.lang.Comparable,接口。
java.io.File,实现类,实现Serializable和Comparable接口。
四、代码
1.读文件
读取文件,逐行读取,每读取一行,立即解析,根据分隔符##,分割成多个String字符串,存放在一个List<String>中。解析完成的一行数据List<String>,再放入List<List>中。
读取文件,逐行读取,每读取一行,立即解析,根据分隔符##,分割成多个String字符串,存放在一个List<String>中。解析完成的一行数据List<String>,再放入List<List>中。
public static ArrayList<ArrayList<String>> readFromTxt(String fileName) {
ArrayList<ArrayList<String>> listAll = new ArrayList<>();
try {
//1.读txt文件,一次读一行
BufferedReader br = new BufferedReader(new FileReader(fileName));
String oneLine = null;
//2.使用readLine方法,一次读一行
while ((oneLine = br.readLine()) != null) {
ArrayList<String> oneLineList = getOneLine(oneLine, "##");
listAll.add(oneLineList);
}
br.close();
} catch (Exception e) {
System.out.println("读异常.");
e.printStackTrace();
}
return listAll;
}
public static ArrayList<String> getOneLine(String content, String split) {
String[] strArr = content.split(split);
ArrayList<String> oneLine = new ArrayList<>(strArr.length);
Collections.addAll(oneLine, strArr);
return oneLine;
}
2.写文件
逐行写文件,以追加方式写入,不覆盖已经写入的内容。
public static void writeFile(String fileName, String content) {
try {
// 设置为追加写入true
BufferedWriter bw = new BufferedWriter(
new FileWriter(fileName, true));
bw.write(content);
bw.close();
} catch (IOException e) {
System.out.println("写异常.");
e.printStackTrace();
}
}
3.生成建表语句和注释和主键
根据从txt读取的表字段信息,生成建表语句和注释和和主键。
public static void createTable(String fileName, ArrayList<ArrayList<String>> content) {
List<String> firstOne = content.get(0);
String tableName = firstOne.get(0);
String tableComment = firstOne.get(1);
String tableType = firstOne.get(2);
String part1 = "create table ";
String part2 = tableName;
String part3 = " ( ";
writeFile(fileName, part1 + part2 + part3 + "\n");
String part5 = " ) " + "ENGINE = INNODB DEFAULT CHARSET = utf8 ";
String part6 = " COMMENT '" + tableComment + "' ";
String dbPartition = "";
String tbPartition = "";
String pkColumn = "";
int size = content.size();
for (int i = 0; i < size; i++) {
ArrayList<String> oneLine = content.get(i);
if (StringUtils.equals(tableName, oneLine.get(0))) continue;
String line = "";
int last = content.size() - 1;
String columnName = oneLine.get(0) + " ";
String dataType = oneLine.get(1) + " ";
String comment = " COMMENT '" + oneLine.get(3) + "'";
if (StringUtils.equals("Y", oneLine.get(2))) {
if (i == last) {
if (StringUtils.equals("", pkColumn)) {
line = columnName + dataType + " NOT NULL " + comment + "\n";
} else {
line = columnName + dataType + " NOT NULL " + comment + "," + "\n";
writeFile(fileName, " " + line);
line = "PRIMARY KEY(" + pkColumn + ")" + "\n";
}
} else {
line = columnName + dataType + " NOT NULL " + comment + "," + "\n";
}
} else {
if (i == last) {
if (StringUtils.equals("", pkColumn)) {
line = columnName + dataType + comment + "," + "\n";
} else {
line = columnName + dataType + comment + "," + "\n";
writeFile(fileName, " " + line);
line = "PRIMARY KEY(" + pkColumn + ")" + "\n";
}
} else {
line = columnName + dataType + comment + "," + "\n";
}
}
writeFile(fileName, " " + line);
if (StringUtils.equals("dbpartition", oneLine.get(5))) {
dbPartition = columnName.trim();
} else if (StringUtils.equals("tbpartition", oneLine.get(5))) {
tbPartition = columnName.trim();
}
if (StringUtils.equals("primary", oneLine.get(4).toLowerCase())) {
pkColumn = columnName.trim();
}
}
String part7 = getTableTypeInfo(tableType, dbPartition, tbPartition);
writeFile(fileName, part5 + part6 + part7 + "\n");
}
// 建表类型
public static String getTableTypeInfo(String tableType, String dbPartition, String tbPartition) {
String result = "";
switch (tableType) {
case "广播表":
result = " broadcast" + ";";
break;
case "单库表":
result = "" + ";";
break;
case "分库不分表":
result = " dbpartition BY HASH(" + dbPartition + ")" + ";";
break;
case "分库分表":
result = " dbpartition BY HASH (" + dbPartition + ") tbpartition BY MMDD (" + tbPartition + ") tbpartitions 365" + ";";
break;
}
return result;
}
4.生成索引
根据从txt读取的表字段信息,生成索引。
public static void createIndex(String fileName, ArrayList<ArrayList<String>> content) {
List<String> firstOne = content.get(0);
String tableName = firstOne.get(0);
int size = content.size();
for (int i = 0; i < size; i++) {
ArrayList<String> oneLine = content.get(i);
if (StringUtils.equals(tableName, oneLine.get(0))) continue;
if (StringUtils.equals("index", oneLine.get(4).toLowerCase())) {
String part1 = "create index ";
String part2 = "IDX_" + tableName + "_" + oneLine.get(0);
String part3 = " on ";
String part4 = tableName;
String part5 = " (" + oneLine.get(0) + ");";
part2 = getLimitString(part2);
String index = part1 + part2 + part3 + part4 + part5;
writeFile(fileName, index + "\n");
}
}
}
// 限定主键名称字符串30个字符
public static String getLimitString(String srcStr) {
int length = srcStr.length();
if (length > 30) {
srcStr = srcStr.substring(0, 30);
String underline = srcStr.substring(29, 30);
if (StringUtils.equals("_", underline)) {
srcStr = srcStr.substring(0, 29);
}
}
return srcStr;
}
5.在main函数调用
在main函数调用测试,从指定目录下读取txt文件名称,逐个生成SQL文件。
public static void main(String[] args) {
System.out.println("开始...");
String baseDir = "D:\\example\\";
List<String> listFile = getAllTxtFile(baseDir);
for (String fileName : listFile) {
singleFile(baseDir, fileName);
}
System.out.println("结束...");
}
public static List<String> getAllTxtFile(String path) {
File dirFile = new File(path);
File[] subFiles = dirFile.listFiles();
List<String> fileList = new ArrayList<>();
if (subFiles != null) {
for (File subFile : subFiles) {
if (subFile.isDirectory()) continue;
else {
if (subFile.isFile() && subFile.getName().endsWith(".txt")) {
fileList.add(subFile.getName().substring(0, subFile.getName().length() - 4));
}
}
}
}
return fileList;
}
public static void singleFile(String baseDir, String fileName) {
String srcFileName = baseDir + fileName + ".txt";
String tarFileName = baseDir + fileName + ".sql";
ArrayList<ArrayList<String>> read = readFromTxt(srcFileName);
// 表结构与注释
createTable(tarFileName, read);
// 主键和索引
createPrimaryAndIndex(tarFileName, read);
}
五、代码
几个SQL,可以查看具体表拓扑信息。
-- 广播表
SHOW topology FROM T_CONSTANT;
-- 单库单表
SHOW topology FROM T_USER_STAT;
-- 分库不分表
SHOW topology FROM T_USER;
-- 分库分表
SHOW topology FROM T_DATA;
以上,感谢。
2022年7月3日