github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/alter_table.result (about) 1 drop table if exists f1; 2 drop table if exists c1; 3 create table f1(fa int primary key, fb int unique key); 4 create table c1 (ca int, cb int); 5 alter table c1 add constraint ffa foreign key f_a(ca) references f1(fa); 6 insert into f1 values (2,2); 7 insert into c1 values (1,1); 8 internal error: Cannot add or update a child row: a foreign key constraint fails 9 insert into c1 values (2,2); 10 select ca, cb from c1 order by ca; 11 ca cb 12 2 2 13 alter table c1 drop foreign key ffa; 14 insert into c1 values (1,1); 15 select ca, cb from c1 order by ca; 16 ca cb 17 1 1 18 2 2 19 drop table c1; 20 drop table f1; 21 create table f1(fa int primary key, fb int unique key); 22 create table c1 (ca int, cb int, constraint ffb foreign key f_a(cb) references f1(fb)); 23 insert into f1 values (2,2); 24 insert into c1 values (2,1); 25 internal error: Cannot add or update a child row: a foreign key constraint fails 26 alter table c1 add constraint ffa foreign key f_a(ca) references f1(fa); 27 insert into c1 values (1,2); 28 internal error: Cannot add or update a child row: a foreign key constraint fails 29 alter table c1 drop foreign key ffb; 30 insert into c1 values (2,1); 31 insert into c1 values (1,2); 32 internal error: Cannot add or update a child row: a foreign key constraint fails 33 alter table c1 drop foreign key ffa; 34 insert into c1 values (1,2); 35 select ca, cb from c1 order by ca; 36 ca cb 37 1 2 38 2 1 39 drop table c1; 40 drop table f1; 41 drop table if exists t1; 42 CREATE TABLE t1( 43 col1 INT NOT NULL, 44 col2 DATE NOT NULL, 45 col3 VARCHAR(16) NOT NULL, 46 col4 INT NOT NULL, 47 PRIMARY KEY(col1) 48 ); 49 insert into t1 values(1, '1980-12-17','Abby', 21); 50 insert into t1 values(2, '1981-02-20','Bob', 22); 51 insert into t1 values(3, '1981-02-22','Carol', 23); 52 insert into t1 values(4, '1981-04-02','Dora', 24); 53 insert into t1 values(5, '1981-09-28','bcvdf', 25); 54 insert into t1 values(6, '1981-05-01','green', 26); 55 ALTER TABLE t1 ADD UNIQUE idx1 (col2, col3); 56 insert into t1 values(7, '1981-05-01','green', 26); 57 Duplicate entry ('\(\d{4}-\d{2}-\d{2},\w{5}\)'|'\d{5}\w\d{17}\w\d{2}') for key '__mo_index_idx_col' 58 show index from t1; 59 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 60 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 61 t1 0 idx1 1 col2 A 0 NULL NULL YES NULL 62 t1 0 idx1 2 col3 A 0 NULL NULL YES NULL 63 select * from t1; 64 col1 col2 col3 col4 65 1 1980-12-17 Abby 21 66 2 1981-02-20 Bob 22 67 3 1981-02-22 Carol 23 68 4 1981-04-02 Dora 24 69 5 1981-09-28 bcvdf 25 70 6 1981-05-01 green 26 71 alter table t1 alter index idx1 invisible; 72 show index from t1; 73 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 74 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 75 t1 0 idx1 1 col2 A 0 NULL NULL NO NULL 76 t1 0 idx1 2 col3 A 0 NULL NULL NO NULL 77 alter table t1 alter index idx1 visible; 78 show index from t1; 79 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 80 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 81 t1 0 idx1 1 col2 A 0 NULL NULL YES NULL 82 t1 0 idx1 2 col3 A 0 NULL NULL YES NULL 83 ALTER TABLE t1 DROP INDEX idx1; 84 show index from t1; 85 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 86 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 87 ALTER TABLE t1 ADD UNIQUE INDEX idx2 (col2, col3); 88 show index from t1; 89 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 90 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 91 t1 0 idx2 1 col2 A 0 NULL NULL YES NULL 92 t1 0 idx2 2 col3 A 0 NULL NULL YES NULL 93 alter table t1 alter index idx2 invisible; 94 show index from t1; 95 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 96 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 97 t1 0 idx2 1 col2 A 0 NULL NULL NO NULL 98 t1 0 idx2 2 col3 A 0 NULL NULL NO NULL 99 ALTER TABLE t1 DROP INDEX idx2; 100 show index from t1; 101 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 102 t1 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 103 drop table t1; 104 drop table if exists t2; 105 CREATE TABLE t2( 106 col1 INT NOT NULL, 107 col2 DATE NOT NULL, 108 col3 VARCHAR(16) NOT NULL, 109 col4 INT NOT NULL, 110 PRIMARY KEY(col1) 111 ); 112 insert into t2 values(1, '1980-12-17','Abby', 21); 113 insert into t2 values(2, '1981-02-20','Bob', 22); 114 insert into t2 values(3, '1981-02-22','Carol', 23); 115 insert into t2 values(4, '1981-04-02','Dora', 24); 116 insert into t2 values(5, '1981-09-28','bcvdf', 25); 117 insert into t2 values(6, '1981-05-01','green', 26); 118 ALTER TABLE t2 ADD INDEX index1 (col2); 119 show index from t2; 120 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 121 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 122 t2 1 index1 1 col2 A 0 NULL NULL YES NULL 123 alter table t2 alter index index1 invisible; 124 show index from t2; 125 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 126 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 127 t2 1 index1 1 col2 A 0 NULL NULL NO NULL 128 select * from t2; 129 col1 col2 col3 col4 130 1 1980-12-17 Abby 21 131 2 1981-02-20 Bob 22 132 3 1981-02-22 Carol 23 133 4 1981-04-02 Dora 24 134 5 1981-09-28 bcvdf 25 135 6 1981-05-01 green 26 136 ALTER TABLE t2 DROP INDEX index1; 137 show index from t2; 138 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 139 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 140 ALTER TABLE t2 ADD INDEX index2 (col2,col3); 141 show index from t2; 142 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 143 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 144 t2 1 index2 1 col2 A 0 NULL NULL YES NULL 145 t2 1 index2 2 col3 A 0 NULL NULL YES NULL 146 alter table t2 alter index index2 invisible; 147 show index from t2; 148 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 149 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 150 t2 1 index2 1 col2 A 0 NULL NULL NO NULL 151 t2 1 index2 2 col3 A 0 NULL NULL NO NULL 152 ALTER TABLE t2 DROP INDEX index2; 153 show index from t2; 154 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 155 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 156 drop table t2; 157 drop table if exists t3; 158 CREATE TABLE t3( 159 col1 INT NOT NULL, 160 col2 DATE NOT NULL, 161 col3 VARCHAR(16) NOT NULL, 162 col4 INT NOT NULL, 163 PRIMARY KEY(col1, col2) 164 ); 165 insert into t3 values(1, '1980-12-17','Abby', 21); 166 insert into t3 values(2, '1981-02-20','Bob', 22); 167 insert into t3 values(3, '1981-02-22','Carol', 23); 168 insert into t3 values(4, '1981-04-02','Dora', 24); 169 insert into t3 values(5, '1981-09-28','bcvdf', 25); 170 insert into t3 values(6, '1981-05-01','green', 26); 171 ALTER TABLE t3 ADD INDEX index1 (col2); 172 show index from t3; 173 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 174 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 175 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 176 t3 1 index1 1 col2 A 0 NULL NULL YES NULL 177 alter table t3 alter index index1 invisible; 178 show index from t3; 179 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 180 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 181 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 182 t3 1 index1 1 col2 A 0 NULL NULL NO NULL 183 select * from t3; 184 col1 col2 col3 col4 185 1 1980-12-17 Abby 21 186 2 1981-02-20 Bob 22 187 3 1981-02-22 Carol 23 188 4 1981-04-02 Dora 24 189 5 1981-09-28 bcvdf 25 190 6 1981-05-01 green 26 191 ALTER TABLE t3 DROP INDEX index1; 192 show index from t3; 193 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 194 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 195 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 196 ALTER TABLE t3 ADD UNIQUE INDEX index2 (col2,col3); 197 show index from t3; 198 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 199 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 200 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 201 t3 0 index2 1 col2 A 0 NULL NULL YES NULL 202 t3 0 index2 2 col3 A 0 NULL NULL YES NULL 203 alter table t3 alter index index2 invisible; 204 show index from t3; 205 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 206 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 207 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 208 t3 0 index2 1 col2 A 0 NULL NULL NO NULL 209 t3 0 index2 2 col3 A 0 NULL NULL NO NULL 210 ALTER TABLE t3 DROP INDEX index2; 211 show index from t3; 212 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 213 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 214 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 215 create unique index idx3 on t3(col2,col3); 216 show index from t3; 217 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 218 t3 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 219 t3 0 PRIMARY 2 col2 A 0 NULL NULL YES NULL 220 t3 0 idx3 1 col2 A 0 NULL NULL YES NULL 221 t3 0 idx3 2 col3 A 0 NULL NULL YES NULL 222 drop table t3; 223 drop table if exists t4; 224 CREATE TABLE t4( 225 col1 INT NOT NULL, 226 col2 DATE NOT NULL, 227 col3 VARCHAR(16) NOT NULL, 228 col4 int unsigned NOT NULL, 229 PRIMARY KEY(col1) 230 ); 231 insert into t4 values(1, '1980-12-17','Abby', 21); 232 insert into t4 values(2, '1981-02-20','Bob', 22); 233 insert into t4 values(3, '1981-02-22','Carol', 23); 234 insert into t4 values(4, '1981-04-02','Dora', 24); 235 insert into t4 values(5, '1981-09-28','bcvdf', 25); 236 insert into t4 values(6, '1981-05-01','green', 26); 237 alter table t4 add constraint index (col3, col4); 238 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 35 near " index (col3, col4);"; 239 alter table t4 add constraint index wwwww (col3, col4); 240 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 35 near " index wwwww (col3, col4);"; 241 alter table t4 add constraint idx_6dotkott2kjsp8vw4d0m25fb7 index zxxxxx (col3); 242 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 65 near " index zxxxxx (col3);"; 243 show index from t4; 244 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 245 t4 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 246 alter table t4 add index zxxxxx(col3); 247 show index from t4; 248 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 249 t4 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 250 t4 1 zxxxxx 1 col3 A 0 NULL NULL YES NULL 251 drop table t4; 252 drop table if exists t5; 253 CREATE TABLE t5( 254 col1 INT NOT NULL, 255 col2 DATE NOT NULL, 256 col3 VARCHAR(16) NOT NULL, 257 col4 int unsigned NOT NULL, 258 PRIMARY KEY(col1) 259 ); 260 insert into t5 values(1, '1980-12-17','Abby', 21); 261 insert into t5 values(2, '1981-02-20','Bob', 22); 262 insert into t5 values(3, '1981-02-22','Carol', 23); 263 insert into t5 values(4, '1981-04-02','Dora', 24); 264 insert into t5 values(5, '1981-09-28','bcvdf', 25); 265 insert into t5 values(6, '1981-05-01','green', 26); 266 alter table t5 add constraint unique key (col3, col4); 267 alter table t5 add constraint unique key wwwww (col3, col4); 268 alter table t5 add constraint idx_6dotkott2kjsp8vw4d0m25fb7 unique key zxxxxx (col3); 269 show index from t5; 270 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 271 t5 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 272 t5 0 col3 1 col3 A 0 NULL NULL YES NULL 273 t5 0 col3 2 col4 A 0 NULL NULL YES NULL 274 t5 0 wwwww 1 col3 A 0 NULL NULL YES NULL 275 t5 0 wwwww 2 col4 A 0 NULL NULL YES NULL 276 t5 0 zxxxxx 1 col3 A 0 NULL NULL YES NULL 277 alter table t5 add unique key zxxxxx(col3); 278 duplicate key name 'zxxxxx' 279 show index from t5; 280 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 281 t5 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 282 t5 0 col3 1 col3 A 0 NULL NULL YES NULL 283 t5 0 col3 2 col4 A 0 NULL NULL YES NULL 284 t5 0 wwwww 1 col3 A 0 NULL NULL YES NULL 285 t5 0 wwwww 2 col4 A 0 NULL NULL YES NULL 286 t5 0 zxxxxx 1 col3 A 0 NULL NULL YES NULL 287 alter table t5 add constraint idx_6dotkott2kjsp8v unique key (col3); 288 alter table t5 add constraint idx_6dotkott2kjsp8v unique key (col4); 289 duplicate key name 'idx_6dotkott2kjsp8v' 290 show index from t5; 291 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 292 t5 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 293 t5 0 col3 1 col3 A 0 NULL NULL YES NULL 294 t5 0 col3 2 col4 A 0 NULL NULL YES NULL 295 t5 0 wwwww 1 col3 A 0 NULL NULL YES NULL 296 t5 0 wwwww 2 col4 A 0 NULL NULL YES NULL 297 t5 0 zxxxxx 1 col3 A 0 NULL NULL YES NULL 298 t5 0 idx_6dotkott2kjsp8v 1 col3 A 0 NULL NULL YES NULL 299 drop table t5; 300 create table t5(a int); 301 alter table t5 comment = "comment_1"; 302 show create table t5; 303 Table Create Table 304 t5 CREATE TABLE `t5` (\n`a` INT DEFAULT NULL\n) COMMENT='comment_1' 305 alter table t5 comment = "comment_2", comment = "comment_3"; 306 show create table t5; 307 Table Create Table 308 t5 CREATE TABLE `t5` (\n`a` INT DEFAULT NULL\n) COMMENT='comment_3' 309 alter table t5 add column a int; 310 Duplicate column name 'a' 311 alter table t5 add column b tinyint, add column c smallint, add column d int, add column e bigint, add column f tinyint unsigned; 312 alter table t5 add column g smallint unsigned, add column h int unsigned, add column i bigint unsigned, add column j float, add column k double; 313 alter table t5 add column l varchar(255), add column m Date, add column n DateTime, add column o timestamp, add column p bool; 314 alter table t5 add column q decimal(5,2), add column r text; 315 show create table t5; 316 Table Create Table 317 t5 CREATE TABLE `t5` (\n`a` INT DEFAULT NULL,\n`b` TINYINT DEFAULT NULL,\n`c` SMALLINT DEFAULT NULL,\n`d` INT DEFAULT NULL,\n`e` BIGINT DEFAULT NULL,\n`f` TINYINT UNSIGNED DEFAULT NULL,\n`g` SMALLINT UNSIGNED DEFAULT NULL,\n`h` INT UNSIGNED DEFAULT NULL,\n`i` BIGINT UNSIGNED DEFAULT NULL,\n`j` FLOAT DEFAULT NULL,\n`k` DOUBLE DEFAULT NULL,\n`l` VARCHAR(255) DEFAULT NULL,\n`m` DATE DEFAULT NULL,\n`n` DATETIME DEFAULT NULL,\n`o` TIMESTAMP DEFAULT NULL,\n`p` BOOL DEFAULT NULL,\n`q` DECIMAL(5,2) DEFAULT NULL,\n`r` TEXT DEFAULT NULL\n) COMMENT='comment_3' 318 show columns from t5; 319 Field Type Null Key Default Extra Comment 320 a INT(32) YES null 321 b TINYINT(8) YES null 322 c SMALLINT(16) YES null 323 d INT(32) YES null 324 e BIGINT(64) YES null 325 f TINYINT UNSIGNED(8) YES null 326 g SMALLINT UNSIGNED(16) YES null 327 h INT UNSIGNED(32) YES null 328 i BIGINT UNSIGNED(64) YES null 329 j FLOAT(0) YES null 330 k DOUBLE(0) YES null 331 l VARCHAR(255) YES null 332 m DATE(0) YES null 333 n DATETIME(0) YES null 334 o TIMESTAMP(0) YES null 335 p BOOL(0) YES null 336 q DECIMAL64(5) YES null 337 r TEXT(0) YES null 338 alter table t5 drop column b, drop column c, drop column d, drop column e, drop column f, drop column g, drop column h; 339 show columns from t5; 340 Field Type Null Key Default Extra Comment 341 a INT(32) YES null 342 i BIGINT UNSIGNED(64) YES null 343 j FLOAT(0) YES null 344 k DOUBLE(0) YES null 345 l VARCHAR(255) YES null 346 m DATE(0) YES null 347 n DATETIME(0) YES null 348 o TIMESTAMP(0) YES null 349 p BOOL(0) YES null 350 q DECIMAL64(5) YES null 351 r TEXT(0) YES null 352 alter table t5 drop column i, drop column j, drop column k, drop column l, drop column m, drop column n, drop column o; 353 show columns from t5; 354 Field Type Null Key Default Extra Comment 355 a INT(32) YES null 356 p BOOL(0) YES null 357 q DECIMAL64(5) YES null 358 r TEXT(0) YES null 359 alter table t5 drop column p, drop column q, drop column r; 360 show columns from t5; 361 Field Type Null Key Default Extra Comment 362 a INT(32) YES null 363 alter table t5 drop column a; 364 A table must have at least 1 column 365 alter table t5 add column b int first, add column c int after b, add column d int first, add column f int after b; 366 show columns from t5; 367 Field Type Null Key Default Extra Comment 368 d INT(32) YES null 369 b INT(32) YES null 370 f INT(32) YES null 371 c INT(32) YES null 372 a INT(32) YES null 373 drop table t5; 374 create table t5(a int primary key, b int, c int unique key); 375 alter table t5 drop column a; 376 alter table t5 drop column c; 377 drop table t5; 378 create table t5(a int, b int, primary key(a, b)); 379 alter table t5 drop column a; 380 drop table t5; 381 create table t5(a int primary key, b int); 382 create table t6(b int, c int, constraint `c1` foreign key(b) references t5(a)); 383 alter table t5 drop column b; 384 alter table t5 add column c int; 385 alter table t6 drop column b; 386 Cannot drop column 'b': needed in a foreign key constraint 'c1' 387 alter table t6 add column d int; 388 drop table t6; 389 drop table t5; 390 create table t5(a tinyint, b smallint, primary key(a))partition by hash(a) partitions 4; 391 alter table t5 add column c int; 392 invalid input: can't add/drop column for partition table now 393 alter table t5 drop column a; 394 invalid input: can't add/drop column for partition table now 395 drop table t5; 396 create table t5(a int, b int) cluster by a; 397 alter table t5 add column c int; 398 alter table t5 drop column a; 399 drop table t5; 400 drop table if exists t6; 401 create table t6(a int not null); 402 insert into t6 values(1),(2); 403 select * from t6; 404 a 405 1 406 2 407 alter table t6 add column b timestamp not null; 408 select * from t6; 409 a b 410 1 0001-01-01 00:00:00 411 2 0001-01-01 00:00:00 412 alter table t6 add column c time not null; 413 select * from t6; 414 a b c 415 1 0001-01-01 00:00:00 00:00:00 416 2 0001-01-01 00:00:00 00:00:00 417 alter table t6 add column d datetime not null; 418 select * from t6; 419 a b c d 420 1 0001-01-01 00:00:00 00:00:00 0001-01-01 00:00:00 421 2 0001-01-01 00:00:00 00:00:00 0001-01-01 00:00:00 422 alter table t6 add column e date not null; 423 select * from t6; 424 a b c d e 425 1 0001-01-01 00:00:00 00:00:00 0001-01-01 00:00:00 0001-01-01 426 2 0001-01-01 00:00:00 00:00:00 0001-01-01 00:00:00 0001-01-01 427 alter table t6 add column f datetime after a; 428 select * from t6; 429 a f b c d e 430 1 null 0001-01-01 00:00:00 00:00:00 0001-01-01 00:00:00 0001-01-01 431 2 null 0001-01-01 00:00:00 00:00:00 0001-01-01 00:00:00 0001-01-01 432 drop table t6; 433 drop table if exists t7; 434 create table t7(a int not null); 435 insert into t7 values(1),(2); 436 select * from t7; 437 a 438 1 439 2 440 alter table t7 add column b int not null; 441 select * from t7; 442 a b 443 1 0 444 2 0 445 alter table t7 add column c float not null; 446 select * from t7; 447 a b c 448 1 0 0.0 449 2 0 0.0 450 alter table t7 add column d int unsigned not null; 451 select * from t7; 452 a b c d 453 1 0 0.0 0 454 2 0 0.0 0 455 alter table t7 add column e decimal(7,2) not null; 456 select * from t7; 457 a b c d e 458 1 0 0.0 0 0.00 459 2 0 0.0 0 0.00 460 alter table t7 add column f bool not null; 461 select * from t7; 462 a b c d e f 463 1 0 0.0 0 0.00 false 464 2 0 0.0 0 0.00 false 465 alter table t7 add column g double after a; 466 select * from t7; 467 a g b c d e f 468 1 null 0 0.0 0 0.00 false 469 2 null 0 0.0 0 0.00 false 470 drop table t7; 471 drop table if exists t8; 472 create table t8(a int not null); 473 insert into t8 values(1),(2); 474 select * from t8; 475 a 476 1 477 2 478 alter table t8 add column b char(20) not null; 479 select * from t8; 480 a b 481 1 482 2 483 alter table t8 add column c varchar(20) not null; 484 select * from t8; 485 a b c 486 1 487 2 488 alter table t8 add column d text not null; 489 select * from t8; 490 a b c d 491 1 492 2 493 alter table t8 add column e binary(2) not null; 494 select * from t8; 495 a b c d e 496 1 497 2 498 alter table t8 add column f blob not null; 499 select * from t8; 500 a b c d e f 501 1 502 2 503 alter table t8 add column g varchar(50) after a; 504 select * from t8; 505 a g b c d e f 506 1 null 507 2 null 508 alter table t8 add column h json not null; 509 select * from t8; 510 a g b c d e f h 511 1 null {} 512 2 null {} 513 drop table t8;