在 Laravel 中为 JSON 列创建功能性索引的实践指南-php教程-PHP中文网

在 Laravel 中为 JSON 列创建功能性索引的实践指南

花韻仙語

发布： 2025-10-03 12:05:31

原创

993人浏览过

在 laravel 中为 json 列创建功能性索引的实践指南

本文详细介绍了如何在 Laravel 应用中为数据库的 JSON 列创建功能性索引，以优化基于 JSON 路径的查询性能。内容涵盖了 Laravel Schema Builder 对 JSON 列的支持、通过生成列（MySQL 5.7+）和直接功能性索引（MySQL 8.0+）两种主要方法实现索引，并提供了具体的代码示例和注意事项，旨在帮助开发者规避常见的 Doctrine DBAL 错误，高效管理 JSON 数据索引。

理解 Laravel 中的 JSON 列

Laravel 框架通过其 Schema Builder 提供了对数据库 JSON 列的良好支持。在迁移文件中，你可以轻松地定义一个 JSON 类型的字段来存储结构化数据。

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

class CreateAreaGroupsTable extends Migration
{
    public function up()
    {
        Schema::create('area_groups', function (Blueprint $table) {
            $table->id();
            $table->json('title'); // 定义一个 JSON 类型的列
            $table->foreignId('area_id')->constrained(); // 示例外键
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('area_groups');
    }
}

登录后复制

通过这种方式，您可以在 title 列中存储 JSON 格式的数据，例如 json_encode(['de' =youjiankuohaophpcn '德语标题', 'en' => 'English Title'])。

为 JSON 子路径创建索引的挑战

尽管 JSON 列可以方便地存储复杂数据，但直接在其内部的特定路径（例如 title->de 或 title->en）上创建索引以加速查询，却并非 Laravel Schema Builder 能够直接支持的简单操作。尝试在原始 SQL CREATE TABLE 语句中直接定义此类功能性索引，如 INDEX area_groups_title_de ((JSON_VALUE(title, '$.de')))，可能会导致像 Argument 1 passed to Doctrine\DBAL\Schema\Index::_addColumn() must be of the type string, null given 这样的 Doctrine DBAL 错误。

这个错误通常发生在 Doctrine（Laravel Schema Builder 的底层库）尝试解析或理解这种复杂的、数据库特定的功能性索引语法时。它无法正确地将功能性索引表达式关联到其内部的列表示，从而导致解析失败。为了解决这一问题，我们需要采用更适合的方式来定义这些高级索引，具体取决于您的数据库版本。

方法一：使用生成列（MySQL 5.7 及更高版本）

对于 MySQL 5.7 及更高版本，可以通过创建“生成列”（Generated Columns）来为 JSON 子路径创建索引。生成列的值是根据其他列计算得出的，可以是虚拟的（VIRTUAL）或存储的（STORED）。通常，VIRTUAL 列更节省空间，但在查询时计算；而 STORED 列占用空间但预先计算。对于索引，两者都有效。

英特尔AI工具

英特尔AI与机器学习解决方案

175

查看详情

以下是在 Laravel 迁移中实现此方法的示例：

<?php

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

class AddJsonIndexesViaGeneratedColumns extends Migration
{
    public function up()
    {
        Schema::table('area_groups', function (Blueprint $table) {
            // 确保 'title' 列已存在，如果不存在，请先添加
            // $table->json('title')->after('id');

            // 创建虚拟生成列，用于提取 JSON 路径的值
            // JSON_UNQUOTE 和 JSON_EXTRACT 组合用于提取并去除字符串引号
            $table->string('title_de_index')
                  ->virtualAs("JSON_UNQUOTE(JSON_EXTRACT(title, '$.de'))")
                  ->nullable()
                  ->after('title'); // 可选：指定列位置

            $table->string('title_en_index')
                  ->virtualAs("JSON_UNQUOTE(JSON_EXTRACT(title, '$.en'))")
                  ->nullable()
                  ->after('title_de_index'); // 可选：指定列位置

            // 为这些生成列添加索引
            $table->index('title_de_index', 'area_groups_title_de_index');
            $table->index('title_en_index', 'area_groups_title_en_index');
        });
    }

    public function down()
    {
        Schema::table('area_groups', function (Blueprint $table) {
            $table->dropIndex('area_groups_title_de_index');
            $table->dropIndex('area_groups_title_en_index');
            $table->dropColumn('title_de_index');
            $table->dropColumn('title_en_index');
        });
    }
}

登录后复制

注意事项：

virtualAs() 方法用于定义生成列的表达式。
JSON_EXTRACT(column, path) 用于从 JSON 列中提取数据。
JSON_UNQUOTE() 用于去除提取出的字符串值周围的引号，确保索引的是纯文本。
为生成列添加索引后，数据库在查询这些生成列时将利用索引，从而加速对 JSON 子路径的查询。
nullable() 是因为 JSON_EXTRACT 在路径不存在时会返回 NULL。

方法二：直接使用功能性索引（MySQL 8.0 及更高版本）

MySQL 8.0 引入了对功能性索引（Functional Indexes）的直接支持，允许在表达式的结果上创建索引。这使得在 JSON 列的子路径上直接创建索引变得更为简洁。由于 Laravel Schema Builder 对这种高级的索引语法支持有限，推荐使用 DB::statement 来执行原始 SQL 语句，以绕过 Doctrine 的解析限制。

<?php

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

class AddJsonFunctionalIndexes extends Migration
{
    public function up()
    {
        // 确保 'title' 列已存在。如果不存在，请先在另一个迁移中创建。
        // Schema::table('area_groups', function (Blueprint $table) {
        //     $table->json('title')->after('id');
        // });

        // 使用 DB::statement 添加功能性索引
        // JSON_VALUE 专门用于提取 JSON 中的标量值，并自动去除字符串引号。
        DB::statement('ALTER TABLE area_groups ADD INDEX area_groups_title_de ((JSON_VALUE(title, "$.de")));');
        DB::statement('ALTER TABLE area_groups ADD INDEX area_groups_title_en ((JSON_VALUE(title, "$.en")));');
    }

    public function down()
    {
        // 删除索引
        DB::statement('ALTER TABLE area_groups DROP INDEX area_groups_title_de;');
        DB::statement('ALTER TABLE area_groups DROP INDEX area_groups_title_en;');
    }
}

登录后复制

注意事项：

DB::statement 允许您执行任何原始 SQL 语句，绕过 Doctrine 的解析限制。
JSON_VALUE(column, path) 函数用于从 JSON 文档中提取指定路径的标量值。它会自动去除字符串引号，因此通常不需要 JSON_UNQUOTE。
这种方法更加直接，但要求数据库版本支持功能性索引（MySQL 8.0+）。
在 down 方法中，务必提供正确的 DROP INDEX 语句来回滚操作。