MySQL - JSON
- MySQL JSON
- 从 JSON 列检索数据
- JSON_UNQUOTE() 函数
- JSON_TYPE() 函数
- JSON_ARRAY_APPEND() 函数
- JSON_ARRAY_INSERT() 函数
- 使用客户端程序操作 JSON
MySQL 提供了原生的 JSON（JavaScript Object Notation）数据类型，可以高效访问 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"}');
输出
表将创建如下 −
| 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 表中检索每个手机的 OS 名称，使用以下查询 −
SELECT NAME, JSON_EXTRACT(FEATURES,'$.OS') AS OS FROM MOBILES;
除了调用函数外，我们还可以使用 -> 作为 JSON_EXTRACT 的快捷方式。请看下面的查询 −
SELECT NAME, FEATURES->'$.OS' AS OS FROM MOBILES;
输出
两个查询都会显示以下相同的输出 −
| NAME | FEATURES |
|---|---|
| iPhone 15 | "iOS" |
| Samsung S23 | "Android" |
| Google Pixel 7 | "Android" |
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;
输出
两个查询都会显示以下相同的输出 −
| 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 列的类型。
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 查询验证值是否已添加 −
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 查询显示更新后的表 −
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
我们也可以使用 Client Program 定义 MySQL 表列的 JSON 数据类型。
语法
PHP
NodeJS
Java
Python
通过 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/Python 的 execute() 函数执行带有 JSON 数据类型的 CREATE TABLE 语句，具体如下 −
create_table_query = 'CREATE TABLE Blackpink (ID int AUTO_INCREMENT PRIMARY KEY NOT NULL, SONGS JSON)' cursorObj.execute(create_table_query)
示例
以下是相应的程序 −
PHP
NodeJS
Java
Python
$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] . "\n";
}
} 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] . "\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"
});
// 连接到 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...!");
// 创建一个包含 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("\nBlackpink 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("\nType 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("\nThird 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"