JSON_STORAGE_SIZE(json_val)

MySQL 8.4 🇮🇩 Bahasa Indonesia

Fungsi ini mengembalikan jumlah byte yang digunakan untuk menyimpan representasi biner dokumen JSON. Jika argumennya adalah kolom JSON, ini adalah ruang yang digunakan untuk menyimpan dokumen JSON saat dimasukkan ke dalam kolom, sebelum pembaruan parsial apa pun yang mungkin dilakukan setelahnya. json_val harus berupa dokumen JSON yang valid atau string yang dapat diuraikan menjadi satu. Jika berupa string, fungsi mengembalikan jumlah ruang penyimpanan dalam representasi biner JSON yang dibuat dengan menguraikan string sebagai JSON dan mengonversinya menjadi biner. Ia mengembalikan NULL jika argumennya

Syntax

MYSQL

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

INSERT INTO jtable VALUES
    ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

SELECT
    jcol,
    JSON_STORAGE_SIZE(jcol) AS Size,
    JSON_STORAGE_FREE(jcol) AS Free
FROM jtable;
+-----------------------------------------------+------+------+
| jcol                                          | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)

Contoh

Example

An error results when json_val is not NULL, and is not—or cannot be successfully parsed as—a JSON document.

MYSQL

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

INSERT INTO jtable VALUES
    ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
Query OK, 1 row affected (0.04 sec)

SELECT
    jcol,
    JSON_STORAGE_SIZE(jcol) AS Size,
    JSON_STORAGE_FREE(jcol) AS Free
FROM jtable;
+-----------------------------------------------+------+------+
| jcol                                          | Size | Free |
+-----------------------------------------------+------+------+
| {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
+-----------------------------------------------+------+------+
1 row in set (0.00 sec)

Example 2

To illustrate this function's behavior when used with a JSON column as its argument, we create a table named jtable containing a JSON column jcol, insert a JSON value into the table, then obtain the storage space used by this column with JSON_STORAGE_SIZE(), as shown here:

MYSQL

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

SELECT
    jcol,
    JSON_STORAGE_SIZE(jcol) AS Size,
    JSON_STORAGE_FREE(jcol) AS Free
FROM jtable;
+--------------------------------------------+------+------+
| jcol                                       | Size | Free |
+--------------------------------------------+------+------+
| {"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"} |   47 |    3 |
+--------------------------------------------+------+------+
1 row in set (0.00 sec)

Example 3

According to the output of JSON_STORAGE_SIZE(), the JSON document inserted into the column takes up 47 bytes. We also checked the amount of space freed by any previous partial updates of the column using JSON_STORAGE_FREE(); since no updates have yet been performed, this is 0, as expected.

MYSQL

UPDATE jtable
    SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT
    jcol,
    JSON_STORAGE_SIZE(jcol) AS Size,
    JSON_STORAGE_FREE(jcol) AS Free
FROM jtable;
+------------------------------------------------+------+------+
| jcol                                           | Size | Free |
+------------------------------------------------+------+------+
| {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |    0 |
+------------------------------------------------+------+------+
1 row in set (0.00 sec)

Example 4

Next we perform an UPDATE on the table that should result in a partial update of the document stored in jcol, and then test the result as shown here:

MYSQL

SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
Query OK, 0 rows affected (0.00 sec)

SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+------------------------------------+------+
| @j                                 | Size |
+------------------------------------+------+
| [100, "sakila", [1, 3, 5], 425.05] |   45 |
+------------------------------------+------+
1 row in set (0.00 sec)

SET @j = JSON_SET(@j, '$[1]', "json");
Query OK, 0 rows affected (0.00 sec)

SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+----------------------------------+------+
| @j                               | Size |
+----------------------------------+------+
| [100, "json", [1, 3, 5], 425.05] |   43 |
+----------------------------------+------+
1 row in set (0.00 sec)

SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
Query OK, 0 rows affected (0.00 sec)

SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
+---------------------------------------------+------+
| @j                                          | Size |
+---------------------------------------------+------+
| [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
+---------------------------------------------+------+
1 row in set (0.00 sec)

Example 5

The value returned by JSON_STORAGE_FREE() in the previous query indicates that a partial update of the JSON document was performed, and that this freed 3 bytes of space used to store it. The result returned by JSON_STORAGE_SIZE() is unchanged by the partial update.

MYSQL

SELECT
    JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
    JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
+----+----+----+----+
| A  | B  | C  | D  |
+----+----+----+----+
| 45 | 44 | 47 | 56 |
+----+----+----+----+
1 row in set (0.00 sec)