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 queryJSON_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;
##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!