一个MYSQL的行转列的存储过程

最新推荐文章于 2024-07-22 03:08:20 发布

iteye_8257

最新推荐文章于 2024-07-22 03:08:20 发布

阅读量212

点赞数

分类专栏：数据库-MYSQL 文章标签： MySQL J#

数据库-MYSQL 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个MYSQL存储过程的示例代码，该过程用于将指定表的数据和结构转换为另一种形式，并展示了如何通过动态SQL语句创建表和插入数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MYSQL 存储过程代码：

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); -- Get the column number of the table select count(1) from information_schema.columns where table_schema=$schema_name and table_name=$table_name into cnt; -- Get the row number of the table select table_rows from information_schema.tables where table_schema = $schema_name and table_name=$table_name into $table_rows; -- Check whether the table exists or not drop table if exists test.temp; create table if not exists test.temp (`1` varchar(255) not null); -- loop1 start 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; -- loop1 end; set s = 0; -- loop2 start loop2:loop -- leave loop2 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 start 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); -- insert new data into table 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 ;

以下是测试结果：
======
select * from a;
select * from b;
select * from salary;

call sp_row_column_wrap('test','a');
select * from test.temp;
call sp_row_column_wrap('test','b');
select * from test.temp;
call sp_row_column_wrap('test','salary');
select * from test.temp;