Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to understand Generated Column in MySQL 5.7. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Text

Generated Column is introduced in MySQL 5.7. this article briefly introduces how to use Generated Column and considerations, and provides a quick and complete tutorial for readers to learn about MySQL 5.7.This article provides a quick and complete tutorial on how to use MySQL. This article revolves around the following questions:

Generated Column is a new feature introduced by MySQL 5.7. the so-called Cenerated Column means that this column in the database is calculated from other columns, which we illustrate with examples in the official reference manual.

For example, knowing the two right sides of a right triangle requires the length of the bevel. Obviously, the length of the beveled edge can be calculated by two right-angled edges, so you can only store the right-angled edge in the database, using Generated Column, as shown below:

CREATE TABLE triangle (

Sidea DOUBLE

Sideb DOUBLE

Sidec DOUBLE AS (SQRT (sidea * sidea + sideb * sideb)

INSERT INTO triangle (sidea, sideb) VALUES (1), (3) 4), (6) 8)

Query results:

Mysql > SELECT * FROM triangle

+-+

| | sidea | sideb | sidec | |

+-+

| | 1 | 1 | 1.4142135623730951 | |

| | 3 | 4 | 5 |

| | 6 | 8 | 10 | |

+-+

This example is enough to illustrate what Generated Columns is and how to use it.

The difference between Virtual Generated Column and Stored Generated Column

In MySQL 5.7, two kinds of Generated Column are supported, Virtual Generated Column and Stored Generated Column, the former only saves Generated Column in the data dictionary (the metadata of the table) and does not persist this column of data to disk, while the latter persists Generated Column to disk instead of calculating it each time it is read. Obviously, the latter stores data that can be calculated from existing data, requires more disk space, and has no advantage over Virtual Column. Therefore, MySQL 5.7 does not specify the type of Generated Column, and the default is Virtual Column. In addition:

The performance of Stored Generated Column is poor, see here

If you need Stored Generated Golumn, it may be more appropriate to index on Generated Column, as described in part 4 of this article.

To sum up, in general, Virtual Generated Column is used, which is also the default method of MySQL. If Stored Generated Column is used, the previous table statement will look like this, that is, an extra stored keyword will be added:

Create Table: CREATE TABLE `triangle` (

`sidea` double DEFAULT NULL

`sideb` double DEFAULT NULL

`sidec` double GENERATED ALWAYS AS (SQRT (sidea * sidea + sideb * sideb)) STORED)

What happens if you do some sabotage to generated column?

We already know what generated column is and how to use generated column. In order to avoid misuse, let's do some experiments to avoid some unknown situations in the specific use.

Define generated column as "divided by 0"

If we define generated column as "x column / 0", MySQL does not report an error directly, but inserts an error in the data Times and prompts "ERROR 1365 (22012): Division by 0"

Mysql > create table t (x int, y int, z int generated always as (x / 0))

Query OK, 0 rows affected (0.22 sec)

Mysql > insert into t (xQuery y) values (1meme 1)

ERROR 1365 (22012): Division by 0

Insert malicious data

If we define generated column as "x column / y column", when inserting data, if y column is 0, the same error is prompted, as shown below:

Mysql > create table t (x int, y int, z int generated always as (x / y))

Query OK, 0 rows affected (0.20 sec)

Mysql > insert into t (xQuery y) values (1meme 0)

ERROR 1365 (22012): Division by 0

Delete source column

If we define generated column as "x column / y column" and try to delete x column or y column, we will prompt "ERROR 3108 (HY000): Column'x 'has a generated column dependency."

Mysql > create table t (x int, y int, z int generated always as (x / y))

Query OK, 0 rows affected (0.24 sec)

Mysql > alter table t drop column x

ERROR 3108 (HY000): Column 'x' has a generated column dependency.

Define Generated Column that is obviously illegal

If we define generated column as "x column + y column", it is obvious that x or y columns are numeric, and if we define (or modify) x or y columns as characters (of course, no one should be stupid enough to do so in actual use), we expect an error, but no, as shown below, we can create it normally.

Mysql > create table t (x int, y varchar, z int generated always as (x + y))

