mysql8联合表查询其中一个表的字段id类型为int，判断是否在另一个表的ids字段类型为Json的数组中是否存在，这个SQL语句怎么写

这可以使用 SQL 的 JSON 操作符 `JSON_CONTAINS()` 实现。

举个例子，假设第一个表名为 `table1`，其中有一个字段名为 `id` 类型为 int，第二个表名为 `table2`，其中有一个字段名为 `ids` 类型为 Json 数组。

那么可以使用以下 SQL 语句检查第一个表中的 `id` 是否存在于第二个表中的 `ids` 数组中:

SELECT 
  *
FROM 
  table1 
  JOIN table2 
    ON JSON_CONTAINS(table2.ids, CAST(table1.id AS JSON), '$')

第三个参数 '$' ,表示寻找在ids json数组中对应的value,

注意: 需要将第一个表的 `id` 字段转换成 JSON 类型才能使用 `JSON_CONTAINS()` 操作符。

相关问题

已知表结构为：CREATE TABLE `dmp_data_verification_rule` ( `id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键', `rule_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '规则编码', `rule_comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '规则说明', `reconfirm_flag` tinyint NOT NULL COMMENT '是否需要人为二次判断', `rule_type_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '规则分类id', `rule_set_ids` json DEFAULT NULL COMMENT '规则集ids，数组形式:["1"]', `rule_order` int DEFAULT NULL COMMENT '规则排序', `extra_note` json DEFAULT NULL COMMENT '备注', `creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人', `creation_time` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建时间', `modifier` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '最后修改人', `modified_time` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '最后修改时间', `valid` tinyint NOT NULL COMMENT '合法标识', `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '乐观锁', PRIMARY KEY (`id`) USING BTREE, KEY `idx_type_id` (`rule_type_id`) USING BTREE COMMENT '规则类型索引', KEY `idx_rule_code` (`rule_code`) USING BTREE COMMENT '规则编码', KEY `idx_rule_order` (`rule_order`) USING BTREE COMMENT '规则排序' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='数据校验规则表';，extra_note 存储的数据有{"scopeCode": "project", "classCodesA": ["ACCCAC"], "classCodesB": ["ACCCCP","ACCCAC"]}，怎么根据ruleTypeId，及classCodesB（数组）进行数据查询，classCodesB传的是个数组，需要判断数组中的元素是否完全相同相等，通过mysql及mapper.xml实现

可以使用MySQL的JSON_CONTAINS函数和JSON_EXTRACT函数来实现。具体的SQL语句如下：

SELECT * FROM dmp_data_verification_rule 
WHERE rule_type_id = 'your_rule_type_id' 
AND JSON_CONTAINS(extra_note->'$.classCodesB', '["ACCCCP", "ACCCAC"]') = 1;

这条SQL语句中，我们查询了dmp_data_verification_rule表中rule_type_id为'your_rule_type_id'，且extra_note字段中的classCodesB数组完全等于["ACCCCP", "ACCCAC"]的数据。其中，JSON_CONTAINS函数用于判断extra_note中的classCodesB数组是否包含指定的数组，如果包含则返回1，否则返回0。而JSON_EXTRACT函数用于提取extra_note中的classCodesB数组。

在mapper.xml中使用该SQL语句的话，可以把SQL语句写在select元素的text属性中。例如：

<select id="selectByRuleTypeAndClassCodesB" resultType="YourResultType">
  SELECT * FROM dmp_data_verification_rule 
  WHERE rule_type_id = #{ruleTypeId} 
  AND JSON_CONTAINS(extra_note->'$.classCodesB', #{classCodesB}) = 1;
</select>

其中，#{ruleTypeId}和#{classCodesB}是使用MyBatis的参数占位符。这样，在调用该方法时，只需要传入ruleTypeId和classCodesB两个参数即可。

但是我的keyword_ids是存在空格或者为空的情况的 请帮我过滤

如果您的 `keyword_ids` 字段可能包含空格或为空值，可以使用 `REGEXP_REPLACE` 函数来过滤这些情况。以下是一个更新后的示例查询：

INSERT INTO sa_ccms_tags_mapping (tag_id, a_id)
SELECT DISTINCT keyword_id, id
FROM sa_cms_article
CROSS JOIN JSON_TABLE(CONCAT('[', REGEXP_REPLACE(keyword_ids, '[ ,]+', ','), ']'), '$[*]' COLUMNS (keyword_id INT PATH '$')) AS jt;

在这个查询中，使用 `REGEXP_REPLACE` 函数将空格或连续的逗号替换为单个逗号。这样可以确保生成的数组中不会包含空元素。

请注意，这里假设您的MySQL版本支持 JSON 函数和 JSON_TABLE 函数。如果不支持，请告诉我您的MySQL版本，我可以提供适用于该版本的替代方案。