MySQL高级特性之：`MySQL`的`Invisible Columns`：其在`DDL`操作和应用兼容性中的作用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL Invisible Columns：DDL 操作与应用兼容性深度解析

大家好，今天我们来深入探讨 MySQL 的一项相对较新的特性：Invisible Columns（不可见列）。这项特性在 MySQL 8.0.23 版本中引入，它在数据库模式演进、数据迁移以及应用兼容性维护方面扮演着重要角色。我们将通过具体的 DDL 操作示例和代码片段，详细分析 Invisible Columns 的使用方法、工作原理，以及它如何影响应用程序。

1. 什么是 Invisible Columns？

顾名思义，Invisible Columns 是指默认情况下在 SELECT * 查询中不可见的列。这意味着，当我们执行 SELECT * FROM table_name 时，这些列的数据不会被返回。然而，Invisible Columns 仍然存在于表中，并且可以通过显式地指定列名来进行查询和操作。

2. Invisible Columns 的语法

创建 Invisible Column 的语法很简单，只需在列定义中使用 INVISIBLE 关键字即可。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT INVISIBLE --  Invisible Column
);

在这个例子中，department_id 列被定义为 Invisible Column。

3. Invisible Columns 的可见性控制

*`SELECT 的影响:** 如前所述，SELECT *` 不会返回 Invisible Columns 的数据。
显式列名查询: 可以通过显式指定列名来查询 Invisible Columns。
```
SELECT id, first_name, last_name, department_id FROM employees;
```

DESCRIBE 命令: DESCRIBE table_name 命令会显示 Invisible Columns，但会通过 Extra 列中的 INVISIBLE 标识来区分。

DESCRIBE employees;

结果类似：

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
salary	decimal(10,2)	YES		NULL
department_id	int	YES		NULL	INVISIBLE

SHOW CREATE TABLE 命令: SHOW CREATE TABLE table_name 命令会显示完整的表定义，包括 Invisible Columns 的定义。
```
SHOW CREATE TABLE employees;
```
结果会包含 department_id INT INVISIBLE 这一行。

4. Invisible Columns 的 DDL 操作

ALTER TABLE: 可以使用 ALTER TABLE 语句来修改 Invisible Columns 的属性，包括将其修改为 Visible Column，反之亦然。
- 将 Invisible Column 修改为 Visible Column:
```
ALTER TABLE employees MODIFY COLUMN department_id INT VISIBLE;
```
- 将 Visible Column 修改为 Invisible Column:
```
ALTER TABLE employees MODIFY COLUMN department_id INT INVISIBLE;
```
ADD COLUMN: 可以使用 ADD COLUMN 语句添加 Invisible Columns。
```
ALTER TABLE employees ADD COLUMN hire_date DATE INVISIBLE;
```
DROP COLUMN: 可以像删除普通列一样删除 Invisible Columns。
```
ALTER TABLE employees DROP COLUMN department_id;
```

5. Invisible Columns 与应用程序兼容性

Invisible Columns 的主要优势在于它们可以帮助我们在不破坏现有应用程序的前提下修改数据库模式。

新增列: 假设我们需要向 employees 表中添加一个新列，例如 email。如果我们将 email 列直接添加为 Visible Column，那么使用 SELECT * 查询的应用程序可能会受到影响，因为它们的代码可能没有考虑到新列的出现。通过将 email 列添加为 Invisible Column，我们可以避免这种情况。
```
ALTER TABLE employees ADD COLUMN email VARCHAR(100) INVISIBLE;
```
然后，我们可以逐步修改应用程序，以便它们能够处理 email 列。一旦所有应用程序都更新完毕，我们可以将 email 列修改为 Visible Column。
数据迁移: 在数据迁移过程中，我们可能需要添加一些临时列来存储中间数据。这些列对于应用程序来说没有意义，因此可以将它们定义为 Invisible Columns，以避免对应用程序造成干扰。
遗留系统集成: 当与遗留系统集成时，可能会遇到数据格式不匹配的问题。可以使用 Invisible Columns 来存储经过转换后的数据，而原始数据仍然保留在 Visible Columns 中，以供遗留系统使用。

