17.2. Using Prepared Statements from the mysql Client

17.2. Using Prepared Statements from the mysql Client

In most circumstances, statements are prepared and executed using the programming interface that you normally use for writing applications that use MySQL. However, to aid in testing and debugging, it is possible to define and use prepared statements from within the mysql command-line client. For purposes of certification, prepared statement use with mysql is the context used to explain prepared statements here. It is also the context in which questions on prepared statements appear on the exam.

Here is a short example that illustrates the use of a prepared statement. It prepares a statement that determines how many languages are spoken in a given country, executes it multiple times, and disposes of it:

 mysql> PREPARE my_stmt FROM     -> 'SELECT COUNT(*) FROM CountryLanguage WHERE CountryCode = ?'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> SET @code = 'ESP'; EXECUTE my_stmt USING @code; Query OK, 0 rows affected (0.00 sec) +----------+ | COUNT(*) | +----------+ |        4 | +----------+ 1 row in set (0.00 sec) mysql> SET @code = 'RUS'; EXECUTE my_stmt USING @code; Query OK, 0 rows affected (0.00 sec) +----------+ | COUNT(*) | +----------+ |       12 | +----------+ 1 row in set (0.00 sec) mysql> DEALLOCATE PREPARE my_stmt; Query OK, 0 rows affected (0.00 sec)