Node.js使用multer上传文件后如何将文件名/路径保存到MySQL数据库

正在尝试将照片保存到数据库中，已经实现了multer中间件将文件上传到服务器，已创建名为uploads的文件夹存储照片，且已在后端成功为文件添加后缀重命名，文件可以正常存入uploads文件夹。但目前遇到的问题是无法将文件名/文件路径保存到MySQL数据库，方便后续在前端调取该文件。

前端代码（editUser.js 表单） #

import axios from "axios";
import { React, useState, useEffect } from "react";
import { useHistory, withRouter } from "react-router-dom";

function UserMaster_Edit(props) {
  const [CmpnyCode, setCmpnyCode] = useState("");
  const [UserID, setUserID] = useState("");
  const [UserFullName, setUserFullName] = useState("");
  const [UserDetail, setUserDetail] = useState("");
  const [LoginID, setLoginID] = useState("");
  const [Password, setPassword] = useState("");
  const [UserPin, setUserPin] = useState("");
  const [UserEmailID, setUserEmailID] = useState("");
  const [UserFP, setUserFP] = useState({});
  const [Photo, setPhoto] = useState({});
  const [IsActive, setIsActive] = useState("");
  const [LicCount, setLicCount] = useState("");
  const [DateCreated, setDateCreated] = useState("");
  const [CreatedBy, setCreatedBy] = useState("");
  const [RecordChanged, setRecordChanged] = useState("");
  const [LocationID, setLocationID] = useState("");
  const [isValid, setisValid] = useState("");
  const [isDeleted, setisDeleted] = useState("");

  const history = useHistory();

  const argu = props.match.params.UserID;

  useEffect(() => {
    axios.get("http://localhost:8000/getuserid/" + argu).then((response) => {
      setCmpnyCode(response.data[0].CmpnyCode);
      setUserID(response.data[0].UserID);
      setUserFullName(response.data[0].UserFullName);
      setUserDetail(response.data[0].UserDetail);
      setLoginID(response.data[0].LoginID);
      setPassword(response.data[0].Password);
      setUserPin(response.data[0].UserPin);
      setUserEmailID(response.data[0].UserEmailID);
      setUserFP(response.data[0].UserFP);
      //setPhoto(response.data[0].Photo);
      setIsActive(response.data[0].IsActive.data[0]);
      setLicCount(response.data[0].LicCount);
      setDateCreated(response.data[0].DateCreated);
      setCreatedBy(response.data[0].CreatedBy);
      setRecordChanged(response.data[0].RecordChanged.data[0]);
      setLocationID(response.data[0].LocationID);
      setisValid(response.data[0].isValid);
      setisDeleted(response.data[0].isDeleted);
    });
  }, [argu]);

  const editData = () => {
    axios.put("http://localhost:8000/upusermst/" + argu, {
      CmpnyCode,
      UserID,
      UserFullName,
      UserDetail,
      LoginID,
      Password,
      UserPin,
      UserEmailID,
      UserFP,
      // Photo,
      IsActive,
      LicCount,
      DateCreated,
      CreatedBy,
      RecordChanged,
      LocationID,
      isValid,
      isDeleted,
    });
    history.push("/usermst");
  };

  const uploadPhoto = (event, argu) => {
    event.preventDefault();
    const formData = new FormData();
    formData.append("avatar", Photo);
    fetch("http://localhost:8000/uploadphoto/" + argu, {
      method: "PUT",
      body: formData,
    });
  };

  return (
    <div className="App">
      <div className="auth-wrapper">
        <div className="auth-inner">
          <form enctype="multipart/form-data">
            <h3> Edit User Master</h3>
            <div>
              <div className="form-class8">
                <div className="form-group">
                  <label>Company Code</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="CompanyCode"
                    value={CmpnyCode}
                    onChange={(e) => {
                      setCmpnyCode(e.target.value);
                    }}
                    name="CmpnyCode"
                  />
                </div>

                <div className="form-group">
                  <label>UserID</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="UserID"
                    value={UserID}
                    onChange={(e) => {
                      setUserID(e.target.value);
                    }}
                    name="UserID"
                  />
                </div>

                <div className="form-group">
                  <label>UserFullName</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="UserFullName"
                    value={UserFullName}
                    onChange={(e) => {
                      setUserFullName(e.target.value);
                    }}
                    name="UserFullName"
                  />
                </div>

                <div className="form-group">
                  <label>UserDetail</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="UserDetail"
                    onChange={(e) => {
                      setUserDetail(e.target.value);
                    }}
                    name="UserDetail"
                    value={UserDetail}
                  />
                </div>

                <div className="form-group">
                  <label>LoginID</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="LoginID"
                    onChange={(e) => {
                      setLoginID(e.target.value);
                    }}
                    name="LoginID"
                    value={LoginID}
                  />
                </div>

                <div className="form-group">
                  <label>Password</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="Password"
                    onChange={(e) => {
                      setPassword(e.target.value);
                    }}
                    name="Password"
                    value={Password}
                  />
                </div>

                <div className="form-group">
                  <label>UserPin</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="UserPin"
                    onChange={(e) => {
                      setUserPin(e.target.value);
                    }}
                    name="UserPin"
                    value={UserPin}
                  />
                </div>

                <div className="form-group">
                  <label>UserEmailID</label>
                  <input
                    type="email"
                    className="form-control"
                    placeholder="UserEmailID"
                    onChange={(e) => {
                      setUserEmailID(e.target.value);
                    }}
                    name="UserEmailID"
                    value={UserEmailID}
                  />
                </div>
              </div>

              <div className="form-class8">
                <div className="form-group">
                  <label>UserFP</label>
                  <input
                    type="file"
                    className="form-control"
                    placeholder="UserFP"
                    onChange={(e) => {
                      setUserFP(e.target.value);
                    }}
                    name="UserFP"
                  />
                </div>

                <div className="form-group">
                  <label>Photo</label>
                  <input
                    type="file"
                    className="form-control"
                    placeholder="Photo"
                    onChange={(e) => {
                      setPhoto(e.target.files[0]);
                    }}
                    id="Photo"
                    name="Photo"
                  />
                  <button onClick={(event) => uploadPhoto(event)}>
                    Upload
                  </button>
                </div>

                <div className="form-group">
                  <label>IsActive</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="IsActive"
                    onChange={(e) => {
                      setIsActive(e.target.value);
                    }}
                    name="IsActive"
                    value={IsActive}
                  />
                </div>

                <div className="form-group">
                  <label>LicCount</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="LicCount"
                    onChange={(e) => {
                      setLicCount(e.target.value);
                    }}
                    name="LicCount"
                    value={LicCount}
                  />
                </div>

                <div className="form-group">
                  <label>DateCreated</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="DateCreated"
                    onChange={(e) => {
                      setDateCreated(e.target.value);
                    }}
                    name="DateCreated"
                    value={DateCreated}
                  />
                </div>

                <div className="form-group">
                  <label>CreatedBy</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="CreatedBy"
                    onChange={(e) => {
                      setCreatedBy(e.target.value);
                    }}
                    name="CreatedBy"
                    value={CreatedBy}
                  />
                </div>

                <div className="form-group">
                  <label>RecordChanged</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="RecordChanged"
                    onChange={(e) => {
                      setRecordChanged(e.target.value);
                    }}
                    name="RecordChanged"
                    value={RecordChanged}
                  />
                </div>

                <div className="form-group">
                  <label>LocationID</label>
                  <input
                    type="text"
                    className="form-control"
                    placeholder="LocationID"
                    onChange={(e) => {
                      setLocationID(e.target.value);
                    }}
                    name="LocationID"
                    value={LocationID}
                  />
                </div>
              </div>

              <div className="form-class8">
                <div className="form-group">
                  <label>isValid</label>
                  <input
                    type="date"
                    className="form-control"
                    placeholder="isValid"
                    onChange={(e) => {
                      setisValid(e.target.value);
                    }}
                    name="isValid"
                    value={isValid}
                  />
                </div>

                <div className="form-group">
                  <label>isDeleted</label>
                  <input
                    type="date"
                    className="form-control"
                    placeholder="isDeleted"
                    onChange={(e) => {
                      setisDeleted(e.target.value);
                    }}
                    name="isDeleted"
                    value={isDeleted}
                  />
                </div>
              </div>

              <button
                className="btn btn-primary btn-block"
                onClick={() => editData()}
              >
                Edit
              </button>
            </div>
          </form>
        </div>
      </div>
    </div>
  );
}

