############################################## # Test instant ADD/DROP COLUMN for REDUNDANT format ############################################## CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=REDUNDANT; INSERT INTO t1 values ("row1_c1", "row1_c2"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) DEFAULT NULL, `c2` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the end [c1, c2, +c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 6 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # INPLACE UPDATE UPDATE t1 SET c1="r1_c1" where c1="row1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # NOT INPLACE UPDATE UPDATE t1 SET c3="row1_c3" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 row1_c3 row2_c1 row2_c2 row2_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c2 2 13 16711934 0 c3 3 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # INPLACE UPDATE UPDATE t1 SET c1="r2_c1" where c1="row2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # NOT INPLACE UPDATE UPDATE t1 SET c0="row2_c0" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 row2_c0 r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # INPLACE UPDATE UPDATE t1 SET c1="r3_c1" where c1="row3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row3_c4" where c1="r3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 row3_c4 row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # ------------------------------------------------------------ # DROP A COLUMN in between [c0, c1, c4, -c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 7 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 c3 3 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # INPLACE UPDATE UPDATE t1 SET c1="r4_c1" where c1="row4_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row1_c4" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 row1_c4 row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # ------------------------------------------------------------ # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # INPLACE UPDATE UPDATE t1 SET c1="r5_c1" where c1="row5_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # NOT INPLACE UPDATE UPDATE t1 SET c0="row1_c0" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 row1_c0 r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # ------------------------------------------------------------ # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c0, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c4 1 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 INSERT INTO t1 values ("row7_c1", "row7_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 row7_c1 row7_c4 # INPLACE UPDATE UPDATE t1 SET c1="r6_c1" where c1="row6_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # NOT INPLACE UPDATE UPDATE t1 SET c4="row2_c4" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # ------------------------------------------------------------ # Rebuild table [c1, c4] # ------------------------------------------------------------ ALTER TABLE t1 FORCE; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c4 1 13 16711934 0 SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 INSERT INTO t1 values ("row8_c1", "row8_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 row8_c1 row8_c4 ########### # CLEANUP # ########### DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for DYNAMIC format ############################################ CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=DYNAMIC; INSERT INTO t1 values ("row1_c1", "row1_c2"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) DEFAULT NULL, `c2` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the end [c1, c2, +c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 6 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # INPLACE UPDATE UPDATE t1 SET c1="r1_c1" where c1="row1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # NOT INPLACE UPDATE UPDATE t1 SET c3="row1_c3" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 row1_c3 row2_c1 row2_c2 row2_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c2 2 13 16711934 0 c3 3 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # INPLACE UPDATE UPDATE t1 SET c1="r2_c1" where c1="row2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # NOT INPLACE UPDATE UPDATE t1 SET c0="row2_c0" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 row2_c0 r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # INPLACE UPDATE UPDATE t1 SET c1="r3_c1" where c1="row3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row3_c4" where c1="r3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 row3_c4 row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # ------------------------------------------------------------ # DROP A COLUMN in between [c0, c1, c4, -c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 7 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 c3 3 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # INPLACE UPDATE UPDATE t1 SET c1="r4_c1" where c1="row4_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row1_c4" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 row1_c4 row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # ------------------------------------------------------------ # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # INPLACE UPDATE UPDATE t1 SET c1="r5_c1" where c1="row5_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # NOT INPLACE UPDATE UPDATE t1 SET c0="row1_c0" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 row1_c0 r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # ------------------------------------------------------------ # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c0, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c4 1 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 INSERT INTO t1 values ("row7_c1", "row7_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 row7_c1 row7_c4 # INPLACE UPDATE UPDATE t1 SET c1="r6_c1" where c1="row6_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # NOT INPLACE UPDATE UPDATE t1 SET c4="row2_c4" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # ------------------------------------------------------------ # Rebuild table [c1, c4] # ------------------------------------------------------------ ALTER TABLE t1 FORCE; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c4 1 13 16711934 0 SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 INSERT INTO t1 values ("row8_c1", "row8_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 row8_c1 row8_c4 ########### # CLEANUP # ########### DROP TABLE t1; ############################################ # Test instant ADD/DROP COLUMN for COMPACT format ############################################ CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=COMPACT; INSERT INTO t1 values ("row1_c1", "row1_c2"); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` char(20) DEFAULT NULL, `c2` char(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the end [c1, c2, +c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 6 0 1 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c2 1 13 16711934 0 c3 2 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3"); SELECT * FROM t1 ORDER BY c1; c1 c2 c3 row1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # INPLACE UPDATE UPDATE t1 SET c1="r1_c1" where c1="row1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 c3_def row2_c1 row2_c2 row2_c3 # NOT INPLACE UPDATE UPDATE t1 SET c3="row1_c3" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c1 c2 c3 r1_c1 row1_c2 row1_c3 row2_c1 row2_c2 row2_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 7 0 2 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c2 2 13 16711934 0 c3 3 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def row2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # INPLACE UPDATE UPDATE t1 SET c1="r2_c1" where c1="row2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 c0_def r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # NOT INPLACE UPDATE UPDATE t1 SET c0="row2_c0" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c2 c3 c0_def r1_c1 row1_c2 row1_c3 row2_c0 r2_c1 row2_c2 row2_c3 row3_c0 row3_c1 row3_c2 row3_c3 # ------------------------------------------------------------ # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 8 0 3 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 c2 3 13 16711934 0 c3 4 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 row3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # INPLACE UPDATE UPDATE t1 SET c1="r3_c1" where c1="row3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 c4_def row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row3_c4" where c1="r3_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c2 c3 c0_def r1_c1 c4_def row1_c2 row1_c3 row2_c0 r2_c1 c4_def row2_c2 row2_c3 row3_c0 r3_c1 row3_c4 row3_c2 row3_c3 row4_c0 row4_c1 row4_c4 row4_c2 row4_c3 # ------------------------------------------------------------ # DROP A COLUMN in between [c0, c1, c4, -c2, c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 7 0 4 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 c3 3 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 row4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # INPLACE UPDATE UPDATE t1 SET c1="r4_c1" where c1="row4_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 c4_def row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # NOT INPLACE UPDATE UPDATE t1 SET c4="row1_c4" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c3 c0_def r1_c1 row1_c4 row1_c3 row2_c0 r2_c1 c4_def row2_c3 row3_c0 r3_c1 row3_c4 row3_c3 row4_c0 r4_c1 row4_c4 row4_c3 row5_c0 row5_c1 row5_c4 row5_c3 # ------------------------------------------------------------ # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 6 0 5 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c0 0 13 16711934 1 c1 1 13 16711934 0 c4 2 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4"); SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 row5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # INPLACE UPDATE UPDATE t1 SET c1="r5_c1" where c1="row5_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 c0_def r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # NOT INPLACE UPDATE UPDATE t1 SET c0="row1_c0" where c1="r1_c1"; SELECT * FROM t1 ORDER BY c1; c0 c1 c4 row1_c0 r1_c1 row1_c4 row2_c0 r2_c1 c4_def row3_c0 r3_c1 row3_c4 row4_c0 r4_c1 row4_c4 row5_c0 r5_c1 row5_c4 row6_c0 row6_c1 row6_c4 # ------------------------------------------------------------ # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3] # ------------------------------------------------------------ ALTER TABLE t1 DROP COLUMN c0, ALGORITHM=INSTANT; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 6 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c4 1 13 16711934 1 SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 INSERT INTO t1 values ("row7_c1", "row7_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 row6_c1 row6_c4 row7_c1 row7_c4 # INPLACE UPDATE UPDATE t1 SET c1="r6_c1" where c1="row6_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 c4_def r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # NOT INPLACE UPDATE UPDATE t1 SET c4="row2_c4" where c1="r2_c1"; SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 # ------------------------------------------------------------ # Rebuild table [c1, c4] # ------------------------------------------------------------ ALTER TABLE t1 FORCE; # Metadata from INFORMATION_SCHEMA.TABLES NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS test/t1 5 0 0 # Metadata from INFORMATION_SCHEMA.COLUMNS NAME POS MTYPE PRTYPE HAS_DEFAULT c1 0 13 16711934 0 c4 1 13 16711934 0 SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 INSERT INTO t1 values ("row8_c1", "row8_c4"); SELECT * FROM t1 ORDER BY c1; c1 c4 r1_c1 row1_c4 r2_c1 row2_c4 r3_c1 row3_c4 r4_c1 row4_c4 r5_c1 row5_c4 r6_c1 row6_c4 row7_c1 row7_c4 row8_c1 row8_c4 ########### # CLEANUP # ########### DROP TABLE t1;