github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/create_table_like.result (about) 1 drop database if exists test; 2 create database test; 3 use test; 4 drop table if exists pri01; 5 create table pri01(col1 int primary key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double); 6 insert into pri01 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 7 insert into pri01 values (2, 3, 'b', '32r32r', 'database', 1111111); 8 insert into pri01 values (3, null, null, null, null, null); 9 drop table if exists pri02; 10 create table pri02 like pri01; 11 show create table pri01; 12 Table Create Table 13 pri01 CREATE TABLE `pri01` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` CHAR(1) DEFAULT NULL,\n`col4` VARCHAR(20) DEFAULT NULL,\n`col5` TEXT DEFAULT NULL,\n`col6` DOUBLE DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 14 show create table pri02; 15 Table Create Table 16 pri02 CREATE TABLE `pri02` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) DEFAULT null,\n`col3` CHAR(1) DEFAULT null,\n`col4` VARCHAR(20) DEFAULT null,\n`col5` TEXT DEFAULT null,\n`col6` DOUBLE DEFAULT null,\nPRIMARY KEY (`col1`)\n) 17 desc pri01; 18 Field Type Null Key Default Extra Comment 19 col1 INT(32) NO PRI null 20 col2 DECIMAL128(38) YES null 21 col3 CHAR(1) YES null 22 col4 VARCHAR(20) YES null 23 col5 TEXT(0) YES null 24 col6 DOUBLE(0) YES null 25 desc pri02; 26 Field Type Null Key Default Extra Comment 27 col1 INT(32) NO PRI null 28 col2 DECIMAL128(38) YES null 29 col3 CHAR(1) YES null 30 col4 VARCHAR(20) YES null 31 col5 TEXT(0) YES null 32 col6 DOUBLE(0) YES null 33 select * from pri01; 34 col1 col2 col3 col4 col5 col6 35 1 2 a 23eiojf r23v324r23rer 3923.324 36 2 3 b 32r32r database 1111111.0 37 3 null null null null null 38 select * from pri02; 39 col1 col2 col3 col4 col5 col6 40 drop table pri01; 41 drop table pri02; 42 drop table if exists pri03; 43 create table pri03(col1 int unsigned, col2 char, col3 binary(10), col4 decimal(20,0)); 44 alter table pri03 add primary key (col1, col3); 45 insert into pri03 values (1, '3', '324', 31.31231); 46 insert into pri03 values (2, 'v', '321', 28390); 47 drop table if exists pri04; 48 create table pri04 like pri03; 49 select * from pri03; 50 col1 col2 col3 col4 51 1 3 324 31 52 2 v 321 28390 53 select * from pri04; 54 col1 col2 col3 col4 55 show create table pri03; 56 Table Create Table 57 pri03 CREATE TABLE `pri03` (\n`col1` INT UNSIGNED NOT NULL,\n`col2` CHAR(1) DEFAULT NULL,\n`col3` BINARY(10) NOT NULL,\n`col4` DECIMAL(20,0) DEFAULT NULL,\nPRIMARY KEY (`col1`,`col3`)\n) 58 show create table pri04; 59 Table Create Table 60 pri04 CREATE TABLE `pri04` (\n`col1` INT UNSIGNED NOT NULL,\n`col2` CHAR(1) DEFAULT null,\n`col3` BINARY(10) NOT NULL,\n`col4` DECIMAL(20,0) DEFAULT null,\nPRIMARY KEY (`col1`,`col3`)\n) 61 desc pri03; 62 Field Type Null Key Default Extra Comment 63 col1 INT UNSIGNED(32) NO PRI null 64 col2 CHAR(1) YES null 65 col3 BINARY(10) NO PRI null 66 col4 DECIMAL128(20) YES null 67 desc pri04; 68 Field Type Null Key Default Extra Comment 69 col1 INT UNSIGNED(32) NO PRI null 70 col2 CHAR(1) YES null 71 col3 BINARY(10) NO PRI null 72 col4 DECIMAL128(20) YES null 73 drop table pri03; 74 drop table pri04; 75 drop table if exists test03; 76 create table test03 ( 77 emp_no int not null, 78 birth_date date not null, 79 first_name varchar(14) not null, 80 last_name varchar(16) not null, 81 gender varchar(5) not null, 82 hire_date date not null, 83 primary key (emp_no) 84 ) partition by range columns (emp_no)( 85 partition p01 values less than (100001), 86 partition p02 values less than (200001), 87 partition p03 values less than (300001), 88 partition p04 values less than (400001) 89 ); 90 insert into test03 values (9001,'1980-12-17', 'SMITH', 'CLERK', 'F', '2008-12-17'), 91 (9002,'1981-02-20', 'ALLEN', 'SALESMAN', 'F', '2008-02-20'); 92 drop table if exists test04; 93 create table test04 like test03; 94 show create table test03; 95 Table Create Table 96 test03 CREATE TABLE `test03` (\n`emp_no` INT NOT NULL,\n`birth_date` DATE NOT NULL,\n`first_name` VARCHAR(14) NOT NULL,\n`last_name` VARCHAR(16) NOT NULL,\n`gender` VARCHAR(5) NOT NULL,\n`hire_date` DATE NOT NULL,\nPRIMARY KEY (`emp_no`)\n) partition by range columns (emp_no) (partition p01 values less than (100001), partition p02 values less than (200001), partition p03 values less than (300001), partition p04 values less than (400001)) 97 show create table test04; 98 Table Create Table 99 test04 CREATE TABLE `test04` (\n`emp_no` INT NOT NULL,\n`birth_date` DATE NOT NULL,\n`first_name` VARCHAR(14) NOT NULL,\n`last_name` VARCHAR(16) NOT NULL,\n`gender` VARCHAR(5) NOT NULL,\n`hire_date` DATE NOT NULL,\nPRIMARY KEY (`emp_no`)\n) partition by range columns (emp_no) (partition p01 values less than (100001), partition p02 values less than (200001), partition p03 values less than (300001), partition p04 values less than (400001)) 100 desc test03; 101 Field Type Null Key Default Extra Comment 102 emp_no INT(32) NO PRI null 103 birth_date DATE(0) NO null 104 first_name VARCHAR(14) NO null 105 last_name VARCHAR(16) NO null 106 gender VARCHAR(5) NO null 107 hire_date DATE(0) NO null 108 desc test04; 109 Field Type Null Key Default Extra Comment 110 emp_no INT(32) NO PRI null 111 birth_date DATE(0) NO null 112 first_name VARCHAR(14) NO null 113 last_name VARCHAR(16) NO null 114 gender VARCHAR(5) NO null 115 hire_date DATE(0) NO null 116 select * from test03; 117 emp_no birth_date first_name last_name gender hire_date 118 9001 1980-12-17 SMITH CLERK F 2008-12-17 119 9002 1981-02-20 ALLEN SALESMAN F 2008-02-20 120 select * from test04; 121 emp_no birth_date first_name last_name gender hire_date 122 drop table test03; 123 drop table test04; 124 drop table if exists test07; 125 create table test07 (col1 int unique key, col2 varchar(20)); 126 insert into test07 (col1, col2) values (133, 'database'); 127 drop table if exists test08; 128 create table test08 like test07; 129 show create table test07; 130 Table Create Table 131 test07 CREATE TABLE `test07` (\n`col1` INT DEFAULT NULL,\n`col2` VARCHAR(20) DEFAULT NULL,\nUNIQUE KEY `col1` (`col1`)\n) 132 show create table test08; 133 Table Create Table 134 test08 CREATE TABLE `test08` (\n`col1` INT DEFAULT null,\n`col2` VARCHAR(20) DEFAULT null,\nUNIQUE KEY `col1` (`col1`)\n) 135 desc test07; 136 Field Type Null Key Default Extra Comment 137 col1 INT(32) YES UNI null 138 col2 VARCHAR(20) YES null 139 desc test08; 140 Field Type Null Key Default Extra Comment 141 col1 INT(32) YES UNI null 142 col2 VARCHAR(20) YES null 143 select * from test07; 144 col1 col2 145 133 database 146 select * from test08; 147 col1 col2 148 drop table test07; 149 drop table test08; 150 drop table if exists test07; 151 [unknown result because it is related to issue#15296] 152 create temporary table test07(col1 int unique key , col2 decimal, col3 char, col4 varchar(20), col5 text, col6 double, unique index(col1, col2)); 153 [unknown result because it is related to issue#15296] 154 insert into test07 values (1, 2, 'a', '23eiojf', 'r23v324r23rer', 3923.324); 155 [unknown result because it is related to issue#15296] 156 insert into test07 values (2, 3, 'b', '32r32r', 'database', 1111111); 157 [unknown result because it is related to issue#15296] 158 insert into test07 values (3, null, null, null, null, null); 159 [unknown result because it is related to issue#15296] 160 drop table if exists test08; 161 [unknown result because it is related to issue#15296] 162 create table test08 like test07; 163 [unknown result because it is related to issue#15296] 164 show create table test07; 165 [unknown result because it is related to issue#15296] 166 show create table test08; 167 [unknown result because it is related to issue#15296] 168 desc test07; 169 [unknown result because it is related to issue#15296] 170 desc test08; 171 [unknown result because it is related to issue#15296] 172 select * from test07; 173 [unknown result because it is related to issue#15296] 174 select * from test08; 175 [unknown result because it is related to issue#15296] 176 drop table test07; 177 [unknown result because it is related to issue#15296] 178 drop table test08; 179 [unknown result because it is related to issue#15296] 180 drop table if exists foreign01; 181 drop table if exists foreign02; 182 create table foreign01 (a int primary key, b varchar(5) unique key); 183 create table foreign02 (a int ,b varchar(5), c int, foreign key(c) references foreign01(a)); 184 insert into foreign01 values (101,'abc'),(102,'def'); 185 insert into foreign02 values (1,'zs1',101),(2,'zs2',102); 186 drop table if exists foreign03; 187 drop table if exists foreign04; 188 create table foreign03 like foreign01; 189 create table foreign04 like foreign02; 190 desc foreign01; 191 Field Type Null Key Default Extra Comment 192 a INT(32) NO PRI null 193 b VARCHAR(5) YES UNI null 194 desc foreign02; 195 Field Type Null Key Default Extra Comment 196 a INT(32) YES null 197 b VARCHAR(5) YES null 198 c INT(32) YES MUL null 199 desc foreign03; 200 Field Type Null Key Default Extra Comment 201 a INT(32) NO PRI null 202 b VARCHAR(5) YES UNI null 203 desc foreign04; 204 Field Type Null Key Default Extra Comment 205 a INT(32) YES null 206 b VARCHAR(5) YES null 207 c INT(32) YES MUL null 208 select * from foreign01; 209 a b 210 101 abc 211 102 def 212 select * from foreign02; 213 a b c 214 1 zs1 101 215 2 zs2 102 216 select * from foreign03; 217 a b 218 select * from foreign04; 219 a b c 220 drop table foreign02; 221 drop table foreign01; 222 internal error: can not drop table 'foreign01' referenced by some foreign key constraint 223 drop table foreign04; 224 drop table foreign03; 225 drop table if exists null01; 226 create table null01(col1 int auto_increment primary key, col2 char, col3 varchar(20)); 227 insert into null01 values (1, '2', 'database'); 228 insert into null01 values (2, 'a', 'table'); 229 drop table if exists null02; 230 create table null02 like null01; 231 show create table null01; 232 Table Create Table 233 null01 CREATE TABLE `null01` (\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` CHAR(1) DEFAULT NULL,\n`col3` VARCHAR(20) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 234 show create table null02; 235 Table Create Table 236 null02 CREATE TABLE `null02` (\n`col1` INT NOT NULL AUTO_INCREMENT,\n`col2` CHAR(1) DEFAULT null,\n`col3` VARCHAR(20) DEFAULT null,\nPRIMARY KEY (`col1`)\n) 237 desc null01; 238 Field Type Null Key Default Extra Comment 239 col1 INT(32) NO PRI null 240 col2 CHAR(1) YES null 241 col3 VARCHAR(20) YES null 242 desc null02; 243 Field Type Null Key Default Extra Comment 244 col1 INT(32) NO PRI null 245 col2 CHAR(1) YES null 246 col3 VARCHAR(20) YES null 247 select * from null01; 248 col1 col2 col3 249 1 2 database 250 2 a table 251 select * from null02; 252 col1 col2 col3 253 drop table null01; 254 drop table null02; 255 drop table if exists prepare01; 256 create table prepare01(col1 int primary key , col2 char); 257 insert into prepare01 values (1,'a'),(2,'b'),(3,'c'); 258 drop table if exists prepare02; 259 prepare s1 from 'create table prepare02 like prepare01'; 260 execute s1; 261 show create table prepare01; 262 Table Create Table 263 prepare01 CREATE TABLE `prepare01` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 264 show create table prepare02; 265 Table Create Table 266 prepare02 CREATE TABLE `prepare02` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT null,\nPRIMARY KEY (`col1`)\n) 267 desc prepare01; 268 Field Type Null Key Default Extra Comment 269 col1 INT(32) NO PRI null 270 col2 CHAR(1) YES null 271 desc prepare02; 272 Field Type Null Key Default Extra Comment 273 col1 INT(32) NO PRI null 274 col2 CHAR(1) YES null 275 select * from prepare01; 276 col1 col2 277 1 a 278 2 b 279 3 c 280 select * from prepare02; 281 col1 col2 282 drop table prepare01; 283 drop table prepare02; 284 drop table if exists table10; 285 create table table10 (id int, name varchar(50)); 286 show create table table10; 287 Table Create Table 288 table10 CREATE TABLE `table10` (\n`id` INT DEFAULT NULL,\n`name` VARCHAR(50) DEFAULT NULL\n) 289 insert into table10 values(1,'ashley'),(2,'ben'),(3,'cindy'); 290 select * from table10; 291 id name 292 1 ashley 293 2 ben 294 3 cindy 295 drop view if exists view01; 296 create view view01 as select * from table10; 297 drop table if exists table11; 298 create table table11 like view01; 299 internal error: test.view01 is not BASE TABLE 300 show create view view01; 301 View Create View character_set_client collation_connection 302 view01 create view view01 as select * from table10; utf8mb4 utf8mb4_general_ci 303 select * from view01; 304 id name 305 1 ashley 306 2 ben 307 3 cindy 308 drop view view01; 309 drop table table10; 310 drop database test;