您如何管理SQL查询 [英] How do you manage SQL Queries
问题描述
目前，我的代码(PHP)中包含太多SQL查询.例如...
At the moment my code (PHP) has too many SQL queries in it. eg...
// not a real example, but you get the idea...
$results = $db->GetResults("SELECT * FROM sometable WHERE iUser=$userid");
if ($results) {
// Do something
}
我正在考虑使用存储过程来减少这种情况并使事情变得更健壮，但我有一些担忧.
I am looking into using stored procedures to reduce this and make things a little more robust, but I have some concerns..
我在网站上使用了数百种不同的查询，其中许多非常相似.将所有这些查询从上下文(使用结果的代码)中删除并放置在数据库中的存储过程中后，我应该如何管理这些查询?
I have hundreds of different queries in use around the web site, and many of them are quite similar. How should I manage all these queries when they are removed from their context (the code that uses the results) and placed in a stored procedure on the database?
推荐答案
对您而言最佳的行动方案将取决于您如何进行数据访问.您可以采用三种方法:
The best course of action for you will depend on how you are approaching your data access. There are three approaches you can take:
- 使用存储过程
- 保留查询中的代码(但如前所述，将所有查询放入函数中并修复所有问题以使用PDO作为参数)
- 使用ORM工具
如果您想将自己的原始SQL传递给数据库引擎，那么，如果您要做的就是从PHP代码中获取原始SQL，但保持相对不变，那么存储过程将是解决之道.存储过程与原始SQL的争论有点激烈，但是K. Scott Allen在一篇有关
If you want to pass your own raw SQL to the database engine then stored procedures would be the way to go if all you want to do is get the raw SQL out of your PHP code but keep it relatively unchanged. The stored procedures vs raw SQL debate is a bit of a holy war, but K. Scott Allen makes an excellent point - albeit a throwaway one - in an article about versioning databases:
第二，在我看来，存储过程已不再受欢迎.我来自WinDNA灌输学校，我说应该一直使用存储过程.今天，我将存储过程视为数据库的API层.如果您需要数据库级别的API层，那么这很好，但是我看到许多应用程序会产生创建和维护它们不需要的额外API层的开销.在那些应用程序中，存储过程带来的负担多于收益.
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，但是存储过程可能会施加一些自身的限制，并且这些项目在不同程度上具有 all ，并使用动态生成的原始SQL在代码中访问数据库.
I tend to lean towards not using stored procedures. I've worked on projects where the DB has an API exposed through stored procedures, but stored procedures can impose some limitations of their own, and those projects have all, to varying degrees, used dynamically generated raw SQL in code to access the DB.
在数据库上具有API层可以更好地描述数据库团队和开发团队之间的职责，但前提是要保留一些查询(如果将查询保留在代码中)的灵活性，但是PHP项目的可能性较小拥有足够多的团队来从这种划定中受益.
Having an API layer on the DB gives better delineation of responsibilities between the DB team and the Dev team at the expense of some of the flexibility you'd have if the query was kept in the code, however PHP projects are less likely to have sizable enough teams to benefit from this delineation.
从概念上讲，您可能应该对数据库进行版本控制.但是，实际上，与对数据库进行版本化相比，仅对代码进行版本化的可能性要高得多.在更改代码时，您可能会更改查询，但是如果在针对数据库存储的存储过程中更改查询，则当您检入代码并丢失时，您可能不会检入这些查询.在您的应用程序的重要区域中进行版本控制的许多好处.
Conceptually, you should probably have your database versioned. Practically speaking, however, you're far more likely to have just your code versioned than you are to have your database versioned. You are likely to be changing your queries when you are making changes to your code, but if you are changing the queries in stored procedures stored against the database then you probably won't be checking those in when you check the code in and you lose many of the benefits of versioning for a significant area of your application.
无论您是否选择不使用存储过程，您都至少应确保每个数据库操作都存储在一个独立的函数中，而不是嵌入到每个页面的脚本中-本质上是一个API层您的数据库，该数据库随代码一起维护和版本控制.如果使用存储过程，这将有效地意味着您的数据库有两个API层，一个是代码层，一个是数据库层，如果您的项目没有单独的团队，您可能会感到不必要的复杂化.我当然知道.
Regardless of whether or not you elect not to use stored procedures though, you should at the very least ensure that each database operation is stored in an independent function rather than being embedded into each of your page's scripts - essentially an API layer for your DB which is maintained and versioned with your code. If you're using stored procedures, this will effectively mean you have two API layers for your DB, one with the code and one with the DB, which you may feel unnecessarily complicates things if your project does not have separate teams. I certainly do.
如果问题是代码整洁的问题之一，则可以采用多种方法使其中夹有SQL的代码更具表现力，下面显示的UserManager类是一个很好的开始方式-该类仅包含与'user相关的查询'表中，每个查询在类中都有自己的方法，查询缩进到prepare语句中并按照在存储过程中设置格式的方式进行格式化.
If the issue is one of code neatness, there are ways to make code with SQL jammed in it more presentable, and the UserManager class shown below is a good way to start - the class only contains queries which relate to the 'user' table, each query has its own method in the class and the queries are indented into the prepare statements and formatted as you would format them in a stored procedure.
// 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'];
但是，如果您的查询非常相似，但是在查询条件(例如复杂的分页，排序，过滤等)中有大量的排列，尽管进行了大修，但对象/关系映射器工具可能是解决之道您现有的使用该工具的代码可能会非常复杂.
However, if your queries are quite similar but you have huge numbers of permutations in your query conditions like complicated paging, sorting, filtering, etc, an Object/Relational mapper tool is probably the way to go, although the process of overhauling your existing code to make use of the tool could be quite complicated.
如果您决定研究ORM工具，则应查看 Propel (
If you decide to investigate ORM tools, you should look at Propel, the ActiveRecord component of Yii, or the king-daddy PHP ORM, Doctrine. Each of these gives you the ability to programmatically build queries to your database with all manner of complicated logic. Doctrine is the most fully featured, allowing you to template your database with things like the Nested Set tree pattern out of the box.
就性能而言，存储过程是最快的，但与原始sql相比，通常并没有很多. ORM工具可以通过多种方式对性能产生重大影响-低效或冗余查询，在每个请求上加载ORM库时的巨大文件IO，每个查询的动态SQL生成...所有这些都会产生影响，但是与使用手动查询创建自己的数据库层相比，使用ORM工具可以大大减少代码量，从而为您提供强大的功能.
In terms of performance, stored procedures are the fastest, but generally not by much over raw sql. ORM tools can have a significant performance impact in a number of ways - inefficient or redundant querying, huge file IO while loading the ORM libraries on each request, dynamic SQL generation on each query... all of these things can have an impact, but the use of an ORM tool can drastically increase the power available to you with a much smaller amount of code than creating your own DB layer with manual queries.
加里·理查森绝对正确，如果您要继续在代码中使用SQL，无论使用查询还是存储过程，都应始终使用PDO的准备好的语句来处理参数.输入清理由PDO进行.
Gary Richardson is absolutely right though, if you're going to continue to use SQL in your code you should always be using PDO's prepared statements to handle the parameters regardless of whether you're using a query or a stored procedure. The sanitisation of input is performed for you by 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会自动将字符串转换为整数.
Caveat: assuming that the ID is 1, the above script will output string(1) "1". PDO->lastInsertId() returns the ID as a string regardless of whether the actual column is an integer or not. This will probably never be a problem for you as PHP performs casting of strings to integers automatically.
以下内容将输出bool(true):
// regular equality test
var_dump($lastInsertId == 1);
，但是如果您有期望该值是整数的代码，例如 is_int 或PHP的确实是100％等于"运算符:
but if you have code that is expecting the value to be an integer, like is_int or PHP's "is really, truly, 100% equal to" operator:
var_dump(is_int($lastInsertId));
var_dump($lastInsertId === 1);
您可能会遇到一些问题.
you could run into some issues.
Some good discussion on stored procedures here
这篇关于您如何管理SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
