• MySQL

    转载 最后发布于2019-08-08 20:23:56 阅读数 1402  收藏

    展开

     

     此学习文是基于MySQL 8.0写的
    得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖

      要在MySQL中存储数据，就必须定义数据库和表结构(schema)，这是一个主要的限制。为了应对这一点，从MySQL 5.7开始，MySQL支恃了 JavaScript对象表示(JavaScriptObject Notation，JSON) 数据类型。之前，这类数据不是单独的数据类型，会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。

    JSON文档以二进制格式存储，它提供以下功能：

    对文档元素的快速读取访问。
    当服务器再次读取JSON文档时，不需要重新解析文本获取该值。
    通过键或数组索引直接查找子对象或嵌套值，而不需要读取文档中的所有值。
    创建一个测试表

    1.  
      mysql> create table employees.emp_details (
    2.  
          -> emp_no int primary key,
    3.  
          -> details json
    4.  
          -> );
    5.  
      Query OK, 0 rows affected (0.17 sec)
    6.  
       
    7.  
      mysql> desc employees.emp_details;
    8.  
      +---------+---------+------+-----+---------+-------+
    9.  
      | Field   | Type    | Null | Key | Default | Extra |
    10.  
      +---------+---------+------+-----+---------+-------+
    11.  
      | emp_no  | int(11) | NO   | PRI | NULL    |       |
    12.  
      | details | json    | YES  |     | NULL    |       |
    13.  
      +---------+---------+------+-----+---------+-------+
    14.  
      2 rows in set (0.00 sec)

     插入JSON

    1.  
      mysql> insert into employees.emp_details (emp_no, details)
    2.  
          -> values ('1',
    3.  
          -> '{"location":"IN","phone":"+11800000000","email":"abc@example.com","address":{"line1":"abc","line2":"xyz street","city":"Bangalore","pin":"560103"}}'
    4.  
          -> );
    5.  
      Query OK, 1 row affected (0.13 sec)
    6.  
       
    7.  
      mysql> select emp_no, details from employees.emp_details;
    8.  
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    9.  
      | emp_no | details                                                                                                                                                           |
    10.  
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    11.  
      |      1 | {"email": "abc@example.com", "phone": "+11800000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN"} |
    12.  
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    13.  
      1 row in set (0.00 sec)

    检索JSON
    可以使用->和->>运算符检索JSON列的字段：

    1.  
      mysql> select emp_no, details -> '$.address.pin' pin 
    2.  
          -> from employees.emp_details;
    3.  
      +--------+----------+
    4.  
      | emp_no | pin      |
    5.  
      +--------+----------+
    6.  
      |      1 | "560103" |
    7.  
      +--------+----------+
    8.  
      1 row in set (0.00 sec)

    如果不用引号检索数据，可以使用->> 运算符（推荐此方式）

    1.  
      mysql> select emp_no, details ->> '$.address.pin' pin 
    2.  
          -> from employees.emp_details;
    3.  
      +--------+--------+
    4.  
      | emp_no | pin    |
    5.  
      +--------+--------+
    6.  
      |      1 | 560103 |
    7.  
      +--------+--------+
    8.  
      1 row in set (0.00 sec)

    JSON函数
    MySQL提供了许多处理JSON数据的函数，让我们看看最常用的几种函数。

    1. 优雅浏览
    想要以优雅的格式显示JSON值，请使用JSON_PRETTY()函数

    1.  
      mysql> select emp_no, json_pretty(details) 
    2.  
          -> from employees.emp_detailsG
    3.  
      *************************** 1. row ***************************
    4.  
                    emp_no: 1
    5.  
      json_pretty(details): {
    6.  
        "email": "abc@example.com",
    7.  
        "phone": "+11800000000",
    8.  
        "address": {
    9.  
          "pin": "560103",
    10.  
          "city": "Bangalore",
    11.  
          "line1": "abc",
    12.  
          "line2": "xyz street"
    13.  
        },
    14.  
        "location": "IN"
    15.  
      }
    16.  
      1 row in set (0.00 sec)

    2. 查找
    可以在WHERE子句中使用col ->> path运算符来引用JSON的某一列

    1.  
      mysql> select emp_no, details 
    2.  
          -> from employees.emp_details 
    3.  
          -> where details ->> '$.address.pin' = "560103";
    4.  
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    5.  
      | emp_no | details                                                                                                                                                           |
    6.  
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    7.  
      |      1 | {"email": "abc@example.com", "phone": "+11800000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN"} |
    8.  
      +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    9.  
      1 row in set (0.00 sec)

    也可以使用JSON_CONTAINS函数查询数据。如果找到了数据，则返回1，否则返回0

    1.  
      mysql> select json_contains(details ->> '$.address.pin',"560103") 
    2.  
          -> from employees.emp_details;
    3.  
      +-----------------------------------------------------+
    4.  
      | json_contains(details ->> '$.address.pin',"560103") |
    5.  
      +-----------------------------------------------------+
    6.  
      |                                                   1 |
    7.  
      +-----------------------------------------------------+
    8.  
      1 row in set (0.00 sec)

    如何查询一个key？使用JSON_CONTAINS_PATH函数检查address. line1是否存在

    1.  
      mysql> select json_contains_path(details, 'one', "$.address.line1") 
    2.  
          -> from employees.emp_details;
    3.  
      +-------------------------------------------------------+
    4.  
      | json_contains_path(details, 'one', "$.address.line1") |
    5.  
      +-------------------------------------------------------+
    6.  
      |                                                     1 |
    7.  
      +-------------------------------------------------------+
    8.  
      1 row in set (0.00 sec)

    one表示至少应该存在一个键，检查address.line1或者address.line2是否存在

    1.  
      mysql> select json_contains_path(details, 'one', "$.address.line1", "$.address.line2") 
    2.  
          -> from employees.emp_details;
    3.  
      +--------------------------------------------------------------------------+
    4.  
      | json_contains_path(details, 'one', "$.address.line1", "$.address.line2") |
    5.  
      +--------------------------------------------------------------------------+
    6.  
      |                                                                        1 |
    7.  
      +--------------------------------------------------------------------------+
    8.  
      1 row in set (0.00 sec)

    如果要检查address.line1或者address.line5是否同时存在，可以使用all，而不是one

    1.  
      mysql> select json_contains_path(details, 'all', "$.address.line1", "$.address.line5") 
    2.  
          -> from employees.emp_details;
    3.  
      +--------------------------------------------------------------------------+
    4.  
      | json_contains_path(details, 'all', "$.address.line1", "$.address.line5") |
    5.  
      +--------------------------------------------------------------------------+
    6.  
      |                                                                        0 |
    7.  
      +--------------------------------------------------------------------------+
    8.  
      1 row in set (0.00 sec)

    3. 修改

    可以使用三种不同的函数来修改数据：JSON_SET()、JSON_INSERT()和JSON _REPLACE()。 在MySQL 8之前的版本中，我们还需要对整个列进行完整的更新，这并不是最佳的方法。

    3.1. JSON_SET() 替换现有值并添加不存在的值

    1.  
      mysql> update employees.emp_details 
    2.  
          -> set details = json_set(details, "$.address.pin", "560100", "$.nickname","kai") 
    3.  
          -> where emp_no = 1;
    4.  
      Query OK, 1 row affected (0.01 sec)
    5.  
      Rows matched: 1  Changed: 1  Warnings: 0
    6.  
       
    7.  
      mysql> select emp_no, json_pretty(details) 
    8.  
          -> from employees.emp_detailsG
    9.  
      *************************** 1. row ***************************
    10.  
                    emp_no: 1
    11.  
      json_pretty(details): {
    12.  
        "email": "abc@example.com",
    13.  
        "phone": "+11800000000",
    14.  
        "address": {
    15.  
          "pin": "560100",
    16.  
          "city": "Bangalore",
    17.  
          "line1": "abc",
    18.  
          "line2": "xyz street"
    19.  
        },
    20.  
        "location": "IN",
    21.  
        "nickname": "kai"
    22.  
      }
    23.  
      1 row in set (0.00 sec)

    3.2. JSON_INSERT() 插入值，但不替换现有值
    在这种情况下，$.address.pin不会被更新，只会添加一个新的字段$.address.line4

    1.  
      mysql> update employees.emp_details 
    2.  
          -> set details = json_insert(details, "$.address.pin", "560132", "$.address.line4","A Wing") 
    3.  
          -> where emp_no = 1;
    4.  
      Query OK, 1 row affected (0.01 sec)
    5.  
      Rows matched: 1  Changed: 1  Warnings: 0
    6.  
       
    7.  
      mysql> select emp_no, json_pretty(details) 
    8.  
          -> from employees.emp_detailsG
    9.  
      *************************** 1. row ***************************
    10.  
                    emp_no: 1
    11.  
      json_pretty(details): {
    12.  
        "email": "abc@example.com",
    13.  
        "phone": "+11800000000",
    14.  
        "address": {
    15.  
          "pin": "560100",
    16.  
          "city": "Bangalore",
    17.  
          "line1": "abc",
    18.  
          "line2": "xyz street",
    19.  
          "line4": "A Wing"
    20.  
        },
    21.  
        "location": "IN",
    22.  
        "nickname": "kai"
    23.  
      }
    24.  
      1 row in set (0.01 sec)

    3.3. JSON_REPLACE()
    仅替换现有值
    在这种情况下，$.address.line5不会被添加， 只有$.address.pin会被更新

    1.  
      mysql> update employees.emp_details 
    2.  
          -> set details = json_replace(details, "$.address.pin", "560132", "$.address.line5","Landmark") 
    3.  
          -> where emp_no = 1;
    4.  
      Query OK, 1 row affected (0.00 sec)
    5.  
      Rows matched: 1  Changed: 1  Warnings: 0
    6.  
       
    7.  
      mysql> select emp_no, json_pretty(details) 
    8.  
          -> from employees.emp_detailsG
    9.  
      *************************** 1. row ***************************
    10.  
                    emp_no: 1
    11.  
      json_pretty(details): {
    12.  
        "email": "abc@example.com",
    13.  
        "phone": "+11800000000",
    14.  
        "address": {
    15.  
          "pin": "560132",
    16.  
          "city": "Bangalore",
    17.  
          "line1": "abc",
    18.  
          "line2": "xyz street",
    19.  
          "line4": "A Wing"
    20.  
        },
    21.  
        "location": "IN",
    22.  
        "nickname": "kai"
    23.  
      }
    24.  
      1 row in set (0.00 sec)

    4. 删除 JSON_REMOVE能从JSON文档中删除数据

    1.  
      mysql> update employees.emp_details 
    2.  
          -> set details = json_remove(details, "$.address.line4") 
    3.  
          -> where emp_no = 1;
    4.  
      Query OK, 1 row affected (0.01 sec)
    5.  
      Rows matched: 1  Changed: 1  Warnings: 0
    6.  
       
    7.  
      mysql> select emp_no, json_pretty(details) 
    8.  
          -> from employees.emp_detailsG
    9.  
      *************************** 1. row ***************************
    10.  
                    emp_no: 1
    11.  
      json_pretty(details): {
    12.  
        "email": "abc@example.com",
    13.  
        "phone": "+11800000000",
    14.  
        "address": {
    15.  
          "pin": "560132",
    16.  
          "city": "Bangalore",
    17.  
          "line1": "abc",
    18.  
          "line2": "xyz street"
    19.  
        },
    20.  
        "location": "IN",
    21.  
        "nickname": "kai"
    22.  
      }
    23.  
      1 row in set (0.00 sec)

    5. 其他函数

     JSON_KEYS()：获取JSON文档中的所有键

    1.  
      mysql> select json_keys(details),json_keys(details ->> "$.address") 
    2.  
          -> from employees.emp_details 
    3.  
          -> where emp_no= 1;
    4.  
      +-------------------------------------------------------+------------------------------------+
    5.  
      | json_keys(details)                                    | json_keys(details ->> "$.address") |
    6.  
      +-------------------------------------------------------+------------------------------------+
    7.  
      | ["email", "phone", "address", "location", "nickname"] | ["pin", "city", "line1", "line2"]  |
    8.  
      +-------------------------------------------------------+------------------------------------+
    9.  
      1 row in set (0.00 sec)

    JSON_LENGTH()：给出JSON文档中的元素数
     

    1.  
      mysql> select json_length(details), json_length(details ->> "$.address") 
    2.  
          -> from employees.emp_details 
    3.  
          -> where emp_no= 1;
    4.  
      +----------------------+--------------------------------------+
    5.  
      | json_length(details) | json_length(details ->> "$.address") |
    6.  
      +----------------------+--------------------------------------+
    7.  
      |                    5 |                                    4 |
    8.  
      +----------------------+--------------------------------------+
    9.  
      1 row in set (0.00 sec)

    延伸阅读： https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html 
  • 相关阅读:
    Linux 软件安装到哪里合适，目录详解
    python如何判断1个列表中所有的数据都是相等的？
    web接口开发基础知识－什么是web接口？
    MIME TYPE是什么？
    jenkins展示html测试报告（不使用html publisher）
    【转】Java虚拟机的JVM垃圾回收机制
    Map 排序
    sql in 和 exist的区别
    distinct和group by 去掉重复数据分析
    sql执行机制
  • 原文地址：https://www.cnblogs.com/fengff/p/12692876.html
Copyright © 2020-2023  润新知