laravel - 如何使用 laravel 迁移在 mysql 8 中为 json 列设置索引

Question

我正在使用 laravel 6 创建一个项目。我的表列类型之一是 json。表格列中的数据格式是这样的:{age:30, gender:male, nation:china,...}。我想知道是否有办法通过 laravel 迁移为该列设置索引。我的数据库版本是mysql 8.0.21。谢谢!

Answer 1

我找到了 this article对解决这个问题很有帮助。因此，对于上面的示例结构，您的迁移可能如下所示:

public function up(){
    Schema::create('my_table', function(Blueprint $table){
        $table->bigIncrements('id');
        $table->json('my_json_col')->nullable();
        $table->timestamps();

        // add stored columns with an index 
        // index in this is optional, but recommended if you will be filtering/sorting on these columns
        $table->unsignedInteger('age')->storedAs('JSON_UNQUOTE(my_json_col->>"$.age")')->index();
        $table->string('gender')->storedAs('JSON_UNQUOTE(my_json_col->>"$.gender")')->index();
        $table->string('nation')->storedAs('JSON_UNQUOTE(my_json_col->>"$.nation")')->index();
    });
}

这相当于下面的 mysql 语句:

create table my_table
(
    id          bigint unsigned auto_increment primary key,
    my_json_col json      null,
    created_at  timestamp null,
    updated_at  timestamp null,
    age         int unsigned as (json_unquote(json_unquote(json_extract(`my_json_col`, _utf8mb4'$.age')))) stored,
    gender      varchar(255) as (json_unquote(json_unquote(json_extract(`my_json_col`, _utf8mb4'$.gender')))) stored,
    nation      varchar(255) as (json_unquote(json_unquote(json_extract(`my_json_col`, _utf8mb4'$.nation')))) stored
)
    collate = utf8mb4_unicode_ci;

create index my_table_age_index
    on my_table (age);

create index my_table_gender_index
    on my_table (gender);

create index my_table_nation_index
    on my_table (nation);

以及创建后表格的简单 View :

这个例子创建了实际的存储列，对于这个场景我认为这是你想要的。但是您也可以制作虚拟列，它们是在查询时创建的而不是持久列，并且您只需在迁移中使用 virtualAs 函数而不是 storedAs 函数。

这些函数记录在 Column Modifiers 中Laravel 迁移文档的部分，但它没有详细介绍 JSON 列，这需要更多的 mysql 知识。
我还找到了this article有助于 JSON 列 (SemiSQL) 的 mysql 方面。