刘俊凯 发表于 2025-3-24 06:24:44

5千字总结冷门的MySQL虚拟字段(虚拟列)

MySQL虚拟字段,因不是必用且耗算力。所以是冷门的存在,许多开发者都会忽略它。当然存在就有价值,花时间整理了相关知识点。
MySQL虚拟字段(或叫虚拟列)


[*]官方文档:https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html
[*]极简概括:一种特别字段,它们的值不是直接存储在表中,而是在查询时根据所填写表达式(或结合其它字段)计算出来。关于虚拟字段的操作,属于DDL。
[*]解决问题:在数据天生时,通过表达式动态天生数据,方便展示与免除客户端调用时的计算。
[*]使用场景:当需要直观展示单个或多个字段的动态计算结果。
[*]长处:

[*]虚拟列(VIRTUAL)类型不占用空间,亲测可支持索引,可以用于 WHERE 条件,提高查询机动性。
[*]存储列(STORED)可以创建索引,相比于VIRTUAL斲丧更少的算力,提高查询性能。

[*]缺点:

[*]计算开销:VIRTUAL每次查询都会实时计算。
[*]版本限制:需要 MySQL 5.7+ 版本才支持虚拟字段。

[*]分类:

[*]虚拟列(VIRTUAL):列值不存储,但在读取行时(在任何 BEFORE触发器之后)立即进行计算,虚拟列不占用存储空间,亲测支持被索引。
[*]存储列(STORED):插入或更新行时计算。存储的列需要存储空间,支持被索引。




[*]语法
GENERATED ALWAYS体现该列的值总是根据其他列的计算结果主动天生,而不是手动插入或更新。
ALTER TABLE `表名` ADD COLUMN `虚拟字段名` 值类型 GENERATED ALWAYS AS (表达式) VIRTUAL或STORED NOT NULL;

[*]这里有一张测试表
CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表主键id',
`num` int NOT NULL COMMENT '存储数字',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[*]添加虚拟字段,用于比num字段 + 1
ALTER TABLE `test` ADD COLUMN `num_v` int GENERATED ALWAYS AS (num + 1) VIRTUAL NOT NULL AFTER `num`;

[*]新增两条数据
INSERT INTO `test` (`num`) VALUES (1)
INSERT INTO `test` (`num`) VALUES (2)

[*]查询得出如下值
id        num        num_v
1        1        2
2        2        3删

ALTER TABLE `表名`DROP COLUMN `虚拟字段名称`;改

ALTER TABLE `表名` MODIFY COLUMN `虚拟字段` int GENERATED ALWAYS AS (表达式) VIRTUAL或STORED NOT NULL;查

show create table 表名
desc 表名虚拟字段使用束缚


[*]答应使用文字、确定性内置函数和运算符。假如给定表中的相同数据,多次调用会产生相同的结果(与毗连的用户无关),则该函数是确定性的。非确定性且不符合此界说的函数示例: CONNECTION_ID()、 CURRENT_USER(), NOW()。
[*]不答应使用存储函数和可加载函数。
[*]不答应使用存储过程和函数参数。
[*]不答应使用变量(系统变量、用户界说变量和存储的程序局部变量)。
[*]不答应使用子查询。
[*]天生列界说可以引用其他天生列,但只能引用表界说中较早出现的列。天生列界说可以引用表中的任何根本(非天生)列,无论其界说发生的时间早晚。
[*]该AUTO_INCREMENT属性不能在天生的列界说中使用。
[*]在天生的列界说中,不能将列AUTO_INCREMENT用作基列。
[*]假如表达式计算导致截断或向函数提供不精确的输入,则 CREATE TABLE语句将以错误终止而且 DDL 操作被拒绝。
[*]存储天生列上的外键束缚不能使用 CASCADE、SET NULL或 SET DEFAULT作为ON UPDATE 参照动作,也不能使用SET NULL 或SET DEFAULT作为ON DELETE参照动作。
[*]存储天生列的基列上的外键束缚不能使用CASCADE、 SET NULL或SET DEFAULT 作为ON UPDATE或ON DELETE 引用操作。
[*]外键束缚不能引用虚拟天生列。
[*]触发器不能使用或用来引用天生的列。 NEW.col_nameOLD.col_name
[*]对于INSERT、 REPLACE和 UPDATE,假如明确插入、替换或更新天生的列,则唯一答应的值为DEFAULT。
[*]视图中的天生列被视为可更新,由于可以为其赋值。但是,假如明确更新此类列,则唯一答应的值是 DEFAULT。
虚拟、天生字段对索引的支持


[*]先说结论:STORED类型有存储的文件实体,因此可以添加索引,而VIRTUAL类型的没有文件实体,但是也支持索引。
[*]论证:
建表
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表id',
`str` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '测试用字符串',
`str_reverse_v` varchar(20) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (reverse(`str`)) VIRTUAL NOT NULL COMMENT '反转字符串,虚拟字段',
`str_upper_s` varchar(20) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (upper(`str`)) STORED NOT NULL COMMENT '大写,生成字段',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


