React对接MySQL报错ER_EMPTY_QUERY: Query was empty排查

问题现象 #

实现React前端与MySQL数据库对接功能时，分别测试了数据库连接配置填写密码、不填写密码两种场景：配置密码后执行数据插入操作触发ER_EMPTY_QUERY错误，提示Query was empty。在执行db.query操作前打印req.body，可正常获取所有提交的表单字段信息，无法定位系统判定查询为空的原因。

现场信息 #

请求体数据 #

{   
  name: '123',   
  age: '123',   
  address: '123',   
  rent: '123',
  leaseLength: '1',   
  startDate: '2022-06-01' 
}

报错详情 #

code: 'ER_EMPTY_QUERY',
errno: 1065,
sqlMessage: 'Query was empty',
sqlState: '42000',
index: 0,
sql: undefined

服务端index.js代码 #

const express = require('express')
const app = express()
const mysql = require('mysql')
const cors = require('cors')

app.use(cors())
app.use(express.json())

const db = mysql.createConnection({
    user: 'root',
    host: 'localhost',
    password: 'password',
    database: 'tenantsystem'
});

app.post('/create', (req, res) => {
    console.log(req.body)
    const name = req.body.name
    const age = req.body.age
    const address = req.body.address
    const rent = req.body.name
    const leaseLength = req.body.leaseLength
    const startDate = req.body.startDate

    db.query('INSERT INTO tenants (name, age, address, rent, lease_length, start_date) VALUES (?,?,?,?,?,?)'[name, age, address, rent, leaseLength, startDate], (err, result) => {
        if (err) {
            console.log(err)
        }
        else {
            res.send("Values Inserted")
        }
    });
});

app.listen(3001, () => {
    console.log("Server is running...")
})

前端React端App.js代码 #

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


function App() {
  const [name, setName] = useState('')
  const [age, setAge] = useState('')
  const [address, setAddress] = useState('')
  const [rent, setRent] = useState('')
  const [leaseLength, setLeaseLength] = useState('')
  const [startDate, setStartDate] = useState('')
  const displayInfo = () => {
    console.log(name + age + address + rent + leaseLength + startDate)
  }


  const addTenant = () => {
    console.log(name)
    Axios.post('http://localhost:3001/create', {
      name: name,
      age: age,
      address: address,
      rent: rent,
      leaseLength: leaseLength,
      startDate: startDate,
    }).then(() => {
      console.log('Success');
    })
  }

  return (
    <div className="App">
      <div className="information">
        <label>Name: </label>
        <input type="text" onChange={(e) => {
          setName(e.target.value)
        }} />
        <label>Age: </label>
        <input type="number" onChange={(e) => {
          setAge(e.target.value)
        }} />
        <label>Address: </label>
        <input type="text" onChange={(e) => {
          setAddress(e.target.value)
        }} />
        <label>Rent: </label>
        <input type="number" onChange={(e) => {
          setRent(e.target.value)
        }} />
        <label> lease length in months</label>
        <input type="number" onChange={(e) => {
          setLeaseLength(e.target.value)
        }} />
        <label>start date: </label>
        <input type="date" onChange={(e) => {
          setStartDate(e.target.value)
        }} />
        <button onClick={addTenant}> Add Tenant</button>
      </div>
    </div>
  );
}

export default App;

根因定位 #

报错和数据库是否配置密码没有关联，是服务端代码存在两处错误：

1. db.query参数语法错误：SQL语句字符串和参数数组之间遗漏了逗号，现有写法'SQL语句'[参数数组]是JS的字符串属性访问语法，根本没有把SQL语句和参数数组作为两个独立参数传入query方法，mysql驱动接收到的SQL值为undefined，直接触发空查询错误，和报错日志里的sql: undefined完全对应。
2. 字段取值错误：提取请求体参数时，rent字段错误赋值为req.body.name，就算修正语法问题，插入的租金数据也会和姓名字段重复，属于逻辑bug。

修复方法 #

修改服务端/create接口的对应代码即可：

app.post('/create', (req, res) => {
    console.log(req.body)
    const name = req.body.name
    const age = req.body.age
    const address = req.body.address
    // 修正rent字段取值
    const rent = req.body.rent
    const leaseLength = req.body.leaseLength
    const startDate = req.body.startDate

    db.query(
        // SQL字符串和参数数组之间补逗号，分隔两个入参
        'INSERT INTO tenants (name, age, address, rent, lease_length, start_date) VALUES (?,?,?,?,?,?)',
        [name, age, address, rent, leaseLength, startDate],
        (err, result) => {
            if (err) {
                console.log(err)
                res.status(500).send('Insert failed')
                return
            }
            res.send("Values Inserted")
        }
    );
});

修改后重启服务即可正常插入数据。

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