6. Invisible Columns 的使用场景示例

让我们通过几个具体的示例来演示 Invisible Columns 的用法。

场景 1：添加审计列

假设我们需要跟踪 employees 表中数据的修改历史。我们可以添加两个 Invisible Columns：created_at 和 updated_at，用于记录数据的创建时间和修改时间。

ALTER TABLE employees ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP INVISIBLE;
ALTER TABLE employees ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP INVISIBLE;

应用程序不需要知道这些审计列的存在，但我们可以使用它们来进行数据分析和审计。

场景 2：存储加密数据

假设我们需要对 employees 表中的 salary 列进行加密存储。我们可以添加一个 Invisible Column encrypted_salary，用于存储加密后的薪水。

ALTER TABLE employees ADD COLUMN encrypted_salary BLOB INVISIBLE;

应用程序只与 Visible Column salary 交互，而加密和解密操作在后台进行。

场景 3：简化复杂查询

有时候，我们需要执行一些复杂的查询，这些查询需要用到一些中间计算结果。我们可以将这些中间计算结果存储在 Invisible Columns 中，以简化查询的逻辑。

例如，假设我们需要计算每个员工的绩效得分，该得分取决于多个因素。我们可以将每个因素的得分存储在 Invisible Columns 中，然后使用一个简单的查询来计算总得分。

ALTER TABLE employees ADD COLUMN factor1_score INT INVISIBLE;
ALTER TABLE employees ADD COLUMN factor2_score INT INVISIBLE;
ALTER TABLE employees ADD COLUMN factor3_score INT INVISIBLE;

--  更新 Invisible Columns 的值
UPDATE employees SET
    factor1_score = calculate_factor1_score(id),
    factor2_score = calculate_factor2_score(id),
    factor3_score = calculate_factor3_score(id);

--  计算总得分
SELECT id, first_name, last_name,
       factor1_score + factor2_score + factor3_score AS total_score
FROM employees;

7. Invisible Columns 的限制

虽然 Invisible Columns 功能强大，但也有一些限制需要注意：

不能作为主键或唯一索引: Invisible Columns 不能作为主键或唯一索引的一部分。这是因为主键和唯一索引必须对所有查询都是可见的，而 Invisible Columns 默认情况下是不可见的。
存储引擎限制: 并非所有存储引擎都支持 Invisible Columns。例如，MySQL 的 MyISAM 存储引擎就不支持此功能。
版本兼容性: Invisible Columns 是 MySQL 8.0.23 版本中引入的特性，因此在较早的版本中无法使用。

8. 代码示例：使用 Python 与 MySQL 连接并操作 Invisible Columns

以下是一个使用 Python 连接 MySQL 数据库，并操作 Invisible Columns 的示例代码：

import mysql.connector

#  数据库连接配置
config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'your_host',
    'database': 'your_database'
}

