解决Node.js应用中HTML表单POST请求失效及数据库重复键错误

<!-- 缺少 <form> 标签，无法触发POST请求 -->
<h2 class="email">Please sign up</h2>
<label for="email" class="sr-only">Your Email address</label>
<input type="email" id="email" name="email" class="form-control" placeholder="Email address" required="" autofocus="">
<label for="pass" class="sr-only">New Password</label>
<input type="password" id="pass" class="form-control" name="pass" placeholder="Password" required="">

<button id="btnSignIn" class="btn btn-lg btn-primary btn-block" type="submit">Register</button>

<form method="POST" action="/myaction">
  <h2 class="email">Please sign up</h2>
  <label for="email" class="sr-only">Your Email address</label>
  <input type="email" id="email" name="email" class="form-control" placeholder="Email address" required="" autofocus="">
  <label for="pass" class="sr-only">New Password</label>
  <input type="password" id="pass" class="form-control" name="pass" placeholder="Password" required="">
  <button id="btnSignIn" class="btn btn-lg btn-primary btn-block" type="submit">Register</button>
</form>

const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql'); // 假设已安装 mysql 模块

const app = express();
// 配置 body-parser 来解析 URL 编码的数据
app.use(bodyParser.urlencoded({ extended: true }));
app.use(bodyParser.json()); // 如果也处理 JSON 数据，可以加上

// 数据库连接配置
const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "new_schema",
});

connection.connect(err => {
    if (err) {
        console.error('Error connecting to the database:', err.stack);
        return;
    }
    console.log('Connected to database as id ' + connection.threadId);
});

app.post("/myaction", function (request, response, next) {
  const email = request.body.email;
  const pass = request.body.pass;

  // 注意：直接拼接字符串构建SQL查询存在SQL注入风险，推荐使用预处理语句或参数化查询
  const query = `INSERT INTO new_schema.new_table (email, pass) VALUES (?, ?)`; // 使用占位符

  connection.query(query, [email, pass], function (error, data) { // 传递参数数组
    if (error) {
      console.error('Database error during insertion:', error);
      // 不要直接 throw error，这会导致服务器崩溃。应向客户端发送错误响应。
      if (error.code === 'ER_DUP_ENTRY') {
          return response.status(409).send('Registration failed: Email already exists.');
      }
      return response.status(500).send('Registration failed due to a server error.');
    } else {
      console.log('User registered successfully:', data);
      response.redirect("/home"); // 注册成功后重定向到首页
    }
  });
});

// 启动服务器
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});

CREATE TABLE new_schema.new_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL, -- 确保email是唯一的
    pass VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 假设 id 是 AUTO_INCREMENT
INSERT INTO new_schema.new_table (id, email, pass) VALUES (0, 'test@example.com', 'password123');
-- 如果 id=0 已存在，就会报错 ER_DUP_ENTRY

// 让数据库自动处理 AUTO_INCREMENT 的 id 列
const query = `INSERT INTO new_schema.new_table (email, pass) VALUES (?, ?)`;
connection.query(query, [email, pass], function (error, data) {
    // ... 错误处理逻辑
});

app.post("/myaction", function (request, response, next) {
  const email = request.body.email;
  const pass = request.body.pass;
  const query = `INSERT INTO new_schema.new_table (email, pass) VALUES (?, ?)`;

  connection.query(query, [email, pass], function (error, data) {
    if (error) {
      console.error('Database error during insertion:', error);
      // 根据错误代码进行判断和响应
      if (error.code === 'ER_DUP_ENTRY') {
        // 如果是重复条目错误，通常意味着email已存在（如果email是UNIQUE）
        return response.status(409).send('Registration failed: The provided email is already registered.');
      }
      // 其他数据库错误
      return response.status(500).send('Registration failed due to an internal server error.');
    } else {
      console.log('User registered successfully. Insert ID:', data.insertId);
      response.redirect("/home");
    }
  });
});