MYSQL对象审核中BIG_TABLE_BY_SIZE规则，对于table_size的单位转换问题

按照文档内的规则文件导入的规则，表大小的单位是GB，value是10.0：

{ 
    "_id" : ObjectId("5af94598a58350ff95d3466f"), 
    "db_type" : "mysql", 
    "exclude_obj_type" : "", 
    "input_parms" : [
        {
            "parm_desc" : "表大小(GB)", 
            "parm_name" : "table_size", 
            "parm_value" : 10.0, 
            "parm_unit" : "GB"
        }
    ], 
    "max_score" : NumberInt(10), 
    "output_parms" : [
        {
            "parm_desc" : "表名称", 
            "parm_name" : "title1"
        }, 
        {
            "parm_desc" : "表大小(GB)", 
            "parm_name" : "title2"
        }
    ], 
    "rule_desc" : "表的规模过大，将影响表的访问效率、增加维护成本等。常见的解决方案就是使用分区表，将大表转换为分区表。对于大表的访问，可采取分片方式处理。", 
    "rule_name" : "BIG_TABLE_BY_SIZE", 
    "rule_complexity" : "complex", 
    "rule_cmd" : "default", 
    "rule_status" : "ON", 
    "rule_summary" : "超过指定规模且没有分区的表", 
    "rule_type" : "OBJ", 
    "solution" : [
        "1.建议分库，分表，分区", 
        "2.历史数据归档"
    ], 
    "weight" : NumberInt(1)
}

执行审核规则时的SQL语句，@table_size@会被直接替换成10，没有做GB的单位转换，导致几乎所有的表都违反规则：

select table_name,round(data_length/1024/1024/1024,2)
        from information_schema.tables
        where table_schema='@username@'
        and CREATE_OPTIONS<>'partitioned'
        and data_length>@table_size@
        union all
        select concat(table_name,':',partition_name),round(data_length/1024/1024/1024,2)
        from information_schema.partitions
        where table_schema='@username@'
        and table_name not in (select table_name from information_schema.tables where table_schema='@username@' and CREATE_OPTIONS<>'partitioned')
        and data_length>@table_size@

目前我直接将SQL语句替换为

round(data_length/1024/1024/1024,2)>@table_size@

因为定义的规则中有parm_unit字段，不知是否有统一的单位格式化模块。

CreditEaseDBA / Themis

MYSQL对象审核中BIG_TABLE_BY_SIZE规则，对于table_size的单位转换问题 #21