MySQL Prepared Statements with 2 easy examples.

Learning and developing MySQL skills continuously presents opportunities to discover new and interesting things as I navigate this path. In this blog post, I will share a recent find with you.
Prepared statements.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.3 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.

Syntax Of Prepared Statements

Prepared Statements are built upon 3 SQL statements:

  1. PREPARE Syntax – Readies a statement for execution.
  2. EXECUTE Syntax – Calls or commands a prepared statement.
  3. DEALLOCATE PREPARE Syntax – Essentially removes the prepared statement. (Also achieved by ending the current session)

Prepared Statements In Action

A description of the example employees table used in this post is shown below.

mysql> DESC employees;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID    | decimal(6,0) | NO   | PRI | 0       |       |
| FIRST_NAME     | varchar(20)  | YES  |     | NULL    |       |
| LAST_NAME      | varchar(25)  | NO   | MUL | NULL    |       |
| EMAIL          | varchar(25)  | NO   | UNI | NULL    |       |
| PHONE_NUMBER   | varchar(20)  | YES  |     | NULL    |       |
| HIRE_DATE      | date         | NO   |     | NULL    |       |
| JOB_ID         | varchar(10)  | NO   | MUL | NULL    |       |
| SALARY         | decimal(8,2) | YES  |     | NULL    |       |
| COMMISSION_PCT | decimal(2,2) | YES  |     | NULL    |       |
| MANAGER_ID     | decimal(6,0) | YES  | MUL | NULL    |       |
| DEPARTMENT_ID  | decimal(4,0) | YES  | MUL | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

In particular, I am interested in the values of the JOB_ID and SALARY columns. Let’s see all the different JOB_ID‘s available.

mysql> SELECT DISTINCT JOB_ID
    -> FROM employees;
+------------+
| JOB_ID     |
+------------+
| AC_ACCOUNT |
| AC_MGR     |
| AD_ASST    |
| AD_PRES    |
| AD_VP      |
| FI_ACCOUNT |
| FI_MGR     |
| HR_REP     |
| IT_PROG    |
| MK_MAN     |
| MK_REP     |
| PR_REP     |
| PU_CLERK   |
| PU_MAN     |
| SA_MAN     |
| SA_REP     |
| SH_CLERK   |
| ST_CLERK   |
| ST_MAN     |
+------------+
19 rows in set (0.00 sec)

Perhaps you need a given individual JOB_ID salary in the forms of the maximum (MAX), minimum (MIN), and the total (SUM) amounts per that JOB_ID?

Without question, ad-hoc queries are feasible for retrieving each desired aggregation. However, you want these values on a per JOB_ID basis for this current, working session.

How about using a Prepared Statement?

Meat And Taters…

Let’s discuss the aforementioned 3 SQL statements required for prepared statements with this simple example.
Up first, PREPARE.

mysql> PREPARE job_pay
    -> FROM 'SELECT MAX(SALARY) AS max_pay, MIN(SALARY) AS min_pay,
    '> SUM(SALARY) AS total_pay
    '> FROM employees
    '> WHERE JOB_ID = ?';
Query OK, 0 rows affected (0.02 sec)
Statement prepared

The question mark (?) is a placeholder for a parameter value.
Once that’s finished, use the SET command to create a user-defined variable to pass to the placeholder (The ? remember?) within the prepared statement via the EXECUTE command.

mysql> SET @hr_pay = 'HR_REP';
Query OK, 0 rows affected (0.00 sec)

EXECUTE, puts things in motion.

mysql> EXECUTE job_pay USING @hr_pay;
+---------+---------+-----------+
| max_pay | min_pay | total_pay |
+---------+---------+-----------+
| 6500.00 | 6500.00 |   6500.00 |
+---------+---------+-----------+
1 row in set (0.03 sec)

There are the results we are after. Three different aggregations for the HR_REP job title.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

To humor ourselves, let’s verify the prepared statement returns the correct results, with the below query:

mysql> SELECT MAX(SALARY), MIN(SALARY), SUM(SALARY)
    -> FROM employees
    -> WHERE JOB_ID = 'HR_REP';
+-------------+-------------+-------------+
| MAX(SALARY) | MIN(SALARY) | SUM(SALARY) |
+-------------+-------------+-------------+
|     6500.00 |     6500.00 |     6500.00 |
+-------------+-------------+-------------+
1 row in set (0.00 sec)

Looks good to me.

DEALLOCATE PREPARE drops the prepared statement and frees up any resources from it.

FYI, logging out of the current session effectively accomplishes the same.

mysql> DEALLOCATE PREPARE job_pay;
Query OK, 0 rows affected (0.00 sec)

Attempting to EXECUTE job_pay now results in an error.

mysql> EXECUTE job_pay USING @hr_pay;
ERROR 1243 (HY000): Unknown prepared statement handler (job_pay) given to EXECUTE

That’s one method in the books for creating prepared statements.

Different Syntax

In addition to the previously discussed syntax, the following is allowed for creating prepared statements.

Let’s build up a prepared statement that will return an official job title from the table, for a passed in search parameter.

Here is the jobs table description:

mysql> DESC jobs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID     | varchar(10)  | NO   | PRI |         |       |
| JOB_TITLE  | varchar(35)  | NO   |     | NULL    |       |
| MIN_SALARY | decimal(6,0) | YES  |     | NULL    |       |
| MAX_SALARY | decimal(6,0) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

And the prepared statement.

mysql> SET @title_name = 'SELECT JOB_TITLE FROM jobs WHERE JOB_ID = ?';
Query OK, 0 rows affected (0.00 sec)

Here, I am selecting the chosen query into the @title_name variable during assignment.

Then, @title_name is supplied in the PREPARE call.

mysql> PREPARE title_search FROM @title_name;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Let’s create a user-defined variable to use with the prepared statement.

mysql> SET @finance_mgr = 'FI_MGR';
Query OK, 0 rows affected (0.00 sec)

Finally executing the prepared statement,

mysql> EXECUTE title_search USING @finance_mgr;
+-----------------+
| JOB_TITLE       |
+-----------------+
| Finance Manager |
+-----------------+
1 row in set (0.00 sec)

The Finance Manager job title is returned for the FI_MGR parameter passed in, which is correct.

Another working example returns as expected.

Important Highlights

I found the below conclusions informative while studying the official documentation (see link below) and thought to share them with you.

  • Prepared statements are not case-sensitive.
  • This link from the official documentation, covers the allowable SQL Statements in prepared statements.
  • PREPARE, EXECUTE, or DEALLOCATE PREPARE cannot themselves, be passed to the PREPARE statement as statements.
  • To set or check the number of allowed prepared statements, see the max_prepared_stmt_count system variable. Setting this variable to 0 (zero), prevents using prepared statements.

A Final Thought

Publishing this blog post enabled me to learn many things about Prepared Statements. By providing that information here, I hope it helps you learn and grow as well.
Explore the official MySQL 5.7 Online Manual for an in-depth overview of all topics covered in this post.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.