在实际工作中,有时我们会想要去清空一张表的所有数据,常用的命令有 delete 和 truncate 这两个命令。
1. delete
在很多情况,我们对于 delete 的应用更多,因为它支持更多的匹配模式,我们可以使用 where 条件语句,清理一些特定的数据,关于 delete 的操作,这里不做过多解释,有空就专门写写 delete 的使用和原理。delete 在清空表时,如果表内有自增 ID 的设定,那么在 delete 清空之后,再插入数据时,自增 ID 不会从 1 开始,默认会继续增加; 除非我们使用 OPTIMIZE TABLE,重置表属性;
mysql> delete from piwik_tmp;
Query OK, 0 rows affected (0.00 sec)
2. truncate
truncate 在清空表时,会同时将自增 ID 重置,再插入新数据时会从 1 开始,而且 truncate 在清空表时,不关心表的行数,所以执行效率会高于 delete;
mysql> truncate table piwik_tmp;
Query OK, 0 rows affected (0.01 sec)
3. 释放表空间
但是在 truncate 清空表数据时,myisam 的表和 innodb 的表在使用上有一定的区别;myisam 表会清空所有数据,并释放表空间,即硬盘空间会得到释放。innodb 表也会清空所有数据,但不释放表空间。Innodb 数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,这就导致 InnoDB 数据库文件不断增长。如果想彻底释放这些已经删除的数据,需要把数据库导出,删除 InnoDB 数据库文件,然后再导入。
# 备份数据库:
mysqldump -u -p --quick --force --all-databases > mysqldump.sql
# 停止数据库
service mysqld stop
# 删除这些大文件
rm /usr/local/mysql/var/ibdata1
rm /usr/local/mysql/var/ib_logfile*
# 手动删除除Mysql之外所有数据库文件夹,然后启动数据库
service mysqld start
# 还原数据
mysql -uroot -proot < mysqldump.sql
4. innodb_file_per_table
还有一种方式是在创建数据库的时候设置 innodb_file_per_table,这样 InnoDB 会对每个表创建一个数据文件,然后只需要运行 OPTIMIZE TABLE 命令就可以释放所有已经删除的磁盘空间。
4.1 修改 mysql 默认配置文件
编辑 my.ini 或 my.cnf 在 innodb 段中加入 innodb_file_per_table=1 # 1 为启用,0 为禁用
4.2 查看修改结果
通过 mysql 语句可以查看该变量的值:
mysql> show variables like '%per_table%';