MySQL 表连接、嵌套选择语句或创建视图？

Question

免责声明：我首先将其发布在另一个网站上，

我有一个大约 200 列宽的表 (res_table)。其中一列名为“feature_lk”，它由一串数字“|”组成。划定的。这些数字代表位于另一个名为“features”的表中的功能类别，

感谢以下线程： http://www.kirupa.com/forum/showthread.php?t=224203 我弄清楚了如何解析这些功能！

现在我的问题是如何查找它们？我觉得我要么需要加入我的两个表，但我不确定如何，或者我需要为我解析的每个功能执行另一个选择查询。这就是我必须要做的（删除了连接字符串）用于发布目的）

PHP Code:
<?php 
$sql = ("SELECT * FROM res_table"); 
$result = mysql_query($sql); 

while($row = mysql_fetch_array($result)) 
{ 
    $feature_string = $row['features_lk']; 
    $features = explode( '|', $feature_string ); 

    foreach( $features as $feature ) { 
        $feature = trim( $feature ); 
        echo $feature.': '; 

        $sql2 = "SELECT * from features where features.feature_id like $feature"; 
        $result2 = mysql_query($sql2); 
        while ($row2 = mysql_fetch_array($result2)) 
        { 
            $feat_desc = $row2['feature_description']; //this is another column in the features table 
            echo $feat_desc . '<br>'; 
        } 
    } 
    echo '<br>'; 
} 
?>

所以工作正常，因为当我运行它时，我会得到如下所示的结果：

13: None
62: Water Softener - Rented
71: Full
168: Barn
222: Storage Shed
226: Walkout
309: Detached
347: 2 Story
384: Attic Storage
439: Laundry Hook Up
466: Rural
476: Trees
512: School Bus
562: Mud Room
563: Pantry
2273: Septic Tank
643: Private Well

我的问题是：有没有更好的方法来做到这一点？主 res_table 中有大约 10k 行，只有几百次命中，您可以看到执行的 select 语句的数量立即变得很大。

我确信这是 PHP + MySQL 101 的东西，但我只是一个初学者，所以有什么想法吗？提前致谢。

Answer 1

当您在一列中存储多条信息时，您的表不会标准化。在 feature_lk 上进行查找必然会很慢而且很困难。 feature_lk 应该成为它自己的表：

Table feature_lk:

• res_table_id FK to res_table
• feature_id FK to feature table
• Primary Key(res_table_id,feature_id)

那么您的查询是：

SELECT f.* from features f 
  JOIN feature_lk lk ON (f.id=lk.feature_id) 
  JOIN res_table r ON (lk.res_table_id=r.id);

仅一个查询。没有循环。没有解析出特征。

ETA

用于按任意字符分割任意长度字符串的

DELIMITER $

DROP PROCEDURE IF EXISTS `dorepeat` $
CREATE PROCEDURE `dorepeat`(in ToBeSplit LONGTEXT , in Splitter CHAR)
Begin

DECLARE TotalLength INT;
DECLARE SplitterPosition INT;
DECLARE SubstringLength INT;
DECLARE SubstringStart INT;

DROP Table if exists Split_Values;
CREATE temporary TABLE Split_Values (split varchar(255));

SET TotalLength = LENGTH(ToBeSplit);
SET SplitterPosition = LOCATE(Splitter, ToBeSplit);
SET SubstringStart = 1;

ss: WHILE SplitterPosition < TotalLength DO
        IF SplitterPosition!=0 THEN 
                SET SubstringLength = SplitterPosition - SubstringStart;
                Insert into Split_Values VALUES (SUBSTRING(ToBeSplit,SubstringStart,SubstringLength));
                SET SubstringStart = SplitterPosition+1;
                SET SplitterPosition = LOCATE(Splitter, ToBeSplit, SplitterPosition+1);
        ELSE
                Insert into Split_Values VALUES (SUBSTRING(ToBeSplit,SubstringStart));
                SET SplitterPosition=TotalLength;
        END IF;
END WHILE ss;
End $

DELIMITER ;

存储过程在另一个过程中使用 dorepeat 生成带有 res_table_id 和每个功能的临时表：

DELIMITER $

DROP PROCEDURE IF EXISTS `multido` $
CREATE PROCEDURE `multido`()
Begin
DECLARE done INT default 0;
DECLARE rt_id INT (10);
DECLARE features LONGTEXT;
DECLARE mycur cursor for select distinct res_table_id, feature_lk from res_table WHERE feature_lk!='';
DECLARE continue handler for sqlstate '02000' set done=1;
drop table if exists tmpfeatures;
create temporary table tmpfeatures( res_table_id int(10),  feature varchar(255));
open mycur;
repeat
  fetch mycur into rt_id,features;
  call dorepeat(features,'|');
  insert into tmpfeatures select rt_id, trim(split) from Split_Values;
until done end repeat;
close mycur;

End $

DELIMITER ;

Answer 2

您在这里感受到了糟糕的数据库建模的痛苦。如果您对数据库模式有任何控制，那么您应该修复它，以便正确规范化。每当您在数据库中看到管道（或逗号、制表符或其他）分隔的列表时，您都应该对此非常怀疑。

您的表和类别之间应该有一个连接表，通常命名为 RES_CATEGORIES 之类的名称，其中包含 RES 中的 ID 和 CATEGORIES 中的 ID。这是在关系数据库中建模多对多关系的标准方法。

如果您无法控制架构，那么最好的选择就是在代码中解析该架构并执行单独的查询（或多个查询）以获取类别信息。您至少可以在 where 子句中指定多个类别 ID，以减轻一些痛苦。

Answer 3

根据我对你问题的理解，你需要一个中间表。例如，您有表 tbl_user 和 tbl_features，其中用户可以订阅多个功能，并且每个功能可以由多个用户订阅。

使用额外的表 tbl_userfeatures {userFeatureID, userID, featureID}，您的数据库将更易于管理，该表链接其他两个表并允许您添加不同的组合。

Answer 4

一个简单的优化步骤是一步获取特征，而不是循环遍历它们。像这样：

$result = mysql_query('SELECT * FROM res_table');
while ($row = mysql_fetch_array($result)) {
    $features = str_replace('|', ',', $features);
    $result2 = mysql_query("SELECT * FROM features WHERE feature_id IN $features");
    while ($row2 = mysql_fetch_array($result2) {
        printf('%d: %s', $row2['feature_id'], $row2['feature_description']);
    }
}

这是对 res_table 中的每一行进行一次查询，而不是对每个功能进行一次查询。

但在这样做之前，请先听听其他人的回应。如果您能够将数据库模式更改为更合理的内容，请这样做！