国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

Table of Contents
為什么選擇 MySQL 的 JSON 功能？
創(chuàng)建 JSON 數(shù)據(jù)
存儲 JSON 數(shù)據(jù)
從 JSON 數(shù)據(jù)中提取數(shù)據(jù)
修改 JSON 數(shù)據(jù)
刪除鍵值對
在 JSON 數(shù)據(jù)中搜索
JSON 數(shù)據(jù)聚合
驗證 JSON 數(shù)據(jù)
格式化 JSON 輸出
其他 JSON 函數(shù)
Home Database Mysql Tutorial Using JSON in MySQL

Using JSON in MySQL

Apr 08, 2025 pm 02:39 PM
mysql ai Data formatting key value pair Why 2025

在現(xiàn)代應用開發(fā)中，有效管理半結(jié)構(gòu)化數(shù)據(jù)至關(guān)重要。MySQL 5.7 版本及以上版本內(nèi)置了對 JSON 數(shù)據(jù)類型的支持，為關(guān)系型數(shù)據(jù)庫中存儲、查詢和操作這類數(shù)據(jù)提供了強有力的工具。本文將介紹 MySQL 提供的核心 JSON 函數(shù)，并結(jié)合實際案例進行講解，幫助您快速上手。

為什么選擇 MySQL 的 JSON 功能？

在關(guān)系型數(shù)據(jù)庫中使用 JSON 數(shù)據(jù)類型，可以簡化半結(jié)構(gòu)化或?qū)蛹墧?shù)據(jù)的處理流程，帶來諸多優(yōu)勢：

  • 靈活性: JSON 結(jié)構(gòu)支持動態(tài)、層級數(shù)據(jù)的靈活存儲。
  • 內(nèi)置函數(shù): MySQL 提供高效的 JSON 數(shù)據(jù)查詢、更新和驗證函數(shù)。
  • 集成性: 可以將關(guān)系型數(shù)據(jù)與 JSON 對象結(jié)合，實現(xiàn)混合數(shù)據(jù)模型。

  1. 創(chuàng)建 JSON 數(shù)據(jù)

利用 JSON_OBJECT() 和 JSON_ARRAY() 函數(shù)，可以方便地構(gòu)建 JSON 對象或數(shù)組。

示例：

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

輸出：

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

  1. 存儲 JSON 數(shù)據(jù)

使用 JSON 數(shù)據(jù)類型定義數(shù)據(jù)庫列，即可存儲 JSON 數(shù)據(jù)。

示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

  1. 從 JSON 數(shù)據(jù)中提取數(shù)據(jù)

MySQL 提供多種函數(shù)用于從 JSON 文檔中提取數(shù)據(jù)：

  • JSON_EXTRACT()：使用 JSONPath 表達式獲取指定值。
  • -> 運算符：JSON_EXTRACT() 的簡寫形式。

示例：

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

輸出：

在 MySQL 中使用 JSON

  1. 修改 JSON 數(shù)據(jù)

以下函數(shù)用于更新或添加 JSON 數(shù)據(jù)元素：

  • JSON_SET()：插入或更新鍵值對。
  • JSON_INSERT()：僅當鍵不存在時插入。
  • JSON_REPLACE()：僅更新已存在的鍵值對。

示例：

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

輸出：

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

  1. 刪除鍵值對

使用 JSON_REMOVE() 函數(shù)刪除 JSON 文檔中的元素。

示例：

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

輸出：

{"name": "Bob", "age": 30, "city": "New York"}

  1. 在 JSON 數(shù)據(jù)中搜索

JSON_CONTAINS() 函數(shù)用于檢查 JSON 文檔是否包含特定值。

示例：

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

輸出：

在 MySQL 中使用 JSON

  1. JSON 數(shù)據(jù)聚合

JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 函數(shù)可以將查詢結(jié)果聚合為 JSON 結(jié)構(gòu)。

示例：

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

輸出：

