「MySQL 8.0.29」JSON型のカラムを含むテーブルを生成する方法

2022年7月16日

環境
Ubuntu 22.04 LTS
MySQL 8.0.29-0ubuntu0.22.04.2

操作方法
1.JSON型のカラムを含むテーブルを生成します

mysql> CREATE TABLE `json_userstbl` (`col` JSON);
Query OK, 0 rows affected (0.53 sec)

mysql> SHOW COLUMNS FROM `json_userstbl`;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| col   | json | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.11 sec)

2.テーブルにjsonレコードを挿入します
SQL構文

INSERT INTO `json_userstbl`
VALUES
('{"name": "user11", "flg": 1, "options": {"x": 150, "y": 250}}'),
('{"name": "user22", "flg": 0, "options": {"x": 300}}'),
('{"name": "user33", "flg": 1, "options": {"x": 150, "y": 200, "z": [2, 3, 4]}}');

実行結果

mysql> INSERT INTO `json_userstbl`
    -> VALUES
    -> ('{"name": "user11", "flg": 1, "options": {"x": 150, "y": 250}}'),
    -> ('{"name": "user22", "flg": 0, "options": {"x": 300}}'),
    -> ('{"name": "user33", "flg": 1, "options": {"x": 150, "y": 200, "z": [2, 3, 4]}}');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

3.テーブルのjsonレコードを取得します

mysql>  SELECT * FROM `json_userstbl`;
+-------------------------------------------------------------------------------+
| col                                                                           |
+-------------------------------------------------------------------------------+
| {"flg": 1, "name": "user11", "options": {"x": 150, "y": 250}}                 |
| {"flg": 0, "name": "user22", "options": {"x": 300}}                           |
| {"flg": 1, "name": "user33", "options": {"x": 150, "y": 200, "z": [2, 3, 4]}} |
+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)