MySQL - JSON

• MySQL JSON
• 从 JSON 列中检索数据
• JSON_UNQUOTE() 函数
• JSON_TYPE() 函数
• JSON_ARRAY_APPEND() 函数
• JSON_ARRAY_INSERT() 函数
• 使用客户端程序的 JSON

MySQL 提供原生 JSON(JavaScript 对象表示法)数据类型，可高效访问 JSON 文档中的数据。此数据类型在 MySQL 5.7.8 及更高版本中引入。

在此之前，JSON 格式的字符串存储在表的字符串列中。然而，由于以下原因，JSON 数据类型被证明比字符串更具优势:

• 它会自动验证 JSON 文档，并在存储无效文档时显示错误。
• 它以内部格式存储 JSON 文档，以便轻松读取文档元素。因此，当 MySQL 服务器稍后以二进制格式读取存储的 JSON 值时，它仅允许服务器直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

JSON 文档的存储要求与 LONGBLOB 或 LONGTEXT 数据类型的存储要求类似。

MySQL JSON

要使用 JSON 数据类型定义表列，我们在 CREATE TABLE 语句中使用关键字 JSON。

我们可以在 MySQL 中创建两种类型的 JSON 值:

• JSON 数组:它是用逗号分隔并用方括号 ([]) 括起来的值列表。

• JSON 对象:具有一组键值对以逗号分隔，并用花括号 ({}) 括起来。

语法

以下是定义数据类型为 JSON 的列的语法 -

CREATE TABLE table_name (
   ...
   column_name JSON,
   ... 
);

示例

让我们看一个示例，演示如何在 MySQL 表中使用 JSON 数据类型。在这里，我们使用以下查询创建一个名为 MOBILES 的表 -

CREATE TABLE MOBILES(
   ID INT NOT NULL,
   NAME VARCHAR(25) NOT NULL,
   PRICE DECIMAL(18,2),
   FEATURES JSON,
   PRIMARY KEY(ID)
);

现在，我们使用 INSERT 语句将值插入此表。在 FEATURES 列中，我们使用 键值 对作为 JSON 值。

INSERT INTO MOBILES VALUES
(121, 'iPhone 15', 90000.00, '{"OS": "iOS", "Storage": "128GB", "Display": "15.54cm"}'),
(122, 'Samsung S23', 79000.00, '{"OS": "Android", "Storage": "128GB", "Display": "15.49cm"}'),
(123, 'Google Pixel 7', 59000.00, '{"OS": "Android", "Storage": "128GB", "Display": "16cm"}');

输出

该表将创建为 −

从 JSON 列中检索数据

由于 JSON 数据类型可以更轻松地读取所有 JSON 元素，因此我们也可以直接从 JSON 列中检索每个元素。MySQL 提供了 JSON_EXTRACT() 函数来实现此目的。

语法

以下是 JSON_EXTRACT() 函数的语法 -

JSON_EXTRACT(json_doc, path)

在 JSON 数组中，我们可以通过指定其索引(从 0 开始)来检索特定元素。在 JSON 对象中，我们通过键值对指定键。

示例

在此示例中，我们使用以下查询从之前创建的 MOBILES 表中检索每部手机的操作系统名称 -

SELECT NAME, JSON_EXTRACT(FEATURES,'$.OS')
AS OS FROM MOBILES;

除了调用该函数，我们还可以使用 -> 作为 JSON_EXTRACT 的快捷方式。查看以下查询 -

SELECT NAME, FEATURES->'$.OS'
AS OS FROM MOBILES;

输出

两个查询均显示以下相同的输出 -

JSON_UNQUOTE() 函数

JSON_UNQUOTE() 函数用于在检索 JSON 字符串时删除引号。语法如下:-

JSON_UNQUOTE(JSON_EXTRACT(json_doc, path))

示例

在此示例中，我们将显示每个移动设备的操作系统名称(不带引号)-

SELECT NAME, JSON_UNQUOTE(JSON_EXTRACT(FEATURES,'$.OS'))
AS OS FROM MOBILES;

或者，我们可以使用 ->> 作为 JSON_UNQUOTE(JSON_EXTRACT(...)) 的快捷方式。

SELECT NAME, FEATURES->>'$.OS'
AS OS FROM MOBILES;

输出

