原始 SQL 与基于 OOP 的查询 (ORM)？
我正在做一个需要频繁访问数据库、插入和删除的项目。我应该选择原始 SQL 命令还是应该选择 ORM 技术？该项目可以在没有任何对象且仅使用 SQL 命令的情况下正常工作吗？这通常会影响可扩展性吗？
编辑：该项目是不向用户提供我的内容，但用户生成内容并且该项目在线的类型之一。那么，内容的数量取决于用户的数量，如果该项目甚至有 50000 个用户，而且每个用户都可以创建内容或阅读内容，那么最合适的方法是什么？
I was doing a project that requires frequent database access, insertions and deletions. Should I go for Raw SQL commands or should I prefer to go with an ORM technique? The project can work fine without any objects and using only SQL commands? Does this affect scalability in general?
EDIT: The project is one of the types where the user isn't provided with my content, but the user generates content, and the project is online. So, the amount of content depends upon the number of users, and if the project has even 50000 users, and additionally every user can create content or read content, then what would be the most apt approach?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
如果您没有（或有限）ORM 经验，那么学习新的 API 将需要时间。另外，你必须记住，为了“魔法”而牺牲速度。例如，大多数 ORM 都会为字段选择通配符“*”，即使您只需要
Articles表中的标题列表。在特殊情况下，ORM 常常会失败。
从 OOP 的角度来看，大多数 ORM（基于 ActiveRecord 模式的 ORM）都存在极大缺陷。它们在数据库结构和类/模型之间创建了紧密耦合。
您可以将 ORM 视为技术债务。这将使项目的启动更加容易。但是，随着代码变得越来越复杂，您将开始遇到越来越多由 ORM API 限制引起的问题。最终，您会遇到这样的情况：无法使用 ORM 执行某些操作，而您必须开始直接编写 SQL 片段和整个语句。
我建议远离 ORM 并在代码中实现 DataMapper 模式。这将使您的域对象和数据库访问层分离。
If you have no ( or limited ) experience with ORM, then it will take time to learn new API. Plus, you have to keep in mind, that the sacrifice the speed for 'magic'. For example, most ORMs will select wildcard '*' for fields, even when you just need list of titles from your
Articlestable.And ORMs will aways fail in niche cases.
Most of ORMs out there ( the ones based on ActiveRecord pattern ) are extremely flawed from OOP's point of view. They create a tight coupling between your database structure and class/model.
You can think of ORMs as technical debt. It will make the start of project easier. But, as the code grows more complex, you will begin to encounter more and more problems caused by limitations in ORM's API. Eventually, you will have situations, when it is impossible to to do something with ORM and you will have to start writing SQL fragments and entires statements directly.
I would suggest to stay away from ORMs and implement a DataMapper pattern in your code. This will give you separation between your Domain Objects and the Database Access Layer.
我想说最好尝试以最简单的方式实现目标。
如果使用 ORM 没有真正的附加优势，并且应用程序相当简单，我就不会使用 ORM。
如果应用程序确实要处理大量数据，并且没有业务逻辑，我就不会使用 ORM。
但这并不意味着您不应该设计应用程序属性，但再次强调：如果使用 ORM 不会给您带来任何好处，那么您为什么要使用它呢？
I'd say it's better to try to achieve the objective in the most simple way possible.
If using an ORM has no real added advantage, and the application is fairly simple, I would not use an ORM.
If the application is really about processing large sets of data, and there is no business logic, I would not use an ORM.
That doesn't mean that you shouldn't design your application property though, but again: if using an ORM doesn't give you any benefit, then why should you use it ?
为了提高开发速度，我会选择 ORM，特别是如果大多数数据访问都是 CRUD 的话。
这样您就不必开发 SQL 和编写数据访问例程。
可扩展性不应该受到影响，尽管您确实需要了解自己在做什么（原始 SQL 也可能会损害可扩展性）。
For speed of development, I would go with an ORM, in particular if most data access is CRUD.
This way you don't have to also develop the SQL and write data access routines.
Scalability should't suffer, though you do need to understand what you are doing (you could hurt scalability with raw SQL as well).
如果项目面向：
- 数据编辑（如查看简单的数据表并编辑它们）
- 性能（如设计最快的算法来完成简单的任务）
然后您可以在代码中使用直接的 sql 命令。
如果这是一个大型软件，您不想做的就是这样做，最终您会得到许多类和大量代码。如果您处于这种情况，并且您将 sql 分散在代码中的各处，那么有一天您显然会后悔。您将很难更改域模型。任何修改都会变得非常困难（除了添加功能或独立于现有功能的实体）。
不过，更多信息会更好，例如：
- 频繁（多频繁）是什么意思？
- 您需要什么性能？
编辑
您似乎正在制作某种 CMS 服务。我敢打赌，您不想开始用 SQL 填充您的代码。 @teresko 的模式建议似乎很有趣，将应用程序逻辑与数据库分开（这总是好的），但提供了自定义每个查询的可能性。尽管如此，添加一个填充内存对象的层可能比简单地使用数据库结果编写页面花费更多的时间，但我认为在您的情况下，微小的差异应该不重要。
我建议选择一个好的模式来分离您的业务逻辑和数据访问，就像 @terekso 所建议的那样。
If the project is either oriented :
- data editing (as in viewing simple tables of data and editing them)
- performance (as in designing the fastest algorithm to do a simple task)
Then you could go with direct sql commands in your code.
The thing you don't want to do, is do this if this is a large software, where you end up with many classes, and lot's of code. If you are in this case, and you scatter sql everywhere in your code, you will clearly regret it someday. You will have a hard time making changes to your domain model. Any modification would become really hard (except for adding functionalities or entites independant with the existing ones).
More information would be good, though, as :
- What do you mean by frequent (how frequent) ?
- What performance do you need ?
EDIT
It seems you're making some sort of CMS service. My bet is you don't want to start stuffing your code with SQL. @teresko's pattern suggestion seems interesting, seperating your application logic from the DB (which is always good), but giving the possiblity to customize every queries. Nonetheless, adding a layer that fills in memory objects can take more time than simply using the database result to write your page, but I don't think that small difference should matter in your case.
I'd suggest to choose a good pattern that seperates your business logique and dataAccess, like what @terekso suggested.
这在一定程度上取决于时间尺度以及您当前对 MySQL 和 ORM 系统的了解。如果你没有太多时间，就做你最了解的事情，而不是浪费时间学习一套全新的代码。
随着时间的推移，像 Doctrine 或 Propel 这样的 ORM 系统可以极大地提高你的开发速度。当架构仍然发生很大变化时，您不希望花费大量时间来重写查询。使用 ORM 系统，它可以像更改模式文件和清除缓存一样简单。
然后，当设计确定后，请关注性能。如果您确实使用 ORM 并且您的代码是可靠的 OOP，那么一次迁移一个查询到 SQL 并不是什么大问题。
这就是使用 OOP 进行编码的伟大之处 - 这样的决定不必永远束缚您。
It depends a bit on timescale and your current knowledge of MySQL and ORM systems. If you don't have much time, just do whatever you know best, rather than wasting time learning a whole new set of code.
With more time, an ORM system like Doctrine or Propel can massively improve your development speed. When the schema is still changing a lot, you don't want to be spending a lot of time just rewriting queries. With an ORM system, it can be as simple as changing the schema file and clearing the cache.
Then when the design settles down, keep an eye on performance. If you do use ORM and your code is solid OOP, it's not too big an issue to migrate to SQL one query at a time.
That's the great thing about coding with OOP - a decision like this doesn't have to bind you forever.
我总是建议对数据访问层使用某种形式的 ORM，因为在安全方面投入了大量时间。仅此一点就是不推出自己的理由，除非您对自己防范 SQL 注入和其他漏洞的技能充满信心。
I would always recommend using some form of ORM for your data access layer, as there has been a lot of time invested into the security aspect. That alone is a reason to not roll your own, unless you feel confident about your skills in protecting against SQL injection and other vulnerabilities.