try:
    #  建立数据库连接
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()

    #  创建表，包含 Invisible Column
    create_table_query = """
    CREATE TABLE IF NOT EXISTS products (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255),
        price DECIMAL(10, 2),
        description TEXT INVISIBLE
    );
    """
    cursor.execute(create_table_query)
    cnx.commit()
    print("Table 'products' created successfully.")

    #  插入数据
    insert_query = """
    INSERT INTO products (name, price, description)
    VALUES (%s, %s, %s);
    """
    data = ('Laptop', 1200.00, 'High-performance laptop with 16GB RAM and 512GB SSD.')
    cursor.execute(insert_query, data)
    cnx.commit()
    print("Data inserted successfully.")

    #  查询所有数据（不包含 Invisible Column）
    select_all_query = "SELECT * FROM products;"
    cursor.execute(select_all_query)
    results = cursor.fetchall()
    print("SELECT * results:", results)  #  description 不会显示

    #  查询所有数据（包含 Invisible Column）
    select_with_invisible_query = "SELECT id, name, price, description FROM products;"
    cursor.execute(select_with_invisible_query)
    results_with_invisible = cursor.fetchall()
    print("SELECT with description results:", results_with_invisible) # description会显示

    #  更新 Invisible Column
    update_description_query = """
    UPDATE products SET description = %s WHERE id = %s;
    """
    new_description = "Updated description: Powerful laptop for professionals."
    product_id = 1  #  假设第一条记录的 ID 为 1
    cursor.execute(update_description_query, (new_description, product_id))
    cnx.commit()
    print("Description updated successfully.")

    #  再次查询，验证更新
    cursor.execute(select_with_invisible_query)
    updated_results = cursor.fetchall()
    print("SELECT with description after update:", updated_results)

    #  将 Invisible Column 修改为 Visible
    alter_table_query = "ALTER TABLE products MODIFY COLUMN description TEXT VISIBLE;"
    cursor.execute(alter_table_query)
    cnx.commit()
    print("Column 'description' made VISIBLE.")

    #  再次查询 SELECT *，验证可见性
    cursor.execute(select_all_query)
    visible_results = cursor.fetchall()
    print("SELECT * results after making description visible:", visible_results) # description会显示

except mysql.connector.Error as err:
    print(f"Error: {err}")
finally:
    #  关闭游标和连接
    if cursor:
        cursor.close()
    if cnx:
        cnx.close()

请注意，你需要将 your_user, your_password, your_host, your_database 替换为你的实际数据库连接信息。该脚本首先创建一个包含 Invisible Column 的表，然后插入数据，查询数据（包括只查询 Visible Columns 和显式查询 Invisible Columns），更新 Invisible Column，并将 Invisible Column 修改为 Visible Column，最后再次查询以验证结果。

9. Invisible Columns 与视图 (Views)

Invisible Columns 在视图中也表现出其特性。如果在创建视图时使用 SELECT *，那么 Invisible Columns 不会被包含在视图中。

CREATE VIEW employee_view AS SELECT * FROM employees;

SELECT * FROM employee_view; -- 不包含 Invisible Columns

但是，如果我们在创建视图时显式指定列名，那么就可以包含 Invisible Columns。

CREATE VIEW employee_view AS SELECT id, first_name, last_name, salary, department_id FROM employees;

SELECT * FROM employee_view; -- 包含 department_id Invisible Column，但会显示出来

请注意，即使在视图中包含了 Invisible Columns，这些列在视图中仍然是可见的。也就是说，即使 department_id 在 employees 表中是 Invisible Column，但在 employee_view 中，它仍然会显示在 SELECT * 的结果中。视图并没有继承底层表的 Invisible 属性，它只是复制了列定义，但不复制 Invisible 属性。

10. Invisible Columns 与存储过程 (Stored Procedures)

存储过程可以访问 Invisible Columns，但需要显式地指定列名。如果存储过程使用 SELECT *，那么 Invisible Columns 不会被包含在结果集中。

以下是一个示例：

DELIMITER //
CREATE PROCEDURE get_employee_data(IN employee_id INT)
BEGIN
    SELECT id, first_name, last_name, salary, department_id FROM employees WHERE id = employee_id;
END //
DELIMITER ;

CALL get_employee_data(1); -- 包含 department_id Invisible Column

总结一下：Invisible Columns 提升模式演进的灵活性

Invisible Columns 是 MySQL 数据库中一项强大的特性，它为数据库模式的演进提供了更大的灵活性。通过将新的列添加为 Invisible Columns，可以避免对现有应用程序造成干扰，从而实现平滑的数据库升级和迁移。在数据迁移、遗留系统集成以及审计跟踪等场景中，Invisible Columns 都可以发挥重要作用。虽然有一些限制需要注意，但只要合理使用，Invisible Columns 就可以帮助我们构建更加健壮和可维护的数据库应用。

MySQL Invisible Columns：DDL 操作与应用兼容性深度解析

发表回复 取消回复

发表回复取消回复