理解 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' => '德语标题', '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 列占用空间但预先计算。对于索引，两者都有效。
以下是在 Laravel 迁移中实现此方法的示例：
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 的解析限制。
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 语句来回滚操作。
