mysql - 基于查询结果更改表，而无需使用MySQL中的过程

我从下面的查询中得到“True”或“False”的结果。

SELECT CASE WHEN a.counter>=1 THEN 'True'

                               ELSE 'False' END AS isUserAuthorised
                               FROM (SELECT Count(*) as counter FROM information_schema.statistics
                                    WHERE  table_name = 'tblechecklistcustomer' AND index_name = 'index_Cust') a;

I want to execute the below query if the above query result  is "True"

ALTER TABLE tblechecklistcustomer DROP INDEX index_Cust;

如何在不使用存储过程的情况下，在单个查询/使用if代码中执行此操作
我试过这样

 SELECT CASE WHEN a.counter>=1 THEN 'True'
                               ELSE 'False' END AS isUserAuthorised
                               FROM (SELECT Count(*) as counter FROM information_schema.statistics
                                    WHERE  table_name = 'tblechecklistcustomer' AND index_name = 'index_Cust') a)=='True')
                                   then ALTER TABLE tblechecklistcustomer DROP INDEX index_Cust;

最佳答案

具有以下结构：

/* CODE FOR DEMONSTRATION PURPOSES */
DROP TABLE IF EXISTS `tblechecklistcustomer`;

CREATE TABLE `tblechecklistcustomer` (
  `cust` INT(11) DEFAULT NULL,
  KEY `index_cust` (`cust`)
) ENGINE=INNODB;

存储过程可以是：

/* CODE FOR DEMONSTRATION PURPOSES */
DELIMITER $$

CREATE PROCEDURE `sp_test`()
BEGIN
    IF EXISTS (SELECT NULL
                FROM
                    `information_schema`.`statistics`
                WHERE
                    `table_schema` = DATABASE() AND
                    `table_name` = 'tblechecklistcustomer' AND
                    `index_name` = 'index_cust') THEN
        ALTER TABLE `tblechecklistcustomer` DROP INDEX `index_cust`;
    END IF;
END$$

DELIMITER ;

如果没有存储过程，您可以尝试以下操作：

/* CODE FOR DEMONSTRATION PURPOSES */
SELECT
    CASE
        WHEN `a`.`counter` >= 1 THEN TRUE
        ELSE FALSE
    END INTO @`counter`
FROM (SELECT @`counter` := NULL) `der`,
    (SELECT COUNT(0) AS `counter`
        FROM
            `information_schema`.`statistics`
        WHERE
            `table_schema` = DATABASE() AND
            `table_name` = 'tblechecklistcustomer' AND
            `index_name` = 'index_cust') `a`;
SET @`alter` := 'ALTER TABLE `tblechecklistcustomer` DROP INDEX `index_cust`';
SET @`nothing` := 'DO (SELECT NULL)';
SET @`stmt` := IF(@`counter`, @`alter`, @`nothing`);
PREPARE `exec` FROM @`stmt`;
EXECUTE `exec`;
DEALLOCATE PREPARE `exec`;

关于mysql - 基于查询结果更改表，而无需使用MySQL中的过程，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20223838/