Mysql的临时表truncate操作失败（转）

今天意外发现Mysql的临时表在进行truncate 操作的时候提示没有drop table的权限而失败，重现步骤：

【步骤一】创建测试用户，授予select,insert,update,delete,create temporary tables 权限
mysql> grant select, insert, update, delete, create temporary tables on test.* to 'hx'@'localhost' identified by 'hx';
Query OK, 0 rows affected (0.00 sec)

【步骤二】使用测试表连接上数据库，创建测试临时表，并插入测试数据
mysql> create temporary table if not exists tempprc (assets_sn varchar(60), ct int, max_de_date varchar(10), new_col int) engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tempprc values (1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tempprc;
+-----------+------+-------------+
| assets_sn | ct   | max_de_date |
+-----------+------+-------------+
| 1         |    1 | 1           |
+-----------+------+-------------+
1 row in set (0.00 sec)

【步骤三】截断临时表 tempprc
mysql> truncate table tempprc;
ERROR 1142 (42000): DROP command denied to user 'hx'@'localhost' for table 'tempprc'

mysql> truncate temporary table tempprc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table tempprc' at line 1

【步骤四】删除临时表 tempprc
mysql> drop temporary table tempprc;                                                                                                Query OK, 0 rows affected (0.00 sec)


根据以上实验和Mysql的文档关于 truncate 操作的解释：

【参考】http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html

Beginning with MySQL 5.1.32, TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE — that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not allow for statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug#36763) However, it is still applied on replication slaves using InnoDB in the manner described previously. 

确认结论：Mysql 对于非INNODB的引擎，truncate的操作都是等价于先 drop table 再create table 来实现的，所以使用在临时表上（engine = memory）使用 truncate 操作的时候会提示没有 drop table 的权限。

其次，对于当前session可以drop temporary table 也能够获得合理的解释：

【参考】http://dev.mysql.com/doc/refman/5.1/en/drop-table.html

The TEMPORARY keyword has the following effects:

• The statement drops only TEMPORARY tables.

• The statement does not end an ongoing transaction.

• No access rights are checked. (A TEMPORARY table is visible only to the session that created it, so no check is necessary.)

Using TEMPORARY is a good way to ensure that you do not accidentally drop a non-TEMPORARY table.

Mysql 在删除临时表的时候，不进行任何权限校验，因为临时表是且仅仅是当前session可见，所以校验是不必要的。

【参考资料】

关于Mysql truncate 临时表的讨论：