3

I am trying to get MySql to execute the result of the below statement as further sql statements. I believe in oracle sqlplus this is achieved using the spool function. How is this achieved in Mysql?

select concat('OPTIMIZE TABLE `', ist.TABLE_SCHEMA,'`.',  ist.TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES ist where table_schema = 'my_schema';
Ajo Koshy
  • 1,205
  • 2
  • 21
  • 33
mR_fr0g
  • 8,462
  • 7
  • 39
  • 54

2 Answers2

5

You have to use prepared statements.

SET @s:='';
SELECT @s:=concat(@s, 'OPTIMIZE TABLE `', ist.TABLE_SCHEMA,'`.',  ist.TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES ist where table_schema = 'my_schema';    
PREPARE stmt FROM @s;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

But you have to put all the optimize table statements into one variable, that's why I'm concatenating @s with itself. Else you'd have to work with a cursor, which is unnecessary work.

As of MySQL 5.0.23, the following additional statements are supported:

ANALYZE TABLE
OPTIMIZE TABLE
REPAIR TABLE

EDIT: An even simpler approach is this:

SELECT CONCAT('OPTIMIZE TABLE `', ist.TABLE_SCHEMA,'`.',  ist.TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES ist WHERE table_schema = 'my_schema'
INTO OUTFILE '/tmp/my_optimization';
SOURCE 'tmp/my_optimization';
Jonny
  • 3,807
  • 8
  • 31
  • 48
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • 1
    This approach may no longer work for MySQL 5.6 and newer. According to [MySQL 5.7 documentation](http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html) _SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters)._ When multi-statement query with OPTIMIZE TABLE or REPAIR TABLE is passed to PREPARE stmt FROM @s; MySQL reports error. – Mr. Girgitt Nov 02 '16 at 11:01
  • 1
    @Mr.Girgitt Added a better way of doing it in my answer. – fancyPants Nov 02 '16 at 11:06
  • Thank you. This is how I ended up doing it. Creating/executing separate prepared statement per select's result would avoid using files but for now temp file is good enough for me. – Mr. Girgitt Nov 02 '16 at 11:13
  • I turned out SOURCE command does not work when used within prepared statement so I needed to change implementation to use stored procedure and cursor iterating over result of select from a prepared statement stored to temp table. Another issue is not being able to overwrite the output file and e.g. on windows without support for SYSTEM command to clear output file variable output file name needs to be created (additional prepared statements) making ironically stored procedure a clearer approach. – Mr. Girgitt Nov 04 '16 at 08:49
1

If you are accessing it via the mysql command line client you could just pipe the output of the first command into the cli tool again.

mysql --batch --silent -nBe "select concat('OPTIMIZE TABLE `', ist.TABLE_SCHEMA,'`.',  ist.TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES ist where table_schema = 'my_schema'" | mysql my_schema
dirkaholic
  • 352
  • 4
  • 11