Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ The new features of MySQL 5.7 Generated Column (index function)     - Dell R710 server disk recovery database one case (record) (Server)

- Linux Troubleshooting: How to save the status of the SSH session is closed (Linux)

- LVM management reduces swap partition space to the root partition (Linux)

- Linux operating system security tools of the Logs (Linux)

- Installation on the way to the root source Ubuntu Server 14.04 LTS version Odoo8.0 (Server)

- Linux System Administrator Network Security Experience (Linux)

- Install Kali Linux via PXE network (Linux)

- Ubuntu install video playback software SMPlayer 14.9.0.7042 (Linux)

- Java reflection summary (Programming)

- Merge sort Java implementation (Programming)

- MySQL simple operation notes under Linux (Database)

- To set up the printer use Nagios Monitoring Server (Server)

- Using Java to build micro-services (Server)

- Using open source software to build XWiki Wiki system installed within the company (Linux)

- Ubuntu resolve sudo: source: command not found error (Linux)

- Linux / Centos anti CC attack script (Linux)

- To share Internet access through NAT mode under Virtual Linux VMware Workstation (Linux)

- CentOS 6.5 Telnet SecureCRT use management tools (Linux)

- Do you know how to build the Linux kernel (Programming)

- How to configure Apache Solr on Ubuntu 14/15 (Server)

 
         
  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:      
 
- Ubuntu manually set the DSL broadband connection (Linux)
- CentOS 7 install Hadoop-cdh-2.5 on Mesos (Server)
- ThinkPad X201i installation notes Debian 7.5 (Linux)
- Formatting Java floating-point types (Programming)
- Linux supports serial output method (Linux)
- Ubuntu 15.04 installation Powercommands 2.0 (Linux)
- Guide: Trickle restrict application bandwidth usage (Linux)
- Linux find command usage practices (Linux)
- Python object-oriented programming (Programming)
- Swift rewrite initialize method of navigation controller class (Programming)
- Install Jetty on CentOS / RHEL 6.X (Server)
- Use Bash script write CVS version control (Server)
- The callback function used in C ++ (Programming)
- Sublime Text 3 shortcuts summary (Linux)
- How to install Eclipse Luna IDE on CentOS 7 / RHEL 7 (Linux)
- Linux rpm command Detailed (Linux)
- CentOS7 installation performance monitoring system (Server)
- CentOS minimal network is unavailable resolved (Linux)
- Java concurrent programming using the synchronized keyword ReentrantLock alternative primitive (Programming)
- Ubuntu 14.04 / 12.04 subscribe users to install software Liferea 1.10.10 (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.