Description:
Instant add columns for innodb is the first feature of TenDB, and using for several years in Tencent Game since 2012.
And this patch is based on 5.7.19.
At present, we did not introduce any new syntax.
But it will use "intant add" directly when it meets the conditions of "instant add columns".
The conditions is:
1. innodb compact/dynamic table
2. add columns last
3. no other alter table opterations
Key functions changed or added:
rec_init_offset
rec_get_nth_field
rec_get_nth_cfield
mlog_open_and_write_index
mlog_parse_index
row_undo_mod_clust_low
row_log_online_op
row_log_table_low
How to repeat:
mysql> CREATE TABLE t3(a INT PRIMARY KEY, b INT) ENGINE=InnoDB ROW_FORMAT=compact;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t3 SET a=1;
Query OK, 1 row affected (0.00 sec)
# instant add columns
mysql> ALTER TABLE t3 ADD COLUMN (c INT, d CHAR(10) NULL DEFAULT 'foo', e INT NOT NULL DEFAULT 42);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t3 SET a=2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+---+------+------+------+----+
| a | b | c | d | e |
+---+------+------+------+----+
| 1 | NULL | NULL | foo | 42 |
| 2 | NULL | NULL | foo | 42 |
+---+------+------+------+----+
2 rows in set (0.00 sec)
# change the default values of added columns, frm changes only. There maybe two default values in dictionary for added columns in MySQL 8.
mysql> ALTER TABLE t3 CHANGE COLUMN b b INT DEFAULT 5,
-> CHANGE COLUMN c c INT DEFAULT 10,
-> CHANGE COLUMN d d char(10) DEFAULT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+---+------+------+------+----+
| a | b | c | d | e |
+---+------+------+------+----+
| 1 | NULL | NULL | foo | 42 |
| 2 | NULL | NULL | foo | 42 |
+---+------+------+------+----+
2 rows in set (0.00 sec)
# instant add columns
mysql> alter table t3 ADD COLUMN f INT UNSIGNED DEFAULT 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t3 SET a=3;
Query OK, 1 row affected (0.00 sec)
# frm changed only
mysql> ALTER TABLE t3
-> CHANGE COLUMN a a INT NOT NULL DEFAULT 101,
-> CHANGE COLUMN b b INT DEFAULT 102,
-> CHANGE COLUMN c c INT DEFAULT 103,
-> CHANGE COLUMN d d char(10) DEFAULT 'eleventy',
-> CHANGE COLUMN e e INT NOT NULL DEFAULT 105,
-> CHANGE COLUMN f f INT UNSIGNED DEFAULT 106;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t3 set a = 4;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t3;
+---+------+------+----------+-----+------+
| a | b | c | d | e | f |
+---+------+------+----------+-----+------+
| 1 | NULL | NULL | foo | 42 | 0 |
| 2 | NULL | NULL | foo | 42 | 0 |
| 3 | 5 | 10 | NULL | 42 | 0 |
| 4 | 102 | 103 | eleventy | 105 | 106 |
+---+------+------+----------+-----+------+
4 rows in set (0.00 sec)
# the default values of added columns in dictionary should be removed.
mysql> truncate table t3;
Query OK, 0 rows affected (0.00 sec)
Description: Instant add columns for innodb is the first feature of TenDB, and using for several years in Tencent Game since 2012. And this patch is based on 5.7.19. At present, we did not introduce any new syntax. But it will use "intant add" directly when it meets the conditions of "instant add columns". The conditions is: 1. innodb compact/dynamic table 2. add columns last 3. no other alter table opterations Key functions changed or added: rec_init_offset rec_get_nth_field rec_get_nth_cfield mlog_open_and_write_index mlog_parse_index row_undo_mod_clust_low row_log_online_op row_log_table_low How to repeat: mysql> CREATE TABLE t3(a INT PRIMARY KEY, b INT) ENGINE=InnoDB ROW_FORMAT=compact; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t3 SET a=1; Query OK, 1 row affected (0.00 sec) # instant add columns mysql> ALTER TABLE t3 ADD COLUMN (c INT, d CHAR(10) NULL DEFAULT 'foo', e INT NOT NULL DEFAULT 42); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t3 SET a=2; Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +---+------+------+------+----+ | a | b | c | d | e | +---+------+------+------+----+ | 1 | NULL | NULL | foo | 42 | | 2 | NULL | NULL | foo | 42 | +---+------+------+------+----+ 2 rows in set (0.00 sec) # change the default values of added columns, frm changes only. There maybe two default values in dictionary for added columns in MySQL 8. mysql> ALTER TABLE t3 CHANGE COLUMN b b INT DEFAULT 5, -> CHANGE COLUMN c c INT DEFAULT 10, -> CHANGE COLUMN d d char(10) DEFAULT NULL; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t3; +---+------+------+------+----+ | a | b | c | d | e | +---+------+------+------+----+ | 1 | NULL | NULL | foo | 42 | | 2 | NULL | NULL | foo | 42 | +---+------+------+------+----+ 2 rows in set (0.00 sec) # instant add columns mysql> alter table t3 ADD COLUMN f INT UNSIGNED DEFAULT 0; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t3 SET a=3; Query OK, 1 row affected (0.00 sec) # frm changed only mysql> ALTER TABLE t3 -> CHANGE COLUMN a a INT NOT NULL DEFAULT 101, -> CHANGE COLUMN b b INT DEFAULT 102, -> CHANGE COLUMN c c INT DEFAULT 103, -> CHANGE COLUMN d d char(10) DEFAULT 'eleventy', -> CHANGE COLUMN e e INT NOT NULL DEFAULT 105, -> CHANGE COLUMN f f INT UNSIGNED DEFAULT 106; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into t3 set a = 4; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t3; +---+------+------+----------+-----+------+ | a | b | c | d | e | f | +---+------+------+----------+-----+------+ | 1 | NULL | NULL | foo | 42 | 0 | | 2 | NULL | NULL | foo | 42 | 0 | | 3 | 5 | 10 | NULL | 42 | 0 | | 4 | 102 | 103 | eleventy | 105 | 106 | +---+------+------+----------+-----+------+ 4 rows in set (0.00 sec) # the default values of added columns in dictionary should be removed. mysql> truncate table t3; Query OK, 0 rows affected (0.00 sec)