MySQL 8.0新特性之隐藏字段的深入讲解

前言

MySQL 8.0.23 版本增加了一个新的功能:隐藏字段(Invisible Column),也称为不可见字段。本文给大家介绍一下 MySQL 隐藏字段的相关概念和具体实现。

基本概念

隐藏字段需要在查询中进行显式引用,否则对查询而言是不可见的。MySQL 8.0.23 开始支持隐藏字段,在此之前所有的字段都是可见字段。

考虑以下应用场景,假如一个应用程序使用SELECT *语句访问某个表,并且必需持续不断地进行查询,即使我们为该表增加了一个该应用不需要的新字段时也要求能够正常工作。对于SELECT *查询,星号(*)代表了表中除隐藏字段之外的所有字段,因此我们可以将新加的字段定义为隐藏字段。该隐藏字段对于SELECT *查询是不可见的,因此应用能够继续运行。如果新版本的应用程序需要使用该字段,可以在查询中显式指定。

PS:不推荐使用SELECT *语句查询数据,应该明确指定需要返回的字段。

隐藏字段与 DDL 语句

默认情况下创建的字段属于可见字段。如果想要显式指定字段的可见性,可以在CREATE TABLE或者ALTER TABLE语句中为字段的定义指定VISIBLE 或者 INVISIBLE 关键字。例如:

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

如果想要修改某个字段的可见性,同样可以使用 VISIBLE 或者 INVISIBLE 关键字。例如:

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

使用隐藏字段时,需要注意以下事项:

  • 一个表至少需要一个可见字段。如果将所有字段都设置为隐藏字段,将会返回错误。
  • 隐藏字段支持常见的字段属性:NULL、NOT NULL 以及 AUTO_INCREMENT 等等。
  • 计算列(Generated column)可以是隐藏字段。
  • 索引可以使用隐藏字段,包括 PRIMARY KEY 和 UNIQUE 索引。虽然一个表至少需要一个可见字段,但是索引定义中可以不包含任何可见字段。
  • 删除某个表中的隐藏字段时,同时会从相关索引中删除该字段。
  • 外键约束可以基于隐藏字段进行定义,同时外键约束也可以引用隐藏字段。
  • CHECK 约束可以基于隐藏字段进行定义。插入或者更新数据时,如果违反了隐藏字段上的 CHECK 约束将会返回错误。

如果使用CREATE TABLE ... LIKE语句复制表结构,将会复制原表中的隐藏字段,而且它们在新表中仍然是隐藏字段。如果使用CREATE TABLE ... SELECT语句复制表,不会包含隐藏字段,除非显式指定了隐藏字段。尽管如此,即使包含了原表中的隐藏字段,新表中的这些字段将会变成可见字段。例如:

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

如果想要保留这些字段的隐藏属性,可以在 CREATE TABLE 之后为它们指定隐藏属性。例如:

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

视图可以引用隐藏字段,需要在定义中显式指定这些字段。在视图定义之后修改字段的可见性不会影响视图。

隐藏字段与 DML 语句

对于 SELECT 语句,除非在查询列表中显式指定了隐藏字段,否则查询结构中不会包含隐藏字段。查询列表中的 * 和 tbl_name.* 不会包含隐藏字段。自然连接不会包含隐藏字段。

对于以下语句:

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 |
+------+------+

第一个 SELECT 语句没有引用隐藏字段 col2(* 不包含隐藏字段),因此查询结果中没有返回 col2 字段。第二个 SELECT 语句显式指定了 col2 字段,因此查询结果返回了该字段。

对于查询语句,如果没有为隐藏字段指定数据,使用隐式默认值规则进行赋值。

对于 INSERT 语句(包括 REPLACE 语句的数据插入),如果没有指定字段列表、指定空白列表或者没有在字段列表中指定隐藏字段时,使用隐式默认值赋值。例如:

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

对于前两个 INSERT 语句,VALUES() 列表必须为每个可见字段和隐藏字段提供一个数值。对于第三个 INSERT 语句,VALUES() 列表必须为每个指定的字段提供一个数值。

对于 LOAD DATA 和 LOAD XML 语句,如果没有指定字段列表或者没有在字段列表中指定隐藏字段时,使用隐式默认值赋值。输入数据中不能包含隐藏字段的值。

如果想要为上面的语句提供一个非默认的数据,可以在字段列表中显式指定隐藏字段并且在 VALUES() 列表中指定一个数值。

