JSON查询bug

[Hydee_zyp (Hacker Guk Vo S16)]

tidb版本5.0

查询SQL：

SELECT t1.ware_code AS `t1.ware_code`, t1.ware_name AS `t1.ware_name`, t2.generic_name AS `t1.generic_name`, t3.group_class_code AS `t3.group_class_code`, t3.group_class_name AS `t3.group_class_name`
	, ct1.class_code AS `ct1.class_code`, ct1.class_name AS `ct1.class_name`, ct2.class_code AS `ct2.class_code`, ct2.class_name AS `ct2.class_name`, ct3.class_code AS `ct3.class_code`
	, ct3.class_name AS `ct3.class_name`, ct4.class_code AS `ct4.class_code`, ct4.class_name AS `ct4.class_name`
FROM h3_ware.t_ware_group_base_info t1
	LEFT JOIN (
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_group_class_info
		WHERE group_id = 100120512
		UNION ALL
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_platform_class_info
	) t
	ON t1.ware_platform_id = t.ware_platform_id
	LEFT JOIN h3_ware.t_ware_platform_base_info t2 ON t.ware_platform_id = t2.ware_platform_id
	LEFT JOIN h3_orgmanager.t_class_group t3 ON t.class_group_id = t3.class_group_id
	LEFT JOIN h3_orgmanager.t_class ct1 ON classid1 = ct1.class_id
	LEFT JOIN h3_orgmanager.t_class ct2 ON classid2 = ct2.class_id
	LEFT JOIN h3_orgmanager.t_class ct3 ON classid3 = ct3.class_id
	LEFT JOIN h3_orgmanager.t_class ct4 ON classid4 = ct4.class_id
WHERE t1.group_id = 100120512
	AND (is_enable = 1
		OR is_enable IS NULL)
	AND (is_delete = 2
		OR is_delete IS NULL)
	AND t.class_group_id = '118'
	AND t.classid1 = '100002048'
	AND t1.group_id = 100120512
ORDER BY t.modify_time DESC
LIMIT 50

在tidb中当条件：AND t.classid1 = ‘100002048’ 执行时，数据为空，AND t.classid1 = 100002048 执行时有数据
在mysql中两种均有数据

[leeray (Lileiaab)]

可以用explain 语法看看同样的sql在mysql和tidb中执行计划的差别

[Hydee_zyp (Hacker Guk Vo S16)]

tidb

eq(json_extract(h3_ware.t_ware_platform_class_info.class_id, "$[0]"), cast("100002048", json BINARY)), or(eq(h3_ware.t_ware_platform_class_info.is_delete, 2), 0), or(eq(h3_ware.t_ware_platform_class_info.is_enable, 1), 0)

mysql

