我有一组名为results_%的表,都具有相同的结构.
我想为这个表添加一个索引.
我可以将每个表的alter语句作为一行select查询结果获取,但我不知道如何执行这些语句:
select concat( 'alter table ', test_db.table_name, ' add index `did` (`did`);' ) as statement
from information_schema.tables test_db
where test_db.table_name like 'results_%';
我错过了什么?
输出(我想执行而不是只显示给我):
+---------------------------------------------------------+
| statement |
+---------------------------------------------------------+
| alter table results_Em7777_spa add index `did` (`did`); |
| alter table results_KaEng_eng add index `did` (`did`); |
| alter table results_Ka_spa add index `did` (`did`); |
| alter table results_Mc_spa add index `did` (`did`); |
| alter table results_Mo_eng add index `did` (`did`); |
| alter table results_Pe_eng add index `did` (`did`); |
| alter table results_SU_spa add index `did` (`did`); |
| alter table results_Ta_spa add index `did` (`did`); |
| alter table results_ba_eng add index `did` (`did`); |
| alter table results_br_eng add index `did` (`did`); |
| alter table results_ca_spa add index `did` (`did`); |
| alter table results_ch_spa add index `did` (`did`); |
| alter table results_da_spa add index `did` (`did`); |
| alter table results_ga_eng add index `did` (`did`); |
| alter table results_ge_spa add index `did` (`did`); |
| alter table results_gk_eng add index `did` (`did`); |
+---------------------------------------------------------+
16 rows in set (0.00 sec)
[编辑]
我试过了:
drop procedure if exists altlike;
delimiter //
create procedure altlike()
begin
set group_concat_max_len = 65535;
select @altrlk:= concat( 'alter table ', test_db.table_name , ' add index `did` (`did`);' )
from information_schema.tables test_db
where test_db.table_name like "results_%";
prepare statement from @altrlk;
execute statement;
end //
delimiter ;
call altlike();
但仍然没有运气:它只会改变最后匹配的表格(results_gk_eng).
解决方法:
你基本上是从数据库中打出字符串行,它不会自动执行它只是因为它看起来像一个sql语句;
您可以做的是使用编程语言逐行执行,然后返回结果.
或者将其抛入一个存储过程,在该过程中它会提供一个辅助执行块.
DELIMITER //
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)
BEGIN
DECLARE a, b, c INT;
DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur1;
SET b = 0;
SET c = 0;
WHILE b = 0 DO
FETCH cur1 INTO a;
IF b = 0 THEN
SET c = c + a;
END IF;
END WHILE;
CLOSE cur1;
SET param1 = c;
END //
标签:mysql,concat,procedure
来源: https://codeday.me/bug/20190613/1233735.html