Следую документу https://mariadb.com/resources/blog/using-json-in-mariadb/
Ставить MySQL 8.X на Fedory 35 - не лучшая идея.
Структурированные данные + Полуструктурированные данные
Существует множество вариантов использования, в которых имеет смысл комбинировать структурированные и полуструктурированные данные. Это просто мир разработки программного обеспечения для вас. Тем не менее, я всегда считал, что проще всего использовать новые технологии, сосредоточившись на простом, (надеюсь) подходящем сценарии использования, который вы затем можете использовать, чтобы получить свои собственные творческие соки.
Чтобы помочь разобраться с возможностями JSON, доступными в MariaDB, я буду использовать гипотетическое приложение. Это приложение будет содержать только одну таблицу, называемую местоположениями, в которой будут храниться, да, как вы уже догадались, местоположения. Достаточно просто, не так ли?
Введите JSON, который можно использовать для управления различной информацией для каждого типа местоположения. Таким образом, таблица местоположений будет содержать как структурированные, так и полуструктурированные данные.
СОЗДАНИЕ ТАБЛИЦ
Использовать JSON в MariaDB так же просто, как включить возможность хранить данные JSON в таблице. На самом деле SQL, используемый для создания новой таблицы местоположений, должен выглядеть очень знакомо.
MariaDB [verify]> create database datajs ;Query OK, 1 row affected (0.001 sec)
MariaDB [verify]> use datajs ;
Database changed
MariaDB [datajs]> CREATE TABLE locations (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(100) NOT NULL,
-> type CHAR(1) NOT NULL,
-> latitude DECIMAL(9,6) NOT NULL,
-> longitude DECIMAL(9,6) NOT NULL,
-> attr JSON,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.010 sec)
MariaDB [datajs]> desc locations ;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| type | char(1) | NO | | NULL | |
| latitude | decimal(9,6) | NO | | NULL | |
| longitude | decimal(9,6) | NO | | NULL | |
| attr | longtext | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [datajs]> INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
-> ('R', 'Lou Malnatis', 42.0021628, -87.7255662,
-> '{"details": {"foodType": "Pizza", "menu":
'> "https://www.loumalnatis.com/our-menu"},
'> "favorites": [{"description": "Pepperoni deep dish", "price": 18.75},
'> {"description": "The Lou", "price": 24.75}]}');
Query OK, 1 row affected, 2 warnings (0.001 sec)
=================
MariaDB [datajs]> INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
-> ('A', 'Cloud Gate', 41.8826572, -87.6233039,
-> '{"category": "Landmark", "lastVisitDate": "11/10/2019"}');
Query OK, 1 row affected, 2 warnings (0.001 sec)
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations
-> WHERE type = 'R';
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
+--------------+-----------+------------+-----------+
1 row in set (0.001 sec)
===================
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations ;
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
| Cloud Gate | 41.882657 | -87.623304 | NULL |
+--------------+-----------+------------+-----------+
2 rows in set (0.001 sec)
==================
Index Creating
==================
MariaDB [datajs]> ALTER TABLE locations ADD COLUMN
-> food_type VARCHAR(25) AS (JSON_VALUE(attr, '$.details.foodType')) VIRTUAL;
Query OK, 0 rows affected (0.009 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [datajs]> CREATE INDEX foodtypes ON locations(food_type);
Query OK, 0 rows affected (0.008 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [datajs]> select id from locations;
+----+
| id |
+----+
| 2 |
| 1 |
+----+
2 rows in set (0.001 sec)
MariaDB [datajs]> UPDATE locations
-> SET attr = JSON_INSERT(attr,'$.nickname','The Bean')
-> WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [datajs]> UPDATE locations
-> SET attr = JSON_INSERT(attr,
-> '$.foodTypes',
-> JSON_ARRAY('Asian', 'Mexican'))
-> WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
================================
[boris@fedora35server ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.5.13-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
=================================
MariaDB [(none)]> use datajs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [datajs]> desc locations;
+-----------+--------------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| type | char(1) | NO | | NULL | |
| latitude | decimal(9,6) | NO | | NULL | |
| longitude | decimal(9,6) | NO | | NULL | |
| attr | longtext | YES | | NULL | |
| food_type | varchar(25) | YES | MUL | NULL | VIRTUAL GENERATED |
+-----------+--------------+------+-----+---------+-------------------+
7 rows in set (0.002 sec)
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations
-> WHERE type = 'R';
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
+--------------+-----------+------------+-----------+
1 row in set (0.001 sec)
MariaDB [datajs]> SELECT name, latitude, longitude,
-> JSON_VALUE(attr, '$.details.foodType') AS food_type
-> FROM locations
-> ;
+--------------+-----------+------------+-----------+
| name | latitude | longitude | food_type |
+--------------+-----------+------------+-----------+
| Lou Malnatis | 42.002163 | -87.725566 | Pizza |
| Cloud Gate | 41.882657 | -87.623304 | NULL |
+--------------+-----------+------------+-----------+
2 rows in set (0.001 sec)
MariaDB [datajs]> select * from locations ;
+----+--------------+------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| id | name | type | latitude | longitude | attr | food_type |
+----+--------------+------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| 1 | Lou Malnatis | R | 42.002163 | -87.725566 | {"details": {"foodType": "Pizza", "menu": "https://www.loumalnatis.com/our-menu"}, "favorites": [{"description": "Pepperoni deep dish", "price": 18.75}, {"description": "The Lou", "price": 24.75}], "nickname": "The Bean", "foodTypes": ["Asian", "Mexican"]} | Pizza |
| 2 | Cloud Gate | A | 41.882657 | -87.623304 | {"category": "Landmark", "lastVisitDate": "11/10/2019"} | NULL |
+----+--------------+------+-----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
2 rows in set (0.001 sec)
MariaDB [datajs]> SELECT name,latitude,longitude,
-> JSON_QUERY(attr, '$.details') AS details
-> FROM locations
-> WHERE type = 'R';
+--------------+-----------+------------+-----------------------------------------------------------------------+
| name | latitude | longitude | details |
+--------------+-----------+------------+-----------------------------------------------------------------------+
| Lou Malnatis | 42.002163 | -87.725566 | {"foodType": "Pizza", "menu": "https://www.loumalnatis.com/our-menu"} |
+--------------+-----------+------------+-----------------------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [datajs]> SELECT name,latitude,longitude,
-> JSON_QUERY(attr, '$.favorites') AS favorites
-> FROM locations
-> WHERE type = 'R';
+--------------+-----------+------------+------------------------------------------------------------------------------------------------------+
| name | latitude | longitude | favorites |
+--------------+-----------+------------+------------------------------------------------------------------------------------------------------+
| Lou Malnatis | 42.002163 | -87.725566 | [{"description": "Pepperoni deep dish", "price": 18.75}, {"description": "The Lou", "price": 24.75}] |
+--------------+-----------+------------+------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
No comments:
Post a Comment