INSERT INTO … SELECT * 和 REPLACE INTO … SELECT * 不会包含隐藏字段,因为 * 不会返回隐藏字段。此时同样会使用隐式默认值规则进行赋值。

基于 PRIMARY KEY 或者 UNIQUE 索引执行插入或者忽略插入、替换或者修改数据的语句中,MySQL 对隐藏字段的处理方式和可见字段相同:隐藏字段同样会用于键值的比较。准确来说,如果某个新的数据行和已有数据行的唯一键字段值相同,无论索引字段是否可见,都会使用以下处理方式:

  • 如果指定了 IGNORE 修饰符,INSERT、LOAD DATA 以及 LOAD XML 都会忽略新的数据行。
  • REPLACE 使用新的数据行替换原有的数据行。如果指定了 REPLACE 修饰符,LOAD DATA 和 LOAD XML 也是如此。
  • INSERT … ON DUPLICATE KEY UPDATE 更新原有的数据行。

如果想要使用 UPDATE 语句更新隐藏字段,像可见字段一样显式进行赋值即可。

隐藏字段相关的元数据

我们可以通过 INFORMATION_SCHEMA.COLUMNS 系统表的 EXTRA 字段或者 SHOW COLUMNS 命令查看字段的可见属性。例如:

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 |
+------------+-------------+-----------+

默认情况下字段是可见的,此时 EXTRA 字段为空。对于隐藏字段,EXTRA 显式为 INVISIBLE。

SHOW CREATE TABLE 命令可以显式表中的隐藏字段,字段定义中包含一个基于版本的注释,其中包含了一个 INVISIBLE 关键字:

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 和 mysqlpump 使用 SHOW CREATE TABLE 命令,因此它们导出的表定义中包含可隐藏字段。同时,它们在导出的数据中包含了隐藏字段的值。如果将导出文件加载到不支持的隐藏字段的低版本 MySQL 中,将会忽略基于版本的注释信息,从而将隐藏字段作为可见字段使用。

隐藏字段与二进制日志

对于二进制日志中的事件,MySQL 使用以下方式处理隐藏字段:

  • 创建表的事件中包含了隐藏字段的 INVISIBLE 属性。
  • 数据行事件中的隐藏字段和可见字段处理方式相同。它们会根据系统变量 binlog_row_image的设置进行处理。
  • 当数据行事件被应用时,隐藏字段和可见字段处理方式相同。其中,使用的算法和索引基于系统变量 slave_rows_search_algorithms的设置进行选择。
  • 计算写入集(writeset)时隐藏字段和可见字段处理方式相同。写入集中包含了基于隐藏字段定义的索引。
  • mysqlbinlog 命令中包含了字段元数据中的可见属性。

总结

