时间戳

首页 > 解决方案 > 如何索引 JSON ARRAY 存储在自己的列中

mysql - 如何索引 JSON ARRAY 存储在自己的列中

问题描述

我有这张桌子：

CREATE TABLE `datacollector` (
  `id` binary(16) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N',
  `processed_at` timestamp NULL DEFAULT NULL,
  `request` json NOT NULL,
  `response` json NOT NULL,
  `response_date` timestamp GENERATED ALWAYS AS (from_unixtime(json_unquote(json_extract(`response`,_utf8mb4'$.date')))) VIRTUAL NULL,
  `sha224` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `available_for` json NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_sha224` (`sha224`),
  KEY `created_at` (`created_at`) /*!80000 INVISIBLE */,
  KEY `cmoa` (`created_at`,`processed`) /*!80000 INVISIBLE */,
  KEY `response_date` (`response_date`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

我使用了一个触发器，它在available_for. 现在我想为available_for.

我已经试过了

ALTER TABLE `hwtools`.`datacollector` 
ADD INDEX `available_for_idx` ((CAST(json_unquote(available_for) AS UNSIGNED ARRAY)));

或者

ALTER TABLE `hwtools`.`datacollector` 
ADD INDEX `available_for_idx` ((CAST(available_for AS UNSIGNED ARRAY)));

这总是给我一个错误。

存储在其中的 JSONavailable_for看起来像'["getUserInfo", "stashClient"]'.

我现在如何使用最后一个 MySQL 8 创建正确的索引？

感谢您对我的问题的意见。

标签： mysqlarraysjson

解决方案

自 MySQL 8.0.17 起使用 InnoDB 引擎才支持多值索引。

您还需要注意您转换数组的类型。Anunsigned array是一个无符号整数数组。对于字符串数组，使用char(255) array或类似的。

这个 DDL 应该可以正常工作：

CREATE TABLE `datacollector` (
  `id` binary(16) NOT NULL,
  `available_for` json NOT NULL,
  PRIMARY KEY (`id`),
  KEY available_for_idx ((CAST(available_for AS char(255) ARRAY)))
) ENGINE=InnoDB;

编辑：我想补充解释。与每个表行只有一个索引节点的传统索引不同，该手册解释说：

多值索引可以有多个索引记录，用于 [/every] 单个数据记录 (N:1)。

那对我来说是个新闻。你每天学习新的东西。

推荐阅读