php - MySQL表结构最佳实践多对多

Question

您好，我有 39 个 8,000 到 1,500,000 行的大表来对 MySQL4 中的数据进行分区。每个表代表一个始发地机场代码，其中的数据是来自每个始发地的套餐假期交易(140 列)。

我正在为酒店名称创建一个自动建议字段，但我想将其基于包裹持续时间、包裹类型和来源。

我有另一个表，其中只有 3000 行所有酒店，所以我想将此表用于自动建议功能，因为它的大小和速度都很小。

每家酒店都有很多出发地、套餐类型和套餐时长。

我的问题是我应该如何使用所有来源填充此酒店表。到目前为止，我唯一的想法是为每种包裹类型、包裹持续时间和机场代码制作一个分隔列表。

示例记录:

hotel_name = Some Hotel
origin = YYC;YEG;YVR
duration = 04;05;06;07;09;10;14
package_Type = 03;04;09

然后使用:

SELECT `hotel_name` from `hotels`
WHERE `hotel_name` LIKE '%$typed_text%' 
AND  `package_duration` LIKE '%09%' 
AND  `package_type` LIKE '%04%' 
AND  `origin` LIKE '%YEG%' 
ORDER BY `hotel_name`

这是解决此类查询的最佳方式吗？我还应该做些什么吗？谢谢

---

编辑:我尝试了几个解决方案，这个适合我的数据结构，并在 0.001 秒内查询了 3000 家酒店。

这绝不是推荐的方法，因为它几乎打破了数据库查询中的每条规则，但无论如何它都在这里

这是一个示例转储:

CREATE TABLE IF NOT EXISTS `hotels` (
  `id` int(9) NOT NULL auto_increment,
  `hotel_name` varchar(255) NOT NULL default '',
  `origins` varchar(255) NOT NULL default '',
  `package_types` varchar(255) NOT NULL default '',
  `package_durations` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `hotels`
--

INSERT INTO `hotels` (`id`, `hotel_name`, `origins`, `package_types`, `package_durations`) VALUES
(1, 'Bel Air Collection Resort & Spa Vallarta', 'YYC;YXX;YQQ;YEG;YLW;YUL;YQR;YYZ;YVR;YYJ;YWG;', '09;', '06;07;08;13;14;15;'),
(2, 'Grand Velas All Suites & Spa Resort, Riviera Maya', 'YYC;YXX;YQQ;YEG;YHM;YXU;YUL;YOW;YQB;YQR;YXE;YYZ;', '09;', '03;04;09;10;11;06;07;08;13;14;15;'),
(3, 'Barcelo Capella Beach Resort', 'YYC;YEG;YHZ;YUL;YOW;YYZ;YVR;YWG;', '09;', '06;07;08;13;14;15;'),
(4, 'Iberostar Cozumel', 'YYC;YBG;YYG;YEG;YFC;YHZ;YXU;YQM;YUL;YOW;YQB;', '09;', '06;07;08;13;14;15;'),
(5, 'Valentin Imperial Maya', 'YYC;YBG;YXX;YQQ;YEG;YHZ;YHM;YXU;YUL;', '09;', '06;07;08;03;04;09;10;11;13;14;15;');

还有一个查询表的示例脚本:

$data[n] = 'resort';  //auto suggest text
$data[t] = '9';       //package_type
$data[d] = '7;8;10';  //durations
$data[o] = 'YUL;YVR'; //origins

//Split origins
foreach(explode(';',$data[o]) as $ori)
{
    $origin_sql[] = "`origins` LIKE '%".str_pad(mysql_real_escape_string($ori), 2, "0", STR_PAD_LEFT).";%'";
}
$data[o] = "(".implode(' OR ',$origin_sql).")";

//Split durations 
foreach(explode(';',$data[d]) as $dur)
{
    $duration_sql[] = "`package_durations` LIKE '%".str_pad(mysql_real_escape_string($dur), 2, "0", STR_PAD_LEFT).";%'";
}
$data[d] = "(".implode(' OR ',$duration_sql).")";

if($data[n]!=''&&$data[o]!=''&&$data[t]!=''&&$data[d]!='')
{
    $n = str_pad(mysql_real_escape_string($data[n]), 2, "0", STR_PAD_LEFT);
    $t = str_pad(mysql_real_escape_string($data[t]), 2, "0", STR_PAD_LEFT);

    $s = "SELECT `hotel_name` FROM `backend_hotels` WHERE
    `hotel_name` LIKE '%".$n."%' AND 
    `package_types` LIKE '%".$t.";%' AND 
    ".$data[o]." AND
    ".$data[d]."
     ORDER BY `hotel_name` ASC;";
    if($q = mysql_query($s))
    {
        while($r=mysql_fetch_array($q,MYSQL_ASSOC))
        {
            $names[] = $r[hotel_name];
        }
        echo json_encode($names);
    }
}

生成如下查询:

SELECT `hotel_name`
FROM `hotels`
WHERE `hotel_name` LIKE '%resort%'
AND `package_types` LIKE '%09;%'
AND (
  `origins` LIKE '%YUL;%'
   OR `origins` LIKE '%YVR;%'
)
AND (
  `package_durations` LIKE '%07;%'
  OR `package_durations` LIKE '%08;%'
  OR `package_durations` LIKE '%10;%'
)
ORDER BY `hotel_name` ASC
LIMIT 0 , 30 

就像我提到的那样，由于 LIKE 查询的速度很慢，这种方法根本不适用于大型数据集。这恰好更容易实现，有 1100 万个套餐交易和 3000 家酒店，我只是想展示我最终实现的内容，以防它对其他人有所帮助

Answer 1

我通常会做的是将关系分成几部分:

Each hotel has many origins, package types and package durations.

所以你有:

• 酒店
• 起源
• 包裹类型
• 套餐时长

现在，如果您想到的话，酒店 是这里的主要主题，因此它应该是要标记的项目(想象一下 facebook)

现在，您如何“标记”？让我们来看看起源。每个酒店可以有很多产地，产地可能有很多酒店。你现在要做的是制作一张“联结表”。我会尝试在这里绘制它:

    Hotels      Hotels_Origin    Origin
1. Marriott          1-1       1. US
2. Waterfront        1-2       2. UAE
                     2-1

您会看到“标签”不过是基于联结的“关系”。在此示例中，Marriott 在两个位置都被标记，Waterfront 被标记为 US。要获得此查询，您必须使用“LEFT JOIN”

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). (W3schools) - it's just an explanation.

左连接确保我们根据参数的左侧得到我们想要的:

SELECT hotels.name, origin.name FROM hotels
LEFT JOIN hotels_origin ON hotels.id = hotels_origin.id  //match the hotel to junction
LEFT JOIN hotels_origin ON hotels_origin.id = origin.id  //match junction to origins
WHERE hotel.id=1                                        //get hoted by id

以下返回 ID 为 1 (Marriott) 的酒店:

hotels.name    origin.name
Marriott       US
Marriott       UAE

话虽如此，您对所有其他表执行相同的操作。

这种方法的优点是

• 表格中不会有重复项。您可以将一个来源标记到许多酒店，只需修改联结表关系。

• 这种方法更具可扩展性和可维护性

• 更容易查找，尤其是对于自动建议(因为没有重复项)

• 您不修改记录，只修改关系。

  现在的问题是..你需要改造数据库。