到此这篇关于MySQL 8.0新特性之隐藏字段的文章就介绍到这了,更多相关MySQL8.0隐藏字段内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL 8.0新特性之隐藏字段的深入讲解

    前言 MySQL 8.0.23 版本增加了一个新的功能:隐藏字段(Invisible Column),也称为不可见字段.本文给大家介绍一下 MySQL 隐藏字段的相关概念和具体实现. 基本概念 隐藏字段需要在查询中进行显式引用,否则对查询而言是不可见的.MySQL 8.0.23 开始支持隐藏字段,在此之前所有的字段都是可见字段. 考虑以下应用场景,假如一个应用程序使用SELECT *语句访问某个表,并且必需持续不断地进行查询,即使我们为该表增加了一个该应用不需要的新字段时也要求能够正常工作.对于

  • MySQL 8.0 新特性之检查约束的实现

    大家好,我是只谈技术不剪发的 Tony 老师.这次我们来介绍一个 MySQL 8.0 增加的新功能:检查约束(CHECK ). SQL 中的检查约束属于完整性约束的一种,可以用于约束表中的某个字段或者一些字段必须满足某个条件.例如用户名必须大写.余额不能小于零等. 我们常见的数据库都实现了检查约束,例如 Oracle.SQL Server.PostgreSQL 以及 SQLite:然而 MySQL 一直以来没有真正实现该功能,直到最新的 MySQL 8.0.16. MySQL 8.0.15 之前

  • MySQL 8.0 新特性之哈希连接(Hash Join)

    MySQL 开发组于 2019 年 10 月 14 日 正式发布了 MySQL 8.0.18 GA 版本,带来了一些新特性和增强功能.其中最引人注目的莫过于多表连接查询支持 hash join 方式了.我们先来看看官方的描述: MySQL 实现了用于内连接查询的 hash join 方式.例如,从 MySQL 8.0.18 开始以下查询可以使用 hash join 进行连接查询: SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; Hash join 不需要索引的支

  • MySQL 8.0新特性 — 管理端口的使用简介

    前言 下面这个报错,相信大多数童鞋都遇见过:那么碰到这个问题,我们应该怎么办呢?在MySQL 5.7及之前版本,出现"too many connection"报错,超级用户root也无法登录上去,除了重启实例,没有其他更好的解决办法:不过在MySQL 8.0版本中,是对连接管理做了一些优化,下面我们就来看一下. ERROR 1040 (HY000): Too many connections 连接管理 在MySQL 8.0版本中,对连接管理这一块,是先后做了两个比较大的改变:一个是允许

  • MySQL 8.0新特性 — 检查性约束的使用简介

    前言 在MySQL 8.0版本中,引入了一个非常有用的新特性 - 检查性约束,它可以提高对非法或不合理数据写入的控制能力:接下来我们就来详细了解一下. 检查性约束 创建.删除与查看 (1)可以在建表时,创建检查性约束 mysql> CREATE TABLE t1 -> ( -> CHECK (c1 <> c2), -> c1 INT CHECK (c1 > 10), -> c2 INT CONSTRAINT c2_positive CHECK (c2 >

  • Android Studio3.0新特性及安装图文教程

    Android Studio是Android的官方IDE.它是专为Android而打造,可以加快您的开发速度,帮助您为每款Android设备构建最优应用. 它提供专为Android开发者量身定制的工具,其中包括丰富的代码编辑.调试.测试和性能分析工具. 一.Android Studio3.0新特性 (1).核心IDE更改 我们将基础IDE从IntelliJ 2016.2升级到2017.1.2,在2016.3和 2017.1中增加了许多新功能, 包括参数提示,语义突出显示,搜索中的即时结果等等.

  • 解析MySQL8.0新特性——事务性数据字典与原子DDL

    前言 事务性数据字典与原子DDL,是MySQL 8.0推出的两个非常重要的新特性,之所以将这两个新特性放在一起,是因为两者密切相关,事务性数据字典是前提,原子DDL是一个重要应用场景. MySQL 8.0之前的数据字典 MySQL 8.0之前的数据字典,主要由以下三部分组成: (1)操作系统文件 db.opt:数据库元数据信息 frm:表元数据信息 par:表分区元数据信息 TRN/TRG:触发器元数据信息 ddl_log.log:DDL过程中产生的元数据信息 (2)mysql库下的非InnoD

  • Mysql 5.7 新特性之 json 类型的增删改查操作和用法

    目录 插入数据 :insert操作 更新记录:update 追加内容: 更新记录:追加内容到数组: 更新记录:update 修改json属性: 删除记录: 查询记录: 扩展: 一.插入一条数据: 1.查找 JSON 中的某个字段: 2.根据JSON中的某个字段查询表中记录: 二.修改 JSON 1.修改 JSON 中的某个字段: 2.往 JSON 中插入一个新的字段: 近几年来,nosql大行其道,json更是火的一塌糊涂,作为数据库的元老,mysql在5.7版本中添加了对json数据的支持.这

  • C# 8.0新特性介绍

    C# 语言是在2000发布的,至今已正式发布了7个版本,每个版本都包含了许多令人兴奋的新特性和功能更新.同时,C# 每个版本的发布都与同时期的 Visual Studio 以及 .NET 运行时版本高度耦合,这也有助于开发者更好的学习掌握 C#,并将其与 Visual Studio 以及 .NET 的使用结合起来. 加快 C# 版本的发布速度 在被称为"新微软"的推动下,微软创新的步伐也加快了.为了做到加快步伐,微软开发部门将一些过去集成在一起的技术现在都分离了出来. Visual S

  • AngularJS 2.0新特性有哪些

    AngularJS已然成为Web应用开发世界里最受欢迎的开源JavaScript框架.自成立以来,见证其成功的是惊人的经济增长以及团体的支持与采用--包括个人开发者.企业.社区. Angular已经变成一个构建复杂单页面应用的客户端MVW框架(Model-View-Whatever).它在应用测试和应用编写方面都扮演重要角色,同时简化了开发过程. Angular目前的版本为1.3,该版本稳定,并被谷歌(框架维护者)用于支持众多应用(据估计,在谷歌有超过1600个应用运行于Angular1.2或1

随机推荐