| |
MySQL 5.7 introduces Generated Column, this article briefly describes the use of methods and precautions Generated Column for readers to understand MySQL 5.7 provides a quick, complete tutorial. This article around a few of the following questions:
What is Generated Column
The difference between the Virtual Column and Stored Column
If I do some vandalism Generated Column happen
Create an index on Generated Column
The difference between the index and create indexes on Oracle function Column Generated
What is Generated Column
Generated Column is a new feature introduced in MySQL 5.7, the so-called Cenerated Column, this column is calculated by the database derived from other columns, we refer to the official manual example will be described.
For example, knowing the two right-angle triangle sides, the required length of the hypotenuse. Obviously, the length of the hypotenuse of the right-angle side can be calculated by two, then this time can only be stored in the database, right-angle side, the hypotenuse use Generated Column, as follows:
CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT (sidea * sidea + sideb * sideb)));
INSERT INTO triangle (sidea, sideb) VALUES (1,1), (3,4), (6,8);
search result:
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 are and how to use use.
The difference between Virtual Generated Column and Stored Generated Column of
In MySQL 5.7, and supports two Generated Column, namely Virtual Generated Column and Stored Generated Column, the former only Generated Column stored in the data dictionary (table metadata), this column will not persist data to disk; the latter will Generated Column persisted to disk instead of reading each time calculated. Obviously, the latter holds the existing data can be calculated from the data, you need more disk space, compared with the Virtual Column no advantage, therefore, MySQL 5.7, it is not specified Generated Column type, the default is Virtual Column. In addition:
Stored Generated Column poor performance, see here
If you need to Stored Generated Golumn, it may be indexed on a Generated Column more appropriate, see Section 4 of this article describes
In summary, under normal circumstances, use the Virtual Generated Column, which is the default MySQL way, if you use Stored Generated Column, built in front of the table the following statement will be such that more than a stored keyword:
Create Table: CREATE TABLE `triangle` (
`Sidea` double DEFAULT NULL,
`Sideb` double DEFAULT NULL,
`Sidec` double GENERATED ALWAYS AS (SQRT (sidea * sidea + sideb * sideb)) STORED)
If you do some generated column vandalism happen?
We already know what the generated column are, and know how to use the generated column, in order to avoid misuse, we first do some experiments, in order to avoid some unknown situation in the specific use.
The generated column is defined as "divide by zero"
If we generated column is defined as "x column / 0", MySQL does not directly given, but when inserting data error, 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 (x, y) values (1,1);
ERROR 1365 (22012): Division by 0
Insert malicious data
If we generated column is defined as "x column / y column" in the insert data, as if y is 0, the same prompt an error, as follows:
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 (x, y) values (1,0);
ERROR 1365 (22012): Division by 0
Delete the source column
If we generated column is defined as "column x / y column", and try to delete the column x or y column will be prompted to "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 clearly not legitimate Generated Column
If we generated column is defined as "x + y column column" Obviously, x or y column column is numeric, if we x or y column column definitions (or modification) for the character (of course, in actual use Some people should not be foolish enough to do so), it is expected to be an error, but did not, as shown below, we can create normal.
mysql> create table t (x int, y varchar (100), z int generated always as (x + y));
Query OK, 0 rows affected (0.13 sec)
And insert the following such data will not be wrong:
mysql> insert into t (x, y) values (1, '0');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+ ------ + ------ + ------ +
| X | y | z |
+ ------ + ------ + ------ +
| 1 | 0 | 1 |
+ ------ + ------ + ------ +
1 row in set (0.00 sec)
However, in the case of MySQL can not handle, it will error:
mysql> insert into t (x, y) values (1, 'x');
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'x'
Create an index on Generated Column
Similarly, we can build on the generated column indexes after indexing to speed up the search speed 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)
And, we can create a common index and unique index, if the index is unique, in violation of a unique constraint, to report the error:
mysql> insert into t (x, y) values (1,1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t (x, y) values (2,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'idz'
Therefore, when using MySQL5.7, Generated Column also need to understand, to be able to solve some problems not previously encountered.
Limit index
While under normal circumstances should be used Virtal Generated Column, however, the current use Virtual Generated Column There are many restrictions, including:
Clustered index can not contain virtual generated column
mysql> create table t1 (a 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 (a int, b int, c int GENERATED ALWAYS AS (a / b) STORED, primary key (c));
Query OK, 0 rows affected (0.11 sec)
You can not create full-text indexes, and spatial indexes on Virtual Generated Column, the following versions of MySQL is expected to solve (Inside Jun ye remember Stored Column listing can it?).
Virtual Generated Column can not serve as a foreign key
You can not use non-deterministic when creating generated column (including virtual generated column and stored generated column) (non-repeatable) function
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 the index and create indexes on Oracle function Column Generated
Introduction to MySQL indexes on Generated Column, and this time the students are familiar with Oracle might think of Oracle's functional index, more similar indexing and Oracle in the MySQL Generated Column column function index, there are differences:
For example, a table, as follows:
mysql> CREATE TABLE t1 (first_name VARCHAR (10), last_name VARCHAR (10));
Query OK, 0 rows affected (0.11 sec)
Assuming that the time required to build a full_name index in Oracle, we can use the function directly in an index is created as follows:
alter table t1 add index full_name_idx (CONCAT (first_name, '', last_name));
However, the above statement in MySQL which will give an error. In MySQL, we can first create a Generated Column, and then build an index on the Generated Column, as follows:
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 need to add on a table, Oracle will be able to achieve a similar function index, the price seems to be much higher. But we said in Part 2, the Virtual Generated Column, MySQL only meta-information in this column is stored in the data dictionary, this column will not persist data to disk, therefore, in the MySQL Virtual Generated Column similar to the indexing function and Oracle indexes do not need more of the cost, just use a little bit just not the same. |
|
|
|