Next.js13+mysql2实现POST请求报HTTP500，数据无法插入MySQL

问题描述 #

使用Next.js 13开发的多输入项表单，尝试提交数据到MySQL数据库。数据库连接正常，GET请求可正常执行，但POST请求无效。提交表单时浏览器控制台和终端均无错误提示，但访问api/application返回HTTP 500状态码。期望点击「Save」按钮后数据插入application表并显示「Success」提示，但目前无任何反应，数据完全未插入。技术栈：Next.js13、MySQL Workbench、mysql2 npm包。

MySQL表结构 #

CREATE TABLE application (
  App_id INT PRIMARY KEY AUTO_INCREMENT, 
  Status VARCHAR(20) DEFAULT "Pending",
  c_id INT DEFAULT NULL,
  Sex CHAR,
  child_age INT,
  p_id VARCHAR(20),
  g_disorder ENUM('Yes', 'No') NOT NULL,
  FOREIGN KEY (c_id) REFERENCES child(Child_id), # Foreign keys 
  FOREIGN KEY (p_id) REFERENCES parents(Parent_id) # Foreign keys
);
ALTER TABLE application AUTO_INCREMENT=1000;

API路由代码（app/api/application/route.js） #

import mysql from "mysql2/promise";
import { query } from "../../lib/db";
import { NextResponse } from "next/server";

export async function POST(request) {
    let message;
    const sex = request.body.Sex;
    const childAge = request.body.child_age;
    const parentID = request.body.p_id;
    const geneticDisorder = request.body.g_disorder;
    const addApplicationData = await query({
        query: "INSERT INTO application(Sex, child_age, p_id, g_disorder) VALUES(?, ?, ?, ?)",
        values: [sex, childAge, parentID, geneticDisorder],
    });

    if (addApplicationData.insertId) {
        message = "success"
    } else {
        message = "error"
    }

    let applicationData = {
        App_id: addApplicationData.insertId,
        Sex: sex,
        child_age: childAge,
        p_id: parentID,
        g_disorder: geneticDisorder
    };

    return NextResponse.json({ response: { message: message, applicationData: applicationData } });
}

客户端表单代码（app/application/page.js） #

"use client"

import React, { useState, useRef, useEffect } from 'react';
import { useRouter } from 'next/navigation';


const Application = () => {
  const parentIDToAddRef = useRef();
  const sexOfChildToAddRef = useRef();
  const ageOfChildToAddRef = useRef();
  const genDisorderToAddRef = useRef();

  const [created, setCreated] = useState(false);
  async function addData () {
      const sex = sexOfChildToAddRef.current.value.trim();
      const childAge = ageOfChildToAddRef.current.value.trim();
      const parentID = parentIDToAddRef.current.value.trim();
      const geneticDisorder = genDisorderToAddRef.current.value.trim();

      const postData = {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
        },
        body: JSON.stringify({
          Sex: sex,
          child_age: parseInt(childAge, 10),
          p_id: parentID, 
          g_disorder: geneticDisorder,
        }),
      };
      if (parentID.length !== 14) {
        return;
      }
      const res = await fetch(
        `${process.env.NEXT_PUBLIC_URL}/api/application`,
      postData
      );
      const response = await res.json();
      console.log(response);
      if (response.response.message !== "success") return;
      setCreated(true);
    }
    
  return (
    <main>
        <form onSubmit={addData}>
            <div>
                <h1>Fill your Application</h1>
                <div>
                    <div>
                        <label htmlFor="p_id">Enter your ID</label>
                        <p></p>
                        <input 
                        type="text"
                        ref={parentIDToAddRef}
                        placeholder="4222 2224 4222"
                        required
                        />
                    </div>
                    <div>
                        <label htmlFor="Sex">Sex of the child</label>
                        <p></p>
                        <input 
                        type="text"
                        ref={sexOfChildToAddRef}
                        placeholder="M / F" 
                        required
                        />
                    </div>
                    <div>
                        <label htmlFor="child_age">Age of the child</label>
                        <p></p>
                        <input
                        type="number"
                        ref={ageOfChildToAddRef}
                        placeholder="Enter age of the child"
                        required
                        />
                    </div>
                    <div>
                        <label htmlFor="g_disorder">Are you okay if the child has a genetic disorder?</label>
                        <p></p>
                        <input 
                        id="yes"
                        type="radio"
                        ref={genDisorderToAddRef}
                        value="Yes"
                        required
                        />
                        <label htmlFor="yes">Yes</label>
                        <p></p>
                        <input  
                        id="no"
                        type="radio"
                        ref={genDisorderToAddRef}
                        value="No"
                        required
                        />
                        <label htmlFor="no">No</label>
                    </div>
                </div>
            </div>
            <div>
            <input
                value="Save"
                type="button"
              />
            </div>
        </form>
        {created && <div>Success!</div>}
    </main>
  );
};

export default Application;

修复步骤 #

1. API路由解析请求体 #

Next.js 13 App Router中，POST请求的request.body无法直接读取，需先调用request.json()解析：

export async function POST(request) {
    try {
        const body = await request.json(); // 新增：解析请求体
        const sex = body.Sex;
        const childAge = body.child_age;
        const parentID = body.p_id;
        const geneticDisorder = body.g_disorder;
        
        // ... 原有插入逻辑
    } catch (err) {
        console.error(err);
        return NextResponse.json({ response: { message: "error", error: err.message } }, { status: 500 });
    }
}

2. 修复表单提交按钮类型 #

原按钮为type="button"，不会触发onSubmit事件，改为type="submit"并阻止默认刷新：

// 按钮修改
<input
    value="Save"
    type="submit"
/>

// addData函数新增阻止默认行为
async function addData (e) {
    e.preventDefault();
    // ... 原有逻辑
}

3. 修复单选框值获取逻辑 #

两个单选框共用一个ref会导致值获取错误，改用state管理：

// 替换ref为state
const [geneticDisorder, setGeneticDisorder] = useState('');

// 单选框修改
<input 
id="yes"
type="radio"
name="g_disorder" // 新增name确保单选互斥
value="Yes"
checked={geneticDisorder === 'Yes'}
onChange={(e) => setGeneticDisorder(e.target.value)}
required
/>
<label htmlFor="yes">Yes</label>

<input  
id="no"
type="radio"
name="g_disorder"
value="No"
checked={geneticDisorder === 'No'}
onChange={(e) => setGeneticDisorder(e.target.value)}
required
/>
<label htmlFor="no">No</label>

// addData中直接使用state值，删除ref取值代码

4. 完善客户端错误处理 #

添加响应状态检查，避免500时解析json报错：

const res = await fetch(
    `${process.env.NEXT_PUBLIC_URL}/api/application`,
    postData
);
if (!res.ok) {
    console.error("请求失败：", res.status);
    const errData = await res.json();
    console.error(errData);
    return;
}
const response = await res.json();

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