mysql - 为每个数据库运行查询(mysql)

Question

我正在寻找一种直接的方法来对我的 mysql 服务器上托管的所有数据库运行查询。

我有一堆 Magento 安装，我想截断所有数据库上的所有 Magento 日志表:

• log_customer
• log_visitor
• log_visitor_info
• 登录网址
• log_url_info
• 日志引用
• report_viewed_product_index
• report_compared_product_index
• 报告事件
• catalog_compare_item

我认为这在 mysql 中很容易完成，但我找不到直接的答案/解决方案。

*更新*
根据@Ollie Jones 的说法，如果没有 STORE PROCEDURE 或服务器端语言(PHP 或其他语言)是不可能做到的

更新 1
我选择遵循 PHP 方法 (@samitha) 有两个原因:

1. STORE PROCEDURE 看起来更复杂
2. 对“information_schema”表的查询非常慢(至少在您有很多 DB/TABLES 的情况下)

Answer 1

  SELECT DISTINCT SCHEMA_NAME AS `database`
    FROM information_schema.SCHEMATA
   WHERE  SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql')
   ORDER BY SCHEMA_NAME

为您提供系统上所有非 MYSQL 数据库的列表。

  SELECT TABLE_SCHEMA AS `database`,
         TABLE_NAME AS `table`
    FROM information_schema.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
   ORDER BY TABLE_SCHEMA, TABLE_NAME

获取所有数据库中所有实际表(不包括系统 View ，如 TABLES 表和用户定义的 View )的列表。

然后，您应该在您的程序中实现逻辑，以确保对于每个数据库，在您截断某些表之前，它确实是一个 Magento 数据库。否则，你可能会成为同事中看不起的人。 :-)

编辑

这是一个存储过程。

您需要对其进行编辑以完全按照您的需要进行操作；特别是，它计算行数而不是截断表，并且它不包含正确的日志表列表。 (我发布这样一个具有破坏性的存储过程是不负责任的，你应该自己编辑它来完成破坏性的部分。)

DELIMITER $$
DROP PROCEDURE IF EXISTS `zap_magento_logs`$$

CREATE PROCEDURE `zap_magento_logs`()
BEGIN

    -- declare variables for database and table names
    DECLARE dbname VARCHAR(128) DEFAULT '';
    DECLARE tbname VARCHAR(128) DEFAULT '';

    DECLARE done INTEGER DEFAULT 0;

    -- declare cursor for list of log tables
    DECLARE log_table_list CURSOR FOR 
      SELECT TABLE_SCHEMA AS `database`,
             TABLE_NAME AS `table`
        FROM `information_schema`.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE'
         AND TABLE_NAME IN 
         (
            'log_customer',
        'log_visitor',
        'log_visitor_info',
        'log_url',
        'log_url_info',
        'log_quote'
         )
       ORDER BY TABLE_SCHEMA, TABLE_NAME;

    -- declare NOT FOUND handler
        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET done = 1;

    OPEN log_table_list;

    log_table: LOOP

        FETCH log_table_list INTO dbname, tbname;

        IF done = 1 THEN
        LEAVE log_table;
        END IF;

        -- create an appropriate text string for a DDL or other SQL statement
        SET @s = CONCAT('SELECT COUNT(*) AS num FROM  ',dbname,'.',tbname);
        PREPARE stmt FROM @s;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt; 
    END LOOP    log_table;
    CLOSE log_table_list;

END$$

DELIMITER ;

您可以通过发出 SQL 命令来运行它

  CALL zap_magento_logs();