In-depth explanation of hidden fields, a new feature of MySQL 8.0

Preface

MySQL version 8.0.23 adds a new feature: Invisible Column, also known as invisible field. This article introduces the concepts and specific implementation of MySQL hidden fields.

Basic Concepts

Hidden fields need to be explicitly referenced in the query, otherwise they are not visible to the query. MySQL 8.0.23 began to support hidden fields. Before that, all fields were visible fields.

Consider the following application scenario: if an application uses the SELECT * statement to access a table and must continue to query, it is required to work properly even if we add a new field to the table that the application does not need. For the SELECT * query, the asterisk (*) represents all fields in the table except the hidden fields, so we can define the newly added fields as hidden fields. The hidden field is not visible to SELECT * queries, so the application continues to run. If a new version of your application needs to use this field, you can specify it explicitly in the query.

PS: It is not recommended to use the SELECT * statement to query data. The fields that need to be returned should be clearly specified.

Hidden fields and DDL statements

By default, the fields created are visible fields. If you want to explicitly specify the visibility of a column, you can specify the VISIBLE or INVISIBLE keywords for the column definition in the CREATE TABLE or ALTER TABLE statement. For example:

CREATE TABLE t1 (
 i INT,
 j DATE INVISIBLE
)ENGINE = InnoDB;
ALTER TABLE t1 ADD COLUMN k INT INVISIBLE;

If you want to change the visibility of a field, you can also use the VISIBLE or INVISIBLE keywords. For example:

ALTER TABLE t1 CHANGE COLUMN jj DATE VISIBLE;
ALTER TABLE t1 MODIFY COLUMN j DATE INVISIBLE;
ALTER TABLE t1 ALTER COLUMN j SET VISIBLE;

When using hidden fields, be aware of the following:

• A table needs at least one visible field. Setting all fields to hidden will return an error.
• Hidden fields support common field attributes: NULL, NOT NULL, and AUTO_INCREMENT, etc.
• A generated column can be a hidden field.
• Indexes can use hidden fields, including PRIMARY KEY and UNIQUE indexes. Although a table must have at least one visible column, an index definition does not have to include any visible columns.
• When you delete a hidden field in a table, the field is also deleted from the associated indexes.
• Foreign key constraints can be defined based on hidden fields, and foreign key constraints can also reference hidden fields.
• CHECK constraints can be defined based on hidden fields. When inserting or updating data, an error will be returned if the CHECK constraint on the hidden field is violated.
  

If you use the CREATE TABLE ... LIKE statement to copy the table structure, the hidden fields in the original table are copied and they remain hidden fields in the new table. If you copy a table using the CREATE TABLE ... SELECT statement, hidden columns are not included unless they are explicitly specified. However, even if you include hidden fields in the original table, those fields will become visible in the new table. For example:

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2_G
*************************** 1. row ***************************
  Table: t2
Create Table: CREATE TABLE `t2` (
 `col1` int DEFAULT NULL,
 `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

If you want to keep the hidden attribute for these columns, you can specify the hidden attribute for them after CREATE TABLE. For example:

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 (col2 INT INVISIBLE) AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2_G
*************************** 1. row ***************************
  Table: t2
Create Table: CREATE TABLE `t2` (
 `col1` int DEFAULT NULL,
 `col2` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Views can reference hidden fields, which need to be explicitly specified in the definition. Modifying the visibility of a field after the view is defined has no effect on the view.

Hidden fields and DML statements

For SELECT statements, hidden fields are not included in the query structure unless they are explicitly specified in the query list. * and tbl_name.* in the query list will not include hidden fields. Natural joins do not include hidden fields.

For the following statement:

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> INSERT INTO t1 (col1, col2) VALUES (1, 2), (3, 4);

mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
| 1 |
| 3 |
+------+

mysql> SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+

The first SELECT statement does not reference the hidden field col2 (* excludes hidden fields), so the col2 field is not returned in the query results. The second SELECT statement explicitly specifies the col2 field, so that field is returned in the query results.

For query statements, if no data is specified for the hidden field, the implicit default value rule is used to assign a value.

For INSERT statements (including data insertion by REPLACE statements), implicit default values ​​are assigned when no field list is specified, an empty list is specified, or no hidden fields are specified in the field list. For example:

CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
INSERT INTO t1 VALUES(...);
INSERT INTO t1 () VALUES(...);
INSERT INTO t1 (col1) VALUES(...);

For the first two INSERT statements, the VALUES() list must provide a value for each visible and hidden field. For the third INSERT statement, the VALUES() list must provide a value for each specified field.

For LOAD DATA and LOAD XML statements, implicit default values ​​are used when no field list is specified or when no hidden fields are specified in the field list. Input data cannot contain values ​​for hidden fields.

If you want to provide a non-default value for the above statement, you can explicitly specify the hidden field in the field list and specify a value in the VALUES() list.

INSERT INTO ... SELECT * and REPLACE INTO ... SELECT * do not include hidden fields because * does not return hidden fields. The implicit default value rule will also be used for assignment.

In statements that insert or ignore inserts, replaces, or modifies data based on a PRIMARY KEY or UNIQUE index, MySQL treats hidden fields the same way as visible fields: hidden fields are also used for key value comparisons. Specifically, if a new row has the same unique key field value as an existing row, the following processing is used regardless of whether the index field is visible:

• If the IGNORE modifier is specified, INSERT, LOAD DATA, and LOAD XML ignore new data rows.
• REPLACE Replaces the existing row of data with the new row of data. The same is true for LOAD DATA and LOAD XML if the REPLACE modifier is specified.
• INSERT ... ON DUPLICATE KEY UPDATE updates the existing data row.
  

If you want to update a hidden field using an UPDATE statement, you can assign a value to it explicitly, just like a visible field.

Hide metadata about fields

We can view the visible properties of the fields through the EXTRA field of the INFORMATION_SCHEMA.COLUMNS system table or the SHOW COLUMNS command. For example:

mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA |
+------------+-------------+-----------+
| t1 | i | |
| t1 | j | |
| t1 | k | INVISIBLE |
+------------+-------------+-----------+

By default the fields are visible, in which case the EXTRA field is empty. For hidden fields, EXTRA is explicitly INVISIBLE.

The SHOW CREATE TABLE command can display hidden columns in the table. The column definition contains a version-based comment that includes an INVISIBLE keyword:

mysql> SHOW CREATE TABLE t1_G
*************************** 1. row ***************************
  Table: t1
Create Table: CREATE TABLE `t1` (
 `i` int DEFAULT NULL,
 `j` int DEFAULT NULL,
 `k` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysqldump and mysqlpump use the SHOW CREATE TABLE command, so the table definitions they export include hideable fields. Also, they include the values ​​of hidden fields in the exported data. If the export file is loaded into an older version of MySQL that does not support hidden fields, the version-based comment information will be ignored, and the hidden fields will be used as visible fields.

Hidden fields and binary logs

For events in the binary log, MySQL handles hidden fields in the following way:

• The event that creates the table contains the INVISIBLE attribute for the hidden field.
• Hidden fields and visible fields are handled the same way in data row events. They are processed according to the setting of the system variable binlog_row_image.
• When data row events are applied, hidden fields are handled the same way as visible fields. The algorithm and index used are selected based on the setting of the system variable slave_rows_search_algorithms.
• Hidden fields are treated the same as visible fields when computing the writeset. The write set contains indexes based on hidden field definitions.
• The mysqlbinlog command includes visible attributes in the field metadata.

Summarize

This is the end of this article about the new feature of MySQL 8.0: hidden fields. For more information about MySQL 8.0 hidden fields, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!