为何添加app.use(express.static(__dirname))会导致程序异常？

我正在开发一款从数据库获取游戏标题、发布日期、评分及精选图片的程序，原本运行正常。但当添加app.use(express.static(__dirname))语句后，Node终端无报错，但前端index.html的开发者控制台出现GET http://localhost:3000/fill 404 (Not Found)错误，注释该行则恢复正常，请问这是怎么回事？

服务器代码 #

//so, let's outline the goals of this project.  There are two sections
//1) Pictures, titles, and links to reviews
//2) A list of games with titles, release date, rating, and I guess links to reviews.

const express = require("express");
const bodyParser = require("body-parser");
const path = require("path");
const mysql = require("mysql");
// const request = require("request");

const app = express();

app.listen(3000, function() {
  console.log("App listening on PORT 3000");
});

const connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "game_site_db"
})

// app.use(bodyParser.urlencoded({ extended: false }));
// app.use(bodyParser.json());

//why does this break the program?
app.use(express.static(__dirname));

var gamesArray = [];

app.get("/", function(req, res)
{
    res.sendFile(path.join(__dirname, "index.html"))

    //function fill creates an array from the database which can be used to 'fill' the index page
fill();

    function fill(){

        //this empties the game array to prevent data build up.  I think there's a more elegant
        //solution (terminating the connection) but I'll look into that later
        gamesArray = [];

        connection.query("SELECT * from games ORDER BY rel DESC;", function(err, result){
            if (err) throw err;
            
            let i;
                //  console.log("result:")
                // console.log(result);
                //this shows result is an array with 8 objects, now why on earth
                //does pushing each item from this array multiply it by 8?

                console.log("result length:")
                console.log(result.length);
                //result.length is 8.  So why oh why does push each individual item of the array
                //into games array...ugh

             for (i = 0; i < result.length; i++) { 
  gamesArray.push(result[i]);



}


console.log("Games Array:")
console.log(gamesArray);

app.get("/fill", function(req, res) {
  return res.json(gamesArray);
});

        })
    }
})

前端脚本代码 #

<script>
            function fill(){
            $.get("/fill", function(data) {
                    console.log(data);

                    //why on Earth does the former work, but not this, even
                    //when I plug in gibberish?
                    for (i = 0; i < data.length; i++)
                    {
                            $("#side").append("<tr><td>" + data[i].title + "<br><br>" + data[i].rel.substring(0,10) + "</td><td><span class='rating'>" + data[i].rating + "</span></td></tr>")
                    }

                    
                    
            })
        }
        </script>

原因分析及修复方案 #

核心问题 #

1. 中间件执行顺序冲突
   app.use(express.static(__dirname)) 是静态文件中间件，你把它放在了所有路由的前面。当用户请求/时，Express会优先扫描当前目录（__dirname）下的静态文件，找到index.html后直接返回响应，完全不会执行后面的app.get("/")路由处理函数。

2. 路由注册时机错误
   你把/fill路由的注册代码嵌套在/路由的回调函数内部，还放在了数据库查询的回调里。这意味着只有当app.get("/")的回调运行后，/fill路由才会被注册。但因为静态中间件直接返回了index.html，app.get("/")的回调根本没执行，/fill路由从未被创建，所以前端请求/fill时自然会返回404。

另外，把路由注册放在回调里还有个隐患：每次用户访问/，都会重复注册一次/fill路由，多次访问后会堆积多个相同的路由处理函数，导致逻辑混乱。

修复步骤 #

1. 调整静态中间件的使用方式
   不要直接把项目根目录设为静态资源目录，建议专门创建一个public文件夹存放静态文件（比如index.html、CSS、JS等），然后修改静态中间件的配置：

   app.use(express.static(path.join(__dirname, "public")));
   

   这样可以避免静态文件路径和路由路径冲突，同时更符合项目规范。

2. 重构路由逻辑
   把/fill路由移到全局作用域，不要嵌套在其他路由的回调里；同时把数据库查询逻辑直接放在/fill路由的回调中，每次请求/fill时直接从数据库获取最新数据，不需要额外用gamesArray缓存：

   修复后的服务器代码示例：

   const express = require("express");
   const path = require("path");
   const mysql = require("mysql");
   
   const app = express();
   
   app.listen(3000, function() {
     console.log("App listening on PORT 3000");
   });
   
   const connection = mysql.createConnection({
       host: "localhost",
       user: "root",
       password: "",
       database: "game_site_db"
   })
   
   // 用专门的public文件夹存静态资源
   app.use(express.static(path.join(__dirname, "public")));
   
   // 根路由返回index.html
   app.get("/", function(req, res) {
       res.sendFile(path.join(__dirname, "public", "index.html"));
   });
   
   // 独立注册/fill路由，直接查询数据库返回结果
   app.get("/fill", function(req, res) {
       connection.query("SELECT * from games ORDER BY rel DESC;", function(err, result){
           if (err) {
               console.error(err);
               return res.status(500).json({error: "数据库查询失败"});
           }
           res.json(result);
       });
   });
   

3. 前端补充调用时机
   确保前端的fill函数在页面加载时自动调用，比如在脚本末尾添加：

   window.onload = fill;
   

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