Cara menggunakan medan jenis JSON dalam MySQL

Persekitaran ujian: MySQL8.0.19

Kerja penyediaan

CREATE TABLE json_demo ( 
	`id` INT ( 11 ) NOT NULL PRIMARY KEY, 
	`content` json NOT NULL 
);
INSERT INTO json_demo ( id, content )
VALUES
	/*这条是数组*/
	( 1, '[{"key": 1, "order": 1, "value": "34252"},{"key": 2, "order": 2, "value": "23423"}]' ),
	/*这条是数组*/
	( 2, '[{"key": 4, "order": 4, "value": "234"},{"key": 5, "order": 5, "value": "234324523"}]' ),
	/*这条是对象*/
	( 3, '{"key": 3, "order": 3, "value": "43242"}' ),
	/*这条是对象*/
	( 4, '{"key": 6, "order": 6, "value": "5423"}' );

Operasi asas objek JSON

Soal nilai medan yang ditentukan

/* 基础查询 */
SELECT
	content -> '$.key' AS 'key',
	JSON_EXTRACT(content, '$.key') AS 'key2',
	content -> '$.value' AS 'value',
	JSON_EXTRACT(content, '$.value') AS 'value2',
	content ->> '$.value' AS 'value3',
	JSON_UNQUOTE(JSON_EXTRACT(content, '$.value')) AS 'value4'
FROM
	json_demo 
WHERE
	id > 2;

PETUA:

• ->> ialah Sintaks yang direka oleh MySQL, yang mana -> disokong dalam MySQL5.7 dan ->> disokong dalam MySQL8.0.

• -> Bersamaan dengan JSON_EXTRACT(), apabila medan pertanyaan ialah rentetan, nilai pulangan juga akan mempunyai "".

