问题

我试图在MySQL 8.0.33中为JSON字段添加索引，使用以下代码：

```sql
ALTER TABLE `json_test` ADD INDEX `test` ((CAST(data->>"$.test" as CHAR(255)) COLLATE utf8mb4_bin)) USING BTREE;

如此处建议的 https://planetscale.com/blog/indexing-json-in-mysql

这个方法在不修改选择查询的情况下可行：

EXPLAIN select * from `json_test` where data->>"$.test" = "foo";
-- 显示 possible_keys = test

问题出在字段长度超过255个字符时插入会失败：

insert into `json_test` set data = '{"test":"长度超过255个字符的字符串..."}';
-- 数据截断以适应函数索引 'test' 在第1行

我对选择查询的控制有限，所以理想情况下我不想修改它们。我正在使用Laravel查询构建器，所以我可能能够扩展 MySqlGrammar.php。

我也宁愿不在MySQL的设置中禁用数据截断警告。

如果值超过255个字符，我并不真的关心它是否被索引，因为大多数人都在搜索短字符串。

非常感谢任何帮助。谢谢！

<details>
<summary>英文:</summary>

I&#39;m trying to add an index to a JSON field in MySql 8.0.33 using

```sql
ALTER TABLE `json_test` ADD INDEX `test` ((
  CAST(data-&gt;&gt;&quot;$.test&quot; as CHAR(255)) COLLATE utf8mb4_bin
)) USING BTREE;

as recommended here https://planetscale.com/blog/indexing-json-in-mysql

This works without modifying the select query:

EXPLAIN select * from `json_test` where data->>"$.test" = "foo";
-- shows possible_keys = test

The problem is when the field is greater than 255 characters the insert fails

insert into `json_test` set data = '{"test":"a string greater than 255 characters ..."}';
-- Data truncated for functional index 'test' at row 1

I don't have much control over the select queries, so ideally I would not have to modify them. I'm using the Laravel query builder so I may be able to extend MySqlGrammar.php.

I would also prefer not to disable the data truncated warnings in mysql's settings.

If the value is over 255 characters I don't really care if it's indexed since most people are searching for short strings.

Any help would be greatly appreciated. Thanks!

答案1

得分: 1

ALTER TABLE json_test ADD INDEX test ((LEFT(data->>"$.test", 255)) COLLATE utf8mb4_bin)) USING BTREE;

尝试这样做。我认为不需要CAST，但如果需要的话，可以添加它。

如果需要，您可以将255降低。

ALTER TABLE `json_test` ADD INDEX `test` ((
  LEFT(data->>"$.test", 255)) COLLATE utf8mb4_bin
)) USING BTREE;