我们如何管理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
}
我正在研究使用存储过程来减少这种情况并使事情变得更健壮，但是我有一些担忧。
我在网站上使用了数百种不同的查询，其中许多非常相似。将所有这些查询从上下文(使用结果的代码)中删除并放置在数据库的存储过程中时，应如何管理所有这些查询？
解决方案
回答
使用ORM软件包，任何一半不错的软件包都可以让我们
- 获取简单的结果集
- 使复杂的SQL靠近数据模型
如果我们有非常复杂的SQL，那么视图也很适合使它更适合于应用程序的不同层。
回答
我们一次处于类似的困境。我们以超过50种以上的多种方式查询了特定的表格。
最后，我们要做的是创建一个Fetch存储过程，其中包含WhereClause的参数值。 WhereClause是在Provider对象中构造的，我们采用了Facade设计模式，可以在其中对任何SQL注入攻击进行清理。
因此，就维护而言，它很容易修改。 SQL Server也是相当不错的选择，它可以缓存动态查询的执行计划，因此总体性能相当不错。
我们必须根据自己的系统和需求来确定性能缺陷，但是所有这一切对我们来说都很有效。
回答
这个其他问题也有一些有用的链接...
回答
首先，我们应该在查询中使用占位符，而不是直接对变量进行插值。 PDO / MySQLi允许我们编写查询，例如：
SELECT * FROM sometable WHERE iUser = ?
API将安全地将值替换为查询。
我也更喜欢在代码而不是数据库中查询。当查询与代码一起使用时，使用RCS会容易得多。
在使用ORM时，我有一个经验法则：如果一次使用一个实体，我将使用该界面。如果我要汇总报告/使用记录，则通常会编写SQL查询来实现。这意味着我的代码中几乎没有查询。
回答
有一些库(例如PEAR中的MDB2)使查询更容易和更安全。
不幸的是，设置它们可能有些麻烦，有时我们必须将相同的信息传递两次。我在几个项目中使用了MDB2，并且倾向于在其周围编写一个薄的贴面，特别是用于指定字段类型。通常，我创建一个知道特定表及其列的对象，然后当我调用MDB2查询函数时，该函数中的帮助器函数将为我填充字段类型。
例如：
function MakeTableTypes($TableName, $FieldNames)
{
$Types = array();
foreach ($FieldNames as $FieldName => $FieldValue)
{
$Types[] = $this->Tables[$TableName]['schema'][$FieldName]['type'];
}
return $Types;
}
显然，该对象具有它所了解的表名->模式的映射，并且仅提取我们指定的字段的类型，并返回适合用于MDB2查询的匹配类型数组。
然后，MDB2(和类似的库)为我们处理参数替换，因此对于更新/插入查询，我们只需从列名到值构建一个哈希/映射，然后使用" autoExecute"函数来构建和执行相关查询。
例如：
function UpdateArticle($Article)
{
$Types = $this->MakeTableTypes($table_name, $Article);
$res = $this->MDB2->extended->autoExecute($table_name,
$Article,
MDB2_AUTOQUERY_UPDATE,
'id = '.$this->MDB2->quote($Article['id'], 'integer'),
$Types);
}
MDB2将构建查询，正确转义所有内容，等等。
我还是建议我们使用MDB2来衡量性能，因为它会引入很多代码，如果我们没有运行PHP加速器，这可能会给我们带来麻烦。
就像我说的那样，设置开销起初似乎令人生畏，但是一旦完成，查询就可以更简单/更具象征性地编写和(尤其是)修改。我认为MDB2应该对模式有更多了解，这将简化一些常用的API调用，但是我们可以通过自己封装模式(如上所述)并提供简单的访问器函数来生成该模式，从而减轻这种烦恼阵列MDB2需要执行这些查询。
当然，如果需要，我们也可以使用query()函数以字符串的形式执行扁平SQL查询，因此我们不必强制切换到完整的" MDB2方式"，可以零碎地尝试一下，看看是否讨厌还是不。
回答
我不得不清理一个项目，其中有许多(重复/类似的)查询，这些查询充满了注入漏洞。
我采取的第一步是使用占位符，并使用创建查询的对象/方法和源代码对每个查询进行标记。
(将PHP常量方法和LINE插入到SQL注释行中)
它看起来像这样：
-- @Line:151 UserClass::getuser(): SELECT * FROM USERS;
在短时间内记录所有查询为我提供了一些合并查询的起点。 (在哪里！)
回答
我尝试使用相当通用的功能，只是传递它们之间的差异。这样，我们只有一个功能可以处理大多数数据库SELECT的功能。显然，我们可以创建另一个函数来处理所有的INSERTS。
例如。
function getFromDB($table, $wherefield=null, $whereval=null, $orderby=null) {
if($wherefield != null) {
$q = "SELECT * FROM $table WHERE $wherefield = '$whereval'";
} else {
$q = "SELECT * FROM $table";
}
if($orderby != null) {
$q .= " ORDER BY ".$orderby;
}
$result = mysql_query($q)) or die("ERROR: ".mysql_error());
while($row = mysql_fetch_assoc($result)) {
$records[] = $row;
}
return $records;
}
这只是我的头上的问题，但是我们明白了。要使用它，只需向函数传递必要的参数：
例如。
$blogposts = getFromDB('myblog', 'author', 'Lewis', 'date DESC');
在这种情况下，$ blogposts将是代表表的每一行的数组的数组。然后，我们可以只使用一个foreach或者直接引用该数组：
echo $blogposts[0]['title'];
回答
最佳的操作方法取决于我们如何进行数据访问。我们可以采用三种方法：
- 使用存储过程
- 将查询保留在代码中(但是将所有查询放入函数中，并修复所有问题，以使用PDO作为参数，如前所述)
- 使用ORM工具
如果我们想将自己的原始SQL传递给数据库引擎，那么，如果我们要做的就是从PHP代码中获取原始SQL，但保持相对不变，那么存储过程将是解决之道。存储过程与原始SQL的争论有点像是一场圣战，但是K.Scott Allen提出了一个很好的观点，尽管在有关版本化数据库的文章中抛弃了这一观点：
Secondly, stored procedures have fallen out of favor in my eyes. I came from the WinDNA school of indoctrination that said stored procedures should be used all the time. Today, I see stored procedures as an API layer for the database. This is good if you need an API layer at the database level, but I see lots of applications incurring the overhead of creating and maintaining an extra API layer they don't need. In those applications stored procedures are more of a burden than a benefit.
我倾向于不使用存储过程。我曾参与过一些项目，这些项目的数据库具有通过存储过程公开的API，但是存储过程可能会施加一些自身的限制，并且这些项目在不同程度上都使用了动态生成的原始SQL代码来访问数据库。
在数据库上具有API层可以更好地划分数据库团队和开发团队之间的职责，但前提是要保留一些保留在代码中的查询灵活性，但是PHP项目的规模较小足够的团队可以从此划定中受益。
从概念上讲，我们可能应该对数据库进行版本控制。但是，实际上，与对数据库进行版本化相比，仅对代码进行版本化的可能性要高得多。在更改代码时，我们可能会更改查询，但是如果在针对数据库存储的存储过程中更改查询，则在签入代码并丢失时可能不会签入查询。在应用程序的重要区域中进行版本控制的许多好处。
无论我们是否选择不使用存储过程，都至少应确保每个数据库操作都存储在一个独立的函数中，而不是嵌入到每个页面脚本中，本质上是数据库的API层，即使用代码进行维护和版本控制。如果使用存储过程，这实际上将意味着数据库有两个API层，一个是代码层，一个是数据库层，如果项目没有单独的团队，我们可能会感到不必要的复杂化。当然可以
如果问题是代码整洁的问题之一，则有多种方法可以使带有SQL的代码更易于呈现，并且以下所示的UserManager类是启动该类的好方法，该类仅包含与'user'表相关的查询，每个查询query在类中有其自己的方法，查询缩进到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);
我们可能会遇到一些问题。
编辑：这里对存储过程的一些很好的讨论
回答
使用像QCodo这样的ORM框架，我们可以轻松地映射现有数据库
回答
我将所有SQL移至单独的Perl模块(.pm)。许多查询可以重用相同的函数，但参数略有不同。
对于开发人员来说，一个常见的错误是进入ORM库，参数化查询和存储过程。然后，我们连续工作了几个月，以使代码"更好"，但是以开发方式来说，这仅仅是"更好"。我们没有任何新功能！
在代码中使用复杂性仅是为了满足客户需求。
