Shulou(Shulou.com)06/01 Report--

In this paper, an example is given to illustrate the usage of mysql5.7 generated column generated column. Share with you for your reference, the details are as follows:

The value of the generated column is calculated based on the expression in the column definition.

Mysql5.7 supports two types of build columns:

1. Virtual generates a column: the column value is calculated only when the record is read from the table. The data will not be persisted on the hard disk.

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

So virtual requires less storage space than stored. If the generated column type is not specified, mysql5.7 default generated column type is virtual.

Define the syntax for generating columns:

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 the 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 piece of data into the table

Insert into test (chinese, math, english) values (66, 72, 54); select * from test

Note that the generated columns do not allow us to specify values artificially, which can cause errors in 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, and delete the generated columns through 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 the generated column

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

Rename the generated column

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

Delete the generated column

Alter table test drop column times_score_new

The virtual column cannot be changed to the generated column of stored, and vice versa. You can only delete it 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

A general field in a table that can be modified to a stored generated column, but not a virtual generated column

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

The stored generated column can be modified to a regular field and the value is the generated value.

Alter table test modify column total_score double

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.