Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ The new features of MySQL 5.7 Generated Column (index function)     - Ubuntu 15.04 and Ubuntu 14.04 installed Cinnamon 2.6 (Linux)

- Spark build standalone version cluster (Server)

- C ++ Supplements - locates the new expression (Programming)

- Firewall Configuration Red Hat Enterprise Linux 4 (Linux)

- Vmstat command Linux Performance Monitoring (Linux)

- How Datadog monitor Nginx (Server)

- Efficient running Linux virtual machine Six Tips (Linux)

- HAProxy Windows version of the compiler and its application in the CORS (Server)

- GAMIT10.5 under CentOS installation (Linux)

- Use Python to perform system commands common usage (Programming)

- hexdump Linux command (Linux)

- Why JavaScript basic types can invoke methods (Programming)

- Android development environment to build under Fedora 13 (Linux)

- MySQL Online DDL tools of pt-online-schema-change (Database)

- Linux network monitoring tools ntopng installation (Linux)

- How to install GIMP 2.8.16 in Ubuntu 16.04,15.10,14.04 (Linux)

- Solaris 10 nagios monitoring system (Linux)

- Docker startups use continuous integration deployment (Server)

- 12 novice Linux command must learn (Linux)

- Ubuntu U disk do not have write privileges can only read but not write (Linux)

 
         
  The new features of MySQL 5.7 Generated Column (index function)
     
  Add Date : 2018-11-21      
         
         
         
  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.
     
         
         
         
  More:      
 
- RHEL / CentOS / Fedora Install Nagios 4.0.1 (Linux)
- Detailed installation of CentOS 6.x (Linux)
- CentOS 6.5 makes the LAN http source (Linux)
- Using Linux strace command trace / debug a program commonly used options (Linux)
- A drop datafile Oracle bug (Database)
- Linux platform NTOP Installation and Configuration (Linux)
- MySQL composite partition (Database)
- Shorewall firewall settings under Ubuntu (Linux)
- Java threads and thread pools (Programming)
- To compile and install Clang 3.5.0 in Linux CentOS (Linux)
- Oracle 11g users to create, authorize and import dmp file (Database)
- Debugging with GDB tool Go (Programming)
- Android in the event delivery and handling mechanism (Programming)
- Linux foundation tutorial: how to modify the host name on CentOS or RHEL 7 (Linux)
- Linux FAQ - How to fix tar:Exitingwith failure status due to previous errors (Linux)
- Configuring s3c-linux-2.6.28.6-Real6410 appears Unable to find the QT3 installation (Linux)
- MySQL backup tool to back up mydumper (Database)
- LNMP Note: Addressing mail function can not send mail (Server)
- CentOS set up FTP server (Server)
- Use Oracle Data Guard to complete cross-platform database migration cases (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.