IT评测·应用市场-qidao123.com技术社区

标题: MySQL 8.0 Reference Manual（读书条记82节-- InnoDB and Online DDL (2)） [打印本页]

---

作者: 鼠扑    时间: 2024-5-16 03:15
标题: MySQL 8.0 Reference Manual（读书条记82节-- InnoDB and Online DDL (2)）
1. Column Operations

The following table provides an overview of online DDL support for column operations. An asterisk indicates additional information, an exception, or a dependency.

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes*	Yes	No*	Yes*	Yes
Dropping a column	Yes*	Yes	Yes	Yes	Yes
Renaming a column	Yes*	Yes	No	Yes*	Yes
Reordering columns	No	Yes	Yes	Yes	No
Setting a column default value	Yes	Yes	No	Yes	Yes
Changing the column data type	No	No	Yes	No	No
Extending VARCHAR column size	No	Yes	No	Yes	Yes
Dropping the column default value	Yes	Yes	No	Yes	Yes
Changing the auto-increment value	No	Yes	No	Yes	No*
Making a column NULL	No	Yes	Yes*	Yes	No
Making a column NOT NULL	No	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column	Yes	Yes	No	Yes	Yes

• Adding a column

1. ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

复制代码

INSTANT is the default algorithm as of MySQL 8.0.12, and INPLACE before that.
The following limitations apply when the INSTANT algorithm adds a column:---场景限定
• A statement cannot combine【kəmˈbaɪn 结合;联合;归并;混合;使融合；兼做;兼办;】 the addition of a column with other ALTER TABLE actions that do not support the INSTANT algorithm.
• The INSTANT algorithm can add a column at any position in the table. Before MySQL 8.0.29, the INSTANT algorithm could only add a column as the last column of the table.--因版本不同，指定位置（befor、after）会影响算法
• Columns cannot be added to tables that use ROW_FORMAT=COMPRESSED, tables with a FULLTEXT index, tables that reside【rɪˈzaɪd 居住在;定居于;】 in the data dictionary tablespace, or temporary tables. Temporary tables only support ALGORITHM=COPY.---这个共关键
• MySQL checks the row size when the INSTANT algorithm adds a column, and throws the following error if the addition exceeds the limit.--不能超过长度限定

1. ERROR 4092 (HY000): Column can't be added with ALGORITHM=INSTANT as
2. after this max possible row size crosses max permissible row size. Try
3. ALGORITHM=INPLACE/COPY.

复制代码

Before MySQL 8.0.29, MySQL does not check the row size when the INSTANT algorithm adds a column. However, MySQL does check the row size during DML operations that insert and update rows in the table.【意思是DDL的时间不检查，DML的时间检查】
• The maximum number of columns in the internal【ɪnˈtɜːrnl 里面的;本身的;内政的;体内的;心田的;(机构)内部的;】 representation【ˌreprɪzenˈteɪʃn 代表;陈述;表现;描述;支持;描绘;表现形式;维护;抗议;有代理人;】 of the table cannot exceed 1022 after column addition with the INSTANT algorithm. The error message is:--不能超过列（字段）数限定

1. ERROR 4158 (HY000): Column can't be added to tbl_name with
2. ALGORITHM=INSTANT anymore. Please try ALGORITHM=INPLACE/COPY

复制代码

 
Multiple columns may be added in the same ALTER TABLE statement. For example: --支持一次添加多列（字段）

1. ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

复制代码

A new row version is created after each ALTER TABLE ... ALGORITHM=INSTANT operation that adds one or more columns, drops one or more columns, or adds and drops one or more columns in the same operation. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.

1. mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
2. WHERE NAME LIKE 'test/t1';
3. +---------+--------------------+
4. | NAME | TOTAL_ROW_VERSIONS |
5. +---------+--------------------+
6. | test/t1 | 0 |
7. +---------+--------------------+

复制代码

When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.---64是临界值

1. ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more
2. columns can be added or dropped instantly. Please use COPY/INPLACE.

