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

This article mainly explains "how to test the MySQL8.0.16 second plus field function". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to test the MySQL8.0.16 second plus field function".

The Instant add column feature was introduced in MySQL version 8.0.12, and the INSTANT operation modifies only the metadata in the data dictionary. During preparation and implementation

No exclusive metadata lock is used on the table, and the table data is not affected, so that the operation takes effect immediately. Allow concurrency of DML.

InnoDB only supports INSTANT to do the following:

Change index option changes indexing options

Rename table (in ALTER way) rename table (in ALTER mode)

SET/DROP DEFAULT sets / removes default values

Add columns (non-generated)-We call this instant ADD COLUMN add column (non-generated)-we call it immediate add column

MODIFY COLUMN modify column

Add/drop virtual columns add / remove virtual columns

Add a new column as the last column in the table.

Add the generated virtual column.

Delete the generated virtual column.

Sets the default value for an existing column.

Delete the default values for existing columns.

Change the list of values allowed for columns with ENUM or SET data types. The requirement is that the storage size of the column does not change.

Limitations of instant functionality:

Adding columns to only one statement is supported, that is, if there are other non-INSTANT operations in the same statement, they cannot be executed immediately

Innodb line format cannot be COMPRESSED.

There can be no full-text index on this table.

Columns that are added immediately cannot be competitive.

Columns can only be added sequentially. Columns can only be added at the end, but not in the middle of existing columns.

Compression table is not supported

Tables that contain any full-text indexes are not supported

Temporary tables are not supported. Temporary tables can only use copy to execute DDL.

Tables created in the data dictionary tablespace are not supported

Tables in the data dictionary cannot use the instant algorithm

The experiments are as follows:

Mysql > CREATE TABLE `test` (

-> `ID`int (11) NOT NULL AUTO_INCREMENT

-> `NAME` varchar (50) NOT NULL

-> PRIMARY KEY (`ID`)

->) AUTO_INCREMENT=1000

Query OK, 0 rows affected (0.19 sec)

Mysql > delimiter $$

Mysql > create procedure pro_test ()

-> begin

-> declare id int

-> set id = 100000

-> while id > 0 do

-> insert into test (name) values ('love')

-> set id = id-1

-> end while

-> end $$

Query OK, 0 rows affected (0.04 sec)

Mysql > delimiter

Mysql > call pro_test ()

Mysql > call pro_test ()

Mysql > call pro_test ()

Mysql > call pro_test ()

Mysql > call pro_test ()

Mysql > call pro_test ()

Mysql > call pro_test ()

Execute a few more times to generate more data.

Mysql > select count (*) from test

+-+

| | count (*) |

+-+

| | 20547289 |

+-+

1 row in set (1 min 6.85 sec)

Second plus field test:

Mysql > alter table test add addr varchar (10), ALGORITHM=INSTANT

Query OK, 0 rows affected (4.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > ALTER TABLE test ADD COLUMN c ENUM ('averse,' baked,'c'), ALGORITHM = INSTANT

Query OK, 0 rows affected (0.12 sec)

Records: 0 Duplicates: 0 Warnings: 0

4.06 seconds for the first time and 0.12 seconds for the second time.

Rename:

Mysql > ALTER TABLE test RENAME TO T2, ALGORITHM = INSTANT

Query OK, 0 rows affected (0.19 sec)

Mysql > ALTER TABLE T2 RENAME TO test, ALGORITHM = INSTANT

Query OK, 0 rows affected (0.10 sec)

Set the column defaults:

Mysql > ALTER TABLE test ALTER COLUMN name SET DEFAULT 100, ALGORITHM = INSTANT

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

Delete column defaults:

Mysql > ALTER TABLE test alter COLUMN name DROP DEFAULT, ALGORITHM = INSTANT

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

Modify the column:

Mysql > ALTER TABLE test MODIFY COLUMN c ENUM ('averse,' baked, 'clocked,' dumped,'e'), ALGORITHM=INSTANT

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

Change the index to apply to existing indexes on the table:

Mysql > > show index from test\ G

* * 1. Row *

Table: test

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: ID

Collation: A

Cardinality: 19998192

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

* 2. Row * *

Table: test

Non_unique: 1

Key_name: name

Seq_in_index: 1

Column_name: NAME

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

Visible: YES

Expression: NULL

2 rows in set (0.04 sec)

Mysql > ALTER TABLE test DROP index name, ADD index name (name), ALGORITHM = INSTANT

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

However, adding a new index to other non-indexed columns is not supported:

Mysql > alter table test ADD index addr (addr), ALGORITHM = INSTANT

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.

Try ALGORITHM=COPY/INPLACE.

Add virtual columns:

ALTER TABLE test ADD COLUMN (d INT GENERATED ALWAYS AS (1 + 1) VIRTUAL), ALGORITHM = INSTANT

Query OK, 0 rows affected (2.83 sec)

Records: 0 Duplicates: 0 Warnings: 0

Ysql > desc test

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | ID | int (11) | NO | PRI | NULL | auto_increment |

| | NAME | varchar (50) | NO | | NULL |

| | addr | varchar (10) | YES | | NULL |

| | ip | int (11) | YES | | NULL |

| | c | enum ('axiomagenics, cinematography, cinematography, cinnamon, phonetics, etc.) | YES | | NULL | |

| | d | int (11) | YES | | NULL | VIRTUAL GENERATED | |

+-- +

Delete the virtual column:

Mysql > ALTER TABLE test DROP COLUMN d, ALGORITHM = INSTANT

Query OK, 0 rows affected (0.48 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc test

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | ID | int (11) | NO | PRI | NULL | auto_increment |

| | NAME | varchar (50) | NO | MUL | NULL |

| | addr | varchar (10) | YES | | NULL |

| | ip | int (11) | YES | | NULL |

| | c | enum ('axiomagenics, cinematography, cinematography, cinnamon, phonetics, etc.) | YES | | NULL | |

+-- +

5 rows in set (0.04 sec)

However, deleting normal columns is not supported:

Mysql > ALTER TABLE test DROP c, ALGORITHM = INSTANT

ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation.

Try ALGORITHM=COPY/INPLACE.

In addition, users can view the results of the real-time ADD COLUMN through the view from information_schema:

Mysql > SELECT table_id, name, instant_cols FROM information_schema.innodb_tables WHERE name LIKE 'test%'

+-+

| | table_id | name | instant_cols | |

+-+

| | 1060 | test/child | 0 | |

| | 1064 | test/t1 | 0 | |

| | 1065 | test/tbl | 0 | |

| | 1068 | test/employees | 0 | |

| | 1072 | test/test_null | 0 | |

| | 1073 | test/test | 2 | |

+-+

6 rows in set (0.00 sec)

Thank you for your reading, the above is the content of "how to test MySQL8.0.16 second plus field function". After the study of this article, I believe you have a deeper understanding of how to test MySQL8.0.16 second plus field function, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.