docker/thirdparties/docker-compose/mysql/init/03-create-table.sql

-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.

create table doris_test.test1 (
    k1 boolean,
    k2 char(100),
    k3 varchar(128),
    k4 date,
    k5 float,
    k6 tinyint,
    k7 smallint,
    k8 int,
    k9 bigint,
    k10 double,
    k11 datetime,
    k12 decimal(10, 3)
);

CREATE TABLE doris_test.ex_tb0 (
  `id` int PRIMARY KEY,
  `name` varchar(128)
);

CREATE TABLE doris_test.ex_tb1 (
  id varchar(128)
);

CREATE TABLE doris_test.ex_tb2 (
  id int,
  count_value varchar(20)
);

CREATE TABLE doris_test.ex_tb3 (
  `game_code` varchar(20) NOT NULL,
  `plat_code` varchar(20) NOT NULL,
  `account` varchar(100) NOT NULL,
  `login_time` bigint(20) NOT NULL,
  `register_time` bigint(20) DEFAULT NULL,
  `pid` varchar(20) DEFAULT NULL,
  `gid` varchar(20) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`game_code`,`plat_code`,`account`,`login_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE doris_test.ex_tb4 (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL,
  `sales_add_time` datetime NOT NULL COMMENT '领款时间',
  `sales_update_time` datetime NOT NULL COMMENT '录入更新时间',
  `finance_admin` int(11) NOT NULL COMMENT '1代表系统自动录入',
  PRIMARY KEY (`products_id`),
  UNIQUE KEY `idx_orders_id` (`orders_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1985724 DEFAULT CHARSET=utf8;

CREATE TABLE doris_test.ex_tb5 (
  `id` int(10) unsigned not null AUTO_INCREMENT comment "主建",
  `apply_id` varchar(32) Default null,
  `begin_value` mediumtext,
  `operator` varchar(32) Default null,
  `operator_name` varchar(32) Default null,
  `state` varchar(8) Default null,
  `sub_state` varchar(8) Default null,
  `state_count` smallint(5) unsigned Default null,
  `create_time` datetime Default CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_apply_id` (`apply_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3732465 DEFAULT CHARSET=utf8mb4;

CREATE TABLE doris_test.ex_tb6 (
  `id` bigint(20) PRIMARY KEY,
  `t_id` bigint(20) NULL,
  `name` text NULL
);

CREATE TABLE doris_test.ex_tb7 (
  `id` varchar(32) NULL DEFAULT "",
  `user_name` varchar(32) NULL DEFAULT "",
  `member_list` DECIMAL(10,3)
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb8 (
   `date` date NOT NULL COMMENT "",
   `uid` varchar(64) NOT NULL,
   `stat_type` int(11) NOT NULL COMMENT "",
   `price` varchar(255) NULL COMMENT "price"
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb9 (
   c_date date null
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb10 (
    `aa` varchar(200) NULL,
    `bb` int NULL,
    `cc` bigint NULL
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb11 (
 `aa` varchar(200) PRIMARY KEY,
 `bb` int NULL
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb12 (
 `cc` varchar(200) PRIMARY KEY,
 `dd` int NULL
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb13 (
     name varchar(128),
     age INT,
     idCode  varchar(128),
     cardNo varchar(128),
     number varchar(128),
     birthday DATETIME,
     country varchar(128),
     gender varchar(128),
     covid BOOLEAN
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb14 (
     tid varchar(128),
     log_time date,
     dt  date,
     cmd varchar(128),
     dp_from varchar(128)
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb15 (
    col1 varchar(10) NULL ,
    col2 varchar(10) NULL ,
    col3 varchar(10) NULL ,
    col4 int(11) NULL ,
    col5 double NULL ,
    col6 double NULL ,
    col7 int(11) NULL ,
    col8 int(11) NULL ,
    col9 int(11) NULL ,
    col10 varchar(10) NULL ,
    col11 varchar(10) NULL ,
    col12 datetime NULL
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb16 (
   `id` bigint(20) NOT NULL COMMENT '',
   `name` varchar(192) NOT NULL COMMENT '',
   `is_delete` tinyint(4) NULL,
   `create_uid` bigint(20) NULL,
   `modify_uid` bigint(20) NULL,
   `ctime` bigint(20) NULL,
   `mtime` bigint(20) NULL
) engine=innodb charset=utf8;

CREATE TABLE doris_test.ex_tb17 (
  `id` bigint(20) NULL,
  `media_order_id` int(11) NULL,
  `supplier_id` int(11) NULL,
  `agent_policy_type` tinyint(4) NULL,
  `agent_policy` decimal(6, 2) NULL,
  `capital_type` bigint(20) NULL,
  `petty_cash_type` tinyint(4) NULL,
  `recharge_amount` decimal(10, 2) NULL,
  `need_actual_amount` decimal(10, 2) NULL,
  `voucher_url` varchar(765) NULL,
  `ctime` bigint(20) NULL,
  `mtime` bigint(20) NULL,
  `is_delete` tinyint(4) NULL,
  `media_remark` text NULL,
  `account_number` varchar(765) NULL,
  `currency_type` tinyint(4) NULL,
  `order_source` tinyint(4) NULL
) engine=innodb charset=utf8;

create table doris_test.ex_tb18 (
    num_tinyint tinyint,
    num_tinyint2 tinyint unsigned,
    num_smallint SMALLINT,
    num_smallint2 SMALLINT unsigned,
    num_mediumint MEDIUMINT,
    num_mediumint2 MEDIUMINT unsigned,
    num_bigint BIGINT,
    num_int int(5),
    num_int2 int(5) unsigned,
    num_int3 int(5) unsigned zerofill,
    num_float float(5, 2),
    num_double double(10, 3),
    num_decimal decimal(20, 2),
    char_value1 char(5),
    char_value2 char(100),
    varchar_value1 varchar(5),
    varchar_value2 varchar(10),
    varchar_value3 varchar(100),
    text_value TEXT(123)
) engine=innodb charset=utf8;

create table doris_test.ex_tb19 (
    date_value date,
    time_value time,
    year_value year,
    datetime_value datetime,
    timestamp_value timestamp
) engine=innodb charset=utf8;

create table doris_test.ex_tb20 (
    decimal_normal decimal(38, 5),
    decimal_unsigned decimal(37, 5) unsigned,
    decimal_out1 decimal(39, 5),
    decimal_unsigned_out1 decimal(38, 5) unsigned,
    decimal_long decimal(65, 5),
    decimal_unsigned_long decimal(65, 5) unsigned
) engine=innodb charset=utf8;

create table doris_test.test_insert (
    `id` varchar(128) NULL,
    `name` varchar(128) NULL,
    `age` int NULL
) engine=innodb charset=utf8;


create table doris_test.test_insert2 (
    id varchar(128) NULL,
    k1 boolean,
    k2 char(100),
    k3 varchar(128),
    k4 date,
    k5 float,
    k6 tinyint,
    k7 smallint,
    k8 int,
    k9 bigint,
    k10 double,
    k11 decimal(10, 3),
    k12 time,
    k13 year,
    k14 datetime,
    k15 timestamp
) engine=innodb charset=utf8;

create table doris_test.all_types (
  `tinyint_u` tinyint unsigned,
  `smallint_u` smallint unsigned,
  `mediumint_u` mediumint unsigned,
  `int_u` int unsigned,
  `bigint_u` bigint unsigned,
  `decimal_u` decimal(18, 5) unsigned,
  `double_u` double unsigned,
  `float_u` float unsigned,
  `boolean` boolean,
  `tinyint` tinyint,
  `smallint` smallint,
  `year` year,
  `mediumint` mediumint,
  `int` int,
  `bigint` bigint,
  `date` date,
  `timestamp` timestamp(4) null,
  `datetime` datetime,
  `float` float,
  `double` double,
  `decimal` decimal(12, 4),
  `char` char(5),
  `varchar` varchar(10),
  `time` time(4),
  `text` text,
  `blob` blob,
  `json` json,
  `set` set('Option1', 'Option2', 'Option3'),
  `bit` bit(6),
  `binary` binary(12),
  `varbinary` varbinary(12),
  `enum` enum('Value1', 'Value2', 'Value3')
) engine=innodb charset=utf8;

CREATE TABLE `doris_test`.`auto_default_t` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `name` varchar(64) DEFAULT NULL,
    `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) engine=innodb charset=utf8;

CREATE TABLE doris_test.dt (
  `timestamp0` timestamp(0) DEFAULT CURRENT_TIMESTAMP(0),
  `timestamp1` timestamp(1) DEFAULT CURRENT_TIMESTAMP(1),
  `timestamp2` timestamp(2) DEFAULT CURRENT_TIMESTAMP(2),
  `timestamp3` timestamp(3) DEFAULT CURRENT_TIMESTAMP(3),
  `timestamp4` timestamp(4) DEFAULT CURRENT_TIMESTAMP(4),
  `timestamp5` timestamp(5) DEFAULT CURRENT_TIMESTAMP(5),
  `timestamp6` timestamp(6) DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE doris_test.dt_null (
  `dt` datetime NOT NULL
) ENGINE=INNODB CHARSET=utf8;

CREATE VIEW doris_test.mysql_view as
select 10086 as col_1, 4294967295 as col_2, tinyint_u as col_3  from doris_test.all_types where tinyint_u=201;

CREATE TABLE doris_test.test_key_word (
`id` int,
`key` int
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE show_test_do_not_modify.ex_tb0 (
  `id` int PRIMARY KEY,
  `name` varchar(128)
);

CREATE TABLE show_test_do_not_modify.ex_tb1 (
  id varchar(128)
);

CREATE TABLE show_test_do_not_modify.ex_tb2 (
  id int,
  count_value varchar(20)
);

CREATE TABLE doris_test.test_zd (
`id` int(10) unsigned NOT NULL,
`d_z` date NOT NULL
);

CREATE TABLE Doris.DORIS (
  id varchar(128)
);

CREATE TABLE Doris.Doris (
  id varchar(128)
);

CREATE TABLE Doris.doris (
  id varchar(128)
);

create table doris_test.compoundpredicate_test (
pk int,
col_int_undef_signed int,
col_int_undef_signed2 int
);

create table doris_test.text_push (pk varchar(10));


create table doris_test.all_types_nullable (
  `tinyint_u` tinyint unsigned,
  `smallint_u` smallint unsigned,
  `mediumint_u` mediumint unsigned,
  `int_u` int unsigned,
  `bigint_u` bigint unsigned,
  `decimal1_u` decimal unsigned,
  `decimal2_u` decimal(9, 2) unsigned,
  `decimal3_u` decimal(18, 5) unsigned,
  `decimal4_u` decimal(38, 10) unsigned,
  `decimal5_u` decimal(65, 30) unsigned,
  `double_u` double unsigned,
  `float_u` float unsigned,
  `boolean` boolean,
  `tinyint` tinyint,
  `smallint` smallint,
  `mediumint` mediumint,
  `int` int,
  `bigint` bigint,
  `double` double,
  `float` float,
  `decimal1` decimal,
  `decimal2` decimal(9, 2),
  `decimal3` decimal(18, 5) ,
  `decimal4` decimal(38, 10),
  `decimal5` decimal(65, 30),
  `year` year,
  `time1` time,
  `time2` time(3),
  `time3` time(6),
  `date` date,
  `datetime` datetime,
  `timestamp1` timestamp null,
  `timestamp2` timestamp(3) null,
  `timestamp3` timestamp(6) null,
  `char` char(5),
  `varchar` varchar(10),
  `text` text,
  `blob` blob,
  `json` json,
  `set` set('Option1', 'Option2', 'Option3'),
  `bit` bit(6),
  `binary` binary(12),
  `varbinary` varbinary(12),
  `enum` enum('Value1', 'Value2', 'Value3')
) engine=innodb charset=utf8;


create table doris_test.all_types_non_nullable (
  `tinyint_u` tinyint unsigned NOT NULL,
  `smallint_u` smallint unsigned NOT NULL,
  `mediumint_u` mediumint unsigned NOT NULL,
  `int_u` int unsigned NOT NULL,
  `bigint_u` bigint unsigned NOT NULL,
  `decimal1_u` decimal unsigned NOT NULL,
  `decimal2_u` decimal(9, 2) unsigned NOT NULL,
  `decimal3_u` decimal(18, 5) unsigned NOT NULL,
  `decimal4_u` decimal(38, 10) unsigned NOT NULL,
  `decimal5_u` decimal(65, 30) unsigned NOT NULL,
  `double_u` double unsigned NOT NULL,
  `float_u` float unsigned NOT NULL,
  `boolean` boolean NOT NULL,
  `tinyint` tinyint NOT NULL,
  `smallint` smallint NOT NULL,
  `mediumint` mediumint NOT NULL,
  `int` int NOT NULL,
  `bigint` bigint NOT NULL,
  `double` double NOT NULL,
  `float` float NOT NULL,
  `decimal1` decimal NOT NULL,
  `decimal2` decimal(9, 2) NOT NULL,
  `decimal3` decimal(18, 5) NOT NULL,
  `decimal4` decimal(38, 10) NOT NULL,
  `decimal5` decimal(65, 30) NOT NULL,
  `year` year NOT NULL,
  `time1` time NOT NULL,
  `time2` time(3) NOT NULL,
  `time3` time(6) NOT NULL,
  `date` date NOT NULL,
  `datetime` datetime NOT NULL,
  `timestamp1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `timestamp2` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `timestamp3` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `char` char(5) NOT NULL,
  `varchar` varchar(10) NOT NULL,
  `text` text NOT NULL,
  `blob` blob NOT NULL,
  `json` json NOT NULL,
  `set` set('Option1', 'Option2', 'Option3') NOT NULL,
  `bit` bit(6) NOT NULL,
  `binary` binary(12) NOT NULL,
  `varbinary` varbinary(12) NOT NULL,
  `enum` enum('Value1', 'Value2', 'Value3') NOT NULL
) engine=innodb charset=utf8;


create table doris_test.all_types_multi_block (
  `tinyint_u` tinyint unsigned,
  `smallint_u` smallint unsigned,
  `mediumint_u` mediumint unsigned,
  `int_u` int unsigned,
  `bigint_u` bigint unsigned,
  `decimal1_u` decimal unsigned,
  `decimal2_u` decimal(9, 2) unsigned,
  `decimal3_u` decimal(18, 5) unsigned,
  `decimal4_u` decimal(38, 10) unsigned,
  `decimal5_u` decimal(65, 30) unsigned,
  `double_u` double unsigned,
  `float_u` float unsigned,
  `boolean` boolean,
  `tinyint` tinyint,
  `smallint` smallint,
  `mediumint` mediumint,
  `int` int,
  `bigint` bigint,
  `double` double,
  `float` float,
  `decimal1` decimal,
  `decimal2` decimal(9, 2),
  `decimal3` decimal(18, 5) ,
  `decimal4` decimal(38, 10),
  `decimal5` decimal(65, 30),
  `year` year,
  `time1` time,
  `time2` time(3),
  `time3` time(6),
  `date` date,
  `datetime` datetime,
  `timestamp1` timestamp null,
  `timestamp2` timestamp(3) null,
  `timestamp3` timestamp(6) null,
  `char` char(5),
  `varchar` varchar(10),
  `text` text,
  `blob` blob,
  `json` json,
  `set` set('Option1', 'Option2', 'Option3'),
  `bit` bit(6),
  `binary` binary(12),
  `varbinary` varbinary(12),
  `enum` enum('Value1', 'Value2', 'Value3')
) engine=innodb charset=utf8;


CREATE TABLE doris_test.`t_varchar` (
  `varchar_col` varchar(21844)
);

CREATE TABLE doris_test.`t_char` (
  `char_col` char(255) COLLATE utf8_bin DEFAULT NULL
);

CREATE TABLE doris_test.`test_cast` (
  `id` int(11) DEFAULT NULL,
  `int_c` varchar(100),
  `date_c` varchar(100),
  `datetime_c` varchar(100)
);

CREATE TABLE doris_test.`test_table_comment` (
  `id` int(11) DEFAULT NULL,
  `int_c` varchar(100),
  `date_c` varchar(100),
  `datetime_c` varchar(100)
) COMMENT = 'test table comment';

CREATE TABLE test_varbinary_db.`test_varbinary` (
  `id` int(11) DEFAULT NULL,
  `varbinary_c` varbinary(100)
);
CREATE TABLE test_varbinary_db.`test_varbinary_udf` (
  `id` int(11) DEFAULT NULL,
  `varbinary_c` varbinary(100)
);

CREATE TABLE test_timestamp_tz_db.ts_test (
    id INT,
    ts_ts TIMESTAMP NULL,
    ts_dt DATETIME NULL
);