如果我们要为所有的表添加同一个字段,比如增加数据的创建时间。如果一个一个表的去添加,那真是太麻烦了,下面我们利用游标和alter table语句,一次性为数据量中所有表添加创建时间CreateDate字段,并为该字段添加默认值为当前时间。
sql server语句如下:
declare @tablename varchar(50)
declare @sql varchar(1000)
declare cu_adcolumn cursor for select name from sys.tables
open cu_adcolumn
fetch next from cu_adcolumn into @tablename
while(@@fetch_status=0)
begin
set @sql = 'alter table '+@tablename+' add CreateDate datetime default(getdate())'
execute(@sql)
fetch next from cu_adcolumn into @tablename
end
close cu_adcolumn
deallocate cu_adcolumn
declare @sql varchar(1000)
declare cu_adcolumn cursor for select name from sys.tables
open cu_adcolumn
fetch next from cu_adcolumn into @tablename
while(@@fetch_status=0)
begin
set @sql = 'alter table '+@tablename+' add CreateDate datetime default(getdate())'
execute(@sql)
fetch next from cu_adcolumn into @tablename
end
close cu_adcolumn
deallocate cu_adcolumn
下面一个是自己写的mysql批量 但是报 [Err] 1146 - Table 'xx' doesn't exist
CREATE PROCEDURE p1 ()
BEGIN
declare tablename varchar(50);
declare i int;
set i = 0;
while i <= 23 do
select TABLE_NAME into tablename from information_schema.tables t where t.table_schema='qxg' limit i,1;
set i = i+1 ;
alter table tablename add created_at datetime;
end while;
END;
最后苦于无奈用java写了个简单的批量添加字段
public void x() {
List<String> list = new ArrayList<String>();
String sql = "select t.table_name from information_schema.tables t where t.table_schema='databaseName'";
try {
conn = getConnection();
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
list.add(rs.getString(1));
System.out.println(rs.getString(1));
}
for (int i = 0; i < list.size(); i++) {
String sqlStr = "alter table "+list.get(i)+" add created_at datetime , add updated_at datetime";
psmt = conn.prepareStatement(sqlStr);
psmt.executeUpdate();
}
} catch (Exception e) {
}
System.out.println("----");
}