建表语句转换

建表语句差异
转换建表语句

建表语句差异

在不同类型的数据库之间同步数据时, 因为数据库方言的不同, 建表语句经常会有差异, 比如:

mysql

CREATE TABLE `user_info` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`NAME` varchar(128) DEFAULT NOT NULL COMMENT '姓名',
`MOBILE_NUM` varchar(20) DEFAULT NOT NULL COMMENT '手机号',
PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='会员表';

clickhouse

CREATE TABLE `user_info` (
`ID` Int64,
`NAME` Nullable(String) COMMENT '姓名',
`MOBILE_NUM` Nullable(String) COMMENT '手机号'
) ENGINE=ReplacingMergeTree() ORDER BY (id) SAMPLE BY id

hive

CREATE TABLE `user_info` (
`ID` Int64,
`NAME` Nullable(String) COMMENT '姓名',
`MOBILE_NUM` Nullable(String) COMMENT '手机号'
) ENGINE=ReplacingMergeTree() ORDER BY (id) SAMPLE BY id

CREATE TABLE `user_info` (
	`ID` BIGINT NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR_IGNORECASE(128) DEFAULT NOT NULL COMMENT '姓名',
	`MOBILE_NUM` VARCHAR_IGNORECASE(20) DEFAULT NOT NULL COMMENT '手机号',
	PRIMARY KEY (`ID`)
);

django

# 以下为模板代码, 请按需修改 Field, max_length, blank 等参数细节
class UserInfo(models.Model):
    ID = models.BigIntegerField(null=False, verbose_name='')
    NAME = models.CharField(null=True, max_length=128, verbose_name='姓名')
    MOBILE_NUM = models.CharField(null=True, max_length=20, verbose_name='手机号')
    
    class Meta:
        managed = False
        db_table = 'user_info'
        verbose_name = '会员表'
        verbose_name_plural = '会员表'

java

/**
 * 会员表
 * 
 * @author sybn
 * @since 2023-02-14 14:23:43
 */
public class UserInfo {
	private Long id;
	/**
	 * 姓名
	 **/
	private String name;
	/**
	 * 手机号
	 **/
	private String mobileNum;
};

转换建表语句

本工具包提供了建表语句转换功能

@Test
public void testClickhouse() throws Exception {
    String sql = "CREATE TABLE hive.bi_dm_dadi_merge.dadi_transaction_data (\n" +
            "id bigint COMMENT 'ID',\n" +
            "serialnumber varchar COMMENT `流水号`,\n" +
            "sale_bill_code varchar COMMENT `订单code`,\n" +
            "date_key_week varchar COMMENT `交易时间-周`,\n" +
            "date_key_month varchar COMMENT `交易时间-月`,\n" +
            "date_key_year varchar COMMENT `交易时间-年`,\n" +
            "date_key varchar COMMENT `日期分区`,\n" +
            "tenant_id varchar COMMENT `租户分区`\n" +
            ")\n" +
            "WITH (\n" +
            "format = 'TEXTFILE',\n" +
            "partitioned_by = ARRAY['date_key','tenant_id']\n" +
            ")";
    SySqlCreateTableEntity entity = new SySqlCreateTableEntity(sql);
    SqlSerializeOptions options = new SqlSerializeCreateTableClickhouseOptions();
    options.setTableNameConver(x -> "dadi_transaction_data_ch");
    String s = entity.toSqlString(options);
    LogUtil.info(s);
    Assert.assertEquals(s, "CREATE TABLE `dadi_transaction_data_ch` (\r\n" +
            "\t`id` Nullable(Int64) COMMENT 'ID',\r\n" +
            "\t`serialnumber` Nullable(String) COMMENT '流水号',\r\n" +
            "\t`sale_bill_code` Nullable(String) COMMENT '订单code',\r\n" +
            "\t`date_key_week` Nullable(String) COMMENT '交易时间-周',\r\n" +
            "\t`date_key_month` Nullable(String) COMMENT '交易时间-月',\r\n" +
            "\t`date_key_year` Nullable(String) COMMENT '交易时间-年',\r\n" +
            "\t`date_key` Nullable(String) COMMENT '日期分区',\r\n" +
            "\t`tenant_id` Nullable(String) COMMENT '租户分区'\r\n" +
            ") ENGINE=ReplacingMergeTree() PARTITION BY toDate(date_key) ORDER BY (id)");
}

web 页面

建表语句转换

建表语句差异

转换建表语句

Comments