MySQL 5.7 Virtual column (Vsan columns)

Resources:

Generated Columns in MySQL 5.7.5

MySQL 5.7 new feature generated Column (function index)

MySQL 5.7 Native JSON format support

Generated Column

In MySQL 5.7, two Generated column, virtual Generated column and stored Generated column, are supported, the former only Generated column is stored in the data dictionary (the table's metadata) and does not persist this column of data to disk, which persists the generated column to disk instead of being computed each time it is read. Obviously, the latter stores data that can be computed from existing data, requires more disk space, and has no advantage over virtual column, so MySQL 5.7 does not specify the type of generated Column, which by default is virtual column.

If you need stored Generated golumn, it might be more appropriate to build an index on the virtual Generated column.

In general, the virtual Generated Column is used, which is the default way of MySQL

Grammar:

<type> [GENERATED always] as (<expression>) [virtual| STORED]
[UNIQUE [KEY]] [[PRIMARY] KEY] [Not NULL] [COMMENT <text>]

Application:

To implement an index query for some of the data in the JSON data, you can use the Virtual column feature in MySQL5.7

Create a table

Create Table User int auto_increment,data JSON,primarykey(UID));

Building data

Insert  into User Values (NULL,'{' "name": "Wang", "Address": "Shenyang"}'); Insert  into User Values (NULL,'{' "name": "Zhao", "Address": "Riben"}');

Build a virtual column of names

Alter Table User Add user_name varchar ( $.name');

Build Index

Alter Table User Add index idx_name (user_name);

Inquire

Select *  from User where user_name = ' "Wang" ';

Query analysis (explain ... \g)

You can see that the index is used.

MySQL 5.7 Virtual column (Vsan columns)