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編程

創建計劃表：

創建市縣編碼表：

您的項目需求