• ->> bersamaan dengan JSON_UNQUOTE(JSON_EXTRACT() Apabila medan pertanyaan ialah rentetan, nilai pulangan tidak akan mempunyai "".

digunakan untuk pertanyaan bersyarat

kandungan -> '$.key' boleh dianggap sebagai medan dan operasi yang boleh dilakukan di padang pada dasarnya Dia boleh melakukannya.

SELECT
	id,
	content -> '$.key' AS 'key',
	content ->> '$.value' AS 'value3'
FROM
	json_demo 
WHERE
	id > 2
	AND content -> '$.key' > 1
	AND content -> '$.value' like '%2%';

Ubah suai nilai medan yang ditentukan

/* 修改 */
UPDATE json_demo 
SET content = JSON_REPLACE(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* JSON_SET也可以 */
UPDATE json_demo 
SET content = JSON_SET(
	content,
	/* 将content.key值 + 1 */
	'$.key', content -> '$.key' + 1,
	/* 将content.value值后拼接'abc' */
	'$.value', concat(content ->> '$.value', 'abc')
) WHERE id = 3;
/* 查询修改结果 */
SELECT id,content,content -> '$.key' AS 'key',content ->> '$.value' AS 'value3'
FROM json_demo WHERE id = 3;
/* 重新赋值 */
UPDATE json_demo SET 
content = JSON_REPLACE(content,'$.key',3,'$.value','43242') WHERE id = 3;

TIPS:

• Kedua-dua JSON_REPLACE dan JSON_SET boleh digunakan untuk mengubah suai nilai medan tertentu Perbezaannya ialah JSON_REPLACE tidak mempunyai kesan apabila menggantikan atribut yang tidak wujud manakala JSON_SET akan memasukkan yang tidak sifat wujud.

• Jadi JSON_SET juga boleh digunakan untuk menambahkan atribut, serupa dengan JSON_INSERT. Perbezaannya ialah operasi JSON_INSERT akan gagal jika atribut sedia ada dimasukkan, manakala JSON_SET akan menggantikannya.

Tambah elemen

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.key', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_INSERT(content, '$.temp', 234)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

UPDATE json_demo 
SET content = JSON_SET(content, '$.temp2', 432)
WHERE id = 3;

SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

Operasi tatasusunan JSON

Tanya nilai medan yang ditentukan

SELECT
	id,
	content -> '$[*].key' AS 'key',
	content ->> '$[*].value' AS 'value',
	content -> '$[0].key' AS 'key2',
	content ->> '$[0].value' AS 'value2',
	/* 查询数组长度 */
	JSON_LENGTH(content) AS 'length'
FROM
	json_demo 
WHERE
	id < 3;

untuk pertanyaan bersyarat

SELECT
	id,
	content -> '$[*].key' AS 'key',
	content ->> '$[*].value' AS 'value'
FROM
	json_demo 
WHERE
	id < 3
	/* content.value的值中存在like&#39;%34%&#39;的值 */
	AND content ->> &#39;$[*].value&#39; like &#39;%34%&#39;
	/* content.key的值中有4 */
	AND JSON_OVERLAPS(content ->> &#39;$[*].key&#39;, &#39;4&#39; );

Ubah suai nilai medan yang ditentukan

Operasi asas tidak jauh berbeza daripada objek JSON, iaitu, tambah bit indeks yang sepadan '$[0]' selepas '$' untuk menentukan semua Kemudian '$[*]'. Jika tatasusunan mengandungi tatasusunan, anda boleh menentukan elemen tatasusunan dalam melalui '$[1][2][3]'.

Tambah elemen

Kedua-dua JSON_ARRAY_APPEND dan JSON_ARRAY_INSERT boleh menambah elemen tatasusunan. Perbezaannya ialah JSON_ARRAY_APPEND tidak perlu menentukan bit indeks, yang mana ia akan dilampirkan pada kedudukan terakhir JSON_ARRAY_INSERT mesti menentukan bit indeks, dan ralat akan dilaporkan jika tidak dinyatakan.

JSON_ARRAY_APPEND dilampirkan selepas bit indeks yang ditentukan, manakala JSON_ARRAY_INSERT dimasukkan sebelum bit indeks yang ditentukan.

Lebih banyak operasi

名称	描述
JSON_ARRAY()	创建JSON数组
JSON_ARRAY_APPEND()	将数据附加到JSON文档
JSON_ARRAY_INSERT()	插入JSON数组
JSON_CONTAINS()	JSON文档是否在路径中包含特定对象
JSON_CONTAINS_PATH()	JSON文档是否在路径中包含任何数据
JSON_DEPTH()	JSON文档的最大深度
JSON_EXTRACT()	从JSON文档返回数据
JSON_INSERT()	将数据插入JSON文档
JSON_KEYS()	JSON文档中的键数组
JSON_LENGTH()	JSON文档中的元素数
JSON_MERGE() （已弃用）	合并JSON文档，保留重复的键。JSON_MERGE_PRESERVE（）的已弃用同义词
JSON_MERGE_PATCH()	合并JSON文档，替换重复键的值
JSON_MERGE_PRESERVE()	合并JSON文档，保留重复的键
JSON_OBJECT()	创建JSON对象
JSON_OVERLAPS() （8.0.17引入）	比较两个JSON文档，如果它们具有共同的任何键值对或数组元素，则返回TRUE（1），否则返回FALSE（0）
JSON_PRETTY()	以易于阅读的格式打印JSON文档
JSON_QUOTE()	引用JSON文档
JSON_REMOVE()	从JSON文档中删除数据
JSON_REPLACE()	替换JSON文档中的值
JSON_SCHEMA_VALID() （8.0.17引入）	根据JSON模式验证JSON文档；如果文档针对架构进行了验证，则返回TRUE / 1；否则，则返回FALSE / 0。
JSON_SCHEMA_VALIDATION_REPORT() （8.0.17引入）	根据JSON模式验证JSON文档；以JSON格式返回有关验证结果的报告，包括成功或失败以及失败原因
JSON_SEARCH()	JSON文档中值的路径
JSON_SET()	将数据插入JSON文档
JSON_STORAGE_FREE()	部分更新后，JSON列值的二进制表示形式中的可用空间
JSON_STORAGE_SIZE()	用于存储JSON文档的二进制表示形式的空间
JSON_TABLE()	从JSON表达式返回数据作为关系表
JSON_TYPE()	JSON值类型
JSON_UNQUOTE()	取消引用JSON值
JSON_VALID()	JSON值是否有效
JSON_VALUE() （8.0.21引入）	在提供的路径所指向的位置从JSON文档中提取值；以VARCHAR（512）或指定的类型返回此值
MEMBER OF() （8.0.17引入）	如果第一个操作数与作为第二个操作数传递的JSON数组的任何元素匹配，则返回true（1），否则返回false（0）

Atas ialah kandungan terperinci Cara menggunakan medan jenis JSON dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!