Sql Un-Wizardry：比较另一个列表中的值

I have a comparison I'd like to make more efficient in SQL.

我有一个比较,我想在SQL中提高效率。

The input field (fldInputField) is a comma separated list of "1,3,4,5"

输入字段(fldInputField)是逗号分隔的“1,3,4,5”列表

The database has a field (fldRoleList) which contains "1,2,3,4,5,6,7,8"

数据库有一个字段(fldRoleList),其中包含“1,2,3,4,5,6,7,8”

So, for the first occurrence of fldInputField within fldRoleList, tell us which value it was.

因此,对于fldRoleList中第一次出现fldInputField,请告诉我们它是哪个值。

Is there a way to achieve the following in MySQL or a Stored Procedure?

有没有办法在MySQL或存储过程中实现以下功能?

pseudo-code

SELECT * FROM aTable t1 WHERE fldInputField in t1.fldRoleList

SELECT * FROM aTable t1 WH1 fldInputField in t1.fldRoleList

/pseudo-code

I'm guessing there might be some functions that are best suited for this type of comparison? I couldn't find anything in the search, if someone could direct me I'll delete the question... Thanks!

我猜可能有一些最适合这种比较的函数?我在搜索中找不到任何东西,如果有人可以指示我,我会删除这个问题...谢谢!

UPDATE: This isn't the ideal (or good) way to do things. It's inherited code and we are simply trying to put in a quick fix while we look at building in the logic to deal with this via normalized rows.. Luckily this isn't heavily used code.

更新:这不是理想(或好)做事的方式。它是继承的代码,我们只是试图快速修复,同时我们在逻辑中构建通过规范化的行来处理这个问题。幸运的是,这不是大量使用的代码。

2 个解决方案

#1

I agree with @Ken White's answer that comma-delimited lists have no place in a normalized database design.

我同意@Ken White的回答,即逗号分隔的列表在规范化数据库设计中没有位置。

The solution would be simpler and perform better if you stored the fldRoleList as multiple rows in a dependent table:

如果将fldRoleList存储为依赖表中的多行,则解决方案会更简单并且性能更好:

SELECT t1.*, r1.fldRole 
FROM aTable t1 JOIN aTableRoles r1 USING (aTable_id) 
WHERE FIND_IN_SET(r1.fldRole, fldInputField);

(see the MySQL function FIND_IN_SET())

(参见MySQL函数FIND_IN_SET())

But that outputs multiple rows if multiple roles match the comma-separated input string. If you need to restrict the result to one row per aTable entry, with the first matching role:

但是,如果多个角色与逗号分隔的输入字符串匹配,则会输出多行。如果需要将每个aTable条目的结果限制为一行,则使用第一个匹配的角色:

SELECT t1.*, MIN(r1.fldRole) AS First_fldRole 
FROM aTable t1 JOIN aTableRoles r1 USING (aTable_id) 
WHERE FIND_IN_SET(r1.fldRole, fldInputField);
GROUP BY t1.aTable_id;

#2

You have a terrible schema design, you know. Comma-delimited lists have no business in a DB.

你知道,你有一个糟糕的架构设计。以逗号分隔的列表在数据库中没有业务。

That being said... You're looking for LIKE.

那就是说......你在寻找喜欢。

SELECT * FROM aTable t1 WHERE t.fldRoleList LIKE fldInputField + '%'

If the content might not always match at the beginning, add another percent sign before fldInputField.

如果内容在开头可能并不总是匹配,请在fldInputField之前添加另一个百分号。

SELECT * FROM aTable t1 WHERE t.fldRoleList LIKE '%' + fldInputField + '%'

#1