如何管理SQL查询

目前,我的代码(PHP)中包含太多SQL查询.例如...

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

我正在研究使用存储过程来减少这种情况并使事情变得更加强大,但我有一些担忧.

我在网站上使用了数百种不同的查询,其中很多都非常相似.当从上下文(使用结果的代码)中删除所有这些查询并将其置于数据库的存储过程中时,我应该如何管理它们？

您的最佳行动方案取决于您接近数据访问的方式.您可以采取三种方法:

使用存储过程

将查询保留在代码中(但将所有查询放入函数中并修复所有内容以使用PDO作为参数,如前所述)

使用ORM工具

如果你想将你自己的原始SQL传递给数据库引擎,那么如果你想要做的就是从你的PHP代码中获取原始SQL但保持相对不变,那么存储过程将会成为可能.存储过程与原始SQL辩论是一场神圣的战争,但K. Scott Allen在一篇关于版本化数据库的文章中提出了一个很好的观点 - 虽然它是一次性的:

其次,存储过程在我眼中已经失宠了.我来自WinDNA灌输学校,说应该一直使用存储过程.今天,我将存储过程视为数据库的API层.如果您需要在数据库级别使用API​​层,这很好,但我发现许多应用程序会产生创建和维护他们不需要的额外API层的开销.在那些应用程序中,存储过程更多的是负担而不是利益.

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

在数据库上拥有一个API层可以更好地描述数据库团队和开发团队之间的责任,但代价是如果查询保存在代码中,您将拥有一些灵活性,但是PHP项目不太可能具有相当大的规模.足够的团队从这个划分中受益.

从概念上讲,您可能应该对数据库进行版本控制.但是,实际上,您更有可能只使用版本化的代码,而不是将数据库版本化.当您对代码进行更改时,您可能会更改查询,但如果要更改存储在程序中的查询,那么在检查代码时您可能不会检查这些查询在应用程序的重要区域进行版本控制的许多好处.

无论您是否选择不使用存储过程,您至少应确保每个数据库操作都存储在一个独立的函数中,而不是嵌入到每个页面的脚本中 - 实质上是数据库的API层.使用您的代码进行维护和版本化.如果您正在使用存储过程,这实际上意味着您有两个用于数据库的API层,一个包含代码,另一个包含数据库,如果您的项目没有单独的团队,您可能会感到不必要的复杂化.我当然这样做.

如果问题是代码整洁,那么有一些方法可以使SQL中的代码更加干净,并且下面显示的UserManager类是一个很好的开始方式 - 该类只包含与'user'表相关的查询,每个查询在类中都有自己的方法,查询将缩进到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工具,您应该查看Propel,Yii的ActiveRecord组件,或者King-daddy 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);

你可能遇到一些问题.

编辑:这里有关存储过程的一些很好的讨论