Stored Procedures in MySQL and PHP

Taylor Ren
Tweet

Put simply, a Stored Procedure ("SP") is a procedure (written in SQL and other control statements) stored in a database which can be called by the database engine and connected programming languages.

In this tutorial, we will see how to create an SP in MySQL and execute it in MySQL server and in PHP.

Note: We are not going to cover the full aspect of the SP here. The official MySQL document should always be the place for reference.

SP are also available in other common database servers (Postgre for example) so what we will discuss here can be applied to those as well.

Why Stored Procedures are recommended

Most of us are quite familiar with the normal setup to build a database application: creating database, creating tables, set up indexes, CRUD the data, issue queries from the client side and do further processing if necessary.

That workflow works fine in most cases but there is one important aspect of database programming missing: the Stored Procedure.

There are at least four advantages I can think of to use an SP in a database application.

Firstly, it reduces the network traffic and overhead. In a typical PHP database web application, there are four layers:

  • The client layer, which is normally a web browser. It receives user interactions and presents the data in a UI.
  • The web server layer, which handles and dispatches user requests and sends back responses to the client layer.
  • The PHP layer, which handles all PHP interpretation, does the application logic and generates the PHP part of response.
  • The database layer, which handles all database queries, including but not limited to a SELECT query, an INSERT statement, etc.

In a typical environment, these layers will most likely not reside on one single machine, maybe not even in one network, for larger applications.

Although network speed has tremendously increased in the past few years, it is still the slowest and most unreliable compared to other ways of transferring the data (CPU cache, memory, hard disk, etc). So, to save bandwidth and increase robustness, it is sometimes a good idea to have more processing and logic done on the server side (in particular, the MySQL server) and have less data transferred through the network.

Secondly, it improves the performance. SP is stored and run directly in the MySQL server. It can be pre-compiled and analyzed by the database server. This is quite different from issuing the same query from the client side, where the query will be parsed by database drivers, analyzed and optimized (if possible) every time the query statement is called. This is somehow quite like the interpreted language execution (at the client end) and the compiled language execution (at the database server end). And we know a compiled program will run faster.

Third, Write Once and Execute Anywhere. SQL is standard and purely 100% platform independent. It only relies on the database server. Consider how many different languages/libs there are that we can use to deal with the database. It increases efficiency to put the data retrieving and processing at the server end instead of writing the same processing logic in a different syntax provided by all these languages/libs, if the data processing logic is so commonly used.

Last but not least, SP is a fundamental aspect of database security.

Let's consider a simple database setup. In a human resource information system (HRIS), it is reasonable to assume that there exists a table holding the salary information of each employee. An HR employee should have the right to grab some figures out of this table: total salary, average salary, etc but this employee should not see the detailed salary of each employee as this information will be too sensitive and should only be available to a few.

We know MySQL has a comprehensive privilege control. In this case, it is obvious that we can't even grant SELECT privilege to this HR employee (which, if we do, means he/she can see the detailed salary of everyone). But if he/she can't access the salary table, how can this employee get the aggregation information related to salary? How can we allow the employee to grab that information without compromising the HR policy?

The answer is using a Stored Procedure that returns the required information and grants that employee the EXECUTE privilege. (For a detailed list and explanation of MySQL privileges, please consult the official documentation. The link here is for MySQL 5.6. Please replace 5.6 with the version you are using.)

SP is now a bridge, bridging the user (our HR employee) and the table (salary), to which the user has no direct access.

That's it! With SP, we can get the user to accomplish the task without compromising the database security (and HR policy)!

Drawbacks of using Stored Procedures

After naming all the advantages of using an SP, we need to be clear about some of the drawbacks and see if there are ways to improve.

  • No version control on SP itself. When an SP is modified, it is modified, no historical trails can be kept in the server side. It may create some frustrations when the user would like to rollback a change. My suggestion is to write the SP in your client side and put it under version control. When the SP is ready, it is easy to copy the code into, say MySQL Workbench and create it at the server side. By doing so, we can have some degree of version control.
  • No easy way to "synchronize" the changes applied and force everyone to use the latest version, in particular, when each of the team member has his/her own local database for development and testing purposes. Version control may be the solution but still requires manual intervention by updating the local copy of the SP in the local db server. Another way is to use "mocking". The team members can be divided so that at least one person will focus on the maintenance of the SP and the implementation of the calling to SP in the code. All others that need the results from the SP can develop and test their portion using mocking objects, i.e, always assuming the "faked" call to the SP will return a desired result. In a later stage, merging can be done to discard the mocking code.
  • Hard to backup/export. The SP is on the server side. Regular developers will only have basic privileges (SELECT, EXECUTE, etc) and no admin rights to backup and export. In a way, I won't call it a drawback but rather a fundamental aspect of db security. There is no way, and it is not recommended to get around this. It is suggested that, in a team, a dedicated DB admin will be appointed to do such jobs. A regular db backup can also serve the backup/export (and import) purpose.

