MySQL / MariaDB event to optimize database tables

Question

I'm interested in creating a recurring event to optimize my database tables every night.

I got to SELECT Concat('OPTIMIZE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='database_name';. How can I execute every line via in a MySQL event?

I prefer to use the MySQL / MariaDB events and not involve bash into this. Thank you!

Answer 1

so, this is your query. you only must change the SELECT statement and then you can call it

ELIMITER //
CREATE PROCEDURE p1()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE val VARCHAR(2000);
  DECLARE curs CURSOR FOR SELECT sqlstring FROM table_a;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN curs;

  read_loop: LOOP
    FETCH curs INTO val;
    IF done THEN
      LEAVE read_loop;
    END IF;

  SET @sql := val;
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  END LOOP;

  CLOSE curs;
END; //

DELIMITER ;

call it

call p1();