MySQL中prepare、execute与deallocate的用法详解

mysql> CREATE TABLE a (a int);

Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO a SELECT 1;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO a SELECT 2;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> INSERT INTO a SELECT 3;

Query OK, 1 row affected (0.04 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SET @select_test = CONCAT('SELECT * FROM ', @table_name);

Query OK, 0 rows affected (0.00 sec)

mysql> SET @table_name = 'a';

Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE pr2 FROM @select_test;

Query OK, 0 rows affected (0.00 sec)

Statement prepared

mysql> EXECUTE pr2 ;

+------+

| a |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.00 sec)

mysql> DROP PREPARE pr2; -- 此处DROP可以替代DEALLOCATE

Query OK, 0 rows affected (0.00 sec)