您可以使用 JSON_INSERT、JSON_REPLACE 和 JSON_SET 函数来更新 JSON 字段中的数据。这些函数还需要路径表达式来指定要修改 JSON 对象的哪些部分。这些函数的输出是一个应用了更改的有效 JSON 对象。

首先，使用 JSON_INSERT 更新 JSON 字段，为所有手机添加一个新的 chipset 键，其值为 “Qualcomm”：

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
 `attributes` ,
 '$.chipset' ,
 'Qualcomm'
)
WHERE
 `category_id` = 2;

$.chipset 路径表达式标识了 chipset 属性位于对象根部的位置。

使用以下查询检查更新后的手机类别：

SELECT
 *
FROM
 `e_store`.`products`
WHERE
 `category_id` = 2

现在所有手机都包含 “Qualcomm”：

现在，使用 JSON_REPLACE 更新 JSON 字段，将所有手机的现有 chipset 键的值修改为 “Qualcomm Snapdragon”：

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
 `attributes` ,
 '$.chipset' ,
 'Qualcomm Snapdragon'
)
WHERE
 `category_id` = 2;

现在所有手机的 “Qualcomm” 已被替换为 “Qualcomm Snapdragon”：

最后，使用 JSON_SET 更新 JSON 字段，为所有电视添加一个新的 body_color 键，其值为 “red”：

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
 `attributes` ,
 '$.body_color' ,
 'red'
)
WHERE
 `category_id` = 1;

现在所有电视都应用了 “red” 颜色：

这些函数看起来相同，但它们的行为方式有所不同。

JSON_INSERT 函数仅在属性不存在时才会将其添加到对象中。

JSON_REPLACE 函数仅在找到属性时才会替换它。

JSON_SET 函数如果未找到属性则添加它，否则替换它。

现在，您可以从 JSON 字段更新数据了。

您可以使用 JSON_REMOVE 函数和 DELETE 从 JSON 字段中删除数据。

JSON_REMOVE 允许您从 JSON 列中删除特定的键/值。

使用 JSON_REMOVE 函数，可以从所有相机中移除 mount_type 键/值对：

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
 `category_id` = 3;

JSON_REMOVE 函数根据路径表达式移除指定的键后，返回更新后的 JSON。

或者，您可以使用 JSON 列来 DELETE 整个行。

使用 DELETE 和 JSON_EXTRACT 以及 LIKE，可以移除所有具有 “Jellybean” 版本 Android 操作系统的手机：

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

此查询将移除 “Desire” 和 “Passion” 型号的手机。

处理特定属性需要使用 JSON_EXTRACT 函数。首先提取手机的 os 属性。然后对包含字符串 Jellybean 的所有记录应用 LIKE 操作符进行 DELETE。

现在，您可以从 JSON 字段删除数据了。

现在，创建一个新的 Laravel 项目。

打开终端窗口并运行以下命令：

composer create-project laravel/laravel estore-example

进入新创建的项目目录：

cd estore-example

配置你的 Laravel 应用程序使用 MySQL 数据库。

你可能需要修改 .env 文件来设置 DB_DATABASE、DB_USERNAME 和 DB_PASSWORD。

你将分别为 brands、categories 和 products 创建三个 migration。

创建 create_brands migration：

php artisan make:migration create_brands

