提取sql中的表名和字段 - smile0821/learngit GitHub Wiki
//匹配整个ddl，将ddl分为表名，列sql部分，表注释 private static final Pattern DDL_PATTERN = Pattern.compile("\screate\s+table\s+(?\S+)[^\(]\((?[\s\S]+)\)[^\)]+?(comment\s*(=|on\s+table)\s*'(?.?)'\s;?)?$", Pattern.CASE_INSENSITIVE); //匹配列sql部分，分别解析每一列的列名 类型 和列注释 private static final Pattern COL_PATTERN = Pattern.compile("\s*(?\S+)\s+(?\w+)\s*(?:\([\s\d,]+\))?((?!comment).)(comment\s'(?.?)')?\s(,|$)", Pattern.CASE_INSENSITIVE);
public static void parse(String sql){
Matcher matcher = DDL_PATTERN.matcher(sql);
if (matcher.find()){
String tableName = matcher.group("tableName");
String tableComment = matcher.group("tableComment");
System.out.println(tableName + "\t\t" + tableComment);
System.out.println("==========");
String columnsSQL = matcher.group("columnsSQL");
if (columnsSQL != null && columnsSQL.length() > 0){
Matcher colMatcher = COL_PATTERN.matcher(columnsSQL);
while (colMatcher.find()){
String fieldName = colMatcher.group("fieldName");
String fieldType = colMatcher.group("fieldType");
String fieldComment = colMatcher.group("fieldComment");
if (!"key".equalsIgnoreCase(fieldType)){
System.out.println(fieldName + "\t\t" + fieldType + "\t\t" + fieldComment);
}
}
}
}
}
public static void main(String[] args){
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
parse("CREATE TABLE `userinfo` (\n" +
" `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',\n" +
" `username` varchar(255) NOT NULL COMMENT '用户名',\n" +
" `addtime` datetime NOT NULL COMMENT '创建时间',\n" +
" PRIMARY KEY (`user_id`)\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息'");
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
parse("CREATE TABLE `USER` (\n" +
"`ID` varchar(32) PRIMARY KEY COMMENT '主键',\n" +
"`password` varchar(32) NOT NULL COMMENT '密码',\n" +
"`username` varchar(32) NOT NULL COMMENT '用户'\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
parse("CREATE TABLE `tb_amount` (\n" +
"`ID` int(10) NOT NULL AUTO_INCREMENT,\n" +
"`PRODUCT_CODE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '产品代码',\n" +
"`GENDER` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '性别(male,female)',\n" +
"`MIN_INSURED_AGE` int(3) NULL DEFAULT NULL COMMENT '最小投保年龄',\n" +
"`MAX_INSURED_AGE` int(3) NULL DEFAULT NULL COMMENT '最大投保年龄',\n" +
"`AMOUNT` double(10, 2) NULL DEFAULT NULL COMMENT '基本保额',\n" +
"`PREMIUM_RATE` double(10, 2) NULL DEFAULT NULL COMMENT '基本保费',\n" +
"`YEAR_NUM` int(3) NULL DEFAULT NULL COMMENT '缴费年限',\n" +
"`PREMIUM_TYPE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '费率类型',\n" +
"`INSURANCE_PERIOD` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '保险期间(30年，60年)',\n" +
"`INSURANCE_PERIOD_TYPE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '保险期间类型(如定期年0,定期岁1,终身2,以后终身对应值：200)',\n" +
"`PAY_MODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '交费方式',\n" +
"PRIMARY KEY (`ID`) USING BTREE\n" +
") ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;");
}
user_id int 用户ID
username varchar 用户名
addtime datetime 创建时间
ID varchar 主键
password varchar 密码
username varchar 用户
ID int null
PRODUCT_CODE varchar 产品代码
GENDER varchar 性别(male,female)
MIN_INSURED_AGE int 最小投保年龄
MAX_INSURED_AGE int 最大投保年龄
AMOUNT double 基本保额
PREMIUM_RATE double 基本保费
YEAR_NUM int 缴费年限
PREMIUM_TYPE varchar 费率类型
INSURANCE_PERIOD varchar 保险期间(30年，60年)
INSURANCE_PERIOD_TYPE varchar 保险期间类型(如定期年0,定期岁1,终身2,以后终身对应值：200)
PAY_MODE varchar 交费方式