两个查询都显示相同的输出 -

我们不能使用链式 -> 或 ->> 从嵌套的 JSON 对象或 JSON 数组中提取数据。这两个函数只能用于顶层。

JSON_TYPE() 函数

众所周知，JSON 字段可以以数组和对象的形式保存值。为了识别字段中存储的值的类型，我们使用 JSON_TYPE() 函数。语法如下:

JSON_TYPE(json_doc)

示例

在此示例中，我们使用 JSON_TYPE() 函数检查 MOBILES 表的 FEATURES 列的类型。

SELECT JSON_TYPE(FEATURES) FROM MOBILES;

输出

从输出中可以看出，歌曲列的类型为 OBJECT。

JSON_ARRAY_APPEND() 函数

如果我们想在 MySQL 的 JSON 字段中添加另一个元素，可以使用 JSON_ARRAY_APPEND() 函数。但是，新元素只会以数组的形式附加。语法如下:

JSON_ARRAY_APPEND(json_doc, path, new_value);

示例

让我们看一个例子，我们使用 JSON_ARRAY_APPEND() 函数在 JSON 对象的末尾添加一个新元素 -

UPDATE MOBILES
SET FEATURES = JSON_ARRAY_APPEND(FEATURES,'$',"Resolution:2400x1080 Pixels");

我们可以使用 SELECT 查询来验证该值是否已添加 -

SELECT NAME, FEATURES FROM MOBILES;

输出

表格将更新为 -

JSON_ARRAY_INSERT() 函数

我们只能使用 JSON_ARRAY_APPEND() 函数在数组末尾插入 JSON 值。但是，我们也可以使用 JSON_ARRAY_INSERT() 函数选择在 JSON 字段中插入新值的位置。语法如下:

JSON_ARRAY_INSERT(json_doc, pos, new_value);

示例

在这里，我们使用 JSON_ARRAY_INSERT() 函数在数组的 index=1 处添加一个新元素 -

UPDATE MOBILES
SET FEATURES = JSON_ARRAY_INSERT(
    FEATURES, '$[1]', "Charging: USB-C"
);

要验证值是否已添加，请使用 SELECT 查询显示更新后的表格 -

SELECT NAME, FEATURES FROM MOBILES;

输出

表格将更新为 -

使用客户端程序的 JSON

我们还可以使用客户端程序定义一个 JSON 数据类型的 MySQL 表列。

语法

$sql = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)';
$mysqli->query($sql);

sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,SONGS JSON)";
con.query(sql)

String sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)";
statement.execute(sql);

create_table_query = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)'
cursorObj.execute(create_table_query)

示例

以下是程序 -

  $dbhost = 'localhost';
  $dbuser = 'root';
  $dbpass = 'password';
  $dbname = 'TUTORIALS';
  $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
 if ($mysqli->connect_errno) {
      printf("Connect failed: %s
", $mysqli->connect_error);
      exit();
  }
  // 创建表 Blackpink
  $sql = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)';
  $result = $mysqli->query($sql);
  if ($result) {
      echo "Table created successfully...!
";
  }
  // 将数据插入到创建的表中
  $q = "INSERT INTO Blackpink (SONGS)
      VALUES (JSON_ARRAY('Pink venom', 'Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'))";
  if ($res = $mysqli->query($q)) {
      echo "Data inserted successfully...!
";
  }
  // 现在显示 JSON 类型
  $s = "SELECT JSON_TYPE(SONGS) FROM Blackpink";
  if ($res = $mysqli->query($s)) {
      while ($row = mysqli_fetch_array($res)) {
          echo $row[0] . "
";
      }
  } else {
      echo 'Failed';
  }
  // JSON_EXTRACT 函数用于获取元素
  $sql = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink";
  if ($r = $mysqli->query($sql)) {
      while ($row = mysqli_fetch_array($r)) {
          echo $row[0] . "
";
      }
  } else {
      echo 'Failed';
  }
  $mysqli->close();

