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
为您提供所有实际表的列表(不包括系统视图,例如TABLES
表和用户定义的视图)在所有数据库中。
然后,您应该在程序中实现逻辑,以确保在截断某些表之前,对于每个数据库,它确实是 Magento 数据库。否则,你可能会成为同事中被鄙视的人。 :-)
Edit
这是一个存储过程。
您需要编辑它以准确地执行您需要它执行的操作;特别是,它计算行而不是截断表,并且它不包含正确的日志表列表。 (发布如此具有破坏性的存储过程对我来说是不负责任的;您应该自己编辑它来完成破坏性的部分。)
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();