在MySql中循环，还是替代? [英] Loop in MySql, or alternative?

问题描述

我有一个带有innoDB表的MySql数据库.非常简单，这是布置两个表的方式:

表A:

• controlID(PK)
• controlText

表B:

• controlOptionID(pk)
• controlID(从FK到表A)
• controlOptionType
• controlOptionValue

这么多的controlOptions(表B)可以引用一个控件(给定多个控件的控制权).但是，对于每个选项，表B中都有两行:一行带有controlOptionType ="linkToCreator"和controlOptionValue =(它是由其制成的模板的ID *).而另一行类型="optionSelected"，值="true"(或false).

• =这是一个非常复杂的设置，但基本上不是设置列，而是通过类型(该列将被调用的类型)来创建动态列.所以我无法使用FK链接到模板.

所以现在我需要选择每个控件(将有2个controlOptions链接到它)，其中一个controlOptionValue值为true或false(取决于我的需要)，另一个controlOptionValue是我指定的文本ID. /p>

我认为最好的方法是

SELECT * FROM tableB WHERE controlOptionType = 'linkToCreator'

然后遍历该结果集并说:

SELECT * FROM tableB WHERE tableB.controlID = (the controlID in this iterations row) AND tableB.controlValue = 'true'

但是也许那真的没效率，而且无论哪种方式我都不知道该怎么做.如果我能得到一个我指定templateID为true或false的查询(即不使用存储过程)，那会很好，如果找不到任何内容，它会给我一个行结果.

顺便说一句，这是在我们的应用程序中进行的搜索，需要遍历TONS行，因此性能至关重要.是的，我知道设置不是最好的...

谢谢:D

解决方案

喜欢吗?

SELECT * FROM tableA AS A
LEFT JOIN tableB AS ctrl1 ON (A.controlID = ctrl1.controlID AND ctrl1.controlOptionType = ? AND ctrl1.controlOptionValue = ?)
LEFT JOIN tableB AS ctrl2 ON (A.controlID = ctrl2.controlID AND ctrl2.controlOptionType = ? AND ctrl2.controlOptionValue = ?)

I have a MySql db with innoDB tables. Very simplified this is how two tables are layed out:

Table A:

• controlID(PK)
• controlText

Table B:

• controlOptionID(pk)
• controlID(FK to table A)
• controlOptionType
• controlOptionValue

So many controlOptions(table B) can reference one control(giving that control multiple options). But for each option two rows are made in table B: one row with controlOptionType = "linkToCreator" and controlOptionValue = (an ID to the template it was made from*). And the other row type = "optionSelected" and value = "true"(or false).

• = its a pretty complicated setup, but basically instead of set columns we are making dynamic ones by means of the type being what the column would have been called. So I couldnt link to the template with FK.

So now I need to select every control(which will have 2 controlOptions linking to it) where the one controlOptionValue value is true or false(depending on what i need) and the other controlOptionValue is an text ID that I specify.

What I think is the best way to do it is a

SELECT * FROM tableB WHERE controlOptionType = 'linkToCreator'

Then do a loop over that result set saying:

SELECT * FROM tableB WHERE tableB.controlID = (the controlID in this iterations row) AND tableB.controlValue = 'true'

But maybe thatls really inefficient, and either way I have no clue how to do that. It would be great if I could get a single query(i.e. not using stored procedures) that I specified templateID and true or false and it gave me a row result if it didn't find anything.

BTW this is for a search in our application with will need to go through TONS of rows so performance is paramount. And yes, I know the setup isnt the greatest...

Thanks :D

解决方案

Like this?

SELECT * FROM tableA AS A
LEFT JOIN tableB AS ctrl1 ON (A.controlID = ctrl1.controlID AND ctrl1.controlOptionType = ? AND ctrl1.controlOptionValue = ?)
LEFT JOIN tableB AS ctrl2 ON (A.controlID = ctrl2.controlID AND ctrl2.controlOptionType = ? AND ctrl2.controlOptionValue = ?)

这篇关于在MySql中循环，还是替代?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！