LBACK
* SELECT
* SET
* SET GLOBAL SQL_SLAVE_SKIP_COUNTER
* SET ROLE
* SET SQL_LOG_BIN
* SET TRANSACTION ISOLATION LEVEL
* SHOW EXPLAIN
* SHOW {DATABASES | TABLES | OPEN TABLES | TABLE STATUS | COLUMNS | INDEX |
TRIGGERS | 
 EVENTS | GRANTS | CHARACTER SET | COLLATION | ENGINES | PLUGINS [SONAME] |
PRIVILEGES | 
 PROCESSLIST | PROFILE | PROFILES | VARIABLES | STATUS | WARNINGS | ERRORS |
 TABLE_STATISTICS | INDEX_STATISTICS | USER_STATISTICS | CLIENT_STATISTICS |
AUTHORS | 
 CONTRIBUTORS}
* SHOW CREATE {DATABASE | TABLE | VIEW | PROCEDURE | FUNCTION | TRIGGER |
EVENT}
* SHOW {FUNCTION | PROCEDURE} CODE
* SHOW BINLOG EVENTS
* SHOW SLAVE HOSTS
* SHOW {MASTER | BINARY} LOGS
* SHOW {MASTER | SLAVE | TABLES | INNODB | FUNCTION | PROCEDURE} STATUS
* SLAVE {START | STOP}
* TRUNCATE TABLE
* SHUTDOWN
* UNINSTALL {PLUGIN | SONAME}
* UPDATE

Synonyms are not listed here, but can be used. For example, DESC can be used
instead of DESCRIBE.

Compound statements can be prepared too.

Note that if a statement can be run in a stored routine, it will work even if
it is called by a prepared statement. For example, SIGNAL can't be directly
prepared. However, it is allowed in stored routines. If the x() procedure
contains SIGNAL, you can still prepare and execute the 'CALL x();' prepared
statement.

PREPARE supports most kinds of expressions as well, for example:

PREPARE stmt FROM CONCAT('SELECT * FROM ', table_name);

When PREPARE is used with a statement which is not supported, the following
error is produced:

ERROR 1295 (HY000): This command is not supported in the prepared statement
protocol yet

Example
-------

create table t1 (a int,b char(10));
insert into t1 values (1,"one"),(2, "two"),(3,"three");
prepare test from "select * from t1 where a=?";
set @param=2;
execute test using @param;
+------+------+
| a    | b    |
+------+------+
|    2 | two  |
+------+------+
set @param=3;
execute test using @param;
+------+-------+
| a    | b     |
+------+-------+
|    3 | three |
+------+-------+
deallocate prepare test;

Since identifiers are not permitted as prepared statements parameters,
sometimes it is necessary to dynamically compose an SQL statement. This
technique is called dynamic SQL). The following example shows how to use
dynamic SQL:

CREATE PROCEDURE test.stmt_test(IN tab_name VARCHAR(64))
BEGIN
	SET @sql = CONCAT('SELECT COUNT(*) FROM ', tab_name);
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END;

CALL test.stmt_test('mysql.user');
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

Use of variables in prepared statements:

PREPARE stmt FROM 'SELECT @x;';

SET @x = 1;

EXECUTE stmt;
+------+
| @x   |
+------+
|    1 |
+------+

SET @x = 0;

EXECUTE stmt;
+------+
| @x   |
+------+
|    0 |
+------+

DEALLOCATE PREPARE stmt;

URL: https://mariadb.com/kb/en/prepare-statement/https://mariadb.com/kb/en/prepare-statement/