現有一個資料庫，有300張表，表名不一樣，都是innodb存盤引擎，表結構都是一樣的。現在要給每張表的同一欄位加一個普通索引，求教。
uj5u.com熱心網友回復：
DROP PROCEDURE IF EXISTS BIGDATA_ADD_INDEX;DELIMITER $$
CREATE PROCEDURE BIGDATA_ADD_INDEX()
BEGIN
DECLARE command VARCHAR(200);
DECLARE founded INT DEFAULT 1;
DECLARE cur_sleest CURSOR FOR
SELECT CONCAT("ALTER TABLE ", TABLE_NAME, " ADD INDEX idx_name(cloumn);") AS SQL_COMMAND
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="schema_name"
AND TABLE_TYPE="BASE TABLE";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET founded = 0;
OPEN cur_sleest;
sleet_loop : LOOP
FETCH cur_sleest INTO command;
IF founded = 0 THEN LEAVE sleet_loop; END IF;
SET @sql := command;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP sleet_loop;
CLOSE cur_sleest;
END$$
DELIMITER ;
uj5u.com熱心網友回復：
命令列mysql 庫名 --skip-column_names -e "SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ADD INDEX idx_name(cloumn);') AS SQL_COMMAND FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_TYPE='BASE TABLE'" | mysql 庫名
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/84059.html
標籤：MySQL
上一篇：FindWindowEX 找不到 Citrix 程式的子視窗
下一篇：Mysql陳述句