ARRAY
"Kill this love"        

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// 连接到 MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");
   // 创建新数据库
    sql = "Create Database TUTORIALS";
    con.query(sql);
   sql = "USE TUTORIALS";
    con.query(sql);
   //创建 Blackpink 表
    sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,SONGS JSON)";
    con.query(sql);
   sql = "INSERT INTO Blackpink (ID, SONGS) VALUES (ID, JSON_ARRAY('Pink venom','Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'));"
    con.query(sql);
   sql = "select * from blackpink;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Records in Blackpink Table");
      console.log(result);
      console.log("--------------------------");
    });
   sql = "SELECT JSON_TYPE(songs) FROM Blackpink;"
    con.query(sql, function(err, result){
      if (err) throw err
      console.log("Type of the column");
      console.log(result);
      console.log("--------------------------");
    });
   sql = "SELECT JSON_EXTRACT(songs, '$[2]') FROM Blackpink;"
    con.query(sql, function(err, result){
      console.log("fetching the third element in the songs array ");
      if (err) throw err
      console.log(result);
    });
});

 
Connected!
--------------------------
Records in Blackpink Table
[
  {
    ID: 1,
    SONGS: [
      'Pink venom',
      'Shutdown',
      'Kill this love',
      'Stay',
      'BOOMBAYAH',
      'Pretty Savage',
      'PLAYING WITH FIRE'
    ]
  }
]
--------------------------
Type of the column
[ { 'JSON_TYPE(songs)': 'ARRAY' } ]
--------------------------
fetching the third element in the songs array
[ { "JSON_EXTRACT(songs, '$[2]')": 'Kill this love' } ]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Json {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");
        //create a table that takes a column of Json...!
         String sql = "CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)";
         statement.execute(sql);
         System.out.println("Table created successfully...!");
        String sql1 = "INSERT INTO Blackpink (SONGS) VALUES (JSON_ARRAY('Pink venom', 'Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'))";
         statement.execute(sql1);
         System.out.println("Json data inserted successfully...!");
        // 现在显示 JSON 类型
         String sql2 = "SELECT JSON_TYPE(SONGS) FROM Blackpink";
         ResultSet resultSet = statement.executeQuery(sql2);
         while (resultSet.next()){
            System.out.println("Json_type:"+" "+resultSet.getNString(1));
         }
        // JSON_EXTRACT 函数用于获取元素
         String sql3 = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink";
         ResultSet resultSet1 = statement.executeQuery(sql3);
         while (resultSet1.next()){
            System.out.println("Song Name:"+" "+resultSet1.getNString(1));
         }
        connection.close();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
}    

Connected successfully...!
Table created successfully...!
Json data inserted successfully...!
Json_type: ARRAY
Song Name: "Kill this love"      

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# 创建 JSON 列的"Blackpink"表
create_table_query = '''
CREATE TABLE Blackpink (
ID int AUTO_INCREMENT PRIMARY KEY NOT NULL,
SONGS JSON
)'''
cursorObj.execute(create_table_query)
print("Table 'Blackpink' is created successfully!")
# 将值添加到上面创建的表中
insert = """
INSERT INTO Blackpink (SONGS) VALUES
(JSON_ARRAY('Pink venom', 'Shutdown', 'Kill this love', 'Stay', 'BOOMBAYAH', 'Pretty Savage', 'PLAYING WITH FIRE'));
"""
cursorObj.execute(insert)
print("Values inserted successfully!")
# 显示表
display_table = "SELECT * FROM Blackpink;"
cursorObj.execute(display_table)
# 打印表"Blackpink"
results = cursorObj.fetchall()
print("
Blackpink Table:")
for result in results:
    print(result)
# 检查"SONGS"列的类型
type_query = "SELECT JSON_TYPE(SONGS) FROM Blackpink;"
cursorObj.execute(type_query)
song_type = cursorObj.fetchone()
print("
Type of the 'SONGS' column:")
print(song_type[0])
# 获取"SONGS"数组中的第三个元素
fetch_query = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink;"
cursorObj.execute(fetch_query)
third_element = cursorObj.fetchone()
print("
Third element in the 'SONGS' array:")
print(third_element[0])
# 关闭游标和连接
cursorObj.close()
connection.close()     

Table 'Blackpink' is created successfully!
Values inserted successfully!

Blackpink Table:
(1, '["Pink venom", "Shutdown", "Kill this love", "Stay", "BOOMBAYAH", "Pretty Savage", "PLAYING WITH FIRE"]')

Type of the 'SONGS' column:
ARRAY

Third element in the 'SONGS' array:
"Kill this love"