html自动生成工具_MySQL自动化生成HTML页面(导出数据)极限SQL编程

今日客户要求表内的数据依据某种分组生成HTML页面进行展示，一般处理这种需求直接上编程工具就好了，从数据库里读取数据，根据规则生成字符串，最后将字符串写出到文件。由于需求比较急，作为数据库编程系列文章，如果能用SQL实现首选还是SQL，这样处理既直接又快速，不过针对SQL要真的有耐心和信心写完，调试更是崩溃。由于要写出文件到硬盘，最后还是选择MySQL作为数据库工具，Navicat作为开发工具。

有两张表计划表、市县表，二者依靠市县编码(sxbm)进行等值连接，计划表内含有各个学校投放在各个市县的专业代号(zydh)，专业名称(zymc)、招生备注(bz)、学制(xz)、要求的学历(xl)、计划数(jh)等字段组成的计划信息，院校编码(yxbm)为学校的两位数编码，院校代号(yxdh)为院校编码(yxbm)+市县编码(sxbm)组成的四位数编码，院校代号其实可以区分出学校在哪个市县的投档的专业计划。要求以学校为单位创建HTML页面，页面首先要以市县作为表格分割，然后根据专业代号排序。具体实现过程如下：

创建计划表：

CREATE TABLE `zzjh2019v` (`YXDH` varchar(9) COMMENT '学校代号',`YXMC` varchar(54) COMMENT '学校名称',`ZYDH` varchar(2) COMMENT '专业代号',`ZYMC` varchar(28) COMMENT '专业名称',`XZ` varchar(3) COMMENT '学制',`XL` varchar(4) COMMENT '学历',`JH` varchar(6) COMMENT '招生计划数',`BZ` varchar(200) COMMENT '备注',`yxbm` char(2) COMMENT '学校编码',`sxbm` char(2) COMMENT '市县编码') ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

创建市县编码表：

CREATE TABLE `sx` (`sxbm` char(2) COMMENT '市县编码',`sxmc` varchar(20) COMMENT '市县名称') ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact;

纠结了很久这个东西怎么写，最后采取游标、拼接字符串、字符串聚合，动态SQL，写文件等一些列操作完成需求，创建的存储过程如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `splitjh`()BEGINdeclare done INT DEFAULT 0;declare pyxbm char(2);declare psxmc varchar(10);declare pyxmc varchar(50);declare pjhall int;declare pjhrows TEXT;declare yxjh cursorforselect yxbm,yxmc,sum(jh) jhall from zzjh2019v a,sx b where a.sxbm=b.sxbm group by yxbm,yxmc order by yxbm;declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;open yxjh;fetch yxjh into pyxbm,pyxmc,pjhall;while done !=1 doselect group_concat(jhrow separator '') into pjhrows from(select concat('',yxdh,'',yxmc,'在  招生计划如下',sum(jh),'',group_concat('',zydh,'',zymc,'(',bz,')',jh,'',xz,'',xl,'' order by zydh separator '')) jhrowfrom zzjh2019v a,sx b where yxbm=pyxbm and a.sxbm=b.sxbm group by yxdh order by yxdh,zydh) jhs;set @pfilename = concat('''d:/32/1/1/jh11',pyxbm,'.html''');set @sql =concat('select concat(''3+2计划

代号	专业及名称备注	人数	学制	学历

首先看效果，执行过程

call splitjh();

在磁盘形成的HTML文件效果如下图(数据有一定的敏感性，进行了遮挡处理)：

文件展示页面

生成的文件列表如下图：

生成的文件列表

这里一共有87所学校，所以生成了87的文件，添加CSS样式文件，让表格呈现如前图所示。

技术点

1)MySQL的游标，以及循环读取游标的方法，涉及的语句如下：

declare yxjh cursorforselect yxbm,yxmc,sum(jh) jhall from zzjh2019v a,sx b where a.sxbm=b.sxbm group by yxbm,yxmc order by yxbm;#游标定义declare CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;#游标循环条件，注意此句一定要定义在游标之后，才起作用open yxjh;#打开游标fetch yxjh into pyxbm,pyxmc,pjhall;#将游标行内容赋值给变量。

2)执行动态SQL，由于MySQL into outfile 后接的文件名不能为变量，所以必须使用动态SQL的方法，涉及的语句如下：

prepare execsql from @sql;#从一个变量准备一个动态sql，注意execsql不用提前定义execute execsql;#执行准备好的语句DEALLOCATE PREPARE execsql;#销毁语句

综上就是使用MySQL数据库，并借用MySQL写文件的方式将数据从数据库内按照需求导出文件，为何不用navicat导出呢？因为无法达到要求，又是聚合、又是格式，所以只能自己编写过程通过SQL语句拼接字符串的方式来实现。没有太多的技术难度，主要是想法和调试难度。后续在此基础上又开发了以市县为单位创建HTML文件，各招生学校作为分割的过程。本案例是实际需求催生出来的做法，在遇到这样的需求前你是先想到SQL还是先想到开发工具呢？从实际效果看使用SQL这种方式更加灵活。这样的SQL实现的字符串拼接是不是有点极限呢？