Search code examples
mysqldatabasefunctiontruncate

Is there a function in MySQL to truncate a table when it reaches a certain value, like 100MB?

    SELECT ehrt_cache_render;
    IF TABLE ehrt_cache_render > 20 MB THEN TRUNCATE TABLE ehrt_cache_render;
    END IF;

That's what I tried.

Solution

  • This is one possibility, to do that

    you should specify the database name in the query YOUR_DATABASE_NAME else, you could truncate unwanted tables

    SELECT 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
    INTO @size_table
FROM information_schema.TABLES 
WHERE 
     table_schema = "YOUR_DATABASE_NAME"   
AND table_name = "ehrt_cache_render";
SELECT IF(@size_table IS NULL,@sql := "DO NULL", IF(@size_table > 100,@sql := "TRUNCATE TABLE YOUR_DATABASE_NAME.ehrt_cache_render",@sql := "DO NULL"));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;