DELIMITER $
DROP PROCEDURE IF EXISTS `test`.`sp_row_column_wrap`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_row_column_wrap`(IN $schema_name varchar(64),
IN $table_name varchar(64))
BEGIN
declare cnt int(11);
declare $table_rows int(11);
declare i int(11);
declare j int(11);
declare s int(11);
declare str varchar(255);
select count(1) from information_schema.columns where table_schema=$schema_name and table_name=$table_name into cnt;
select table_rows from information_schema.tables where table_schema = $schema_name and table_name=$table_name into $table_rows;
drop table if exists test.temp;
create table if not exists test.temp (`1` varchar(255) not null);
set i = 0;
loop1:loop
if i = $table_rows-1 then
leave loop1;
end if;
set @stmt1 = concat('alter table test.temp add `',i+2,'` varchar(255) not null');
prepare s1 from @stmt1;
execute s1;
deallocate prepare s1;
set i = i + 1;
end loop loop1;
set s = 0;
loop2:loop
if s=cnt then
leave loop2;
end if;
set @stmt2 = concat('select column_name from information_schema.columns where table_schema="',$schema_name,
'" and table_name="',$table_name,'" limit ',s,',1 into @temp;');
prepare s2 from @stmt2;
execute s2;
deallocate prepare s2;
set j=0;
set str = ' select ';
loop3:loop
if j = $table_rows then
leave loop3;
end if;
set @stmt3 = concat('select ',@temp,' from ',$schema_name,'.',$table_name,' limit ',j,',1 into @temp2;');
prepare s3 from @stmt3;
execute s3;
set str = concat(str,'"',@temp2,'"',',');
deallocate prepare s3;
set j = j+1;
end loop loop3;
set str = left(str,length(str)-1);
set @stmt4 = concat('insert into test.temp',str,';');
prepare s4 from @stmt4;
execute s4;
deallocate prepare s4;
set s=s+1;
end loop loop2;
END$
DELIMITER ;
参考资源:
http://www.javaeye.com/topic/134703
http://www.ajaxstu.com/MySQLshujuku/233482.html