MySQL - JSON



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

在引入它之前,JSON 格式的字符串存储在表的字符串列中。但是,由于以下原因,JSON 数据类型被证明比字符串更有优势:

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

JSON 文档的存储需求类似于LONGBLOBLONGTEXT数据类型。

MySQL JSON

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

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

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

  • JSON 对象:一个对象,包含用花括号({})括起来的、用逗号分隔的一组键值对。

语法

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

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

示例

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

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

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

Open Compiler
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"}');

输出

表将创建为:

ID NAME PRICE FEATURES
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 表中,我们使用以下查询检索每部手机的操作系统名称:

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

我们也可以使用->作为 JSON_EXTRACT 的快捷方式,而不是调用函数。请查看以下查询:

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

输出

这两个查询都显示以下相同的输出:

NAME FEATURES
iPhone 15 "iOS"
Samsung S23 "Android"
Google Pixel 7 "Android"

Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.

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(...))的快捷方式。

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

输出

这两个查询都显示以下相同的输出:

NAME FEATURES
iPhone 15 iOS
Samsung S23 Android
Google Pixel 7 Android
我们不能使用链式 -> 或 ->> 从嵌套的 JSON 对象或 JSON 数组中提取数据。这两个运算符只能用于顶层。

JSON_TYPE() 函数

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

JSON_TYPE(json_doc)

示例

在这个例子中,让我们使用JSON_TYPE()函数检查 MOBILES 表的 FEATURES 列的类型。

Open Compiler
SELECT JSON_TYPE(FEATURES) FROM MOBILES;

输出

从输出中可以看到,songs 列的类型是 OBJECT。

JSON_TYPE(FEATURES)
OBJECT
OBJECT
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 查询验证值是否已添加:

Open Compiler
SELECT NAME, FEATURES FROM MOBILES;

输出

表将更新为:

NAME FEATURES
iPhone 15 {"OS": "iOS", "Storage": "128GB", "Display": "15.54cm", "Resolution: 2400 x 1080 Pixels"}
Samsung S23 {"OS": "Android", "Storage": "128GB", "Display": "15.49cm", "Resolution: 2400 x 1080 Pixels"}
Google Pixel 7 {"OS": "Android", "Storage": "128GB", "Display": "16cm", "Resolution: 2400 x 1080 Pixels"}

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 查询显示更新后的表:

Open Compiler
SELECT NAME, FEATURES FROM MOBILES;

输出

表将更新为:

NAME FEATURES
iPhone 15 {"OS": "iOS", "Storage": "128GB", "Display": "15.54cm", "Charging: USB-C", "Resolution: 2400 x 1080 Pixels"}
Samsung S23 {"OS": "Android", "Storage": "128GB", "Display": "15.49cm", "Charging: USB-C", "Resolution: 2400 x 1080 Pixels"}
Google Pixel 7 {"OS": "Android", "Storage": "128GB", "Display": "16cm", "Charging: USB-C", "Resolution: 2400 x 1080 Pixels"}

使用客户端程序的 JSON

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

语法

要通过 PHP 程序创建 JSON 类型列,我们需要使用mysqli函数query()执行包含 JSON 数据类型的 CREATE TABLE 语句,如下所示:

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

要通过 JavaScript 程序创建 JSON 类型列,我们需要使用mysql2库的query()函数执行包含 JSON 数据类型的 CREATE TABLE 语句,如下所示:

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

要通过 Java 程序创建 JSON 类型列,我们需要使用JDBC函数execute()执行包含 JSON 数据类型的 CREATE TABLE 语句,如下所示:

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

要通过 Python 程序创建 JSON 类型列,我们需要使用MySQL Connector/Pythonexecute()函数执行包含 JSON 数据类型的 CREATE TABLE 语句,如下所示:

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(); } // Create table 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...!"; } // Insert data into the created table $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...!"; } // Now display the JSON type $s = "SELECT JSON_TYPE(SONGS) FROM Blackpink"; if ($res = $mysqli->query($s)) { while ($row = mysqli_fetch_array($res)) { echo $row[0] . "\n"; } } else { echo 'Failed'; } // JSON_EXTRACT function to fetch the element $sql = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink"; if ($r = $mysqli->query($sql)) { while ($row = mysqli_fetch_array($r)) { echo $row[0] . "\n"; } } else { echo 'Failed'; } $mysqli->close();

输出

获得的输出如下所示:

ARRAY
"Kill this love"        

var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); // Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); // Create a new database sql = "Create Database TUTORIALS"; con.query(sql); sql = "USE TUTORIALS"; con.query(sql); //Creating Blackpink table 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...!"); // Now display the JSON type 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 function to fetch the element 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 # Establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) # Creating a cursor object cursorObj = connection.cursor() # Creating the table 'Blackpink' with JSON column 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!") # Adding values into the above-created table 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 display_table = "SELECT * FROM Blackpink;" cursorObj.execute(display_table) # Printing the table 'Blackpink' results = cursorObj.fetchall() print("\nBlackpink Table:") for result in results: print(result) # Checking the type of the 'SONGS' column type_query = "SELECT JSON_TYPE(SONGS) FROM Blackpink;" cursorObj.execute(type_query) song_type = cursorObj.fetchone() print("\nType of the 'SONGS' column:") print(song_type[0]) # Fetching the third element in the 'SONGS' array fetch_query = "SELECT JSON_EXTRACT(SONGS, '$[2]') FROM Blackpink;" cursorObj.execute(fetch_query) third_element = cursorObj.fetchone() print("\nThird element in the 'SONGS' array:") print(third_element[0]) # Closing the cursor and connection 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"       
广告