Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the online status of MySQL5.6 Online DDL is, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Yes* and No* show that the result depends on some other additional conditions:

Perform actions to allow ALGORITHM=INPLACE

Whether to copy the table

Allow concurrent DML

Allow concurrent queries

Remarks and precautions

Create index

Add index

Yes*

No*

Yes

Yes

For full-text indexing, there are some restrictions, see the next line. At present, the operation is not performed in place, you need to copy the table.

Add fulltext index

Yes

No*

No

Yes

Creating the first full-text index involves copying the table unless the FTS_DOC_ID column is used. The later full-text index is performed in place.

Drop index

Yes

No

Yes

Yes

Optimize table

Yes

Yes

Yes

Yes

Use ALGORITHM=INPLACE in MySQL5.6.17.

If you set old_alter_table=1 or use the mysqld-skip-new option, use ALGORITHM=COPY. Exe.

ALGORITHM=INPLACE does not apply if the table uses a full-text index

Set default value for column

Yes

No

Yes

Yes

Modify the .frm file without involving the data file

Change auto-increment value

Yes

No

Yes

Yes

Modify a value stored in memory without modifying the data file

Add foreign key constraint

Yes*

No*

Yes

Yes

If you disable foreign_key_checks, you can avoid copying tables

Drop forgien key constraing

Yes

No

Yes

Yes

Foreign_key_checks can be disabled or enabled

Rename column

Yes*

No*

Yes*

Yes

Allow concurrent DML, keep the same data type, and only change the field name

Add column

Yes

Yes

Yes*

Yes

DML operation is not allowed when adding auto-increment field.

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Drop column

Yes

Yes

Yes

Yes

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Reorder columns

Yes

Yes

Yes

Yes

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Change ROW_FORMAT

Property

Yes

Yes

Yes

Yes

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Change KEY_BLOCK_SIZE

Property

Yes

Yes

Yes

Yes

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Make column null

Yes

Yes

Yes

Yes

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Make cplumn not null

Yes*

Yes

Yes

Yes

When SQL_MODE is strict_all_tables, execution fails if the executed column contains null.

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

Change data type

Of column

No

Yes

Yes

Yes

Add primary key

Yes*

Yes

Yes

Yes

Although ALGORITHM=INPLACE can allow

But the data have to be reorganized, which is more expensive.

If the column must be converted to a non-empty condition

ALGORITHM=INPLACE is not allowed.

Drop primary key

And add other

Yes

Yes

Yes

Yes

ALGORITHM=INPLACE is allowed when adding a primary key to the same alter table. The data has to be reorganized, so it is more expensive.

Drop primary key

No

Yes

No

Yes

Deleting a primary key without adding a new primary key is restricted

Convert character set

No

Yes

No

Yes

If the new character encoding is different, the table will be rebuilt.

Specify character set

No

Yes

No

Yes

If the new character encoding is different, the table will be rebuilt.

Rebulid with

Force option

Yes

Yes

Yes

Yes

Use ALGORITHM=INPLACE in MySQL5.6.17.

If you set old_alter_table=1 or use the mysqld-skip-new option, use ALGORITHM=COPY. Exe.

ALGORITHM=INPLACE does not apply if the table uses a full-text index

Rebulid with

"null"

Alter table...

Engine=innodb

Yes

Yes

Yes

Yes

Use ALGORITHM=INPLACE in MySQL5.6.17.

If you set old_alter_table=1 or use the mysqld-skip-new option, use ALGORITHM=COPY. Exe.

ALGORITHM=INPLACE does not apply if the table uses a full-text index

So much for sharing about MySQL5.6 Online DDL online status. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.