Vgbhfive's Blog

MySQL-TRUNCATE函数与truncate命令

Posted on Edited on

TRUNCATE 函数

说明

TRUNCATE(X, D) 函数是 MySQL 自带的一个系统函数。其中 X 是数值，而 D 是保留小数的位数。其作用就是按照小数位数，进行数值截取（此处的截取是按保留位数直接进行截取，没有四舍五入）。

规则

规则如下：

  • 当 D 大于 0，是对数值 X 的小数位数进行操作。
  • 当 D 等于 0，是将数值 X 的小数部分去除，只保留整数部分。
  • 当 D 小于 0，是将数值 X 的小数部分去除，并将整数部分按照 D 指定位数，用 0 替换。

示例

  1. X 为正数

    1
    2
    3
    4
    5
    6
    7
    SELECT TRUNCATE(123.4567, 3);   # 123.456
    SELECT TRUNCATE(123.4567, 2);   # 123.45
    SELECT TRUNCATE(123.4567, 1);   # 123.4
    SELECT TRUNCATE(123.4567, 0);   # 123
    SELECT TRUNCATE(123.4567, -1);  # 120
    SELECT TRUNCATE(123.4567, -2);  # 100
    SELECT TRUNCATE(123.4567, -3);  # 0

  2. X 为负数

    1
    2
    3
    4
    5
    6
    7
    SELECT TRUNCATE(-123.4567, 3);   # -123.456
    SELECT TRUNCATE(-123.4567, 2);   # -123.45
    SELECT TRUNCATE(-123.4567, 1);   # -123.4
    SELECT TRUNCATE(-123.4567, 0);   # -123
    SELECT TRUNCATE(-123.4567, -1);  # -120
    SELECT TRUNCATE(-123.4567, -2);  # -100
    SELECT TRUNCATE(-123.4567, -3);  # 0

  3. 其他示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    // 查询数据库的所有表的容量和索引数
    SELECT 
    table_schema as '数据库',
    sum(table_rows) as '记录数',
    sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
    sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
    sum(truncate(DATA_FREE/1024/1024, 2)) as '碎片占用(MB)'
    from information_schema.tables
    where table_schema='kalacloud_test_data'
    group by table_schema and table_name='product_demo'
    order by sum(data_length) desc, sum(index_length) desc;

truncate 命令

说明

truncate 的作用是清空表，只能作用于表。执行 truncate 语句需要 drop 权限，从逻辑上讲，truncate table 类似于 delete 删除所有行的语句或 drop table 然后再 create table 语句的组合。
其中为了实现高性能，它绕过了删除数据的 DML 方法，因此不支持回滚。尽管 truncate table 与 delete 类似，但是他被分类为 DDL 语句而不是 DML 语句。

区别

  • truncate 和 drop 都是 DDL 语句，执行后无法回滚，而 delete 是可以回滚的。
  • truncate 是直接删除原表然后再创建一个一样的表，执行速度比 delete 快，而 delete 则是一行一行的删除记录。
  • truncate 不支持使用 WHERE 子句，只能删除全部整体，而 delete 支持 WHERE 子句，通过执行条件来删除部分数据。
  • truncate 语句只能用于表，而 drop 和 delete 可以作用于视图、表等。
  • truncate 会清除表中所有的行，但表结构及其约束、索引保持不变，但 drop 会删除表的结构及其所依赖的约束和索引。
  • truncate 会重置表的自增值，delete 不会。
  • truncate 不会激活与表有关的删除触发器，但 delete 可以。
  • truncate 使用后会使表和索引所占的空间恢复到初始大小， delete 操作不会减少表或索引所占用的空间，drop语句则会将表所占用的空间全部释放。

使用

其语法格式如下：

1
TRUNCATE TABLE table_name;

注意事项

  • 如果想删除部分数据用 delete 再带上 WHERE 子句；如果想删除表就需要使用 drop ；如果想保留表而将所有数据删除且和事务无关那就用 truncate 即可。
  • 如果和事务有关或者想触发 trigger 可以用 delete ；如果想整理表内部的碎片可以用 truncate 然后再重新插入数据。
  • truncate 无法通过 binlog 回滚。
  • truncate 会清空所有数据且执行速度很快。
  • truncate 不能对有外键约束引用的表使用。
  • 执行 truncate 需要 drop 权限，不建议给账号 drop 权限。
  • 执行 truncate 前一定要再三检查确认，最好提前备份下表数据。

引用

个人备注

此博客内容均为作者学习所做笔记，侵删！
若转作其他用途，请注明来源！