oracle integer mysql,oracle转Mysql数据库 字段类型 INTEGER（orcle） 转化mysql变为 （decimal） 解决办法...

oracle转Mysql数据库 字段类型 INTEGER(orcle) 转化mysql变为 (decimal) 解决办法

我们需要把decimal 的列批量变为int型 ，那么一个mysql存储过程就可以搞定。

CREATE DEFINER=`zwb`@`%` PROCEDURE `batch_alter_tables_columntype`(

dbname varchar(50),

from_type varchar(32),

to_type varchar(32)

)

begin

declare tbl_name varchar(128);

declare parent_done int(1) default 0;

declare parent_cursor cursor for select table_name from information_schema.`TABLES` where table_type=’BASE TABLE’ and table_schema=dbname;

declare continue handler for not found set parent_done=1;

open parent_cursor;

parent_loop:loop fetch parent_cursor into tbl_name;

if parent_done=1 then leave parent_loop;

end if;

begin

declare col_name varchar(128);

declare son_done int(1) default 0;

declare sql_for_alter varchar(1024);

declare son_cursor cursor for select column_name from information_schema.`COLUMNS` where CONVERT(table_name using utf8) collate utf8_bin=convert(tbl_name using utf8) and convert(data_type using utf8) collate utf8_bin in (from_type) and table_schema=dbname;

declare continue handler for not found set son_done=1;

open son_cursor;

son_loop:loop fetch son_cursor into col_name;

if son_done=1 then leave son_loop;

end if;

set sql_for_alter=concat(“alter table “,tbl_name,” modify column “,col_name,” “,to_type);

set @sql=sql_for_alter;

prepare stmt from @sql;

execute stmt;

set son_done=0;

end loop son_loop;

close son_cursor;

end;

set parent_done=0;

end loop parent_loop;

close parent_cursor;

end