["Bob"]

  1. 驗證 JSON 數(shù)據(jù)

JSON_VALID() 函數(shù)用于檢查字符串是否為有效的 JSON 數(shù)據(jù)。

示例：

SELECT 
    JSON_VALID('{"key": "value"}') AS is_valid,
    JSON_VALID('invalid json') AS is_invalid;

輸出：

在 MySQL 中使用 JSON

  1. 格式化 JSON 輸出

JSON_PRETTY() 函數(shù)將 JSON 數(shù)據(jù)格式化為易于閱讀的格式。

示例：

SELECT JSON_PRETTY(details) AS pretty_json FROM users;

輸出：

{
    "name": "Bob",
    "age": 30,
    "city": "New York"
}

其他 JSON 函數(shù)

MySQL 提供了豐富的 JSON 函數(shù)，本文僅介紹了部分常用函數(shù)。其他函數(shù)包括：JSON_ARRAY_APPEND()、JSON_ARRAY_INSERT()、JSON_CONTAINS_PATH()、JSON_DEPTH()、JSON_KEYS()、JSON_LENGTH()、JSON_MERGE_PATCH()、JSON_MERGE_PRESERVE()、JSON_OVERLAPS()、JSON_QUOTE()、JSON_SEARCH()、JSON_STORAGE_FREE()、JSON_STORAGE_SIZE()、JSON_TABLE()、JSON_TYPE()、JSON_UNQUOTE() 等。

MySQL 的 JSON 函數(shù)為關(guān)系型數(shù)據(jù)庫中半結(jié)構(gòu)化數(shù)據(jù)的管理提供了強大的支持，簡化了 JSON 數(shù)據(jù)的存儲、查詢和操作，為數(shù)據(jù)庫設計提供了新的思路。 熟練掌握這些函數(shù)，將極大地提高開發(fā)效率。

The above is the detailed content of Using JSON in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undress AI Tool

Undress AI Tool

Undress images for free

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

PHP Tutorial
1502
276
Ouyi Exchange APP Android version v6.132.0 Ouyi APP official website download and installation guide 2025 Ouyi Exchange APP Android version v6.132.0 Ouyi APP official website download and installation guide 2025 Aug 04, 2025 pm 11:18 PM

OKX is a world-renowned comprehensive digital asset service platform, providing users with diversified products and services including spot, contracts, options, etc. With its smooth operation experience and powerful function integration, its official APP has become a common tool for many digital asset users.

Best Practices for Managing Large MySQL Tables Best Practices for Managing Large MySQL Tables Aug 05, 2025 am 03:55 AM

When dealing with large tables, MySQL performance and maintainability face challenges, and it is necessary to start from structural design, index optimization, table sub-table strategy, etc. 1. Reasonably design primary keys and indexes: It is recommended to use self-increment integers as primary keys to reduce page splits; use overlay indexes to improve query efficiency; regularly analyze slow query logs and delete invalid indexes. 2. Rational use of partition tables: partition according to time range and other strategies to improve query and maintenance efficiency, but attention should be paid to partitioning and cutting issues. 3. Consider reading and writing separation and library separation: Read and writing separation alleviates the pressure on the main library. The library separation and table separation are suitable for scenarios with a large amount of data. It is recommended to use middleware and evaluate transaction and cross-store query problems. Early planning and continuous optimization are the key.

How to configure MySQL for SSL/TLS connections? How to configure MySQL for SSL/TLS connections? Aug 03, 2025 pm 01:59 PM

