New features interpretation | MySQL 8.0.23 new features invisible fields

Author: Wang Fuxiang

A member of the Aikesheng DBA team, responsible for customer database fault handling and tuning. Good at troubleshooting and performance optimization. Have a strong interest in database-related technologies and like to analyze various logics.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

In MySQL 8.0.23 version, a new feature is added: invisible attributes can be attached to fields. For queries with non-specified fields, the contents of invisible fields are hidden by default. This function can be used when you need to add fields to the table and need to be hidden from the existing business system, and when you need to add primary key fields or index fields to the table.

Characteristic description

Before MySQL version 8.0.23, all table fields are visible fields. After version 8.0.23, invisible attributes can be added to the fields. By default, operations such as select * are hidden, and will only be displayed when the field value is specified in the sql statement
Official website link: https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

Feature display

1. Create a new table and assign invisible attributes to the fields (INVISIBLE)

mysql> CREATE TABLE t1 (id INT, name varchar(10) ,age INT INVISIBLE);
Query OK, 0 rows affected (0.02 sec)

2. The field information of invisible fields can be viewed in the table building statement and the table structure

| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |

+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| id    | int         | YES  |     | NULL    |           |
| name  | varchar(10) | YES  |     | NULL    |           |
| age   | int         | YES  |     | NULL    | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+

3. In the EXTRA field value in the system table INFORMATION_SCHEMA.COLUMNS, you can also view the invisible attribute of the table field value.

mysql>  select TABLE_NAME, COLUMN_NAME, EXTRA from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='t1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA     |
+------------+-------------+-----------+
| t1         | id          |           |
| t1         | name        |           |
| t1         | age         | INVISIBLE |
+------------+-------------+-----------+
3 rows in set (0.00 sec)

Precautions

1. Every table must have at least one visible field.
2. Invisible fields are allowed to be defined as primary keys or to create 2-level indexes, and self-incremental attributes can also be defined. Suitable for adding primary keys or indexes to existing tables.
3. When using DML statements, if the value of an invisible field is involved, the invisible field needs to be displayed and specified in the sql statement. Otherwise, invisible fields will be ignored for parsing and processing of DML statements according to the default method. Similarly, create table as select, insert into select, etc., invisible fields need to be specified in plain text, otherwise they will not be processed by default.

mysql> insert into t1  values(2,'mqd',23);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t1  values(2,'mqd');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | wfx  |
|    2 | mqd  |
+------+------+
2 rows in set (0.00 sec)

mysql> select id,name,age from t1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | wfx  |   25 |
|    2 | mqd  | NULL |
+------+------+------+
2 rows in set (0.00 sec)

4. When using select...outfile and load data methods to import and export tables with invisible fields, invisible columns are not processed by default. If you need to export data in invisible columns or import data into invisible columns, you also need to specify the field name in plain text.

mysql> load data infile "/tmp/t1.sql" into table t1;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

mysql> load data infile "/tmp/t1.sql" into table t1 (id,name,age);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

5. Allow to attach invisible attributes to existing fields

mysql> ALTER TABLE t1 MODIFY COLUMN name varchar(10) INVISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+-------------+------+-----+---------+-----------+
| Field | Type        | Null | Key | Default | Extra     |
+-------+-------------+------+-----+---------+-----------+
| id    | int         | YES  |     | NULL    |           |
| name  | varchar(10) | YES  |     | NULL    | INVISIBLE |
| age   | int         | YES  |     | NULL    | INVISIBLE |
+-------+-------------+------+-----+---------+-----------+
3 rows in set (0.01 sec)

6. When using mysqldump to back up, the logical backup file comes with a table creation statement for invisible fields, and the field value of the inserted data is specified in clear text.

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` (`id`, `name`, `age`) VALUES (1,'wfx',25),(2,'mqd',NULL);
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

7. Binlog will record the invisible column field attributes of DDL. When binlog is in STATEMENT mode, the original DML statement will be recorded. When binlog is in ROW mode, if the invisible column has a value, it will also be recorded.

### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='wfx' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3=25 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='mqd' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @3=NULL /* INT meta=0 nullable=1 is_null=1 */

in conclusion

This new feature of invisible fields is a supplement to the MySQL table structure system. Not only allows users to make structural changes to the used tables, but also takes into account the needs of the business side. It makes up for the problem of creating the wrong table structure in the early stage of the business. To a certain extent, the fault tolerance rate of MySQL in use is improved.