如何用原生NodeJS实现HTML表格数据随MySQL自动更新

数据不刷新是代码逻辑存在本质缺陷：

• 数据库查询仅在read_html.js模块被Node.js首次加载时执行1次，查询得到的rows结果被闭包永久缓存，后续所有调用read_content()的操作都只会复用这份进程启动时的旧数据，不会重新访问数据库获取最新内容。
• 代码重复创建了2个独立的MySQL连接，分别存在于两个文件中，属于不必要的资源浪费。
• 原有POST表单提交逻辑存在SQL注入风险，且插入数据后没有给客户端返回响应，会导致请求挂起。
• 数据库查询是异步IO操作，原有代码用同步返回的方式写逻辑，本身就不符合Node.js的异步执行模型。

第一步：重构read_html.js #

移除模块内独立的数据库连接创建、模块加载时的一次性查询逻辑，将查询动作放到导出函数内部，每次调用都实时查库，通过回调返回拼接好的最新HTML：

// read_html.js
exports.read_content = function compose_html(dbCon, callback) {
  // 每次调用都执行实时查询，获取最新表数据
  dbCon.query('SELECT * FROM customers.customers', function(err, rows) {
    if (err) return callback(err);
    const htmlContent = '<!Doctype html>' +
      '<html lang="fr">' +
      '<head>' +
      '<title>Read</title>' +
      '<meta charset="utf-8">' +
      '<style>' +
      'html{font-family: Arial, Helvetica, sans-serif;}' +
      'a{color:cornflowerblue}' +
      'table {border-collapse: collapse;width: 100%;}' +
      'th, td {padding: 8px;text-align: left;border-bottom: 1px solid #ddd;}' +
      '</style>' +
      '</head>' +
      '<header>' +
      '<h1>Read</h1>' +
      '<a href="/">Accueil</a>' +
      '<br>' +
      '</header>' +
      '<body>' +
      '<table>' +
      '<tr><th>id</th><th>name</th><th>email</th></tr>' +
      rows.map(function(row) {
        return `<tr><td>${row.id}</td><td>${row.name}</td><td>${row.email}</td></tr>`;
      }).join('') +
      '</table>' +
      '</body></html>';
    callback(null, htmlContent);
  });
};

第二步：修正HTTP服务主文件 #

全局只创建1个共享的MySQL连接，修正路由的异步响应逻辑，修复POST提交的注入和无响应问题：

// server.js
var http = require('http');
var fs = require('fs');
var url = require('url');
var mysql = require('mysql2');
var querystring = require('querystring');
var read_html = require('./read_html.js');

// 全局唯一MySQL连接，所有逻辑复用
var con = mysql.createConnection({
  host: "localhost",
  user: "Nodejs",
  password: "NodeJS_MySQL",
});
// 初始化连接
con.connect(function(err) {
  if (err) throw err;
});

