JSON_STORAGE_FREE(json_val)

MySQL 8.4 🇮🇩 Bahasa Indonesia

Untuk nilai kolom JSON, fungsi ini menunjukkan berapa banyak ruang penyimpanan yang dikosongkan dalam representasi binernya setelah diperbarui menggunakan JSON_SET(), JSON_REPLACE(), atau JSON_REMOVE(). Argumennya juga bisa berupa dokumen JSON yang valid atau string yang bisa diurai menjadi satu—baik sebagai nilai literal atau sebagai nilai variabel pengguna—dalam hal ini fungsi akan mengembalikan 0. Fungsi akan mengembalikan nilai positif dan bukan nol jika argumennya adalah nilai kolom JSON yang telah diperbarui seperti yang dijelaskan sebelumnya, sehingga representasi binernya memerlukan lebih sedikit ruang dibandingkan sebelum pembaruan. Untuk rekan JSON

Syntax

MYSQL

CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

INSERT INTO jtable VALUES
    ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

SELECT * FROM jtable;
+----------------------------------------------+
| jcol                                         |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

Contoh

Example

If json_val is not NULL, and neither is a valid JSON document nor can be successfully parsed as one, an error results.

MYSQL

CREATE TABLE jtable (jcol JSON);
Query OK, 0 rows affected (0.38 sec)

INSERT INTO jtable VALUES
    ('{"a": 10, "b": "wxyz", "c": "[true, false]"}');
Query OK, 1 row affected (0.04 sec)

SELECT * FROM jtable;
+----------------------------------------------+
| jcol                                         |
+----------------------------------------------+
| {"a": 10, "b": "wxyz", "c": "[true, false]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

Example 2

In this example, we create a table containing a JSON column, then insert a row containing a JSON object:

MYSQL

UPDATE jtable
    SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wxyz", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM jtable;
+--------------------------------+
| jcol                           |
+--------------------------------+
| {"a": 10, "b": "wxyz", "c": 1} |
+--------------------------------+
1 row in set (0.00 sec)

SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      14 |
+-------------------------+
1 row in set (0.00 sec)

Example 3

Now we update the column value using JSON_SET() such that a partial update can be performed; in this case, we replace the value pointed to by the c key (the array [true, false]) with one that takes up less space (the integer 1):

MYSQL

UPDATE jtable
    SET jcol = JSON_SET(jcol, "$.a", 10, "$.b", "wx", "$.c", 1);
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

Example 4

The effects of successive partial updates on this free space are cumulative, as shown in this example using JSON_SET() to reduce the space taken up by the value having key b (and making no other changes):

MYSQL

UPDATE jtable SET jcol = '{"a": 10, "b": 1}';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT JSON_STORAGE_FREE(jcol) FROM jtable;
+-------------------------+
| JSON_STORAGE_FREE(jcol) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (0.00 sec)

Example 5

Updating the column without using JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0, as shown here:

MYSQL

SET @j = '{"a": 10, "b": "wxyz", "c": "[true, false]"}';
Query OK, 0 rows affected (0.00 sec)

SET @j = JSON_SET(@j, '$.a', 10, '$.b', 'wxyz', '$.c', '1');
Query OK, 0 rows affected (0.00 sec)

SELECT @j, JSON_STORAGE_FREE(@j) AS Free;
+----------------------------------+------+
| @j                               | Free |
+----------------------------------+------+
| {"a": 10, "b": "wxyz", "c": "1"} |    0 |
+----------------------------------+------+
1 row in set (0.00 sec)

Example 6

Partial updates of JSON documents can be performed only on column values. For a user variable that stores a JSON value, the value is always completely replaced, even when the update is performed using JSON_SET():

MYSQL

SELECT JSON_STORAGE_FREE('{"a": 10, "b": "wxyz", "c": "1"}') AS Free;
+------+
| Free |
+------+
|    0 |
+------+
1 row in set (0.00 sec)