export default withRouter(UserMaster_Edit);

当前正在调试代码中Photo对应的div模块部分。

后端代码（index.js） #

const multer = require("multer");
const fs = require("fs");

const upload = multer({ dest: "uploads/" });


app.put("/uploadphoto/:UserId", upload.single("avatar"), (req, res) => {
  const userid = req.params.userId;
  const photo = req.file;
  const fileType = photo.mimetype.split("/")[1];
  let newFileName = photo.filename + "." + fileType;

  fs.rename(
    `./uploads/${photo.filename}`,
    `./uploads/${newFileName}`,
    function () {
      console.log("file renamed and uploaded");
    }
  );
  console.log(photo);
  console.log("fileName ", newFileName);

  db.query(
    "UPDATE usermst SET Photo=? WHERE UserID=?",
    [newFileName, userid],
    (err, result) => {
      console.log(err);
      res.json({ result });
    }
  );
});

三个需要修改的错误点：

• 前端调用uploadPhoto方法时未传递用户ID参数：按钮的onClick事件当前只传了event参数，需要补充传入argu变量，修改为：onClick={(event) => uploadPhoto(event, argu)}
• 后端路由参数大小写不匹配：路由定义的参数名为UserId（首字母大写），但取值时写的是req.params.userId（首字母小写），Node.js中对象属性大小写敏感，需要修改为const userid = req.params.UserId;
• 数据库更新逻辑位置错误：当前在文件重命名操作完成前就执行了数据库更新，如果重命名失败会出现数据库存储的文件名不存在的问题，建议将数据库更新逻辑挪到fs.rename的回调函数中，保证文件操作成功后再写入数据库。

修正后的后端核心逻辑示例：

app.put("/uploadphoto/:UserId", upload.single("avatar"), (req, res) => {
  const userid = req.params.UserId;
  const photo = req.file;
  const fileType = photo.mimetype.split("/")[1];
  let newFileName = photo.filename + "." + fileType;

  fs.rename(
    `./uploads/${photo.filename}`,
    `./uploads/${newFileName}`,
    function (err) {
      if(err) {
        console.log("文件重命名失败", err);
        return res.status(500).json({msg: "文件上传失败"});
      }
      console.log("file renamed and uploaded");
      db.query(
        "UPDATE usermst SET Photo=? WHERE UserID=?",
        [newFileName, userid],
        (err, result) => {
          console.log(err);
          res.json({ result });
        }
      );
    }
  );
});

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