MySQL 5.7 generated column usage example analysis

This article uses examples to illustrate the usage of generated columns in MySQL 5.7. Share with you for your reference, the details are as follows:

The value of a generated column is calculated from the expression in the column definition.

MySQL 5.7 supports two types of generated columns:

1. Virtual generated column: The column value is calculated when records are read from the table. The data will not be persisted on the hard disk.

2. Stored generated column: When a record is written to the table, the column value is calculated and persisted on the hard disk as a regular column.

Therefore, virtual requires less storage space than stored. If the generated column type is not specified, the default generated column type in MySQL 5.7 is virtual.

The syntax for defining a generated column is:

col_name data_type [GENERATED ALWAYS] AS (expression)
 [VIRTUAL | STORED] [NOT NULL | NULL]
 [UNIQUE [KEY]] [[PRIMARY] KEY]
 [COMMENT 'string']

We create a table and specify one of the fields as a generated column.

CREATE TABLE test (
 id INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
 chinese DOUBLE NOT NULL DEFAULT '0',
 math DOUBLE NOT NULL DEFAULT '0',
 english DOUBLE NOT NULL DEFAULT '0',
 total_score DOUBLE AS (chinese + math + english),
 PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

We insert a data into the table

insert into test(chinese, math, english) values(66, 72, 54);

select * from test;

Note that generated columns do not allow us to specify values ​​manually, which will trigger ERROR 3105.

If you want to include the total_score field name in the insert statement, you can only set its value to DEFAULT

insert into test(chinese, math, english, total_score) values(33, 44, 55, DEFAULT);

If the table already exists, we can create, modify, or delete generated columns using the alter table statement.

alter table test add column times_score double generated always 
as (chinese * math * english) stored;

Modify the data type and expression of generated columns

alter table test modify column times_score float generated always 
as (chinese * math * english * 10) stored;

Rename the generated columns

alter table test change times_score times_score_new float generated always 
as (chinese * math * english * 10) stored;

Remove generated columns

alter table test drop column times_score_new;

A virtual column cannot be changed to a stored generated column, and vice versa. You can only delete it first and then add it again

alter table test drop column total_score;
alter table test add column total_score double generated always 
as (chinese + math + english) stored;

Regular fields in a table can be modified to be stored generated columns, but not virtual generated columns.

alter table test modify column chinese double generated always 
as (math + 1) stored;

The stored generated column can be modified to a regular field with a generated value

alter table test modify column total_score double;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.