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`()
BEGIN
declare done INT DEFAULT 0;
declare pyxbm char(2);
declare psxmc varchar(10);
declare pyxmc varchar(50);
declare pjhall int;
declare pjhrows TEXT;
declare yxjh cursor
for
select 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 do
select group_concat(jhrow separator '') into pjhrows from
(select concat('<tr class="subtitle"><td>',yxdh,'</td><td>',yxmc,'在 <span><font color="red">',b.sxmc,'</font></span> 招生计划如下</td><td>',sum(jh),'</td><td></td><td></td></tr>',group_concat('<tr class="jhrow"><td>',zydh,'</td><td>',zymc,'(',bz,')</td><td>',jh,'</td><td>',xz,'</td><td>',xl,'</td></tr>' order by zydh separator '')) jhrow
from 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(''<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><link rel="stylesheet" type="text/css" href="zsjh.css" ><title>3+2计划</title></head><body><h3></h3><table><tr class="subtitle"><th>代号</th><th>专业及名称备注</th><th>人数</th><th>学制</th><th>学历</th></tr>'',''',pjhrows,''',''</body></html>'') from dual into outfile ',@pfilename);
prepare execsql from @sql;
execute execsql;
DEALLOCATE PREPARE execsql;
fetch yxjh into pyxbm,pyxmc,pjhall;
end while;
close yxjh;
END;

首先看效果，执行过程

call splitjh();

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

文件展示页面

生成的文件列表如下图：

生成的文件列表

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

技术点

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

declare yxjh cursor
for
select 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实现的字符串拼接是不是有点极限呢？

文：https://www.enmotech.com/web/detail/1/838/1.html （复制链接，打开浏览器即可查看）

北冥有 Data，其名为鲲，鲲之大，一个 MySQL 放不下。千万量级的数据，用 MySQL 要怎么存？

初学者在看到这个问题的时候，可能首先想到的是 MySQL 一张表到底能存放多少条数据？

根据 MySQL 官方文档的介绍，MySQL 理论上限是 (232)2 条数据，然而实际操作中，往往还受限于下面两条因素：

1. myisamdatapointersize，MySQL 的 myisamdatapointersize 一般默认是 6，即 48 位，那么对应的行数就是 248-1。

2. 表的存储大小 256TB

那有人会说，只要我的数据大小不超过上限，数据行数也不超过上限，是不是就没有问题了？其实不尽然。

在实际项目中，一般没有哪个项目真的触发到 MySQL 数据的上限了，因为当数据量变大了之后，查询速度会慢的吓人，而一般这个时候，你的数据量离 MySQL 的理论上限还远着呢！

传统的企业应用一般数据量都不大，数据也都比较容易处理，但是在互联网项目中，上千万、上亿的数据量并不鲜见。在这种时候，还要保证数据库的操作效率，我们就不得不考虑数据库的分库分表了。

那么接下来就和大家简单聊一聊数据库分库分表的问题。

数据库切分

看这个名字就知道，就是把一个数据库切分成 N 多个数据库，然后存放在不同的数据库实例上面，这样做有两个好处：

1. 降低单台数据库实例的负载

2. 可以方便的实现对数据库的扩容

一般来说，数据库的切分有两种不同的切分规则：

1. 水平切分

2. 垂直切分

接下来我们就对这两种不同的切分规则分别进行介绍。

水平切分

先来一张简单的示意图，大家感受一下什么是水平切分：

假设我的 DB 中有 table-1、table-2 以及 table-3 三张表，水平切分就是拿着我的绝世好剑，对准黑色的线条，砍一剑或者砍 N 剑！

砍完之后，将砍掉的部分放到另外一个数据库实例中，变成下面这样：

这样，原本放在一个 DB 中的 table 现在放在两个 DB 中了，观察之后我们发现：

1. 两个 DB 中表的个数都是完整的，就是原来 DB 中有几张表，现在还是几张。

2. 每张表中的数据是不完整的，数据被拆分到了不同的 DB 中去了。

这就是数据库的水平切分，也可以理解为按照数据行进行切分，即按照表中某个字段的某种规则来将表数据分散到多个库之中，每个表中包含一部分数据。

这里的某种规则都包含哪些规则呢？这就涉及到数据库的分片规则问题了，这个松哥在后面的文章中也会和大家一一展开详述。这里先简单说几个常见的分片规则：

1. 按照日期划分：不容日期的数据存放到不同的数据库中。

2. 对 ID 取模：对表中的 ID 字段进行取模运算，根据取模结果将数据保存到不同的实例中。

3. 使用一致性哈希算法进行切分。

详细的用法，将在后面的文章中和大家仔细说。

垂直切分

先来一张简单的示意图，大家感受一下垂直切分：

所谓的垂直切分就是拿着我的屠龙刀，对准了黑色的线条砍。砍完之后，将不同的表放到不同的数据库实例中去，变成下面这个样子：

这个时候我们发现如下几个特点：

1. 每一个数据库实例中的表的数量都是不完整的。

2. 每一个数据库实例中表的数据是完整的。

这就是垂直切分。一般来说，垂直切分我们可以按照业务来划分，不同业务的表放到不同的数据库实例中。

老实说，在实际项目中，数据库垂直切分并不是一件容易的事，因为表之间往往存在着复杂的跨库 JOIN 问题，那么这个时候如何取舍，就要考验架构师的水平了！

优缺点分析

通过上面的介绍，相信大家对于水平切分和垂直切分已经有所了解，优缺点其实也很明显了，松哥再来和大家总结一下。

水平切分

· 优点

水平切分最大的优势在于数据库的扩展性好，提前选好切分规则，数据库后期可以非常方便的进行扩容。

有效提高了数据库稳定性和系统的负载能力。拆分规则抽象好， join 操作基本可以数据库做。

· 缺点

水平切分后，分片事务一致性不容易解决。

拆分规则不易抽象，对架构师水平要求很高。

跨库 join 性能较差。

垂直切分

· 优点

一般按照业务拆分，拆分后业务清晰，可以结合微服务一起食用。

系统之间整合或扩展相对要容易很多。

数据维护相对简单。

· 缺点

最大的问题在于存在单库性能瓶颈，数据表扩展不易。

跨库 join 不易。

事务处理复杂。

结语

虽然 MySQL 中数据存储的理论上限比较高，但是在实际开发中我们不会等到数据存不下的时候才去考虑分库分表问题，因为在那之前，你就会明显的感觉到数据库的各项性能在下降，就要开始考虑分库分表了。

想了解更多数据库、云技术的内容吗？

快来关注“数据和云”公众号、“云和恩墨”官方网站，我们期待大家一同学习和进步！

数据和云小程序“DBASK”在线问答随时解惑，欢迎了解和关注。

client]

port = 3306

[mysqld]

#默认存储引擎INNODB

default-storage-engine=INNODB

#GROUP_CONCAT长度

group_concat_max_len =99999

#端口号

port = 3306

#套接字文件

#这里要注意：有时候重启mysql会提示/tmp/mysql.sock不存在，此时通常会由于两种情况导致，解决方法可以参考我之前记录的文章，亲测有效：https://www.cnblogs.com/zhangweizhong/p/12179452.html

socket = /usr/local/mysql/mysql.sock

#pid写入文件位置

pid-file = /usr/local/mysql/mysqld.pid

#数据库文件位置

datadir = /home/data/mysql/data

#控制文件打开的个数；

open_files_limit = 10240

#当外部锁定（external-locking）起作用时，每个进程若要访问数据表,

#则必须等待之前的进程完成操作并解除锁定。由于服务器访问数据表时经常需要等待解锁,

skip-external-locking

#跳过DNS反向解析

skip-name-resolve

#关闭TIMESTAMP类型默认值

explicit_defaults_for_timestamp

#不受client字符集影响，保证sever端字符集

skip-character-set-client-handshake

#初始连接字符集UTF8

init-connect='SET NAMES utf8'

#默认数据库字符集

character-set-server=utf8

#查询缓存0,1,2，分别代表了off、on、demand

query_cache_type = 1

#单位秒,握手时间超过connect_timeout,连接请求将会被拒绝

connect_timeout = 20

#设置在多少秒没收到主库传来的Binary Logs events之后,从库认为网络超时,Slave IO线程会重新连接主库。

#该参数的默认值是3600s

slave_net_timeout = 30

#这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，

log-slave-updates=1

#用于slave服务器，io线程会把server id与自己相同的event写入日志，与log-slave-updates选项冲突

replicate-same-server-id=0

server_id=10112879101

# 打开二进制日志功能.

log-bin =/home/data/mysql/binlog/mysql-bin.log

#relay-log日志

relay-log=mysql-relay-bin

master-info-repository=TABLE

relay-log-info-repository=TABLE

#不写入binlog二进制日志中的数据库

binlog-ignore-db=mysql # No sync databases

binlog-ignore-db=test # No sync databases

binlog-ignore-db=information_schema # No sync databases

binlog-ignore-db=performance_schema # No sync databases

#写入binlog二进制日志中数据库

binlog-do-db=business_db

binlog-do-db=user_db

binlog-do-db=plocc_system

#清理binlog

expire-logs-days=15

max_binlog_size = 1073741824 # Bin logs size ( 1G )

#使binlog在每1000次binlog写入后与硬盘同步

sync_binlog = 1000

#指定只复制哪个库的数据

replicate-do-db=business_db

replicate-do-db=user_db

replicate-do-db=plocc_system

#开启事件调度器Event Scheduler

event_scheduler=1

#MySQL能暂存的连接数量。

back_log = 500

#MySQL允许最大的进程连接数，

max_connections = 6000

#每个用户的最大的进程连接数

max_user_connection = 3000

#每个客户端连接请求异常中断的最大次数

max_connect_errors = 6000

#表调整缓冲区大小。

table_cache = 614

#表描述符缓存大小，可减少文件打开/关闭次数

table_open_cache = 2048

#设置在网络传输中一次消息传输量的最大值。

max_allowed_packet = 64M

# 在一个事务中binlog为了记录SQL状态所持有的cache大小

binlog_cache_size = 1M

# 独立的内存表所允许的最大容量.

max_heap_table_size = 256M

#Sort_Buffer_Size被用来处理类似ORDER BY以及GROUP

sort_buffer_size = 8M

#用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

join_buffer_size = 8M

#thread_cache_size表示可以重新利用保存在缓存中线程的数量

thread_cache_size = 128

#此值表示允许应用程序在同一时间运行的线程的数量.

thread_concurrency = 8

#此值用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果

query_cache_size = 64M

#指定单个查询能够使用的缓冲区大小

query_cache_limit = 2M

#被全文检索索引的最小的字长

ft_min_word_len = 4

#设置MYSQL线程使用的堆大小

thread_stack = 192K

#设定默认的事务隔离级别.可用的级别如下:

#READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_isolation = READ-COMMITTED

#此值表示内存中临时表的最大大小

tmp_table_size = 256M

#binlog日志类型

#mixed：混合型

binlog_format=mixed

#开启慢查询日志

slow_query_log

#文件格式

log_output = FILE

# 所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询

long_query_time = 0.5

#慢查询日志位置

slow_query_log_file=/usr/local/mysql/mysqld_slow.log

########MyISAM 相关选项

#用于索引的缓冲区大小

key_buffer_size = 2048M

#MySql读入缓冲区大小。

read_buffer_size = 2M

#MySql的随机读（查询操作）缓冲区大小。

read_rnd_buffer_size = 16M

#批量插入数据缓存大小，

bulk_insert_buffer_size = 16M

#MyISAM表发生变化,重建索引时所需的缓冲

myisam_sort_buffer_size = 128M

#MySQL重建索引时所允许的临时文件的大小

myisam_max_sort_file_size = 1G

#如果一个表拥有多个索引, MyISAM 会通过并行排序使用多个线程去修复他们。

myisam_repair_threads = 1

# 自动检查和修复没有适当关闭的 MyISAM 表.

myisam_recover

########INNODB相关选项

#如果你的MySQL服务包含InnoDB支持但是并不打算使用的话,

#skip-innodb

#这对Innodb表来说非常重要，Innodb把所有的数据和索引都缓存起来，此参数设置越大，数据存取时所需要的磁盘I/O越少。

innodb_buffer_pool_size = 2048M

# InnoDB 将数据保存在一个或者多个数据文件中成为表空间

innodb_data_file_path = ibdata1:1024M:autoextend

# 文件IO的线程数，一般为 4

innodb_file_io_threads = 4

# 允许线程数量。

innodb_thread_concurrency = 16

# 如果设置为1 ,InnoDB会在每次事务提交后将事务日志写到磁盘上,

# 基于性能考虑，可以设置为0或2，但要承担在发生故障时丢失数据的风险。

# 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.

# 2代表每次提交后日志写入日志文件,但是日志文件每秒刷新到磁盘上。

innodb_flush_log_at_trx_commit = 2

#此参数用于写日志文件所用的内存大小，以M为单位。

innodb_log_buffer_size = 16M

#此参数用于确定日志文件的大小

innodb_log_file_size = 1024M

#日志组中的文件总数

innodb_log_files_in_group = 3

# InnoDB的日志文件所在位置

#innodb_log_group_home_dir

# 在InnoDB缓冲池中最大允许的脏页面的比例

innodb_max_dirty_pages_pct = 90

# InnoDB用来刷新日志的方法

innodb_flush_method=O_DSYNC

# 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久

innodb_lock_wait_timeout = 30

[mysqldump]

# 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项

max_allowed_packet = 64M

[mysql]

no-auto-rehash

#指定一个请求的最大连接时间，对于4GB左右的内存服务器来说，可以将其设置为5-10。

wait_timeout = 10

#将没有使用索引的查询也记录下来

log-queries-not-using-indexes

在线咨询

上一篇：零基础学习HTML背景表单单选按钮复选框上传文件下拉列表
下一篇：HTML5网页设计制作作业-环境保护组织HTML5网站模板

您的项目需求

*请认真填写需求信息，我们会在24小时内与您取得联系。

整合营销服务商

MySQL自动化生成HTML页面(导出数据)极限SQL编程

创建计划表：

创建市县编码表：

您的项目需求