Query OK, 0 rows affected (0.13 sec)

And there is no error in inserting data like this:

Mysql > insert into t (xQuery y) values (1meme0')

Query OK, 1 row affected (0.01sec)

Mysql > select * from t

+-+

| | x | y | z |

+-+

| | 1 | 0 | 1 |

+-+

1 row in set (0.00 sec)

However, for situations that cannot be handled by MySQL, an error is reported:

Mysql > insert into t (xQuery y) values (1meme Zongx')

ERROR 1292 (22007): Truncated incorrect DOUBLE value:'x'

Create an index on Generated Column

Similarly, we can build an index on generated column, and after indexing, we can speed up the lookup, as follows:

Mysql > create table t (x int primary key, y int, z int generated always as (x / y), unique key idz (z))

Query OK, 0 rows affected (0.11 sec)

Mysql > show create table t\ G

* * 1. Row *

Table: t

Create Table: CREATE TABLE `t` (

`x`int (11) NOT NULL

`y`int (11) DEFAULT NULL

`z` int (11) GENERATED ALWAYS AS (x / y) VIRTUAL

PRIMARY KEY (`x`)

UNIQUE KEY `idz` (`z`) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.01 sec)

Also, we can create a normal index and a unique index, and if it is a unique index, report an error when the uniqueness constraint is violated:

Mysql > insert into t (xQuery y) values (1meme 1)

Query OK, 1 row affected (0.02 sec)

Mysql > insert into t (xQuery y) values (2meme 2)

ERROR 1062 (23000): Duplicate entry'1' for key 'idz'

Therefore, when using MySQL5.7, you also need to know something about Generated Column in order to solve some problems that you have never encountered before.

Restrictions on the index

Although Virtal Generated Column should be used in general, there are currently many restrictions on using Virtual Generated Column, including:

Clustered index cannot contain virtual generated column

Mysql > create table T1 (an int, b int, c int GENERATED ALWAYS AS (a / b), primary key (c))

ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.

Mysql > create table T1 (an int, b int, c int GENERATED ALWAYS AS (a / b) STORED, primary key (c))

Query OK, 0 rows affected (0.11 sec)

Full-text indexes and spatial indexes cannot be created on Virtual Generated Column, which is expected to be solved in later versions of MySQL. (how does Inside remember that Stored Column can be listed? ).

Virtual Generated Column cannot be used as a foreign key

Non-deterministic (non-repeatable) functions cannot be used when creating generated column (including virtual generated column and stored generated column)

Mysql > ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime ()) virtual

ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.

Mysql > ALTER TABLE `t1` ADD p3 DATE GENERATED ALWAYS AS (curtime ()) stored

ERROR 3102 (HY000): Expression of generated column 'p3' contains a disallowed function.

The difference between creating Index on Generated Column and functional Index of Oracle

After introducing MySQL's index on Generated Column, students who are familiar with Oracle may think of Oracle's functional index. Establishing an index on the Generated Column column of MySQL is similar to but different from Oracle's functional index:

For example, there is a table like this:

Mysql > CREATE TABLE T1 (first_name VARCHAR (10), last_name VARCHAR (10))

Query OK, 0 rows affected (0.11 sec)

Suppose you need to build an index of full_name at this time. In Oracle, we can use the function directly when creating the index, as shown below:

Alter table T1 add index full_name_idx (CONCAT (first_name,'', last_name))

However, the above statement will report an error in MySQL. In MySQL, we can first create a new Generated Column, and then index on this Generated Column, as shown below:

Mysql > alter table T1 add column full_name VARCHAR (255) GENERATED ALWAYS AS (CONCAT (first_name,'', last_name))

Mysql > alter table T1 add index full_name_idx (full_name)

At first glance, MySQL needs to add a column to the table to implement a functional index similar to Oracle, which seems to be much more expensive. However, as we said in part 2, Virtual Generated Column,MySQL only stores the meta-information of this column in the data dictionary and does not persist this column of data to disk, so indexing on MySQL's Virtual Generated Column is similar to Oracle's functional index, at no more cost, but in a slightly different way.

The above is the editor for you to share how to understand MySQL 5.7 in the Generated Column, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.