category_base.sql (89.1 MB)
category_team_developer_config.sql (6.0 MB)
【 TiDB 使用环境】测试
【 TiDB 版本】7.5
表结构:
CREATE TABLE `category_base` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`platformCode` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '平台简码',
`siteCode` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '站点名称',
`categoryId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '分类id',
`parentId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '父类id',
`categoryIdPath` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`categoryIdPathReverse` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`categoryName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '分类名称',
`categoryNamePath` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '分类名称路径',
`type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '分类类型(homePage:首页;newProductList: 新品榜单)',
`url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '分类url',
`level` int unsigned NOT NULL DEFAULT '0' COMMENT '级别',
`isDel` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 1.删除 0.未删除,默认为0',
`isLeaf` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否有子分类 1是 0否',
`version` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '版本号',
`createdTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '创建日期',
`createdUser` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '创建人编号',
`updateTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '最后更新时间',
`updateUser` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '更新人',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_platform_site_level_type` (`platformCode`,`siteCode`,`categoryId`,`level`,`type`) USING BTREE,
KEY `idx_site_category` (`siteCode`,`categoryName`) USING BTREE,
KEY `idx_categoryId` (`categoryId`) USING BTREE,
KEY `idx_parentId` (`parentId`) USING BTREE,
KEY `idx_categoryIdPath` (`categoryIdPath`) USING BTREE,
KEY `idx_categoryIdPathReverse` (`categoryIdPathReverse`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=329579 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='category base 分类';
CREATE TABLE `category_team_developer_config` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`teamConfigId` int unsigned NOT NULL COMMENT '团队分类配置id',
`teamName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '团队名称',
`platform` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '平台',
`site` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '站点',
`categoryId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '分类id',
`level` tinyint(1) NOT NULL DEFAULT '0' COMMENT '分类级别',
`parentCategoryId` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '父分类id',
`developer` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '开发员,多个逗号隔开',
`createTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '配置创建时间',
`createUser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '配置创建人',
`updateTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '配置更新时间',
`updateUser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '配置更新人',
`isDel` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除 1.是 0.否',
`delTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '删除时间',
`delUser` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '删除人',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_t_p_s_c_d` (`teamName`,`platform`,`site`,`categoryId`,`delTime`) USING BTREE,
KEY `idx_platform` (`platform`) USING BTREE,
KEY `idx_site` (`site`) USING BTREE,
KEY `idx_categoryId` (`categoryId`) USING BTREE,
KEY `idx_teamConfigId` (`teamConfigId`) USING BTREE,
KEY `idx_del` (`isDel`) USING BTREE,
KEY `idex_teamName` (`teamName`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=56710 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='团队分类开发员配置';
查询SQL:
SELECT a.`teamConfigId`, a.`teamName`, a.`platform`,
CASE
WHEN a.platform = 'EB'
THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND a.level = `level`)
WHEN a.platform = 'SM'
THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND `type` = 'java' AND a.level = `level`)
WHEN a.platform = 'YA'
THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND `type` = 'homePage' AND a.level = `level`)
WHEN a.platform = 'BB'
THEN (SELECT categoryIdPath FROM `category_base` WHERE isDel = 0 AND platformCode = a.platform AND siteCode = a.site AND categoryId = a.categoryId AND `type` = 'list' AND a.level = `level`)
END AS `categoryIdPath`
FROM
`category_team_developer_config` as `a`
WHERE
a.`isDel` = 0 AND (FIND_IN_SET('liupan', `developer`)) LIMIT 0,10
报错信息:
> 1242 - Subquery returns more than 1 row
> 时间: 0.067s
同样的表结构和数据,在mysql和DB2的数据库里面里面是正常的,但是在tidb里面是报错,并且看了下数据,不应该存在这个错误
大家可以拿这个SQL和脚本数据测试下,是不是我的写法有问题,还是说这是一个BUG
数据:
category_base.sql (89.1 MB)
category_team_developer_config.sql (6.0 MB)