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;

#3, JSON_SEARCH query

JSON_SEARCH string search function, searches for records whose value in all keys in json is the string 'wangwei', which is an exact match; adding % means something like:

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;

4. JSON_CONTAINS determines whether the key contains the specified value.

JSON_CONTAINS determines whether the key contains the specified value. The return value is 0 or 1 or null. The corresponding cases are not included, included, and The specified key does not exist in a centralized situation:

5. JSON_CONTAINS_PATH determines whether the key exists in the field.

JSON_CONTAINS_PATH determines whether the key exists in the field and returns the value. It is 0 and 1, and the format is JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path]...). If it is one, it means that as long as there is one in the path, it will be 1, otherwise it will be 0; if it is all, it must be all Contains, the return is 1, otherwise it is 0:

6. JSON_LENGTH length query

JSON_LENGTH is used to calculate the length of the JSON field:

7: JSON_DEPTH level query

JSON_DEPTH is mainly used to query the level of the Json field. The empty value is 1, so under normal circumstances the Json field type is not empty. The result of querying this value should be greater than or equal to 2:

8: JSON_TYPE queries the data type of the key value in the Json field

JSON_TYPE is mainly used for query The data type of the key value in the Json field type. The displayed types are classified as follows:

Purely JSON types:

OBJECT: JSON objects;ARRAY: JSON arrays;BOOLEAN: The JSON true and false literals;NULL: The JSON null literal

##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)

##9. JSON_UNQUOTE removes the special characters before and after.

JSON_UNQUOTE is mainly Used to remove special characters before and after, format the value, currently supports the following characters:

## 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 (www.php.cn) for more related content!