nextjs14连接MySQL-JZTXT

第一步

npm install mysql2

第二步新建一个db.js

db.js

import mysql from "mysql2/promise";

export async function query({ query, values = [] }) {

  const dbconnection = await mysql.createPool({
    host: process.env.MYSQL_HOST,
    post: process.env.MYSQL_PORT,
    database: process.env.MYSQL_DATABASE,
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
  });

  try {
    const [results] = await dbconnection.execute(query, values);
    dbconnection.end();
    return results;
  } catch (error) {
    throw Error(error.message);
  }
}

第三步，配置数据库连接

/** @type {import('next').NextConfig} */
const nextConfig = {
    // reactStrictMode: true,  
    // transpilePackages: ['antd','@ant-design/icons']  
    env:{
        'MYSQL_HOST':'你的地址',
        'MYSQL_PORT':'3306',
        'MYSQL_DATABASE':'lg',
        'MYSQL_USER':'root',
        'MYSQL_PASSWORD':'root'
    }
}

module.exports = nextConfig

第四步，在数据库中创建表

// CREATE TABLE users(

// id Int AUTO_INCREMENT PRIMARY KEY,

// name VARCHAR(255) NULL,

// password VARCHAR(255) NULL,

// email VARCHAR(255) NULL,

// type ENUM('admin','user','guest') NOT null,

// create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

// UPDATE CURRENT_TIMESTAMP

// );

第五步，创建api路由

import { query } from "@/lib/db";

export async function GET(request) {
    const users = await query({
        query: "SELECT * FROM users",
        values: [],
    });

    let data = JSON.stringify(users);
    return new Response(data, {
        status: 200,
    });
}

export async function POST(request) {

    try {
        const { email } = await request.json();
        const updateUsers = await query({
            query: "INSERT INTO users (email) VALUES (?)",
            values: [email],
        });
        const result = updateUsers.affectedRows;
        let message = "";
        if (result) {
            message = "success";
        } else {
            message = "error";
        }
        const user = {
          email: email,
        };
        return new Response(JSON.stringify({
            message: message,
            status: 200,
            product: user
        }));
    } catch (error) {
        return new Response(JSON.stringify({
            status: 500,
            data: request
        }));
    }
}

export async function PUT(request) {

    try {
        const { id, visitor_name } = await request.json();
        const updateProducts = await query({
            query: "UPDATE users SET visitor_name = ? WHERE id = ?",
            values: [visitor_name, id],
        });
        const result = updateProducts.affectedRows;
        let message = "";
        if (result) {
            message = "success";
        } else {
            message = "error";
        }
        const product = {
            id: id,
            visitor_name: visitor_name,
        };
        return new Response(JSON.stringify({
            message: message,
            status: 200,
            product: product
        }));
    } catch (error) {
        return new Response(JSON.stringify({
            status: 500,
            data: res
        }));
    }

}


export async function DELETE(request) {

    try {
        const { id } = await request.json();
        const deleteUser = await query({
            query: "DELETE FROM users WHERE id = ?",
            values: [id],
        });
        const result = deleteUser.affectedRows;
        let message = "";
        if (result) {
            message = "success";
        } else {
            message = "error";
        }
        const product = {
            id: id,
        };
        return new Response(JSON.stringify({
            message: message,
            status: 200,
            product: product
        }));
    } catch (error) {
        return new Response(JSON.stringify({
            status: 500,
            data: res
        }));
    }

}

第六步，发送post,添加数据

第7步,查看