MariaDB10.6.4でJSON型のカラムを含むテーブルを生成する方法
環境
MariaDB 10.6.4
1.JSON型のカラムを含むテーブルを生成します
操作例
SQL構文
create table `json_users_tba` (`cola` json);
create table `json_users_tba` (`cola` json);
create table `json_users_tba` (`cola` json);
カラムを確認します
SQL構文
SHOW COLUMNS FROM `json_users_tba`
SHOW COLUMNS FROM `json_users_tba`
SHOW COLUMNS FROM `json_users_tba`
結果
Field Type Null Key Default Extra
cola longtext YES NULL
Field Type Null Key Default Extra
cola longtext YES NULL
Field Type Null Key Default Extra cola longtext YES NULL
2.JSON情報レコードを挿入します
SQL構文
INSERT INTO `json_users_tba`
VALUES
('{"username": "山田", "female": "男", "options": {"age": 20, "level": "A"}}'),
('{"username": "大崎", "female": "女", "options": {"age": 30}}'),
('{"username": "小村", "female": "男", "options": {"age": 40, "level": "B", "tt": [11, 23, 54]}}');
INSERT INTO `json_users_tba`
VALUES
('{"username": "山田", "female": "男", "options": {"age": 20, "level": "A"}}'),
('{"username": "大崎", "female": "女", "options": {"age": 30}}'),
('{"username": "小村", "female": "男", "options": {"age": 40, "level": "B", "tt": [11, 23, 54]}}');
INSERT INTO `json_users_tba`
VALUES
('{"username": "山田", "female": "男", "options": {"age": 20, "level": "A"}}'),
('{"username": "大崎", "female": "女", "options": {"age": 30}}'),
('{"username": "小村", "female": "男", "options": {"age": 40, "level": "B", "tt": [11, 23, 54]}}');
3.JSON情報レコードを取得します
SQL構文
SELECT * FROM `json_users_tba`;
SELECT * FROM `json_users_tba`;
SELECT * FROM `json_users_tba`;
結果
cola
{"username": "山田", "female": "男", "options": {"age": 20, "level": "A"}}
{"username": "大崎", "female": "女", "options": {"age": 30}}
{"username": "小村", "female": "男", "options": {"age": 40, "level": "B", "tt": [11, 23, 54]}}
cola
{"username": "山田", "female": "男", "options": {"age": 20, "level": "A"}}
{"username": "大崎", "female": "女", "options": {"age": 30}}
{"username": "小村", "female": "男", "options": {"age": 40, "level": "B", "tt": [11, 23, 54]}}
cola
{"username": "山田", "female": "男", "options": {"age": 20, "level": "A"}}
{"username": "大崎", "female": "女", "options": {"age": 30}}
{"username": "小村", "female": "男", "options": {"age": 40, "level": "B", "tt": [11, 23, 54]}}