MySQL中的存储函数 - 值得做什么?

当我第一次进入数据库时​​,我使用的是SQL Server。 我最初使用经典的ASP进入了那个。 我们被告知的一件大事是,如果您使用存储过程,而不是使用ASP SQL命令(我认为“在线”),您在SQL事务中节省了大量时间。 所以几乎我曾经想用数据库编写的所有内容都为我编写了一个存储过程,然后从我的代码中调用了存储过程。</ p>

无论如何,快进几年,我现在 用PHP和MySQL(以及一点Python)完成我的所有工作。 我似乎没有看到太多的东西是人们使用存储过程/函数,所以我并没有真正担心它。</ p>

然而,它发生在我身上 我只是做错了而没​​有意识到这一点。 在MySQL中使用存储函数有一个主要优势吗? 我正在建立一个相当大的网站来处理大量的数据库调用。 这些调用都是在我的PHP代码中完成的。 我是否会更好地使用存储函数进行一致的调用,然后只是将变量传递给PHP中的函数?</ p>
</ div>

展开原文

原文

When I first got into databases I was using SQL Server. I got into that originally with classic ASP. One of the big things we were told was that you saved a LOT of time in your SQL transactions if you used stored procedures, rather than working with the ASP SQL commands (doing it "in line", I suppose). So almost everything I ever wanted to do with the database I wrote a stored procedure for, and then called the stored procedure from my code.

Anyway, fast forward several years and I'm now doing all of my work with PHP and MySQL (and a little Python). One thing that I don't seem to see much of at all is people using stored procedures/functions, so I haven't really been worrying about it.

However, it occurs to me that I'm just doing it wrong and don't realize it. Is there a major advantage to using stored functions in MySQL? I'm building a fairly good-sized website that handles a lot of calls to the database. The calls are all done in-line with my PHP code. Would I be better off using stored functions for the calls that I'm making consistently, and then just passing variables to the function from the PHP?

3个回答



那取决于。 存储过程是处理功能分解的一种方法,如果您有多个应用程序与同一数据库交互,则这些方法绝对必不可少。 几年前使用存储采购的想法一直处于优势地位,随着世界转向服务/ RAD世界,他们正在失去一些基础。 </ p>

存储过程的一些好处是</ p>


  1. 重用/这当然可以在您的代码库中完成但是 它打破了10次子连接写入相同查询的地狱</ p> </ li>

  2. 安全性 - 当sp是暴怒的sql注入攻击来到前面和一个 减少暴露的方法是提供参数化的sp,在大多数情况下自动清理你的输入</ p> </ li>

  3. 文档按照定义在真正的大型数据库表格布局上并不总是足以解释 你存储的是什么以及为什么SP有时会给你以及那些追随你的东西</ p> </ li>

  4. 一个已定义的界面。</ p> </ li> \ n </ ol>

    我认为所有这些专业人员都可以在假设良好的应用程序设计的情况下提供,并且只在一定程度上的某些项目中制作场景。 </ p>

    一些缺点</ p>


    1. 冗余功能 - 我见过很多商店,其中有商业和粗略的逻辑传播 应用程序和业务逻辑在数据库中。 </ p> </ li>

    2. SP上缺乏配置管理 - 虽然已经建立了代码SP管理的程序和工具,但远远落后。</ p> </ li>
      < / ol>
      </ div>

展开原文

原文

Well that depends. Stored procedures are a way to handle functional decomposition and can be absolutely essential if you have multiple applications interacting with the same database. The idea of using stored procures for everything was on the ascendancy a couple of years ago and as the world has moved to a service/RAD world they are losing some ground.

Some of the benefits of stored procedures are

  1. Reuse / This can certainly be accomplished within your code base but it beats the hell out of writing the same query with 10 sub joins 15 times

  2. Security - when sp were the rage sql injection attacks were coming to the for front and one way to reduce your exposure is to offer parameterized sp that sanitize you input automatically for the most case

  3. Documentation by definition on really large databases table layout is not always sufficient to explain what you are storing and why and have SP sometimes gives you and those that come after you what was intended

  4. A defined interface.

