Description:
The documentation of Prepared Statements makes the following claim regarding which statements may be prepared:
The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. supported. ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE are supported as of MySQL 5.0.23. Other statements are not yet supported.
[ http://dev.mysql.com/doc/refman/5.0/en/sqlps.html ]
This is not an exhaustive list. I attempted every listed DDL, account management and replication management operation in the manual, and found 7 additional operations that are supported:
# CREATE VIEW
# ALTER VIEW
# DROP VIEW
# ALTER TABLE
# CREATE INDEX
# TRUNCATE
# RENAME TABLE
# SET PASSWORD
mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TABLE t (a INT);
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> PREPARE stmt FROM 'CREATE VIEW v AS SELECT * FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM information_schema.views WHERE table_schema = DATABASE() AN
D table_name = 'v';
+---------------+--------------+------------+-----------------------------------
-------------------------------------+--------------+--------------+------------
----+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION
| CHECK_OPTION | IS_UPDATABLE | DEFINER
| SECURITY_TYPE |
+---------------+--------------+------------+-----------------------------------
-------------------------------------+--------------+--------------+------------
----+---------------+
| NULL | test | v | /* ALGORITHM=UNDEFINED */ select `
test`.`t`.`a` AS `a` from `test`.`t` | NONE | YES | root@localh
ost | DEFINER |
+---------------+--------------+------------+-----------------------------------
-------------------------------------+--------------+--------------+------------
----+---------------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql> PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM information_schema.views WHERE table_schema = DATABASE() AN
D table_name = 'v';
+---------------+--------------+------------+-----------------------------------
-------------------------------------+--------------+--------------+------------
----+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION
| CHECK_OPTION | IS_UPDATABLE | DEFINER
| SECURITY_TYPE |
+---------------+--------------+------------+-----------------------------------
-------------------------------------+--------------+--------------+------------
----+---------------+
| NULL | test | v | /* ALGORITHM=UNDEFINED */ select `
test`.`t`.`a` AS `a` from `test`.`t` | NONE | YES | root@localh
ost | DEFINER |
+---------------+--------------+------------+-----------------------------------
-------------------------------------+--------------+--------------+------------
----+---------------+
1 row in set (0.02 sec)
mysql>
mysql> PREPARE stmt FROM 'DROP VIEW v';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SELECT * FROM information_schema.views WHERE table_schema = DATABASE() AN
D table_name = 'v';
Empty set (0.00 sec)
mysql>
mysql> PREPARE stmt FROM 'ALTER TABLE t ADD COLUMN b INT';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW CREATE TABLE t;
+-------+-----------------------------------------------------------------------
-------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) default NULL,
`b` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> PREPARE stmt FROM 'CREATE INDEX test_idx ON t(a)';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW CREATE TABLE t;
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) default NULL,
`b` int(11) default NULL,
KEY `test_idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO t (a) VALUES (1);
Query OK, 1 row affected (0.16 sec)
mysql>
mysql> PREPARE stmt FROM 'TRUNCATE t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 1 row affected (0.08 sec)
mysql>
mysql> SELECT * FROM t;
Empty set (0.00 sec)
mysql>
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> PREPARE stmt FROM 'RENAME TABLE t TO t1';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> SHOW CREATE TABLE t1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) default NULL,
`b` int(11) default NULL,
KEY `test_idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> PREPARE stmt FROM 'SET PASSWORD = PASSWORD(''test'')'; # might want to r
eset this later ;-)
Query OK, 0 rows affected (0.02 sec)
Statement prepared
mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.03 sec)
mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
How to repeat:
DROP TABLE IF EXISTS t;
DROP VIEW IF EXISTS v;
CREATE TABLE t (a INT);
PREPARE stmt FROM 'CREATE VIEW v AS SELECT * FROM t';
EXECUTE stmt;
SELECT * FROM information_schema.views WHERE table_schema = DATABASE() AND table_name = 'v';
PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
EXECUTE stmt;
SELECT * FROM information_schema.views WHERE table_schema = DATABASE() AND table_name = 'v';
PREPARE stmt FROM 'DROP VIEW v';
EXECUTE stmt;
SELECT * FROM information_schema.views WHERE table_schema = DATABASE() AND table_name = 'v';
PREPARE stmt FROM 'ALTER TABLE t ADD COLUMN b INT';
EXECUTE stmt;
SHOW CREATE TABLE t;
PREPARE stmt FROM 'CREATE INDEX test_idx ON t(a)';
EXECUTE stmt;
SHOW CREATE TABLE t;
INSERT INTO t (a) VALUES (1);
PREPARE stmt FROM 'TRUNCATE t';
EXECUTE stmt;
SELECT * FROM t;
DROP TABLE IF EXISTS t1;
PREPARE stmt FROM 'RENAME TABLE t TO t1';
EXECUTE stmt;
SHOW CREATE TABLE t1;
PREPARE stmt FROM 'SET PASSWORD = PASSWORD(''test'')'; # might want to reset this later ;-)
EXECUTE stmt;
DROP TABLE IF EXISTS t;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v;
Suggested fix:
Add documentation of the additional operations supported by prepared statements.
