5千字总结冷门的MySQL虚拟字段（虚拟列）

刘俊凯  金牌会员 | 2025-3-24 06:24:44 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 974|帖子 974|积分 2926

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体现该列的值总是根据其他列的计算结果主动天生，而不是手动插入或更新。
  1. ALTER TABLE `表名` ADD COLUMN `虚拟字段名` 值类型 GENERATED ALWAYS AS (表达式) VIRTUAL或STORED NOT NULL;
复制代码

  • 这里有一张测试表
  1. CREATE TABLE `test` (
  2.   `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表主键id',
  3.   `num` int NOT NULL COMMENT '存储数字',
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
复制代码

  • 添加虚拟字段，用于比num字段 + 1
  1. ALTER TABLE `test` ADD COLUMN `num_v` int GENERATED ALWAYS AS (num + 1) VIRTUAL NOT NULL AFTER `num`;
复制代码

  • 新增两条数据
  1. INSERT INTO `test` (`num`) VALUES (1)
  2. INSERT INTO `test` (`num`) VALUES (2)
复制代码

  • 查询得出如下值
  1. id        num        num_v
  2. 1        1        2
  3. 2        2        3
复制代码
删
  1. ALTER TABLE `表名`  DROP COLUMN `虚拟字段名称`;
复制代码
改
  1. ALTER TABLE `表名` MODIFY COLUMN `虚拟字段` int GENERATED ALWAYS AS (表达式) VIRTUAL或STORED NOT NULL;
复制代码
查
  1. show create table 表名
  2. 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类型的没有文件实体，但是也支持索引。
  • 论证：
  1. 建表
  2. CREATE TABLE `test` (
  3.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表id',
  4.   `str` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '测试用字符串',
  5.   `str_reverse_v` varchar(20) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (reverse(`str`)) VIRTUAL NOT NULL COMMENT '反转字符串，虚拟字段',
  6.   `str_upper_s` varchar(20) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (upper(`str`)) STORED NOT NULL COMMENT '大写，生成字段',
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  11. 新增两条数据
  12. INSERT INTO `test` (`str`) VALUES ('abc');
  13. INSERT INTO `test` (`str`) VALUES ('xyz');
  15. 尝试将两个虚拟字段添加索引，没发现报错
  16. ALTER TABLE `test` ADD INDEX(`str_reverse_v`), ADD INDEX(`str_upper_s`);
  18. 用explain分析，发现确实用到了索引
  19. explain SELECT str_reverse_v FROM `test` where str_reverse_v = 'mn';
  20. id        select_type        table        partitions        type        possible_keys        key        key_len        ref        rows        filtered        Extra
  21. 1        SIMPLE        test                ref        str_reverse_v        str_reverse_v        82        const        1        100.00        Using index
  23. explain SELECT str_upper_s FROM `test` where str_upper_s = 'mn';
  24. id        select_type        table        partitions        type        possible_keys        key        key_len        ref        rows        filtered        Extra
  25. 1        SIMPLE        test                ref        str_upper_s        str_upper_s        82        const        1        100.00        Using index
复制代码
常见用法举例


  • 格式化数字IP为标准IPv4地点格式
  1. CREATE TABLE `test` (
  2.   `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表主键id',
  3.   `ip` bigint NOT NULL DEFAULT '0' COMMENT 'IPV4 数字格式',
  4.   `ip_v` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (inet_ntoa(`ip`)) STORED NOT NULL COMMENT 'IPV4 字符串格式',
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  9. INSERT INTO `test` (`ip`) VALUES (2130706433);
  10. INSERT INTO `test` (`ip`) VALUES (3232235776);
  13. select * from test
  15. id        ip                ip_v
  16. 1        2130706433        127.0.0.1
  17. 2        3232235776        192.168.1.0
复制代码

  • JSON数据提取
  1. CREATE TABLE `test` (
  2.   `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表主键id',
  3.   `user_info` json NOT NULL COMMENT '用户信息',
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  8. 假设要插入一条多层级的json数据，如下
  9. {
  10.     "name": "张三",
  11.     "gender": "男",
  12.     "age": 8,
  13.     "birthplace": "北京市",
  14.     "occupation": "学生",
  15.     "hobbies": [
  16.         "打篮球",
  17.         "读书"
  18.     ],
  19.     "contact": {
  20.         "phone": "18888888888",
  21.         "email": "zhangsan@example.com"
  22.     },
  23.     "examination": [
  24.         {
  25.             "subject": "语文",
  26.             "score": 80
  27.         },
  28.         {
  29.             "subject": "数学",
  30.             "score": 80
  31.         },
  32.         {
  33.             "subject": "英文",
  34.             "score": 80
  35.         }
  36.     ]
  37. }
  39. 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": "英文"}]}');
  43. 可需求是就像查看这个学生考了哪些科目，其它数据都不重要
  44. 此时就需要定向取值，若取单条，可用
  45. SELECT json_unquote(json_extract(`user_info`,_utf8mb4'$.examination[*].subject')) as subjects FROM `test`
  48. 若是多条，可以新建一个虚拟字段，可以很直观的展示考取了哪些科目
  49. ALTER TABLE `test`
  50. ADD COLUMN `subjects` json AS (json_unquote(json_extract(`user_info`,_utf8mb4'$.examination[*].subject'))) STORED NOT NULL AFTER `user_info`;
  53. select id, subjects from test
  54. id        subjects
  55. 1        ["语文", "数学", "英文"]
复制代码

  • 日期格式化（需留意时区）
  1. CREATE TABLE `test` (
  2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表id',
  3.   `timestamp` int(11) NOT NULL COMMENT '时间戳',
  4.   `date_time` datetime GENERATED ALWAYS AS (from_unixtime(`timestamp`)) STORED COMMENT '格式化时间戳 存储字段',
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  9. INSERT INTO `test` (`timestamp`) VALUES (1700000000);
  12. select * from test
  14. id        timestamp        date_time
  15. 1        1700000000        2023-11-15 06:13:20
复制代码

  • 状态判断
  1. CREATE TABLE `test` (
  2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '测试表id',
  3.   `age` int(11) NOT NULL COMMENT '年龄',
  4.   `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 '年龄可视化判断',
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  9. INSERT INTO `test` (`age`) VALUES (11);
  10. INSERT INTO `test` (`age`) VALUES (16);
  11. INSERT INTO `test` (`age`) VALUES (25);
  12. INSERT INTO `test` (`age`) VALUES (53);
  13. INSERT INTO `test` (`age`) VALUES (65);
  16. SELECT * FROM test
  18. id        age        age_v
  19. 1        11        儿童
  20. 2        16        少年
  21. 3        25        青年
  22. 4        53        壮年
  23. 5        65        老年
复制代码

  • 等

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

刘俊凯

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表