MySQL 5.7 new features | Json Column and Generated Column (Part 2)

The JSON field type does not have its own index in the current version, so it is very terrible in production. The efficiency of adding, deleting, modifying, and checking JSON fields can be imagined, and it is basically unusable. Maybe it is based on this. MySQL5.7 provides a Generated field type, which is called generated column or calculated column on the Internet. Let’s first understand what Generated Column is.

1. Introduction to Generated Column

Generated Column is a new feature introduced in MySQL 5.7.6. The so-called Centerated Column means that this column in the database is calculated from other columns. To illustrate, quote the example in the official reference manual:

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);
mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

There are two types of Generated Column, namely Virtual Generated Column and Stored Generated Column. The former only saves Generated Column in the data dictionary (metadata of the table), and This column of data will not be persisted to disk; the latter will persist the 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, in MySQL 5.7, the type of Generated Column is not specified, and the default is Virtual Column. Although Virtal Generated Column should generally be used, there are currently many restrictions on using Virtual Generated Column: it cannot be used as a primary key, cannot be used as a primary key, cannot create full-text indexes and spatial indexes, etc., but it may be supported in subsequent versions. Therefore, if you use Generated Column fields for indexing, you should use Stored Generated Column. When using Generated Column for indexing, the official solution for JSON field indexing is to use Stored Generated Column. The table creation statement using Stored Generated Column is as follows, just adding a word:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb) STORED)
);

2. Notes on Generated Column

Generated Column cannot be written, it is automatically generated; When creating, you must consider whether the calculation formula of this column is reasonable. If it is unreasonable, no error will be reported when creating it, but an error will be reported when inserting a value when using it. The columns that Generated Column depends on will prompt an error when deleting, and Generated Column must be deleted first. Only then can we delete the columns it depends on; the definition of Generated Column is illegal. For example, if we define generated column as "column x + column y", it is obvious that column x or column y are both numerical. If we define column x or column y If the column is defined (or modified) as character type, an error is expected, but in fact we can create it normally, but an error will be reported when inserting.

3. Use Generated Column to add an index to the JSON field

Normally, the query related to the JSON field scans the entire table, because the JSON field itself If the index cannot be created, we use the Generated Column feature to generate columns for the relevant keys in the JSON field as Generated Column, and then index the Generated Column:

ALTER TABLE json_test ADD COLUMN age INT AS 
(JSON_EXTRACT(user_info,'$.age')) STORED,
 ADD KEY idx_age (age);

The before and after comparison is as follows:

#It can be clearly seen that after using Generated Column and adding an index, the index is used to query the value in the JSON field.

Conclusion

The emergence of Generated Column and JSON Column in MySQL5.7 makes it possible to replace NoSQL such as MongoDB in some scenarios, although overall it has not been done by MongoDB and others. is so powerful, but I believe that there will be more and more scenarios in which these two types are used in the future. At the same time, the challenges to DBA will also become greater and greater. It is hoped that intensive use of JSON type business will be run using independent MySQL instances to prevent JSON from becoming a large field. (The size of the JSON column stored in the JSON document is limited to the value of the max_allowed_packet system variable) This has an impact on other operations.

The above is the content of MySQL 5.7 new features | Json Column and Generated Column (Part 2). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!