获取多个列,同时减少 SQL 负载


Fetch multiple columns while reducing SQL load

如果这是一个微不足道的问题,我提前道歉,但我确实检查了 PHP 手册,我似乎只能找到获取多行的位(我没有问题)。请考虑以下代码:

$fetch = $db->prepare("
    SELECT col1, col2
        FROM table
        WHERE col0 = '1'
");
$fetch->execute();
//SQL request made, returns 1 row, 2 columns
echo $fetch->fetchColumn(); //perfect
echo $fetch->fetchColumn(1); //doesn't work
//only one column can be fetched per SQL request

根据我对PDO的有限理解,我需要做如下事情:

$fetch->execute(); //execute 1st time
echo $fetch->fetchColumn(); //perfect
$fetch->execute(); //execute 2nd time
echo $fetch->fetchColumn(1); //perfect
//or
$fetch->execute(); //execute only once
foreach($fetch as $fetcher) {} //ugly
echo $fetcher["col1"]; //perfect
echo $fetcher["col2"]; //perfect

第一种方法很慢,但第二种方法缺乏技巧。我知道我可以将逻辑放在 foreach() 循环中,但除了看起来更干净之外,它做同样的事情,因为请求只返回一行(foreach()只会迭代一次)。

如果只是为了提高效率,我想尽可能减少SQL负载。实际处理差异是千分之一秒长。但我也希望代码干净简洁。

如果你想提高SQL流量的效率,那么只需执行并获取一次:

$fetch->execute(); //execute only once
$row = $fetch->fetch(PDO::FETCH_ASSOC);
echo $row["col1"]; //perfect
echo $row["col2"]; //perfect