关于mysql:新特性解读-MySQL-8023-新特性不可见字段

作者：王福祥

爱可生 DBA 团队成员，负责客户的数据库故障解决以及调优。善于故障排查及性能优化。对数据库相干技术有浓重的趣味，喜爱剖析各种逻辑。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

在 MySQL 8.0.23 版本中新增加了一个性能：能够给字段附加不可见属性。对于非指定字段的查问语句默认暗藏不可见字段的内容。该性能可实用于须要给表增加字段并须要对已有的业务零碎暗藏时应用以及给表增加主键字段或索引字段时应用。

MySQL 从 8.0.23 版本之前，所有表的字段均为可见字段，在 8.0.23 版本之后，能够给字段增加不可见属性。默认对 select * 等操作暗藏，只有当 sql 语句中指定该字段值时才会显示
官网连贯：https://dev.mysql.com/doc/ref…

1、新建一张表，给当中字段赋予不可见属性（INVISIBLE）

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

2、建表语句以及表构造中均能够查看到不可见字段的字段信息

| 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、在零碎表 INFORMATION_SCHEMA.COLUMNS 中的 EXTRA 字段值中也能够查看到表字段值的不可见属性。

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)

1、每张表必须要有至多一个可见字段。
2、不可见字段容许被定义为主键或创立 2 级索引，也能够定义自增属性。适宜给已有的表增加主键或者索引。
3、应用 DML 语句时，如果波及到不可见字段的值，须要在 sql 语句中显示指定该不可见字段。否则会依照默认形式，疏忽不可见字段进行 DML 语句的解析和解决。同理 create table as select，insert into select 等，不可见字段须要明文指定，否则按默认不做解决。

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、应用 select…outfile 以及 load data 形式导入导出含不可见字段表时，默认对不可见列不做解决。如果须要导出不可见列的数据或者将数据导入至不可见列中，也须要明文指定字段名。

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、容许给已有的字段附件不可见属性

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、应用 mysqldump 备份时，逻辑备份文件自带不可见字段的建表语句，并明文指定了插入数据的字段值。

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 会记录 DDL 的不可见列字段属性。当 binlog 为 STATEMENT 模式时，原始 DML 语句会被记录，当 binlog 为 ROW 模式时，不可见列如果有值，也会被记录。

### 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 */

不可见字段这项新性能是对 MySQL 表构造体系的一种补充。不仅容许用户对已应用的表做构造变更并且兼顾业务侧的须要。补救了业务初期创立谬误表构造这样的问题点。肯定水平上晋升了 MySQL 在应用上的容错率。

关于mysql:新特性解读-MySQL-8023-新特性不可见字段

个性形容

个性展现

注意事项

论断