FirstcheckifSSLisenabledbyrunningSHOWVARIABLESLIKE'%ssl%';ensurehave_sslisYESandssl_ca,ssl_cert,ssl_keypointtovalidfiles,thenuseSTATUStoconfirmSSLisinuse.2.GenerateSSLcertificateseitherusingMySQL’sbuilt-inauto-generationfortesting(enablesslinmy.cnfan

Binance (BINANCE) contract to launch ALL 75x composite index U-standard perpetual contract Binance (BINANCE) contract to launch ALL 75x composite index U-standard perpetual contract Aug 06, 2025 pm 06:48 PM

Table of Contents About Binance ALL Comprehensive Index About Binance ALL Comprehensive Index Fixed-point adjustment component mechanism The newly launched U-standard perpetual contract with USDT quoted U-standard perpetual contract The Binance Contract is scheduled to officially launch the ALL75 times comprehensive index U-standard perpetual contract at 17:00 on August 6, 2025 (East Eighth District time). About Binance ALL Comprehensive Index Binance Exchange: Official Registration Official Download Binance ALL Comprehensive Index is designed to track the performance of all U-standard perpetual contracts denominated in USDT on the Binance Contract Platform, but the following types of contracts are not included: ETHBTC perpetual contracts quoted using USDC or other stablecoins U-standard delivery contracts All comprehensive index perpetual contracts are permanent

What is a parabolic SAR indicator? How does SAR indicator work? Comprehensive introduction to SAR indicators What is a parabolic SAR indicator? How does SAR indicator work? Comprehensive introduction to SAR indicators Aug 06, 2025 pm 08:12 PM

Contents Understand the mechanism of parabola SAR The working principle of parabola SAR calculation method and acceleration factor visual representation on trading charts Application of parabola SAR in cryptocurrency markets1. Identify potential trend reversal 2. Determine the best entry and exit points3. Set dynamic stop loss order case study: hypothetical ETH trading scenario Parabola SAR trading signals and interpretation Based on parabola SAR trading execution Combining parabola SAR with other indicators1. Use moving averages to confirm trend 2. Relative strength indicator (RSI) for momentum analysis3. Bollinger bands for volatility analysis Advantages of parabola SAR and limitations Advantages of parabola SAR

Cardano (ADA Coin) price forecast: 2025, 2026 and beyond Cardano (ADA Coin) price forecast: 2025, 2026 and beyond Aug 06, 2025 pm 07:33 PM

Directory What is Cardano? Key Features of Cardano How does Cardano work? Why Cardano deserves to consider price and market performance history 2025 ADA forecast 2025, 2026 and 2027 Price forecasts 2040 and 2030 ADA price forecast Factors affecting ADA costs Chart analysis and technical outlook Cardano Forecast Table: Key points summary As an important force in the cryptocurrency industry, Cardano (ADA) provides cutting-edge blockchain solutions with a focus on sustainability, scalability and security. Cardano is co-founder of Ethereum.

Solana (SOL Coin) Price Forecast: 2025-2030 and Future Outlook Solana (SOL Coin) Price Forecast: 2025-2030 and Future Outlook Aug 06, 2025 pm 08:42 PM

Table of Contents Solana's Price History and Important Market Data Important Data in Solana Price Chart: 2025 Solana Price Forecast: Optimistic 2026 Solana Price Forecast: Maintain Trend 2026 Solana Price Forecast: 2030 Solana Long-term Price Forecast: Top Blockchain? What affects the forecast of sun prices? Scalability and Solana: Competitive Advantages Should you invest in Solana in the next few years? Conclusion: Solana's price prospects Conclusion: Solana has its excellent scalability, low transaction costs and high efficiency

Blockchain browser: a must-have tool for querying digital currency transaction information Blockchain browser: a must-have tool for querying digital currency transaction information Aug 06, 2025 pm 11:27 PM

Blockchain browser is a necessary tool for querying digital currency transaction information. It provides a visual interface for blockchain data, so that users can query transaction hash, block height, address balance and other information; its working principle includes data synchronization, parsing, indexing and user interface display; core functions cover querying transaction details, block information, address balance, token data and network status; when using it, you need to obtain TxID and select the corresponding blockchain browser such as Etherscan or Blockchain.com to search; query address information to view balance and transaction history by entering the address; mainstream browsers include Bitcoin's Blockchain.com, Ethereum's Etherscan.io, B

See all articles