oracle, mysql, clickhouse创建表的DDL语句参考

这里展示的oracle,mysql, clickhouse 下面如何创建一个表的DDL语句。

请注意这里的数据类型，在不同的表，表示形式不一样。

Oracle 的DDL语句 

-- UPCENTER.PUB_PLATE_INFO definition

CREATE TABLE "UPCENTER"."PUB_PLATE_INFO" 
   (        "ISVALID" NUMBER(1,0) NOT NULL ENABLE, 
        "CREATETIME" TIMESTAMP (6) NOT NULL ENABLE, 
        "UPDATETIME" TIMESTAMP (6) NOT NULL ENABLE, 
        "PLATE_UNI_CODE" NUMBER(10,0) NOT NULL ENABLE, 
        "PLATE_NAME" VARCHAR2(200), 
        "PLATE_CODE" VARCHAR2(50), 
        "PLATE_LEVEL" NUMBER(4,0), 
        "FAT_UNI_CODE" NUMBER(10,0), 
        "RELA_UNI_CODE" NUMBER(10,0), 
        "PLATE_TYPE_PAR" NUMBER(4,0), 
        "IS_VALID_PAR" NUMBER(4,0), 
        "END_DATE" DATE, 
        "MQ_START_DATE" DATE, 
        "CONC_TYPE_PAR" NUMBER(2,0), 
        "BASE_POINT" NUMBER(16,4), 
        "PLATE_INFO_TYPE" NUMBER(2,0), 
         CONSTRAINT "PK_PUB_PLATE_INFO" PRIMARY KEY ("PLATE_UNI_CODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HT"  ENABLE, 
         SUPPLEMENTAL LOG GROUP "GGS_153524" ("PLATE_UNI_CODE") ALWAYS
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HT" ;

CREATE UNIQUE INDEX "UPCENTER"."PK_PUB_PLATE_INFO" ON "UPCENTER"."PUB_PLATE_INFO" ("PLATE_UNI_CODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HT" ;
  CREATE INDEX "UPCENTER"."IDX_PUB_PLATE_INFO" ON "UPCENTER"."PUB_PLATE_INFO" ("UPDATETIME") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "HT" ;

mysql 的 DDL语句

-- db_assist.PUB_PLATE_INFO definition

CREATE TABLE `PUB_PLATE_INFO` (
  `ISVALID` decimal(1,0) DEFAULT NULL,
  `CREATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `PLATE_UNI_CODE` decimal(10,0) NOT NULL,
  `PLATE_NAME` text,
  `PLATE_CODE` text,
  `PLATE_LEVEL` decimal(4,0) DEFAULT NULL,
  `FAT_UNI_CODE` decimal(10,0) DEFAULT NULL,
  `RELA_UNI_CODE` decimal(10,0) DEFAULT NULL,
  `PLATE_TYPE_PAR` decimal(4,0) DEFAULT NULL,
  `IS_VALID_PAR` decimal(4,0) DEFAULT NULL,
  `END_DATE` datetime DEFAULT NULL,
  `MQ_START_DATE` datetime DEFAULT NULL,
  `CONC_TYPE_PAR` decimal(2,0) DEFAULT NULL,
  PRIMARY KEY (`PLATE_UNI_CODE`),
  KEY `idx_update_time` (`UPDATETIME`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

clickhouse的 DDL语句

-- db_center.PUB_PLATE_INFO definition

CREATE TABLE db_center.PUB_PLATE_INFO
(
    `ISVALID` Nullable(Int64),
    `CREATETIME` DateTime64(3),
    `UPDATETIME` DateTime64(3),
    `PLATE_UNI_CODE` Int64,
    `PLATE_NAME` Nullable(String),
    `PLATE_CODE` Nullable(String),
    `PLATE_LEVEL` Nullable(Int64),
    `FAT_UNI_CODE` Nullable(Int64),
    `RELA_UNI_CODE` Nullable(Int64),
    `PLATE_TYPE_PAR` Nullable(Int64),
    `IS_VALID_PAR` Nullable(Int64),
    `END_DATE` Nullable(DateTime),
    `MQ_START_DATE` Nullable(DateTime),
    `CONC_TYPE_PAR` Nullable(Int64),
    `BASE_POINT` Nullable(Float64)
)
ENGINE = ReplacingMergeTree
ORDER BY PLATE_UNI_CODE
SETTINGS index_granularity = 8192;