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

Question

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?

Answer 1

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.