表加密和列加密

表加密和列加密适合需要对敏感字段进行额外保护，并在元数据中标记加密属性的场景。例如，业务系统可以对身份证号、手机号、密钥材料、敏感配置等列启用列加密，普通查询返回密文，只有在 SQL 中显式调用 column_decryption() 时才读取明文。

本文介绍的功能包含两部分：

  • 表级 ENCRYPTION='Y'：用于标记表的加密属性，并在 SHOW CREATE TABLE 与 INFORMATION_SCHEMA.TABLES 中回显。
  • 列级 ENCRYPTION='Y'：用于标记列为加密列。查询该列或包含该列的普通表达式时，结果会以密文形式返回；使用 column_decryption() 时返回明文。

使用场景

  • 对表中的敏感字段进行列级加密，降低明文直接暴露在查询结果中的风险。
  • 在 DDL 和元数据查询中标记某张表或某些列已启用加密。
  • 在应用默认读取密文的同时，允许在受控 SQL 中显式解密读取明文。
  • 对需要兼顾兼容 MySQL 协议访问和字段保护的场景提供轻量级加密能力。

使用前提与开启方式

表加密和列加密由全局系统变量 pkdb_eal 控制，默认值为 OFF。

在使用 ENCRYPTION='Y' 之前，先确认并开启该变量：

SHOW GLOBAL VARIABLES LIKE 'pkdb_eal';
SET GLOBAL pkdb_eal = ON;

开启后：

  • 可以在 CREATE TABLE 中使用表级 ENCRYPTION='Y'。
  • 可以在列定义中使用列级 ENCRYPTION='Y'。
  • 可以在 ALTER TABLE ... ADD COLUMN 中为新列指定 ENCRYPTION='Y'。
  • 加密列查询结果默认返回密文，可通过 column_decryption() 返回明文。

关闭该变量后：

  • 使用 ENCRYPTION='Y' 的建表或加列语句会报错，错误信息中包含 pkdb_eal is off。
  • 已省略 ENCRYPTION 或显式使用 ENCRYPTION='N' 的对象按普通表或普通列处理。

用法概览

表加密和列加密都使用 ENCRYPTION 选项，支持以下写法：

  • 表级：CREATE TABLE ... ENCRYPTION='Y'
  • 列级：col_name data_type ENCRYPTION='Y'
  • 新增列：ALTER TABLE ... ADD COLUMN ... ENCRYPTION='Y'

其中：

  • ENCRYPTION='Y' 表示启用加密标记。
  • ENCRYPTION='N' 表示不启用加密，与未指定时行为一致。

表级加密

表级加密用于标记表的加密属性。启用后：

  • SHOW CREATE TABLE 会在表定义末尾显示 ENCRYPTION='Y'。
  • INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS 会显示 ENCRYPTION='Y'。

示例如下：

CREATE TABLE t_meta (
    id INT PRIMARY KEY,
    secret VARCHAR(255) ENCRYPTION='Y'
) ENCRYPTION='Y';

列级加密

列级加密用于控制列值的查询返回行为。启用后：

  • 直接查询加密列时返回密文。
  • 查询包含加密列的普通表达式时，结果仍按密文返回。
  • 使用 column_decryption() 查询时返回明文。

示例如下：

CREATE TABLE t_secret (
    id INT PRIMARY KEY,
    secret VARCHAR(255) ENCRYPTION='Y'
);

INSERT INTO t_secret VALUES (1, 'this is secret');

SELECT secret FROM t_secret;
SELECT column_decryption(secret) FROM t_secret;

支持的数据类型

当前实现已覆盖以下常见列类型的列级加密场景：

  • 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
  • 浮点与定点类型：FLOAT、DOUBLE、DECIMAL
  • 日期与时间类型：DATE、DATETIME、TIMESTAMP、TIME、YEAR
  • 字符与二进制类型：CHAR、VARCHAR、BINARY、VARBINARY、BIT
  • 大对象类型：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB、TEXT
  • 其他类型：ENUM、SET、JSON

SQL 示例

下面的示例展示了如何创建启用表加密和列加密的表、插入数据、查看元数据，以及通过 column_decryption() 读取明文：

SET GLOBAL pkdb_eal = ON;

DROP TABLE IF EXISTS t_encrypt;

CREATE TABLE t_encrypt (
    id INT PRIMARY KEY,
    secret VARCHAR(255) ENCRYPTION='Y',
    note VARCHAR(64)
) ENCRYPTION='Y';

SHOW CREATE TABLE t_encrypt;

SELECT table_name, create_options
FROM information_schema.tables
WHERE table_schema = 'test' AND table_name = 't_encrypt';

INSERT INTO t_encrypt VALUES (1, 'this is secret', 'plain note');

SELECT id, secret, note FROM t_encrypt;

SELECT
    id,
    column_decryption(secret) AS secret_plain,
    note
FROM t_encrypt;

SELECT concat(secret, 'x') AS cipher_expr FROM t_encrypt;

SELECT column_decryption(concat(secret, 'x')) AS plain_expr FROM t_encrypt;

ALTER TABLE t_encrypt ADD COLUMN ext VARCHAR(10) ENCRYPTION='Y';

SHOW CREATE TABLE t_encrypt;

DROP TABLE t_encrypt;

在上面的示例中：

  • SHOW CREATE TABLE 会显示列级和表级的 ENCRYPTION='Y'。
  • information_schema.tables.create_options 会显示表级 ENCRYPTION='Y'。
  • SELECT secret FROM t_encrypt 返回密文。
  • SELECT column_decryption(secret) FROM t_encrypt 返回明文 this is secret。
  • 对包含加密列的普通表达式，如 concat(secret, 'x')，结果仍按密文返回。
  • 如果希望按明文参与表达式计算，应先调用 column_decryption()，或对表达式结果再调用 column_decryption()。

注意事项与限制

  • 表加密和列加密功能受全局系统变量 pkdb_eal 控制，默认关闭。
  • 当 pkdb_eal = OFF 时，使用 ENCRYPTION='Y' 的建表或加列语句会报错。
  • ENCRYPTION='N' 与未指定 ENCRYPTION 的行为一致，不会启用加密；SHOW CREATE TABLE 和 INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS 也不会显示 ENCRYPTION='N'。
  • 当前实现中，直接决定查询结果是否返回密文的是列级加密标记。若需要保护列值，建议对需要保护的列显式指定 ENCRYPTION='Y'。
  • 对加密列执行普通查询时返回的是密文；如果查询表达式中包含加密列，表达式结果也会按密文返回。
  • column_decryption() 用于显式读取明文。该函数既可以读取加密列，也可以读取符合当前加密格式的字符串表达式结果。
  • SHOW CREATE TABLE 会回显表级与列级 ENCRYPTION='Y'；INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS 仅回显表级 ENCRYPTION='Y'。
  • 当前已验证 CREATE TABLE、ALTER TABLE ... ADD COLUMN 两类 DDL 用法。对于其他 ALTER TABLE 修改已有列加密属性的场景，本文暂不展开。
  • 预处理语句和二进制协议读取加密列时，同样返回密文。

相关文档

下载 PDF