新增两条数据
INSERT INTO `test` (`str`) VALUES ('abc');
INSERT INTO `test` (`str`) VALUES ('xyz');

尝试将两个虚拟字段添加索引,没发现报错
ALTER TABLE `test` ADD INDEX(`str_reverse_v`), ADD INDEX(`str_upper_s`);

用explain分析,发现确实用到了索引
explain SELECT str_reverse_v FROM `test` where str_reverse_v = 'mn';
id        select_type        table        partitions        type        possible_keys        key        key_len        ref        rows        filtered        Extra
1        SIMPLE        test                ref        str_reverse_v        str_reverse_v        82        const        1        100.00        Using index

explain SELECT str_upper_s FROM `test` where str_upper_s = 'mn';
id        select_type        table        partitions        type        possible_keys        key        key_len        ref        rows        filtered        Extra
1        SIMPLE        test                ref        str_upper_s        str_upper_s        82        const        1        100.00        Using index常见用法举例


[*]格式化数字IP为标准IPv4地点格式
CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表主键id',
`ip` bigint NOT NULL DEFAULT '0' COMMENT 'IPV4 数字格式',
`ip_v` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (inet_ntoa(`ip`)) STORED NOT NULL COMMENT 'IPV4 字符串格式',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `test` (`ip`) VALUES (2130706433);
INSERT INTO `test` (`ip`) VALUES (3232235776);


select * from test

id        ip                ip_v
1        2130706433        127.0.0.1
2        3232235776        192.168.1.0

[*]JSON数据提取
CREATE TABLE `test` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表主键id',
`user_info` json NOT NULL COMMENT '用户信息',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


假设要插入一条多层级的json数据,如下
{
    "name": "张三",
    "gender": "男",
    "age": 8,
    "birthplace": "北京市",
    "occupation": "学生",
    "hobbies": [
      "打篮球",
      "读书"
    ],
    "contact": {
      "phone": "18888888888",
      "email": "zhangsan@example.com"
    },
    "examination": [
      {
            "subject": "语文",
            "score": 80
      },
      {
            "subject": "数学",
            "score": 80
      },
      {
            "subject": "英文",
            "score": 80
      }
    ]
}

INSERT INTO `test` (`id`, `user_info`) VALUES (1, '{\"age\": 8, \"name\": \"张三\", \"gender\": \"男\", \"contact\": {\"email\": \"zhangsan@example.com\", \"phone\": \"18888888888\"}, \"hobbies\": [\"打篮球\", \"读书\"], \"birthplace\": \"北京市\", \"occupation\": \"学生\", \"examination\": [{\"score\": 80, \"subject\": \"语文\"}, {\"score\": 80, \"subject\": \"数学\"}, {\"score\": 80, \"subject\": \"英文\"}]}');



可需求是就像查看这个学生考了哪些科目,其它数据都不重要
此时就需要定向取值,若取单条,可用
SELECT json_unquote(json_extract(`user_info`,_utf8mb4'$.examination[*].subject')) as subjects FROM `test`


若是多条,可以新建一个虚拟字段,可以很直观的展示考取了哪些科目
ALTER TABLE `test`
ADD COLUMN `subjects` json AS (json_unquote(json_extract(`user_info`,_utf8mb4'$.examination[*].subject'))) STORED NOT NULL AFTER `user_info`;


select id, subjects from test
id        subjects
1        ["语文", "数学", "英文"]

[*]日期格式化(需留意时区)
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表id',
`timestamp` int(11) NOT NULL COMMENT '时间戳',
`date_time` datetime GENERATED ALWAYS AS (from_unixtime(`timestamp`)) STORED COMMENT '格式化时间戳 存储字段',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `test` (`timestamp`) VALUES (1700000000);


select * from test

id        timestamp        date_time
1        1700000000        2023-11-15 06:13:20

[*]状态判断
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表id',
`age` int(11) NOT NULL COMMENT '年龄',
`age_v` varchar(2) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS ((case when (`age` < 12) then '儿童' when (`age` between 12 and 18) then '少年' when (`age` between 19 and 40) then '青年' when (`age` between 41 and 60) then '壮年' else '老年' end)) STORED NOT NULL COMMENT '年龄可视化判断',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `test` (`age`) VALUES (11);
INSERT INTO `test` (`age`) VALUES (16);
INSERT INTO `test` (`age`) VALUES (25);
INSERT INTO `test` (`age`) VALUES (53);
INSERT INTO `test` (`age`) VALUES (65);


SELECT * FROM test

id        age        age_v
1        11        儿童
2        16        少年
3        25        青年
4        53        壮年
5        65        老年

[*]等

免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。
页: [1]
查看完整版本: 5千字总结冷门的MySQL虚拟字段(虚拟列)