在MySql中执行sql select的结果 [英] Execute the result of a sql select in MySql

问题描述

我正在尝试让MySql将以下语句的结果作为其他sql语句执行.我相信在oracle sqlplus中，这是使用 spool 函数实现的.如何在 Mysql 中实现?

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';

推荐答案

您必须使用但是您必须将所有优化表语句放入一个变量中，这就是为什么我将 @s 与自身连接在一起的原因.否则，您将不得不使用游标，这是不必要的工作.

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.

从MySQL 5.0.23开始，支持以下附加语句:

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

ANALYZE TABLE
OPTIMIZE TABLE
REPAIR TABLE

一个更简单的方法是:

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';

这篇关于在MySql中执行sql select的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！