Creating a Stored Procedure in MySQL

As SPs are stored in the server, it is recommended to create the SP directly in the server, i.e., not by using PHP or other programming languages to issue SQL commands to do so.

Let's see how to create the SP in MySQL server, create a user and apply privileges and run (as that user) the SP to see if the result is correct. In my working environment, I am using MySQL Workbench. Other tools are available (PHPMyAdmin for example) so feel free to use the tools that best fit you.

Assume we have a table like this:

CREATE TABLE `salary` (
  `empid` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And for the HR employee that needs to get the aggregated information on salary (average, max, min, etc) from that table, we first create a user 'tr' like this:

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

and for this user, we only grant EXECUTE privilege to the schema where the salary table resides:

grant execute on hris.*  to tr@`%`

We can verify the necessary privilege is granted by visiting the "Users and Privileges" in MySQL Bench:

Now let's create the SP like this:

DELIMITER $$

CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;

END

NOTE: All the above operations will require an admin role in the MySQL server.

After issuing the command in MySQL Workbench, the avg_sal SP will be created and ready to be called. It will return the average salary of table salary.

To test if the user tr can actually run the SP but should not be able to access the salary table, we can switch the role by logging into the MySQL server using user tr. It can be done by creating another connection in MySQL Workbench using a different user/password pair.

After logging in as tr, the first thing we will notice is that the user will not be able to see any tables and can only see the SP:

It is clear that the user tr won't be able to select anything from any table (thus unable to see the detailed salary number of the salary table) but he/she is able to execute the SP we just created and get the average salary of the company:

call avg_sal(@out);
select @out;

The average salary will be displayed.

We have so far done all the preparation to create a user, grant privileges, create a SP and test the running of the SP. Next we will show how to call that SP from within PHP.

Calling a Stored Procedure from PHP

With PDO, calling an SP is straightforward. The PHP code is as follows:

$dbms = 'mysql';

//Replace the below connection parameters to fit your environment
$host = '192.168.1.8'; 
$db = 'hris';
$user = 'tr';
$pass = 'mypass';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$q=$cn->exec('call avg_sal(@out)');
$res=$cn->query('select @out')->fetchAll();
print_r($res);

The $res will contain the average salary of the table salary. The user can further process the output with PHP now.

Conclusion

In this article, we reviewed the long-forgotten component in MySQL database: the Stored Procedure. The advantages to use an SP are obvious and let me re-emphasize: Stored Procedures allow us to apply stronger database access control to certain data to fit in with the business requirement.

We also illustrated the basic steps to create Stored Procedures, create a user and assign a privilege, and how to call it in PHP.

This article does not cover the full scope of Stored Procedures. Some important aspects like input/output parameters, control statement, cursors, the complete syntax, etc are not discussed in this short article.

If you feel interested, please leave your comments here and we will be glad to bring more in-depth articles about this useful and powerful aspect of MySQL.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • David Stanley

    Great article, but I have to pick on a few points.

    First, while stored procedures are great for performance, they are untestable via standard unit tests. As you pointed out, they can’t be versioned with the application, either. When there are multiple different systems talking to the same db, it makes sense to use a stored procedure to centralize the business logic. When it is just one application, however, I don’t think it is necessarily a good choice. The same centralization and security can be accomplished via smart application architecture that can be versioned, tested, and updated easily.

    As far as performance goes, I totally agree with you. For simple’SELECT’ statements, though, consider using a view instead. This gives you the same performance gain (execution plan is already determined) as well as a modicum of security (can only select against the view, not do other operations). The same versioning issues are present with a view. Another alternative is a read-only db, but that gets complicated pretty quickly.

    You mentioned that “SQL is standard and purely 100% platform independent.” This is just wrong. While the ANSI standard for SQL is meant to be independent, there is enough difference between flavors to make it more than a trivial exercise in moving between platforms. Putting your business logic in a stored procedure ties you to database engine. If you ever need to move, then all of the benefit of using PDO or some other DB abstraction layer is negated. Rather, move that logic to the business layer of your app.

    Finally, pointing to the security benefits of Stored Procedures is silly. While you can force consistent and compliant access to CRUD operations, the same can be done in the application via smart architecture and API.

    • Taylor Ren

      Hi David,

      Thanks for the quick comments.

      On your raised points, my quick comments are:

      1. Agreed in general. In particular on the test aspect, I know SP is not testable in the server end but you can still test it by testing the result of the query (be it a DML or DDL) from the client side by asserting the returned query results or if a table is created when we have a testing db for the SP.

      2. Totally agree with you. A simple select statement can be done by a view and enforcing the same kind of security control discussed over here. This article is just an entry level introduction to bring us back to this long forgotten topic (Bruno searched the SP site and the last article on SP is 8 years ago!) When more delicate control logic is in picture, SP will be the solution.

      3. I may over stated on the standardness of SQL. Maybe I shall put it more flexible by saying that normally a business entity will stick to a db engine and not readily to switch from one to another, Thus the compatibility will be obtained to a maximum.

      4. I am not sure if I grab your last point, especially the last part of it. For me, if there is a built-in and straightforward mechanism to achieve a goal — you will agree that db access control is definitely a goal to achieve in a db program, I presume — I will use that method. Maybe you can share your experience on this portion by contributing another more detailed article?

    • http://www.bitfalls.com/ Bruno Skvorc

      Thanks for taking the time to provide this detailed feedback, comments like these are really appreciated

  • Oscar Blank

    I know you said that you weren’t going to cover everything about SPs in this article, however, I think a lot of people would benefit from knowing exactly how a DEFINER effects the way SPs are used. For instance, part of definer is the domain or IP of the user, and if not specified can lead to some confusion after creating the SP.

    • Taylor Ren

      Hi Oscar,

      Yes, you got a point here.

  • Raymond Zhang

    Thank you Taylor. This is the first time for me to use SP but I have to say your step-by-step explanation style made it a lot easier for me to touch base the basic knowledge of SP. I will play with it for some more time and with some fun for sure. Thanks again.

  • Josh Hartman

    Great teaching style, very easy to follow!

  • Bas

    Excellent article on an often overlooked brillant feature!
    I’ve used stored procedures and functions in the past and used a mysql debugging tool for dvelopment. Worked great!
    One other powerful concept is triggers. They also use the same scripting language as SPs. One great use case is a cache table. Lots of CMSes, like Drupal, create a table for every property. Extensive joins are needed when searching data. Triggers allow you to implement a low-level caching table which can save the day. Idea for your next article, maybe?

    • Taylor Ren

      Triggers are a important aspect of db. I may not cover the triggers in my future articles, however.

      To me, it looked like a more intrinsic feature of a db. For example, after an insertion, the db engine will “trigger” some after-insert checkings to see if all constraints are met.

      Previously the only practical usage for me to use trigger is to calculate “calculated fields”.

      Also, to avoid joinings, triggers can be a good way but I guess it relies on redundant tables too.

  • Taylor Ren

    IMHO, caching the returned result is a more advanced topic and it is really under database commit isolation topic. For me, I would rather give caching a miss for two reasons:

    1. In a very dynamic and almost all-time-inserting db, caching may not increase performance at all.
    2. In a very dull and almost no-inserting db app, a local copy of db may be even better.

    There are always pros-cons in consideration of performance and robustness provided by, say SP.

    Open views are welcomed.

  • Taylor Ren

    To some extent, I agree. SP in my aspect is very suitable for C/S applications.

    In a multi-db-server environment, I believe syncing could always come to rescue if we need to change the SP? DB structure change are rare, in my view, for any cases.

    Security control is still one of my biggest votes to use an SP.

  • Pratik Shah

    Nice Article Taylor..
    But it would be great if you would have added example of complex situation using cursor as I have already used and definitely it reduces processing time

    • Taylor Ren

      Hi Pratik,

      Nice to hear this, Cursor usage in SP is in my mind. I am just constructing some daily usage samples now. If you have any suggestions, please do let me know.

      • Pratik Shah

        Hey Taylor,
        That’s great as your going to write about cursor.But,I would like to suggest to have a example of actual use of cursor so it would be great to understand it’s advantage.

  • Sangeeta Arora

    Hi Taylor,
    This is really a good article. Even, I am using Stored Procedures since last 6 years.

  • Artem Prokopenko

    You forgot to write about other important disadvantages of stored procedures, which inevitably leads to the fact that:
    1. You should forget about encapsulating business logic inside classes
    2. You should forget about ORM/AR
    3. You should forget about SOLID
    4. You should forget about Unit tests
    5. You should forget about the progress in programming
    6. You should forget about good sleep and quiet work …

    Generally, the word “procedure” – is the past, he smelled of old age. It’s time to think in a new way! Not worth to wake dinosaur named “Stored Procedures”.

  • Taylor Ren

    @Artem Prokopenko

    I am not sure how you jump to the conclusions in your post. I am sure I NEVER said to abandon all the technologies you mentioned.

    Well, if we follow your way to draw conclusions, I can also raise the challenge that, say:

    You use ORM. Fine. But if the ORM you use if discontinued?

    Business logics implemented in SP means no business logic in user classes?

    I don’t follow here.

    • Artem Prokopenko

      Encapsulating business logic in a class that works with other service (SQL: MySQL), implies that the service can not affect the class. In other words, the class provides a clear service command (query) and receives the expected response. If we need to change the logic of the class – we change it within the class (refactoring, inheritance, etc.). In your case, a piece of business logic goes to the procedure. It is a violation of encapsulation – class is no longer holistic element. You do not see this problem? Then the procedure for you.

      I had experience with them. One customer insisted very much on them. It was hell.

      • Taylor Ren

        Of course I agree with you on the point of encapsulation. But encap is not everything. We stll have layers of different implementations. Right? To extend your logic, we shall encapsulate ORM into PHP too, instead of leaving it outside as an add-on.

        You have used a very good and exact word there: provide/provider. For me, SP is a provider. Business logic still can be applied on the “provided” outcome to tailor request.

        We are not talking about two things that can’t co-exist together. Certain business logic must be applied to something that is provided. I am bringing the attention to this probably long-forgotten feature and you are trying to emphasize that in business logic, the provider job should also be encapsulated.

  • CreedFeed

    Is there another article or book to read discussing the typical four layers of a PHP web app, and how they integrate together?

    • http://www.bitfalls.com/ Bruno Skvorc

      You mean you’d like an article discussing the basic architecture of today’s website? We can do that.

      • CreedFeed

        Along those lines, yes. But I’m thinking of architecture of a large scale web site… like an Amazon, Facebook, or Twitter. Something to explain how you design/develop a web application using PHP – creating a presentation layer, application layer, REST API layer, etc…. larger scale explanations. :)

        • http://www.bitfalls.com/ Bruno Skvorc

          There’s an article about scaling coming out soon, that might help, but besides the scaling issues and distributing load across many datacenters, there’s not much difference from app to app when you look at those that are designed with quality in mind. I’ll try and think of a topic that encompasses this well and we’ll see if we can get the ball rolling, thanks for the suggestion.

          • CreedFeed

            Excellent. Perhaps a series of articles documenting the entire process of planning, designing, developing and launching a large scale app would be very cool to see. The article(s) do not necessarily have to tackle full scale programming, rather focus on the designing of the architecture – including the typical layers of a large scale app – but do it from a PHP perspective! :)

        • Rahul Patwa

          yes, would be fantastic to see a detailed article on the lines mentioned by @creedfeed:disqus

  • laxman ch

    nice article.

  • http://www.matthewsetter.com/ Matthew Setter

    Taylor, thanks for the coverage of the topic. With respect to Stored Procedures, the most often cited critique I’ve heard about them is “what if you leave and our other developers don’t have that knowledge”.

    I don’t see this as a truly valid reason for not making use of them and thank you for continuing to spread the word about the benefits of them and how to use them.

    With respect to versioning, I use the Doctrine Migrations Phar file for keeping all of my database changes under version control with much success. Perhaps for those looking for a solution, it or dbdeploy can present viable solutions.

    Are you writing follow up articles on stored procedures? I’m quite keen to hear more if you’re keen to write.

  • moses