复制代码

Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially【[səbˈstænʃəli 根本上;大要上;非常;大大地;总的来说;】, making it an expensive operation. At a minimum, ALGORITHM=INPLACE, LOCK=SHARED is required.
 The table is rebuilt if ALGORITHM=INPLACE is used to add a column.
• Dropping a column

1. ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

复制代码

INSTANT is the default algorithm as of MySQL 8.0.29, and INPLACE before that.
The following limitations apply when the INSTANT algorithm is used to drop a column:
• Dropping a column cannot be combined in the same statement with other ALTER TABLE actions that do not support ALGORITHM=INSTANT.
• Columns cannot be dropped from tables that use ROW_FORMAT=COMPRESSED, tables with a FULLTEXT index, tables that reside in the data dictionary tablespace, or temporary tables. Temporary tables only support ALGORITHM=COPY.
Multiple columns may be dropped in the same ALTER TABLE statement; for example:

1. ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;

复制代码

Each time a column is added or dropped using ALGORITHM=INSTANT, a new row version is created. The INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS column tracks the number of row versions for a table. The value is incremented each time a column is instantly added or dropped. The initial value is 0.

1. mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
2. WHERE NAME LIKE 'test/t1';
3. +---------+--------------------+
4. | NAME | TOTAL_ROW_VERSIONS |
5. +---------+--------------------+
6. | test/t1 | 0 |
7. +---------+--------------------+

复制代码

When a table with instantly added or dropped columns is rebuilt by table-rebuilding ALTER TABLE or OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64, as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.

1. ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more
2. columns can be added or dropped instantly. Please use COPY/INPLACE.

复制代码

If an algorithm other than ALGORITHM=INSTANT is used, data is reorganized substantially, making it an expensive operation.
• Renaming a column

1. ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT, LOCK=NONE;

复制代码

ALGORITHM=INSTANT support for renaming a column was added in MySQL 8.0.28. Earlier MySQL Server releases support only ALGORITHM=INPLACE and ALGORITHM=COPY when renaming a column.
To permit concurrent DML, keep the same data type and only change the column name.---保持字段类型稳定，更新的只是名字
When you keep the same data type and [NOT] NULL attribute, only changing the column name, the operation can always be performed online.---并且NULL属性值也没有调解
Renaming a column referenced from another table is only permitted with ALGORITHM=INPLACE.【此时，只能是INPLACE】 If you use ALGORITHM=INSTANT, ALGORITHM=COPY, or some other condition that causes the operation to use those algorithms, the ALTER TABLE statement fails.
ALGORITHM=INSTANT supports renaming a virtual column; ALGORITHM=INPLACE does not.
ALGORITHM=INSTANT and ALGORITHM=INPLACE do not support renaming a column when adding or dropping a virtual column in the same statement. In this case, only ALGORITHM=COPY is supported.
• Reordering columns

To reorder columns, use FIRST or AFTER in CHANGE or MODIFY operations.

1. ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

复制代码

Data is reorganized substantially, making it an expensive operation.
• Changing the column data type

1. ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

复制代码

 Changing the column data type is only supported with ALGORITHM=COPY.
• Extending VARCHAR column size

1. ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

复制代码

The number of length bytes required by a VARCHAR column must remain【rɪˈmeɪn 保持稳定；仍然存在，继续存在；】 the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY). For example, attempting to change VARCHAR column size for a single byte character set from VARCHAR(255) to VARCHAR(256) using in-place ALTER TABLE returns this error:

1. ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
2. ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

复制代码

Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).
• Setting a column default value

1. ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

复制代码

Only modifies table metadata. Default column values are stored in the data dictionary.
• Dropping a column default value

1. ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

复制代码

• Changing the auto-increment value

1. ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

复制代码

Modifies a value stored in memory, not the data file.
In a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically【ˌpiriˈɑdɪkəli】 empty all the tables and reload them, and restart the auto-increment sequence from 1.
• Making a column NULL

1. ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

复制代码

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
• Making a column NOT NULL

1. ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

复制代码

Rebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits【prəˈhɪbɪts (尤指以法令)禁止;阻止;使不可能;】 changes to foreign key columns that have the potential【pəˈtenʃl 潜在的;可能的;】 to cause loss of referential【refəˈrenʃl】 integrity【参照完备性】.Data is reorganized substantially, making it an expensive operation.
• Modifying the definition of an ENUM or SET column

1. CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
2. ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;

复制代码

Modifying the definition of an ENUM or SET column by adding new enumeration【ɪˌnuːməˈreɪʃn】 or set members to the end of the list of valid member values may be performed instantly or in place, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy【个数和类型长度很重要】.
2. Generated Column Operations

The following table provides an overview of online DDL support for generated column operations.

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a STORED column	No	No	Yes	No	No
Modifying STORED column order	No	No	Yes	No	No
Dropping a STORED column	No	Yes	Yes	Yes	No
Adding a VIRTUAL column	Yes	Yes	No	Yes	Yes
Modifying VIRTUAL column order	No	No	Yes	No	No
Dropping a VIRTUAL column	Yes	Yes	No	Yes	Yes

• Adding a STORED column

1. ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) STORED), ALGORITHM=COPY;

复制代码

ADD COLUMN is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server.
• Modifying STORED column order

1. ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED FIRST, ALGORITHM=COPY;

复制代码

Rebuilds the table in place.
• Dropping a STORED column

1. ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INPLACE, LOCK=NONE;

复制代码

Rebuilds the table in place.
• Adding a VIRTUAL column

1. ALTER TABLE t1 ADD COLUMN (c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL), ALGORITHM=INSTANT;

复制代码

Adding a virtual column can be performed instantly or in place for non-partitioned tables.
Adding a VIRTUAL is not an in-place operation for partitioned tables.
• Modifying VIRTUAL column order

1. ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL FIRST, ALGORITHM=COPY;

复制代码

• Dropping a VIRTUAL column

1. ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;

复制代码

Dropping a VIRTUAL column can be performed instantly or in place for non-partitioned tables.
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)

Powered by Discuz! X3.4