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

MySQL 5.7 json introduction preface

MySQL5.7 adds two new field types: Json and Generated. The generation of the Generated type is inseparable from Json. If there is no Generated type, the Json type is powerful and productive. It may not be used in Json because Json does not support indexes, but if you want to query the data in Json, without an index it is a full table scan. In terms of execution efficiency, it certainly cannot be used in a production environment. However, with the Generated type, it is different. The Generated type is simply a virtual field. The value cannot be updated. The value comes from other fields or calculations or conversions between fields. This type can create indexes. Using the characteristics of Generated, it can be indirectly added to Json. The key in the type creates an index to solve the problem that Json cannot create an index. In short, the generation of the Generated type provides support for the indexing issues of the Json type. JSON values ​​include several formats such as single value, array, tuple, annotated Json format, etc.

Objectively speaking, the emergence of the Json type provides convenience for research and development and provides support similar to NoSQL. However, from the perspective of DBA operation and maintenance, it may be a trouble. This trouble is related to large fields such as text. The trouble is the same. json may become a variant of the text field in actual production. I hope DBA will pay close attention to this type in the future use of 5.7.

Create a table with JSON fields

Directly create a test table with JSON fields

CREATE TABLE json_test (
  id int(11) NOT NULL AUTO_INCREMENT,
  group_name varchar(20) DEFAULT NULL,
  user_info json DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Or add a JSON type field to the table

 ALTER TABLE json_test ADD COLUMN user_info JSON DEFAULT NULL;

Insert data

JSON field related query

1. Query all results

2: JSON_EXTRACT query

JSON_EXTRACT This function is mainly used for exact matching, such as finding records with age=21 in user_info or querying records with name='wangwei', or name like '%wangwei%' Fuzzy query:

SELECT * FROM json_test WHERE JSON_EXTRACT(user_info,'$.age')=21;

When the value is an array, the starting position value of the array is 0. At the same time, MySQL is in 5.5. Version 9 adds ->, which provides another way to write equivalent to JSON_EXTRACT:

SELECT * FROM json_test WHERE user_info->"$.age"=21;

SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei') IS NOT NULL;
SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei%') IS NOT NULL;
SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','%wangwei%') IS NOT NULL;

##Numeric types:

INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT and INT and BIGINT scalars;DOUBLE: MySQL DOUBLE FLOAT scalars;DECIMAL: MySQL DECIMAL and NUMERIC scalars

Temporal types:

DATETIME: MySQL DATETIME and TIMESTAMP scalars;DATE: MySQL DATE scalars;TIME: MySQL TIME scalars

String types:

STRING: MySQL utf8 character type scalars;CHAR, VARCHAR, TEXT, ENUM, and SET

Binary types:

BLOB: MySQL binary type scalars;BINARY, VARBINARY, BLOB;BIT: MySQL BIT scalars

All other types:

OPAQUE (raw bits)

## The above are the new features of MySQL 5.7| The content of Json Column and Generated Column (Part 1), please pay attention to the PHP Chinese website (m.sbmmt.com) for more related content!