如何通过React+Node将表单数据与文件一同提交至MySQL

项目技术栈：React.js、Node.js、Express、MySQL，当前表单数据提交和文件上传分为两个独立操作，需合并为单个按钮完成同步提交。

现有代码 #

App.js（前端） #

import React,{ useState, useEffect} from 'react';
import './App.css';
import Axios from 'axios';


function App() {
  const [invoice, setInvoice] = useState("");
  const [date, setDate] = useState ("");
  const [currency, setCurrency] = useState ("IDR");
  const [amount, setAmount] = useState("");
  const [title, setTitle] = useState("");
  const [path, setPath] = useState("");
  const [attachment, setAttachment] = useState("");
  const [dataList, setDataList] = useState([]);

  
 
  useEffect(() => {
    Axios.get('http://localhost:3001/api/get').then((response)=> {
      setDataList(response.data);
    })
  })


  const submitData = () => {
    Axios.post('http://localhost:3001/api/insert', {
      Invoice_No: invoice,
      Invoice_Date: date,
      Curr: currency,
      Amount: amount,
      Supplier: title,
      Path: path
    })
}

  return (
    <div className="App">
      <p>CRUD Applicaiton</p>


      <div className="formControl">
    
          <label>Invoice No</label>
          <input type="number"  onChange={(e)=> {
            setInvoice(e.target.value);
          }} />
          <label>Invoice Date</label>
          <input type="date"  onChange={(e)=> {
            setDate(e.target.value);
          }} />
          <label>Currency</label>
          <select onChange={(e)=> {
            setCurrency(e.target.value);
          }}>
            <option value="IDR">IDR</option>
            <option value="USD">USD</option>
            <option value="YEN">YEN</option>
          </select>
          <label>Amount</label>
          <input type="number"  onChange={(e)=> {
            setAmount(e.target.value);
          }} />
          <label>Title</label>
          <input type="text"  onChange={(e)=> {
            setTitle(e.target.value);
          }} />
      
          <button onClick={submitData}>Add Data</button>     
          <button type='reset'>Reset</button>
    
      </div>

    <div>
      <form method='POST' encType='multipart/form-data' action='http://localhost:3001/api/upload'>
        <input type="file" name="DocumentFile" />

        <button>ADD to DB</button>
        </form>
    </div>

    </div>
  );
}

export default App;

index.js（后端） #

const express = require('express');
const fs = require('fs');
const bodyParser = require('body-parser');
const cors = require("cors");
const app = express();
const mysql = require('mysql');
const exphbs = require('express-handlebars'); 
const multer = require("multer")


const db = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "",
    database: "work"
});

const upload = multer({storage:multer.memoryStorage()});



app.use(cors());
app.use(express.json() || express.static(".public") || express.static(".upload"));
app.use(bodyParser.urlencoded({extended: true}));





app.get("/api/get", (req, res) => {
    const sqlSelect= "SELECT * FROM invoice"
    db.query(sqlSelect, (err, result)=>{
        res.send(result);
    });
})

app.post("/api/insert", (req, res) => {
    const Invoice_No = req.body.Invoice_No
    const Invoice_Date = req.body.Invoice_Date
    const Currency = req.body.Curr
    const Amount = req.body.Amount
    const Title = req.body.Supplier

    const sqlInsert = "INSERT INTO invoice(Invoice_No, Invoice_Date, Curr, Amount, Supplier) VALUES (?,?,?,?,?)"
    db.query(sqlInsert, [Invoice_No, Invoice_Date, Currency, Amount, Title], (err, result)=>{
        console.log(err)
    });
})

app.post("/api/upload", upload.single('DocumentFile'), (req, res) => {
    const Path = req.file.buffer.toString('base64')

    q = "INSERT INTO invoice (Path) VALUES (?)"
    db.query(q, [Path], (err, result) => {
        console.log(Path)
    })
})

app.listen(3001, ()=> {
    console.log('running on port 3001')
});

修改方案 #

前端（App.js）修改 #

1. 合并两个表单为一个，设置encType="multipart/form-data"以支持文件上传
2. 使用FormData收集所有表单字段和文件
3. 调整提交逻辑，通过Axios发送multipart/form-data类型的请求

修改后的App.js：

import React,{ useState, useEffect, useRef} from 'react';
import './App.css';
import Axios from 'axios';


