MySQL 5.7 new feature generated Column (function index)

Website Original: https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

Note: The following is my reference to the official website documents and the combination of their own understanding written, because the English level is limited, do not rule out errors, welcome correction.

I. Description

Generated column is a new feature introduced by MySQL 5.7, the so-called cenerated column, which is calculated from the other columns in the database. In MySQL 5.7, two Generated columns are supported, namely Virtual Generated column (a dummy-generated row) and Stored Generated column (which stores the generated columns). The meanings are as follows:

• 1. Virtual Generated column (generated columns): The column value is not stored, that is, the column data is not persisted to disk, but when the row is read, the trigger triggers a computed display of the column. InnoDB support for Virtual Generated Column, refer to "https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html" for details
• 2. Stored Generated column (stores the generated columns): stores the column value, that is, the column value is calculated and stored when the row is inserted or updated. Therefore, more disk space is required relative to the virtual column, and there is no advantage over virtual column. Therefore, in MySQL 5.7, the type of generated column is not specified, and the default is virtual column
  
• Allow mixed use of virtual column and stored column in a table
• Increased efficiency: since MySQL adds functions on the normal index to invalidate the index, resulting in degraded query performance, Generated column (function index) just solves the problem and can be indexed in Generated column to improve efficiency

Ii. creation of rules

1 col_nameData_type[GENERATED always]  as(expression)2   [VIRTUAL | STORED] [Not NULL | NULL]3   [UNIQUE [KEY]][[PRIMARY] KEY]4   [COMMENT ' string ']

Third, the use

for generated column use, refer to the official website case to explain:

CREATE TABLE triangle (  Sidea double,  sideb double,  sidec double as (SQRT (Sidea * Sidea + sideb * sideb))); INS ERT into triangle (Sidea, Sideb) VALUES (+), (3,4), (6,8);

　　

MySQL 5.7 new feature generated Column (function index)