I think that all of these pros can be provided assuming good application design and only make scene to a degree on projects of some scale.

Some cons

  1. Redundant functionality - I have seen a lot of shops where business and crud logic is spread in the application and business logic is in the database.

  2. Lack of configuration management on SPs -- While there are established procedures and tools for code SP management lags far behind.

duadlkc5762218
duadlkc5762218 “几年前”? 很多年来,我看到它们在衰落。
9 年多之前 回复



据我所知,您所选语言中的存储过程和预处理语句通常在计算上是等效的。 </ p>

无论我如何回应,这都是一个神圣的战争问题,在此之前已被打死(在这个网站上): MySQL存储过程与复杂查询 </ p>
</ div>

展开原文

原文

It is my understanding that stored procedures and prepared statements from within your chosen language are often computationally equivalent.

Regardless of how I respond, this is a holy war issue that has been beat to death before (on this very site): MySQL Stored Procedure vs. complex query



这是一个问题,问:“我应该将代码分解为方法/函数/过程并调用它们,还是应该编码 一切都进入我当前的功能?“</ p>

存储过程给你的一些优点:</ p>


  1. 更容易测试。 您可以在不运行应用程序的情况下测试存储过程。</ li>
  2. 更容易开发。 您可以让DB开发人员编写存储过程,GUI开发人员编写UI等。</ li>
  3. 更容易移植到其他数据库。 这些更改都包含在数据库中,与app的合同(传递给存储过程的参数)不应更改。</ li>
  4. 从多个前端使用存储过程中的逻辑的能力 。 您不必在每个需要创建新客户的应用程序中编写相同的客户创建逻辑。</ li>
    </ ol>

    最大的缺点是您必须学习多个diciplines 并且可能使用多种工具。 这是在.Net中使用Linq for SQL的一个重要原因。 您不必学习SQL,并且所有内容都包含在.Net代码中。</ p>

    我们使用存储过程来处理所有事情。 它非常好用。 抽象是你的朋友。</ p>
    </ div>

展开原文

原文

This is the same question as asking, "Should I break up my code into methods/functions/procedures and call them or should I code everything into my current function?"

Some of the advantages that stored procedures give you:

  1. Easier testing. You can test the stored procedures without running the app.
  2. Easier development. You can have a DB developer write the stored procedures and a GUI developer write the UI, etc.
  3. Easier porting to a different database. The changes are all contained in the database and the contract with the app (The parameters passed to the stored procedures) should not change.
  4. The ability to use the logic in the stored procedure from multiple front ends. You do not have to code the same customer create logic in every app that needs to create a new customer.

The big downside is that you have to learn multiple diciplines and probably use multiple tools. This is one of the big reasons for using Linq for SQL in .Net. You do not have to learn SQL and everything is contained in the .Net code.

We use stored procedures for everything. It works exceptionally well. Abstraction is your friend.

dqkx69935
dqkx69935 Marvo,我们使用Sql Server,Oracle和Teradata,我们的DBA似乎能够很好地扩展数据库。
9 年多之前 回复
dongna1593
dongna1593 Michael,我们所有的存储过程源代码都在TFS中。 如果SQL存储在应用程序的源代码中,则更容易管理。
9 年多之前 回复
dpbz14739
dpbz14739 那么版本控制呢? 如果SQL在源代码内部,则对SQL的更改将存储在源代码存储库(SVN,CVS等)中。
9 年多之前 回复
dongpi9494
dongpi9494 我认为还有另一个缺点:可扩展性。 数据库可能是要扩展的体系结构中最难的部分。 它可以完成,但比添加更多的PHP或Java服务器更具挑战性。 因此,如果可扩展性是一个问题,您可能需要平衡将多少业务逻辑放入数据库服务器,以及如果在您的Web / app服务器之间展开,可能会更好地扩展。
9 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