MySQL通过两列查找多个记录 - MySQL find multiple records by two columns

MySQL find multiple records by two columns

本文关键字：查找两列记录 MySQL | 更新日期: 2023-09-26

假设我有一个如下的数组(ID => Type):

$contentIndexes = [
    32 => 'news', 
    40 => 'blog', 
    68 => 'blog', 
    109 => 'document', 
    124 => 'news'
]

和下面的数据库表:

CREATE TABLE `ContentIndex` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `ItemID` INT(11) NOT NULL,
    `ItemType` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    //...
);

我如何根据"ItemID"answers"ItemType"列的组合检索每个ContentIndex(最好只有一个查询)。

使用WHERE IN不是一个选择，因为它不会考虑组合:

ContentIndexQuery::create()
    ->filterByItemID(array_keys($contentIndexes))
    ->filterByItemType($contentIndexes)
    ->find();

任何想法?

我不知道Propel语法，但基本的SQL语法将与OR。

WHERE ((ItemID = 32 AND ItemType = 'news')
        OR
       (ItemID = 40 AND ItemType = 'blog')
        OR
       (ItemID = 68 AND ItemType = 'blog')
        OR
       (ItemID = 109 AND ItemType = 'document')
        OR
       (ItemID = 124 AND ItemType = 'news')
      )

对于将来遇到此问题的任何Propel用户，我想创建一个查询，如Barmar所述:

$items = ContentIndexQuery::create();
$i = 0;
foreach ($contentIndexes as $id = > $type) {
    if ($i > 0) $items->_or();
    $items->condition('cond1'.$i, ContentIndexTableMap::COL_ITEM_ID . ' = ?', $id)
          ->condition('cond2'.$i, ContentIndexTableMap::COL_ITEM_TYPE . ' = ?', $type)
          ->where(['cond1'.$i, 'cond2'.$i], 'AND');
    $i += 1;
}
$items = $items->find()->getData();