知行合一，止于至善
千里之行，始于足下；合抱之木，生于毫末；九层之台，起于累土mysql 虚拟字段(virtual column)
12月 11, 2021 |
Nix.Huang
应用场景，
- 给flag列添加说明字段
- 给json 列的某个字段添加索引
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 添加虚拟说明字段 `ALTER TABLE t1 ADD COLUMN f1_str varchar(64) AS (case when f1=1 then 'odd' else 'even' end) after f1;` #提取json 字段成为虚拟字段 `alter table t1 add column f4_name varchar(32) as (f4->>'$.name') after f4;` #由于'->>'返回的数据类型是longtext,对应utf8mb4-bin collation, 要是不适配无法使用索引 #使用函数表达式生成索引 alter table t1 add index idx_name((CAST(f4->>'$.name' AS CHAR(32)) COLLATE utf8mb4_bin)); select * from t1 where f4->>'$.name' = 'javacoder.cn'; #方案2，使用cast将'->>'操作符的结果从utf8mb4-bin collation转换成utf8mb4_0900_ai_ci alter table t1 add index idx_name((CAST(f4->>'$.name' AS CHAR(32)))); select * from t1 where cast(f4->>'$.name' as char(32)) = 'javacoder.cn'; |
参考文档
https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns
https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.html
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued
Comments are closed.