您如何管理SQL查询

Question

目前，我的代码（PHP）中包含太多SQL查询。例如…

// not a real example, but you get the idea...
$results = $db->GetResults("SELECT * FROM sometable WHERE iUser=$userid");
if ($results) {
    // Do something
}

我正在研究使用存储过程来减少这种情况并使事情变得更健壮，但是我有一些担忧。

我在网站上使用了数百种不同的查询，其中许多非常相似。将所有这些查询从上下文（使用结果的代码）中删除并放在数据库的存储过程中时，我应该如何管理这些查询？

Answer 1

最佳的操作方法取决于您如何进行数据访问。您可以采用三种方法：

• 使用存储过程
• 将查询保留在代码中（但是将所有查询放入函数中，并修复所有问题，以使用PDO作为参数，如前所述）
• 使用ORM工具

如果您想将自己的原始SQL传递给数据库引擎，那么，如果您要做的就是从PHP代码中获取原始SQL，但保持相对不变，那么存储过程将是您的最佳选择。存储过程与原始SQL的争论有点激烈，但是K.Scott
Allen在一篇有关版本控制数据库的文章中指出了一个很好的观点-
尽管它是一次性的：

其次，存储过程在我眼中不受欢迎。我来自WinDNA灌输学校，我说应该一直使用存储过程。今天，我将存储过程视为数据库的API层。如果您需要数据库级别的API层，那么这很好，但是我看到许多应用程序会产生创建和维护它们不需要的额外API层的开销。在那些应用程序中，存储过程更多的是负担，而不是好处。

我倾向于不使用存储过程。我在哪里的DB具有通过存储过程暴露的API项目的工作，但存储程序可以征收自身的一些限制，这些项目有 一切
，在不同程度上使用动态生成的原始SQL代码来访问数据库。

在数据库上具有API层可以更好地划分数据库团队和开发团队之间的职责，但前提是要保留一些保留在代码中的查询灵活性，但是PHP项目不太可能具有相当大的规模足够的团队可以从此划定中受益。

从概念上讲，您可能应该对数据库进行版本控制。但是，实际上，与对数据库进行版本化相比，仅对代码进行版本化的可能性要大得多。在更改代码时，您可能会更改查询，但是如果在针对数据库存储的存储过程中更改查询，则当您检入代码并丢失时，您可能不会检入这些查询。在您的应用程序的重要区域中进行版本控制的许多好处。

无论您是否选择不使用存储过程，都至少应确保每个数据库操作都存储在一个独立的函数中，而不是嵌入到每个页面的脚本中-
本质上是数据库的API层，与您的代码一起维护和版本控制。如果使用存储过程，这将有效地意味着您的数据库有两个API层，一个是代码层，一个是数据库层，如果您的项目没有单独的团队，您可能会感到不必要的复杂化。当然可以

如果问题是代码整洁的问题之一，则可以采用多种方法使其中包含SQL的代码更易于呈现，并且下面显示的UserManager类是一个很好的开始方式-
该类仅包含与“用户”表相关的查询，每个查询在类中都有其自己的方法，查询缩进到prepare语句中并按照在存储过程中对其进行格式化的方式进行格式化。

// UserManager.php:

class UserManager
{
    function getUsers()
    {
        $pdo = new PDO(...);
        $stmt = $pdo->prepare('
            SELECT       u.userId as id,
                         u.userName,
                         g.groupId,
                         g.groupName
            FROM         user u
            INNER JOIN   group g
            ON           u.groupId = g.groupId
            ORDER BY     u.userName, g.groupName
        ');
        // iterate over result and prepare return value
    }

    function getUser($id) {
        // db code here
    }
}

// index.php:
require_once("UserManager.php");
$um = new UserManager;
$users = $um->getUsers();
foreach ($users as $user) echo $user['name'];

但是，如果查询非常相似，但是查询条件中存在大量置换，例如复杂的分页，排序，过滤等，那么尽管对现有代码进行了大修，但对象/关系映射器工具可能是解决之道使用该工具可能会非常复杂。

如果决定研究ORM工具，则应查看Proi（Yii的ActiveRecord组件）或父级PHP
ORM（Doctrine）。这些中的每一个都使您能够以各种复杂逻辑的方式以编程方式构建对数据库的查询。Doctrine是功能最全的功能，可让您使用开箱即用的“嵌套集”树模式之类的模板来对数据库进行模板化。

就性能而言，存储过程是最快的，但是通常不超过原始sql。ORM工具可以通过多种方式对性能产生重大影响-
低效或冗余查询，在每个请求上加载ORM库时的巨大文件IO，每个查询的动态SQL生成…所有这些都会产生影响，但是与使用手动查询创建自己的数据库层相比，使用ORM工具可以大大减少代码量，从而为您提供强大的功能。

不过，加里·理查森（Gary
Richardson）绝对正确，如果您要继续在代码中使用SQL，则无论您使用查询还是存储过程，都应该始终使用PDO的预处理语句来处理参数。输入清理由PDO替您完成。

// optional
$attrs = array(PDO::ATTR_PERSISTENT => true);

// create the PDO object
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass", $attrs);

// also optional, but it makes PDO raise exceptions instead of 
// PHP errors which are far more useful for debugging
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO venue(venueName, regionId) VALUES(:venueName, :regionId)');
$stmt->bindValue(":venueName", "test");
$stmt->bindValue(":regionId", 1);

$stmt->execute();

$lastInsertId = $pdo->lastInsertId();
var_dump($lastInsertId);

注意：假设ID为1，则将输出上述脚本string(1) "1"。PDO->lastInsertId()不管实际列是否为整数，都以字符串形式返回ID。对您来说，这可能永远不会成为问题，因为PHP会自动将字符串转换为整数。

将输出以下内容bool(true)：

// regular equality test
var_dump($lastInsertId == 1);

但是，如果您有期望该值是整数的代码，例如is_int或PHP的“确实是，实际上是100％等于”运算符：

var_dump(is_int($lastInsertId));
var_dump($lastInsertId === 1);

您可能会遇到一些问题。

编辑： 这里对存储过程的一些很好的讨论