表结构
CREATE TABLE `test_json` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`extend` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
json结构
{
"order_id": 7021,
"order_sn": "CHMAIN2023121115475192454"
}
添加虚拟列
ALTER TABLE `test_json` ADD COLUMN `order_id` INT GENERATED ALWAYS AS (`extend`->'$.order_id') STORED;
-- 字符串类型 去除双引号
ALTER TABLE `test_json` ADD COLUMN `order_id` INT GENERATED ALWAYS AS json_unquote(`extend`->'$.order_id') STORED;
虚拟列类型(计算列)
STORED 计算列是在插入或更新数据时计算并存储在表中的列。它的值在数据写入时计算,并且在后续查询时直接从存储的值中获取,类似于普通列。由于计算结果被存储在表中,STORED计算列的查询性能较高。然而,由于存储了计算结果,当相关列的值发生变化时,需要手动更新STORED计算列的值。
VIRTUAL 计算列是在查询时根据其他列的值动态计算得出的列。它的值不会实际存储在表中,而是在查询时根据定义的计算逻辑实时计算。VIRTUAL计算列的值是根据其他列的值动态计算的,因此不需要手动更新。但是,由于在每次查询时都要计算值,VIRTUAL计算列可能会对查询性能产生一定的影响。
虚拟列添加索引
ALTER TABLE `test_json` ADD INDEX order_id (order_id);
验证
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_json | NULL | ref | order_id | order_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+----------+---------+-------+------+----------+-------+