![image|690x174](upload://hHEQLnaJPOvuf2loC3ODrNmVz7G.png) 

[Kongdom]

估计是字段转换的时候，有截取了

[Hydee_zyp (Hacker Guk Vo S16)]

不至于吧，100002048 也不长啊。

怎么处理避免被截取？

[Kongdom]

这一句放到t表里面，也会出现相同的情况么？
AND t.classid1 = ‘100002048’

[Kongdom]

@Hacker_GUKVoS16

cast(“100002048”, json BINARY)

执行计划中很明显是做了一个类型转换

[Hydee_zyp (Hacker Guk Vo S16)]

[Hydee_zyp (Hacker Guk Vo S16)]

mysql里也没有数据，但是上面那个在mysql里有数据。。。

[Kongdom]

提供一下表结构和脱敏数据吧，我们验证一下

[听风吹雨]

建议把SQL简化测试这个值的查询是否有这个问题。

[Joyz]

- 如果你的问题已解决：
  - 如果你自己排查解决了，请附上你的解决方案，对自己的方案标记【对我有用】。
  - 如果别人帮助你解决了问题，那么请选择【最有价值】的回复，标记为【对我有用】，对帮助你的人，也是一种嘉奖和赞赏。
- 被标记了【对我有用】的问题，才能被搜索到，这样子也能帮助他人更高效地找到答案。标记了【对我有用】还能获得 5 积分，5 经验值。
- 如果你的问题还没有解决，请继续追问及反馈你遇到的问题。

[Hydee_zyp (Hacker Guk Vo S16)]

又是union all的问题。之前也我提过类似的，union或导致数据查询问题，好像还没解决。

下面这个SQL不带union all和带union all结果不一样。
1.不带union all数据都不查不出来；
2.带union all，mysql能查出来，tidb查不出 （筛选条件：AND t1.group_id = ‘100120512’）
3.不带union all，数据都能查出来（筛选条件：AND t1.group_id = 100120512）

SELECT *
FROM h3_ware.t_ware_group_base_info t1
	LEFT JOIN (
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_group_class_info
		WHERE group_id = 100120512
		UNION ALL
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_platform_class_info
	) t
	ON t1.ware_platform_id = t.ware_platform_id
	LEFT JOIN h3_orgmanager.t_class ct1 ON classid1 = ct1.class_id
WHERE t1.group_id = 100120512
	AND t.class_group_id = '118'
	AND t.classid1 = '100002048'
	AND t1.group_id = '100120512'
ORDER BY t.modify_time DESC
LIMIT 50

[Kongdom]

提供一下表结构，和可验证的脱敏数据吧。

建议新开贴询问，会有更多大佬关注~

[听风吹雨]

麻烦提供下能复现的操作步骤，包括表结构、SQL等。还有你说的之前也遇到过，这个也一起提供给我们。谢谢。

[Hydee_zyp (Hacker Guk Vo S16)]

tidb查询结果与筛选不一致问题二

问题： 查询： [image] 加上条件查询：t1.change_stock_qty <> 0 [image] 版本：mysql 5.7查询结果正常，tidb 4.0 5.0均异常 说明： 1.与change_stock_qty类型有关，decimal类型时，结果正常 2.与负号【-】有关，删除-，结果正常 3.与union有关，不进行union，结果正常 4.【t1.chan…

[Hydee_zyp (Hacker Guk Vo S16)]

CREATE TABLE `t_ware_group_class_info` (
  `ware_group_class_id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `group_id` bigint(19) NOT NULL DEFAULT '0' COMMENT '集团ID',
  `ware_platform_id` bigint(19) NOT NULL COMMENT '平台商品ID',
  `ware_inside_code` bigint(19) NOT NULL DEFAULT '0' COMMENT '商品ID',
  `class_group_id` bigint(19) NOT NULL COMMENT '类别组ID',
  `class_id` json NOT NULL COMMENT '类别ID',
  `class_code` varchar(32) NOT NULL COMMENT '类别编码',
  `maintain_class_type` tinyint(2) NOT NULL COMMENT '维护层级(1.平台级,2.集团级,3.企业级)',
  `is_enable` tinyint(2) NOT NULL COMMENT '是否启用(1.是,2.否)',
  `data_source` varchar(255) DEFAULT 'HC' COMMENT '来源  HC DATA_CENTER',
  `is_delete` tinyint(2) NOT NULL DEFAULT '2' COMMENT '是否删除(1.是,2.否)',
  `create_user` bigint(19) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_user` bigint(19) NOT NULL COMMENT '最后修改人',
  `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最后修改时间',
  PRIMARY KEY (`ware_group_class_id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_classgroup` (`class_group_id`),
  KEY `idx_classcode` (`class_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=127105 COMMENT='商品集团类别信息表';


INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58553, 100120512, 3975, 3975, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:12', 20133, '2020-10-29 15:48:54.817');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58557, 100120512, 1408, 1408, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:12', 20186, '2020-12-29 10:17:39.137');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58561, 100120512, 3143, 3143, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:12', 20186, '2020-12-29 11:49:32.421');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58569, 100120512, 1826, 1826, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:12', 1003, '2020-12-29 10:06:13.288');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58603, 100120512, 2004, 2004, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:12', 20133, '2021-07-06 17:13:58.423');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58606, 100120512, 2315, 2315, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:12', 20133, '2020-10-29 15:48:54.817');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58632, 100120512, 3073, 3073, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:13', 20133, '2020-10-29 15:48:54.817');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58636, 100120512, 2476, 2476, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:13', 20133, '2020-10-29 15:48:54.817');
INSERT INTO `h3_ware`.`t_ware_group_class_info`(`ware_group_class_id`, `group_id`, `ware_platform_id`, `ware_inside_code`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `data_source`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (58640, 100120512, 4125, 4125, 118, '[100002048]', '04', 1, 1, 'HC', 2, 20133, '2020-10-16 23:48:13', 20133, '2020-10-29 15:48:54.817');




CREATE TABLE `t_ware_platform_class_info` (
  `ware_platform_class_id` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `ware_platform_id` bigint(19) NOT NULL COMMENT '平台商品ID',
  `class_group_id` bigint(19) NOT NULL COMMENT '类别组ID',
  `class_id` json NOT NULL COMMENT '类别ID',
  `class_code` varchar(32) NOT NULL COMMENT '类别编码',
  `maintain_class_type` tinyint(2) NOT NULL COMMENT '维护层级(1.平台级,2.集团级,3.企业级)',
  `is_enable` tinyint(2) NOT NULL COMMENT '是否启用(1.是,2.否)',
  `is_delete` tinyint(2) NOT NULL DEFAULT '2' COMMENT '是否删除(1.是,2.否)',
  `create_user` bigint(19) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_user` bigint(19) NOT NULL COMMENT '最后修改人',
  `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最后修改时间',
  PRIMARY KEY (`ware_platform_class_id`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uqe_warePlatformId_classGroupId` (`ware_platform_id`,`class_group_id`),
  KEY `idx_classgroup` (`class_group_id`),
  KEY `idx_classcode` (`class_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=35627 COMMENT='商品平台类别信息表';

INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (213, 325, 34, '[100000074, 100000152, 100000577, 100001752]', '18010501', 2, 1, 2, 20086, '2020-08-12 16:31:38', 20133, '2021-08-31 16:40:27.039');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (216, 328, 34, '[100000067, 100000084, 100000201, 100000699]', '11090204', 2, 1, 2, 20086, '2020-08-12 16:31:38', 20133, '2021-08-31 16:40:27.040');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (1296, 1408, 34, '[100000067, 100000078, 100000173, 100000628]', '11030202', 2, 1, 2, 20086, '2020-08-12 16:32:35', 20186, '2021-08-31 16:43:16.913');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (1714, 1826, 34, '[100000067, 100000078, 100000173, 100000628]', '11030202', 2, 1, 2, 20086, '2020-08-12 16:32:56', 1003, '2021-08-31 16:43:18.335');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (1892, 2004, 34, '[100000067, 100000083, 100000198, 100000687]', '11080501', 2, 1, 2, 20086, '2020-08-12 16:33:06', 20133, '2021-07-06 17:13:58.252');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2153, 2265, 34, '[100000071, 100000132, 100000484, 100001483]', '15050601', 2, 1, 2, 20086, '2020-08-12 16:33:20', 20186, '2021-01-13 16:20:08.069');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2203, 2315, 34, '[100000071, 100000133, 100000490, 100001509]', '15060405', 2, 1, 2, 20086, '2020-08-12 16:33:22', 20086, '2021-01-13 16:20:08.407');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2364, 2476, 34, '[100000067, 100000093, 100000255, 100000860]', '11180108', 2, 1, 2, 20086, '2020-08-12 16:33:31', 20086, '2021-01-13 16:19:59.529');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2961, 3073, 34, '[100000067, 100000092, 100000248, 100000829]', '11170202', 2, 1, 2, 20086, '2020-08-12 16:34:03', 20086, '2021-01-13 16:19:59.077');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2979, 3091, 34, '[100000067, 100000085, 100000211, 100000717]', '11100901', 2, 1, 2, 20086, '2020-08-12 16:34:03', 20086, '2021-01-13 16:19:57.554');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3026, 3138, 34, '[100000071, 100000131, 100000475, 100001454]', '15040401', 2, 1, 2, 20086, '2020-08-12 16:34:06', 20086, '2021-01-13 16:20:07.709');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3031, 3143, 34, '[100000067, 100000091, 100000241, 100000807]', '11160104', 2, 1, 2, 20086, '2020-08-12 16:34:06', 20186, '2021-01-13 16:19:58.789');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3708, 3820, 34, '[100000074, 100000152, 100000580, 100001755]', '18010801', 2, 1, 2, 20086, '2020-08-12 16:34:41', 1003, '2021-01-13 16:20:11.568');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3863, 3975, 34, '[100000067, 100000085, 100000211, 100000717]', '11100901', 2, 1, 2, 20086, '2020-08-12 16:34:49', 20086, '2021-01-13 16:19:57.554');
INSERT INTO `h3_ware`.`t_ware_platform_class_info`(`ware_platform_class_id`, `ware_platform_id`, `class_group_id`, `class_id`, `class_code`, `maintain_class_type`, `is_enable`, `is_delete`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (4013, 4125, 34, '[100000067, 100000093, 100000255, 100000860]', '11180108', 2, 1, 2, 20086, '2020-08-12 16:34:57', 20086, '2021-01-13 16:19:59.529');









CREATE TABLE `t_ware_group_base_info` (
  `ware_inside_code` bigint(19) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `group_id` bigint(19) NOT NULL COMMENT '集团ID',
  `ware_platform_id` bigint(19) NOT NULL COMMENT '平台商品ID',
  `ware_code` varchar(100) NOT NULL COMMENT '商品编码',
  `generic_name_abc` varchar(50) NOT NULL COMMENT '通用名助记码',
  `generic_name_used` varchar(100) NOT NULL DEFAULT '' COMMENT '通用名别名/曾用名',
  `ware_nickname` varchar(20) NOT NULL COMMENT '商品名',
  `trademark` varchar(20) NOT NULL COMMENT '商标',
  `measurement_unit_id` bigint(19) NOT NULL COMMENT '基本计量单位',
  `production_company_used_name` varchar(60) NOT NULL DEFAULT '' COMMENT '生产企业曾用名',
  `ware_name` varchar(200) NOT NULL COMMENT '商品描述',
  `ware_abc` varchar(200) NOT NULL COMMENT '商品描述助记码',
  `platform_production_origin_place_id` bigint(19) NOT NULL COMMENT '产地',
  `functional_efficacy` varchar(750) NOT NULL DEFAULT '' COMMENT '功能主治/适应症/适应范围',
  `business_scope_record_id` json NOT NULL COMMENT '经营范围',
  `business_scope_code` varchar(50) NOT NULL COMMENT '经营范围编码',
  `holding_conditions` tinyint(2) NOT NULL COMMENT '储存条件(1.阴凉,2.冷藏,3.常温,4.冷冻)',
  `is_cold_chain` tinyint(2) NOT NULL COMMENT '是否冷链药品(1.是,2.否)',
  `cold_chain_upper_limit` int(11) NOT NULL DEFAULT '0' COMMENT '冷链储存温度上限',
  `cold_chain_lower_limit` int(11) NOT NULL DEFAULT '0' COMMENT '冷链储存温度下限',
  `drug_market_permit_holder` varchar(50) NOT NULL COMMENT '上市许可持有人',
  `file_no_expiration_date` date NOT NULL COMMENT '批准文号效期',
  `registration_number_expiration_date` date NOT NULL COMMENT '注册证号效期',
  `production_license_number` varchar(80) NOT NULL COMMENT '生产许可证号',
  `production_license_expiration_date` date NOT NULL COMMENT '生产许可证有效期',
  `prescription_drug_classification_id` bigint(19) NOT NULL COMMENT '处方药分类',
  `drug_standard_code` varchar(300) NOT NULL COMMENT '药品本位码',
  `is_doping` tinyint(2) NOT NULL COMMENT '是否兴奋剂(1.是,2.否)',
  `old_system_code` varchar(20) NOT NULL DEFAULT '' COMMENT '旧系统编码',
  `is_need_first_camp` tinyint(2) NOT NULL COMMENT '是否需要首营(1.是,2.否)',
  `chinese_drug_level_spec` varchar(30) NOT NULL DEFAULT '' COMMENT '中药等级规格',
  `is_made_number_management` tinyint(2) NOT NULL COMMENT '是否批号管理(1.是,2.否)',
  `expiration_date` int(11) NOT NULL COMMENT '有效期/保质期/使用年限（天）',
  `maintenance_type` tinyint(2) NOT NULL COMMENT '养护类型(1.不养护,2.重点养护,3.一般养护)',
  `element` varchar(30) NOT NULL DEFAULT '' COMMENT '成分',
  `inside_barcode` varchar(20) NOT NULL DEFAULT '' COMMENT '内部条形码',
  `is_dangerous_ware` tinyint(2) NOT NULL COMMENT '是否危险品(1.是,2.否)',
  `tax_category_id` bigint(19) NOT NULL COMMENT '税率分类',
  `tax_revenue_class_id` json DEFAULT NULL COMMENT '征税所得收入ID',
  `is_have_discount` tinyint(2) DEFAULT NULL COMMENT '是否享受优惠',
  `is_zero_tax_flag` tinyint(2) DEFAULT NULL COMMENT '是否是零税率标志',
  `registration_approval_no` varchar(20) DEFAULT NULL COMMENT '注册批件号',
  `registration_approval_date` date DEFAULT NULL COMMENT '注册批件号日期',
  `data_source` varchar(255) DEFAULT 'HC' COMMENT '数据来源  HC DATA_CENTER',
  `create_user` bigint(19) NOT NULL COMMENT '创建人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_user` bigint(19) NOT NULL COMMENT '最终修改人',
  `modify_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最终修改时间',
  PRIMARY KEY (`ware_inside_code`) /*T![clustered_index] CLUSTERED */,
  UNIQUE KEY `uk_g_ware_code` (`group_id`,`ware_code`),
  UNIQUE KEY `uk_g_platform_id` (`group_id`,`ware_platform_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=149467 COMMENT='集团商品表';

INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (1408, 100120512, 1408, '1001433', 'AXZFSP', '', '泰方', '天方/泰方', 4, '', '奥硝唑分散片,泰方,天方/泰方,天方,0.25g*12片', 'AXZFSP,TF,TF/TF,TF,0.25G*12P', 2, '适应氧菌感染。', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86903098000184', 2, '19678', 1, '', 1, 1095, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:11', 1003, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (1826, 100120512, 1826, '1001851', 'XFLGRJN(TMPZ)', '', '无', '鑫福来/纽倍乐牌', 4, '', '鑫福来钙软胶囊(透明瓶装),鑫福来/纽倍乐牌,上海纽倍乐科技,1000mg*200粒', 'XFLGRJN(TMPZ),XFL/NBLP,SHNBLKJ,1000MG*200L', 2, '补充钙', '[1, 47, 48]', '101501', 1, 2, 0, 0, '无', '2021-01-08', '2021-01-08', 'SC12731011400408', '2021-01-08', 3, '无', 2, '23866', 1, '', 1, 730, 1, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:13', 1003, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2004, 100120512, 2004, '1002029', 'GZFLW', '', '无', '紫金山泉', 4, '榆社阿胶厂', '桂枝茯苓丸,紫金山泉,山西天生,6g*10丸', 'GZFLW,ZJSQ,SXTS,6G*10W', 2, '活血,化瘀,消癥。用于妇人宿有癥块,或血瘀经闭,行经腹痛,产后恶露不尽。', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86902950001079', 1, '00142', 1, '', 1, 1460, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:13', 21128, '2021-07-06 17:13:58.245');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2315, 100120512, 2315, '1002340', 'NHZYCXSYWJZSZ', '', '无', '诺和针/诺和诺德', 4, '', '诺和针一次性使用无菌注射针,诺和针/诺和诺德,诺和诺德(),30G(0.30*8mm)*7个', 'NHZYCXSYWJZSZ,NHZ/NHND,NHND(),30G(0.30*8MM)*7G', 2, '', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '无', 2, '04723', 1, '', 1, 1825, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:14', 20186, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (2476, 100120512, 2476, '1002501', 'CHXKC', '', '无', '成博士', 4, '', '参花消渴茶,成博士,辽宁德善,3g*60袋', 'CHXKC,CBS,LNDS,3G*60D', 2, '滋阴补肾,益气生津。适用于Ⅱ型糖尿病气阴两虚,肾气不足证,可改善口渴喜饮,多食易饥,倦怠乏力,腰膝酸软,烦热失眠等症状', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86901078000179', 2, '22068', 1, '', 1, 730, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:15', 20186, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3073, 100120512, 3073, '1003098', 'BHSZALDPP', '', '无', '静瑞欣/华北制药', 4, '', '苯磺酸左氨氯地平片,静瑞欣/华北制药,华北,2.5mg*7片*3板', 'BHSZALDPP,JRX/HBZY,HB,2.5MG*7P*3B', 2, '高血压病,心绞痛', '[1, 47, 48]', '101501', 1, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86902697000359', 2, '22876', 1, '', 1, 1095, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:17', 20186, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3143, 100120512, 3143, '1003168', 'YSFXLWP', '', '无', '南国春', 4, '', '盐酸伐昔洛韦片,南国春,湖北潜龙,0.15g*6片', 'YSFXLWP,NGC,HBQL,0.15G*6P', 2, '用于治疗水痘带状疱疹及I型、II型单纯疱疹病毒感染,包括初发和复发的生殖器疱疹病毒感染。', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86901841000245', 2, '17813', 1, '', 1, 730, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:17', 1003, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (3975, 100120512, 3975, '1004000', 'JWBHW', '', '无', '同仁堂', 4, '', '加味保和丸,同仁堂,北京同仁堂,6g*12袋', 'JWBHW,TRT,BJTRT,6G*12D', 2, '健胃消食。用于饮食积滞,消化不良', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86900173000763', 2, '00449', 1, '', 1, 1460, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:19', 20186, '2021-04-16 14:31:10.749');
INSERT INTO `h3_ware`.`t_ware_group_base_info`(`ware_inside_code`, `group_id`, `ware_platform_id`, `ware_code`, `generic_name_abc`, `generic_name_used`, `ware_nickname`, `trademark`, `measurement_unit_id`, `production_company_used_name`, `ware_name`, `ware_abc`, `platform_production_origin_place_id`, `functional_efficacy`, `business_scope_record_id`, `business_scope_code`, `holding_conditions`, `is_cold_chain`, `cold_chain_upper_limit`, `cold_chain_lower_limit`, `drug_market_permit_holder`, `file_no_expiration_date`, `registration_number_expiration_date`, `production_license_number`, `production_license_expiration_date`, `prescription_drug_classification_id`, `drug_standard_code`, `is_doping`, `old_system_code`, `is_need_first_camp`, `chinese_drug_level_spec`, `is_made_number_management`, `expiration_date`, `maintenance_type`, `element`, `inside_barcode`, `is_dangerous_ware`, `tax_category_id`, `tax_revenue_class_id`, `is_have_discount`, `is_zero_tax_flag`, `registration_approval_no`, `registration_approval_date`, `data_source`, `create_user`, `create_time`, `modify_user`, `modify_time`) VALUES (4125, 100120512, 4125, '1004150', 'TMJTJN', '', '无', '步长', 4, '保定步长天浩制药有限公司', '通脉降糖胶囊,步长,保定天浩,0.4g*20粒*3板', 'TMJTJN,BZ,BDTH,0.4G*20L*3B', 2, '养阴清热、清热活血。用于气阴两虚,脉络瘀阻所致的消渴病(糖尿病),证见:神疲乏力,肢麻疼痛,头晕耳鸣,自汗', '[1, 47, 48]', '101501', 3, 2, 0, 0, '无', '2021-01-08', '2021-01-08', '无', '2021-01-08', 3, '86902551000105', 2, '00725', 1, '', 1, 545, 3, '', '', 2, 112, NULL, NULL, NULL, NULL, NULL, 'HC', 20186, '2020-08-07 07:41:20', 20186, '2021-04-16 14:31:10.749');

[听风吹雨]

上面提到的TiDB版本是5.0，麻烦提供下具体的版本号

[听风吹雨]

t_ware_platform_base_info 这个表结构提供下。