使用以下代码修改 create_brands.php migration：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateBrands extends Migration
{
    /**
     * 运行 migration。
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * 回滚 migration。
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('brands');
    }
}

创建 create_categories migration：

php artisan make:migration create_categories

使用以下代码修改 create_categories.php migration：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCategories extends Migration
{
    /**
     * 运行 migration。
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * 回滚 migration。
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('categories');
    }
}

create_products migration 还将包含索引和外键的指令：

php artisan make:migration create_products

使用以下代码修改 create_products.php migration：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateProducts extends Migration
{
    /**
     * 运行 migration。
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('brand_id');
            $table->unsignedInteger('category_id');
            $table->json('attributes');
            $table->timestamps();
            // 外键约束
            $table->foreign('brand_id')->references('id')->on('brands')->onDelete('restrict')->onUpdate('cascade');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('restrict')->onUpdate('cascade');
            // 索引
            $table->index('brand_id');
            $table->index('category_id');
        });
    }

    /**
     * 回滚 migration。
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('products');
    }
}

注意 migration 中的 $table->json('attributes'); 语句。

类似于使用适当的数据类型命名方法创建其他类型的表字段，你使用 json 方法创建了一个名为 attributes 的 JSON 列。

你将分别为 brands、categories 和 products 创建三个模型。

创建 Brand 模型：

php artisan make:model Brand

使用以下代码修改 Brand.php 文件：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    use HasFactory;

    // 一个品牌对应多个产品
    public function products(){
        return $this->hasMany('Product')
    }
}

创建 Category 模型：

php artisan make:model Category

使用以下代码修改 Category.php 文件：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // 一个分类对应多个产品
    public function products(){
        return $this->hasMany('Product')
    }
}

创建 Product 模型：

php artisan make:model Product

使用以下代码修改 Product.php 文件：

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasFactory;

    public $timestamps = false;

    // 将 attributes JSON 转换为数组
    protected $casts = [
        'attributes' => 'array'
    ];

    // 每个产品属于一个品牌
    public function brand(){
        return $this->belongsTo('Brand');
    }

    // 每个产品属于一个分类
    public function category(){
        return $this->belongsTo('Category');
    }
}

$casts 数组中将键 attributes 设置为 array，确保每次从数据库获取产品时，其 attributes JSON 都会被转换为关联数组。这允许你在 controller 操作中更新记录。

本教程剩余部分的重点将是相机产品类别。

你将构建一个带有相机专用字段的表单视图。为了简洁起见，不会涵盖电视和手机产品类别——但设计会非常相似。

为相机产品类别创建 controller：

php artisan make:controller CameraController

使用以下代码修改 CameraController.php：

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class CameraController extends Controller
{
    // 在数据库中创建产品
    // 使用表单字段
    public function store(Request $request){
        // 创建对象并设置属性
        $camera = new \App\Models\Product();
        $camera->name = $request->name;
        $camera->brand_id = $request->brand_id;
        $camera->category_id = $request->category_id;
        $camera->attributes = [
            'processor' => $request->processor,
            'sensor_type' => $request->sensor_type,
            'monitor_type' => $request->monitor_type,
            'scanning_system' => $request->scanning_system,
        ];
        // 保存到数据库
        $camera->save();
        // 显示创建的相机
        return view('product.camera.show', ['camera' => $camera]);
    }
}

这完成了相机的 store function。

在 resources/views/product/camera 目录树中创建 new.blade.php 文件来创建视图：

<form method="POST" action="/product/camera/store">
    @csrf
    <table>
        <tr>
            <td><label for="name">名称</label></td>
            <td><input id="name" name="name" type="text"></td>
        </tr>
        <tr>
            <td><label for="brand-id">品牌 ID</label></td>
            <td>
                <select id="brand-id" name="brand_id">
                    <option value="1">Samsung</option>
                    <option value="2">Nokia</option>
                    <option value="3">Canon</option>
                </select>
            </td>
        </tr>
        <tr>
            <td><label for="attributes-processor">处理器</label></td>
            <td><input id="attributes-processor" name="processor" type="text"></td>
        </tr>
        <tr>
            <td><label for="attributes-sensor-type">传感器类型</label></td>
            <td><input id="attributes-sensor-type" name="sensor_type" type="text"></td>
        </tr>
        <tr>
            <td><label for="attributes-monitor-type">显示器类型</label></td>
            <td><input id="attributes-monitor-type" name="monitor_type" type="text"></td>
        </tr>
        <tr>
            <td><label for="attributes-scanning-system">扫描系统</label></td>
            <td><input id="attributes-scanning-system" name="scanning_system" type="text"></td>
        </tr>
    </table>
    <input name="category_id" value="3" type="hidden">
    <button type="submit">提交</button>
</form>

brand_id 以硬编码的 select 元素呈现，包含先前创建的三个品牌作为 option。 category_id 以硬编码的隐藏输入值呈现，设置为相机的 id。

修改 routes/web.php 中的路由以显示相机：

// ...

use App\Http\Controllers\CameraController;

Route::get('/product/camera/new', function() {
    return view('product/camera/new');
});

Route::post(
    '/product/camera/store',
    [CameraController::class, 'store']
);

使用以下命令启动应用：

php artisan serve

然后，在浏览器中访问 localhost:8000/product/camera/new。它将显示一个用于添加新相机的表单。

先前在 Product 模型中声明的 $casts 数组将帮助您将属性视为关联数组来读取和编辑产品。

使用以下代码修改 CamerasController：

<?php

// ...

class CameraController extends Controller
{
    // ... store ...

    // 从数据库获取单个产品
    public function show($id){
        $camera = \App\Models\Product::find($id);
        return view('product.camera.show', ['camera' => $camera]);
    }
}

这就完成了相机产品的 show 函数。

在 resources/views/product/camera 目录树中创建 show.blade.php 视图文件：

<table>
    <tr>
        <td>名称</td>
        <td>{{ $camera->name }}</td>
    </tr>
    <tr>
        <td>品牌 ID</td>
        <td>{{ $camera->brand_id }}</td>
    </tr>
    <tr>
        <td>分类 ID</td>
        <td>{{ $camera->category_id }}</td>
    </tr>
    <tr>
        <td>处理器</td>
        <td>{{ $camera->attributes['processor'] }}</td>
    </tr>
    <tr>
        <td>传感器类型</td>
        <td>{{ $camera->attributes['sensor_type'] }}</td>
    </tr>
    <tr>
        <td>显示器类型</td>
        <td>{{ $camera->attributes['monitor_type'] }}</td>
    </tr>
    <tr>
        <td>扫描系统</td>
        <td>{{ $camera->attributes['scanning_system'] }}</td>
    </tr>
</table>

修改 routes/web.php 中的路由以显示相机产品：

// ...

Route::get(
    '/product/camera/show/{id}',
    [CameraController::class, 'show']
);

使用以下命令启动应用程序：

php artisan serve

然后，在浏览器中访问有效的相机产品 id（例如 localhost:8000/product/camera/show/11）。它将显示 ID 为“11”的产品的相机信息表格。

通过结合 store 和 show 的技术，您可以创建一个视图来 edit 现有产品。

您可以创建类似于 new.blade.php 中的表单。然后使用类似于 show.blade.php 中使用的产品变量来预填充它：

<tr>
    <td><label for="attributes-processor">处理器</label></td>
    <td><input id="attributes-processor" name="processor" type="text" value="{{ $camera->attributes['processor'] }}"></td>
</tr>

现在，表单会显示现有值，让用户更容易看到需要更新的内容。

首先，使用 id 检索模型。然后，应用请求中的值。最后，将新值保存到数据库。

您还可以使用 Eloquent ORM 查询 JSON 列。

考虑一个搜索页面，允许用户根据他们感兴趣的属性搜索相机。

public function search(Request $request){
    $cameras = \App\Models\Product::where([
        ['attributes->processor', 'like', $request->processor],
        ['attributes->sensor_type', 'like', $request->sensor_type],
        ['attributes->monitor_type', 'like', $request->monitor_type],
        ['attributes->scanning_system', 'like', $request->scanning_system]
    ])->get();
    return view('product.camera.search', ['cameras' => $cameras]);
}

检索到的记录现在将作为 $cameras 集合提供给 product.camera.search 视图。这将允许您循环遍历结果并显示满足用户搜索请求条件的相机。

使用非 JSON 列属性，您可以通过指定 where 子句然后调用 delete 方法来删除产品。

例如，在 id 的情况下。

\App\Models\Product::where('id', $id)->delete();

对于 JSON 列，使用单个或多个属性指定 where 子句，然后调用 delete 方法。

\App\Models\Product::where('attributes->sensor_type', 'CMOS')->delete();

在这个例子中，这段代码将删除所有具有设置为“CMOS”的 sensor_type 属性的产品。

1. 哪些 MySQL 版本支持 JSON？

原生 JSON 数据类型和 JSON 函数在 MySQL 5.7.8 及更高版本中可用，包括 MySQL 8.x。如果您使用的是较旧版本，则无法使用 JSON 列类型或内置 JSON 函数。

2. 如何在 MySQL 中为 JSON 字段创建索引？

您不能直接在 JSON 列上创建索引。为了加速查询，请使用生成的（虚拟或存储）列来提取您过滤的 JSON 路径，然后为该列创建索引。有关 MySQL 中索引的更多信息，请参阅《如何在 MySQL 中使用索引》。

3. 在 MySQL 中存储 JSON 是一种好实践吗？

这取决于使用场景。MySQL 中的 JSON 适用于半结构化或可变属性（例如，产品规格、功能标志），同时将数据的其余部分保留在普通列中。对于高度关系型、严格结构化的数据，传统的列和规范化通常更好。有关何时选择关系型模型与其他模型的更多信息，请参阅数据库类型以及 SQL 与 NoSQL 的比较。

4. JSON 列可以强制执行模式吗？

不能。MySQL 中的 JSON 类型存储有效的 JSON，但不对键或值类型强制执行模式。从 MySQL 8.0.17 开始，您可以使用 JSON_SCHEMA_VALID() 验证文档是否符合 JSON Schema；否则，验证由您的应用程序负责。

5. MySQL 中 JSON 的限制是什么？

JSON 文档以二进制形式存储，并受与其他数据相同的行和数据包大小限制。在实践中，文档大小是有限的（例如，最大行大小和 max_allowed_packet）。JSON 值的理论最大值为 1GB。对于大型或深度嵌套的 JSON，请考虑文档存储或规范化表是否更合适。

在本文中，您设计了一个使用 JSON 数据类型的 MySQL 数据库，并使用 Laravel Web 应用程序连接到它。

每当您需要在单独的表中以键/值对形式保存数据或处理实体的灵活属性时，都应考虑使用 JSON 数据类型字段，因为它可以极大地帮助压缩您的数据库设计。

如果您有兴趣深入了解，MySQL 文档是一个很好的资源，可以进一步探索 JSON 概念。

有关 Laravel 的更多信息，您可以参考我们的技术讲座《Laravel 入门》。

• 在云端运行 MySQL： 使用 Managed Databases for MySQL 来实现自动化备份、高可用性和扩展，这样您就可以专注于构建应用程序，而不必管理数据库。
• 深入了解 MySQL： 阅读 How To Use Indexes in MySQL 和 How To Optimize Queries and Tables in MySQL and MariaDB on a VPS，以提升处理 JSON 和其他列时的性能。
• 选择合适的数据模型： 查看 What Are the Different Types of Databases? 和 A Comparison of NoSQL Database Management Systems and Models，以决定关系型数据库中的 JSON 是否适合，还是文档型或其他数据库类型更合适。