var server = http.createServer(function(request, response) {
  var reqPath = url.parse(request.url).pathname;
  
  switch (reqPath) {
    case '/':
      fs.readFile("page/index.html", function(error, data) {
        if (error) {
          response.writeHead(404);
          response.write(error.message);
          return response.end();
        }
        response.writeHead(200, {'Content-Type': 'text/html; charset=utf-8'});
        response.write(data);
        response.end();
      });
      break;
    case '/post.html':
      fs.readFile("page/post.html", function(error, data) {
        if (error) {
          response.writeHead(404);
          response.write(error.message);
          return response.end();
        }
        response.writeHead(200, {'Content-Type': 'text/html; charset=utf-8'});
        response.write(data);
        response.end();
      });
      break;
    case '/read.html':
      // 传入共享数据库连接，等待查询完成后返回最新HTML
      read_html.read_content(con, function(err, html) {
        if (err) {
          response.writeHead(500, {'Content-Type': 'text/html; charset=utf-8'});
          response.write('数据库查询错误：' + err.message);
          return response.end();
        }
        response.writeHead(200, {'Content-Type': 'text/html; charset=utf-8'});
        response.write(html);
        response.end();
      });
      break;
    default:
      if (request.method === 'GET') {
        response.writeHead(404, {'Content-Type': 'text/html; charset=utf-8'});
        response.write('<html><head><meta charset="UTF-8"></head><body style="font-family: Arial, Helvetica, sans-serif;"><svg enable-background="new 0 0 32 32" height="128px" id="Layer_1" version="1.1" viewBox="0 0 32 32" width="128px" xml:space="preserve" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"><g id="Error_x2C__lost_x2C__no_page_x2C__not_found"><g><g><g><circle cx="7.5" cy="5.5" fill="#263238" r="0.5"/><circle cx="5.5" cy="5.5" fill="#263238" r="0.5"/><circle cx="3.5" cy="5.5" fill="#263238" r="0.5"/><path d="M30.5,8h-29C1.224,8,1,7.776,1,7.5S1.224,7,1.5,7h29C30.776,7,31,7.224,31,7.5S30.776,8,30.5,8z" fill="#263238"/><path d="M29.5,29h-27C1.673,29,1,28.327,1,27.5v-23C1,3.673,1.673,3,2.5,3h27C30.327,3,31,3.673,31,4.5v23      C31,28.327,30.327,29,29.5,29z M2.5,4C2.224,4,2,4.225,2,4.5v23C2,27.775,2.224,28,2.5,28h27c0.276,0,0.5-0.225,0.5-0.5v-23      C30,4.225,29.776,4,29.5,4H2.5z" fill="#263238"/></g></g></g><g><path d="M24.5,24c-0.276,0-0.5-0.224-0.5-0.5V21h-3.5c-0.163,0-0.315-0.079-0.409-0.212s-0.117-0.303-0.062-0.456    l2.5-7C22.6,13.133,22.789,13,23,13h1.5c0.276,0,0.5,0.224,0.5,0.5V20h0.5c0.276,0,0.5,0.224,0.5,0.5S25.776,21,25.5,21H25v2.5    C25,23.776,24.776,24,24.5,24z M21.209,20H24v-6h-0.647L21.209,20z" fill="#263238"/><path d="M10.5,24c-0.276,0-0.5-0.224-0.5-0.5V21H6.5c-0.163,0-0.315-0.079-0.409-0.212s-0.117-0.303-0.062-0.456    l2.5-7C8.6,13.133,8.789,13,9,13h1.5c0.276,0,0.5,0.224,0.5,0.5V20h0.5c0.276,0,0.5,0.224,0.5,0.5S11.776,21,11.5,21H11v2.5    C11,23.776,10.776,24,10.5,24z M7.209,20H10v-6H9.353L7.209,20z" fill="#263238"/><path d="M17.5,24h-3c-0.827,0-1.5-0.673-1.5-1.5v-8c0-0.827,0.673-1.5,1.5-1.5h3c0.827,0,1.5,0.673,1.5,1.5v8    C19,23.327,18.327,24,17.5,24z M14.5,14c-0.276,0-0.5,0.225-0.5,0.5v8c0,0.275,0.224,0.5,0.5,0.5h3c0.276,0,0.5-0.225,0.5-0.5v-8    c0-0.275-0.224-0.5-0.5-0.5H14.5z" fill="#263238"/></g></g></svg><h3>Oups cette page n&#39;existe pas...</h3><a href="/">Page principale</a></body></html>');
        response.end();
      }
      break;
  }

  // 处理POST表单提交
  if (request.method === 'POST') {
    let body = '';
    // 限制请求体大小，防止内存溢出
    request.on('data', chunk => {
      body += chunk;
      if (body.length > 1024 * 1024) request.connection.destroy();
    });
    request.on('end', () => {
      const postData = querystring.parse(body);
      // 参数化查询，避免SQL注入
      const insertSql = "INSERT INTO customers.customers (name,email) VALUES (?, ?)";
      con.query(insertSql, [postData.name, postData.email], err => {
        if (err) {
          response.writeHead(500, {'Content-Type': 'text/html; charset=utf-8'});
          response.write('数据提交失败：' + err.message);
          return response.end();
        }
        // 插入成功后跳转到列表页，直接展示最新数据
        response.writeHead(302, {'Location': '/read.html'});
        response.end();
      });
    });
  }
});

server.listen(8080, () => {
  console.log('服务运行在 http://localhost:8080');
});

• 核心修复点是把数据库查询从「模块初始化阶段执行一次」移到「每次请求处理阶段实时执行」，从根源上解决旧数据缓存的问题，不管数据库是被当前服务的POST接口更新，还是被外部工具修改，刷新页面都能拿到最新结果。
• 遵循Node.js异步IO模型，所有数据库操作完成后再返回HTTP响应，避免返回空内容或者不完整内容。
• 全局复用单个数据库连接，减少不必要的连接开销。
• 增加了请求体大小限制、参数化查询、字符集配置、错误处理等生产环境必要的基础逻辑，修复了原有代码的隐性bug。

内容的提问来源于stack exchange，提问作者Augustin Mauroy