5以上可打开 SELECT A.`TABLE_NAME` FROM `information_schema`.`TABLES` A WHERE A.`TABLE_TYPE`='TABLE' AND A.`TABLE_SCHEMA`='DATABASENAME'游标循环->取得表名->修改结构
SELECT concat('alter table ',A.`TABLE_NAME` ,' add column a int;') FROM `information_schema`.`TABLES` A WHERE A.`TABLE_TYPE`='TABLE' AND A.`TABLE_SCHEMA`='DATABASENAME'
BEGIN DECLARE I_last_row int DEFAULT 0; declare I_dept_id VARCHAR(255); declare c_dept CURSOR FOR SELECT A.TABLE_NAME FROM information_schema.TABLES A WHERE A.TABLE_TYPE='BASE TABLE' AND A.TABLE_SCHEMA='sq'; DECLARE CONTINUE HANDLER for NOT FOUND SET I_last_row=1; open c_dept; c_cursor:LOOP FETCH c_dept INTO I_dept_id; IF(I_last_row=1)THEN LEAVE c_cursor; END IF; alter table ●●●●I_dept_id●●●●● add sssssssssss char(200); END LOOP c_cursor; CLOSE c_dept;END写完了,最后再添加字段的时候报错 应该怎么调用变量啊?●●●●I_dept_id●●●●存储的是表名
set @asql=concat('alter table ',I_dept_id,' add sssssssssss char(200) '); prepare stml from @asql; execute stml;
谢了,执行SQL语句必须用 prepare 懂了,结贴
如果是一次性的活,则可以直接 show tables得到所有表名,然后复制到EXCEL中,再利用公式生成 alter table xxx1 add column x int; alter table xxx2 add column x int; ... alter table xxxN add column x int; 这样的字符串,再一次性贴到命令工具中执行。
SELECT A.`TABLE_NAME` FROM `information_schema`.`TABLES` A WHERE A.`TABLE_TYPE`='TABLE' AND A.`TABLE_SCHEMA`='DATABASENAME'游标循环->取得表名->修改结构
FROM `information_schema`.`TABLES` A
WHERE A.`TABLE_TYPE`='TABLE' AND A.`TABLE_SCHEMA`='DATABASENAME'
DECLARE I_last_row int DEFAULT 0;
declare I_dept_id VARCHAR(255);
declare c_dept CURSOR FOR
SELECT A.TABLE_NAME FROM information_schema.TABLES A WHERE A.TABLE_TYPE='BASE TABLE' AND A.TABLE_SCHEMA='sq';
DECLARE CONTINUE HANDLER for NOT FOUND SET I_last_row=1;
open c_dept;
c_cursor:LOOP
FETCH c_dept INTO I_dept_id;
IF(I_last_row=1)THEN
LEAVE c_cursor;
END IF;
alter table ●●●●I_dept_id●●●●● add sssssssssss char(200);
END LOOP c_cursor;
CLOSE c_dept;END写完了,最后再添加字段的时候报错 应该怎么调用变量啊?●●●●I_dept_id●●●●存储的是表名
prepare stml from @asql;
execute stml;
懂了,结贴
alter table xxx1 add column x int;
alter table xxx2 add column x int;
...
alter table xxxN add column x int; 这样的字符串,再一次性贴到命令工具中执行。