function App() {
  const [invoice, setInvoice] = useState("");
  const [date, setDate] = useState ("");
  const [currency, setCurrency] = useState ("IDR");
  const [amount, setAmount] = useState("");
  const [title, setTitle] = useState("");
  const [dataList, setDataList] = useState([]);
  const fileInputRef = useRef(null);

  useEffect(() => {
    Axios.get('http://localhost:3001/api/get').then((response)=> {
      setDataList(response.data);
    })
  }, [])

  const submitData = async (e) => {
    e.preventDefault();
    const formData = new FormData();
    // 添加普通表单字段
    formData.append('Invoice_No', invoice);
    formData.append('Invoice_Date', date);
    formData.append('Curr', currency);
    formData.append('Amount', amount);
    formData.append('Supplier', title);
    // 添加文件
    if(fileInputRef.current?.files[0]){
      formData.append('DocumentFile', fileInputRef.current.files[0]);
    }

    try {
      await Axios.post('http://localhost:3001/api/submit', formData, {
        headers: {
          'Content-Type': 'multipart/form-data'
        }
      });
      // 提交成功后重置表单
      setInvoice("");
      setDate("");
      setCurrency("IDR");
      setAmount("");
      setTitle("");
      if(fileInputRef.current) fileInputRef.current.value = "";
      // 更新数据列表
      const res = await Axios.get('http://localhost:3001/api/get');
      setDataList(res.data);
    } catch (err) {
      console.error('提交失败:', err);
    }
  }

  return (
    <div className="App">
      <p>CRUD Application</p>

      <form className="formControl" onSubmit={submitData} encType="multipart/form-data">
        <label>Invoice No</label>
        <input type="number" value={invoice} onChange={(e)=> setInvoice(e.target.value)} required />
        
        <label>Invoice Date</label>
        <input type="date" value={date} onChange={(e)=> setDate(e.target.value)} required />
        
        <label>Currency</label>
        <select value={currency} onChange={(e)=> setCurrency(e.target.value)}>
          <option value="IDR">IDR</option>
          <option value="USD">USD</option>
          <option value="YEN">YEN</option>
        </select>
        
        <label>Amount</label>
        <input type="number" value={amount} onChange={(e)=> setAmount(e.target.value)} required />
        
        <label>Title</label>
        <input type="text" value={title} onChange={(e)=> setTitle(e.target.value)} required />
        
        <label>Attachment</label>
        <input type="file" name="DocumentFile" ref={fileInputRef} />
        
        <button type="submit">Add Data & Upload File</button>     
        <button type='reset'>Reset</button>
      </form>
    </div>
  );
}

export default App;

后端（index.js）修改 #

1. 删除原有的/api/insert和/api/upload接口，新增/api/submit接口
2. 在新接口中同时处理表单数据和文件，将两者插入到同一条数据库记录中

修改后的index.js：

const express = require('express');
const fs = require('fs');
const bodyParser = require('body-parser');
const cors = require("cors");
const app = express();
const mysql = require('mysql');
const multer = require("multer")


const db = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "",
    database: "work"
});

const upload = multer({storage:multer.memoryStorage()});

app.use(cors());
app.use(express.json());
app.use(bodyParser.urlencoded({extended: true}));
app.use(express.static(".public"));
app.use(express.static(".upload"));

app.get("/api/get", (req, res) => {
    const sqlSelect= "SELECT * FROM invoice"
    db.query(sqlSelect, (err, result)=>{
        if(err) {
          console.error(err);
          return res.status(500).send('获取数据失败');
        }
        res.send(result);
    });
})

// 合并后的提交接口
app.post("/api/submit", upload.single('DocumentFile'), (req, res) => {
    const { Invoice_No, Invoice_Date, Curr, Amount, Supplier } = req.body;
    const Path = req.file ? req.file.buffer.toString('base64') : null;

    const sqlInsert = "INSERT INTO invoice(Invoice_No, Invoice_Date, Curr, Amount, Supplier, Path) VALUES (?,?,?,?,?,?)"
    db.query(sqlInsert, [Invoice_No, Invoice_Date, Curr, Amount, Supplier, Path], (err, result)=>{
        if(err) {
          console.error(err);
          return res.status(500).send('提交失败');
        }
        res.status(200).send('数据与文件提交成功');
    });
})

app.listen(3001, ()=> {
    console.log('running on port 3001')
});

关键说明 #

• 前端通过FormData统一承载普通字段和文件，确保请求格式符合文件上传要求
• 后端利用multer解析文件，同时从req.body获取普通表单数据，一次完成数据插入
• 若文件体积较大，建议将文件存储到服务器磁盘，仅把文件路径存入数据库，避免数据库性能问题

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