mysql - 与 CONCAT 一起使用时,SQL 命令在查询中挂起
问题描述
我在 Jira 数据库上的 MySQL 上运行了以下命令:
SELECT LOCAL_ISSUE_KEY , REMOTE_ISSUE_KEY
FROM AO_A912D8_SYNC_INFORMATION
WHERE substr(LOCAL_ISSUE_KEY, 5)
IN
(SELECT issuenum
FROM jiraissue
WHERE issuetype
IN
(SELECT ID
FROM issuetype
WHERE pname = "Story"
)
)
;
这就像一个魅力,但是,我不满意,因为值的长度LOCAL_ISSUE_KEY
是可变的:EFW-12345 或 FWAI-5432。所以我必须改为substr(LOCAL_ISSUE_KEY, 5)
或substr(LOCAL_ISSUE_KEY, 6)
所以我想做一些不同的事情并WHERE IN
检查正确的字符串CONCAT()
,而不是减去它的一部分。
作为草稿,我首先尝试这样做:
SELECT LOCAL_ISSUE_KEY , REMOTE_ISSUE_KEY
FROM AO_A912D8_SYNC_INFORMATION
WHERE LOCAL_ISSUE_KEY
IN
(SELECT CONCAT('EFW-',issuenum)
FROM jiraissue
WHERE issuetype
IN
(SELECT ID
FROM issuetype
WHERE pname = "Story"
)
)
;
但这不起作用,查询永远继续
请注意,它可以使用以下方式进行查询:
SELECT LOCAL_ISSUE_KEY , REMOTE_ISSUE_KEY
FROM AO_A912D8_SYNC_INFORMATION
WHERE LOCAL_ISSUE_KEY
IN
(CONCAT('EFW-','58276'))
;
我也尝试了更多()
的SELECT CONCAT()
,但它仍然让数据库像疯了一样搜索。CPU 达到 100%。
有关信息,第二个查询SELECT
有效:
SELECT CONCAT('EFW-',issuenum) FROM jiraissue WHERE issuetype IN (SELECT ID FROM issuetype WHERE pname = "Story" );
10610 rows in set (0.06 sec)
以下是数据示例:
issuetype
ID pname
10000 Epic
10001 Story
jiraissue
ID issuenum PROJECT issuetype
154705 1942 12000 10001
project
ID pname pkey
12000 myproject EFW
AO_A912D8_SYNC_INFORMATION
LOCAL_ISSUE_KEY REMOTE_ISSUE_KEY
EFW-1942 ABC-12354
解决方案
我不知道您的表有多大,即存储了多少 jiraissues 或项目。但是,如果这执行得更快,您是否尝试过?
SELECT LOCAL_ISSUE_KEY , REMOTE_ISSUE_KEY
FROM AO_A912D8_SYNC_INFORMATION
WHERE LOCAL_ISSUE_KEY
IN
(SELECT distinct CONCAT('EFW-',issuenum)
FROM jiraissue
WHERE issuetype
IN
(SELECT distinct ID
FROM issuetype
WHERE pname = "Story"
)
)
;