An existing database, there are 300 table, the table name is not the same, are the innodb storage engine, the table structure is the same, will now give each table in the same field to add a general index, consult,

CodePudding user response:

DROP PROCEDURE IF the EXISTS BIGDATA_ADD_INDEX;
DELIMITER $$
CREATE PROCEDURE BIGDATA_ADD_INDEX ()
The BEGIN

DECLARE the command VARCHAR (200);

DECLARE founded INT the DEFAULT 1;

DECLARE cur_sleest CURSOR FOR
SELECT CONCAT (" ALTER TABLE ", TABLE_NAME, "ADD INDEX idx_name (cloumn);" ) AS SQL_COMMAND
The FROM INFORMATION_SCHEMA. TABLES
WHERE TABLE_SCHEMA="schema_name"
AND TABLE_TYPE="BASE TABLE";

DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET founded=0;

The OPEN cur_sleest;

Sleet_loop: LOOP
The FETCH cur_sleest INTO the command;
IF founded=0 THEN LEAVE sleet_loop; END IF;
The SET @ SQL:=command;
PREPARE STMT FROM @ SQL;
The EXECUTE STMT.
DEALLOCATE PREPARE STMT.
END LOOP sleet_loop;

The CLOSE cur_sleest;
END $$
DELIMITER ;

CodePudding user response:

The command line

Mysql library name - skip column_names - e "SELECT CONCAT (' the 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 library name