MySQL Truncate与外键限制

  从数据库的概念上说,Truncate操作是对于数据表的截断操作,即简单地调用文件系统的截断操作系统调用来实现表的清空操作。在基于MyISAM引擎的MySQL数据库上,Truncate始终如此操作,因此Truncate结果虽然从基本等效于无where限定的Delete语句(除了Truncate会将自增字段值重置为0以外),不过效率却高得多。

  但是由于InnoDB存储引擎支持外键,Truncate操作就不是那么简单,因为外键是数据完整性的限定,如果对数据文件进行简单的截断操作,将有可能破坏其外键约束完整性。

  因此,在MySQL 5.1(及以下版本)中Truncate一张InnoDB表时,如果该表存在外键约束,那么实际的操作也等同于无where限定的Delete语句,也就是说,MySQL将逐条删除记录,并检查该次删除操作是否可能违反外键操作,如果没有违反约束的情况出现,那么表被顺利清空,自增字段值被重置为0;一旦出现违反外键约束,那么分为两种情况:

  1. 该外键设定了ON DELETE CASCADE,那么MySQL会同时删除参照表中的相应记录。
  2. 如果该外键没有设定ON DELETE CASCADE,那么MySQL将停止Truncate操作并报错(Error 1451)。

  而在MySQL 5.5中,存在外键约束的InnoDB表,在任何情况下都不允许进行Truncate操作,并报错(Error 1701)。因此,为了向后兼容性的考虑,官方也建议,即使使用MySQL 5.1,也尽量在InnoDB引擎下使用Delete语句代替Truncate语句。