search
HomeWeb Front-endFront-end Q&AHow to use mysql in nodejs
How to use mysql in nodejsMay 14, 2023 am 10:25 AM

Node.js is a very popular server-side JavaScript runtime environment that can be used to build high-performance web applications and APIs. Compared with other server-side programming languages, Node.js provides a powerful and flexible development platform that can easily integrate various third-party libraries and modules, including the MySQL database. In this article, we will explore how to connect and operate a MySQL database using Node.js.

MySQL is a popular relational database management system. In Node.js, various modules can be used to connect and operate MySQL database. Here, we will use Node.js’s mysql module to achieve this.

Before you begin, make sure you have installed Node.js and a MySQL database, and that you have created a database and a table. In this article, we will use a database named "test" and a table named "users". This table has three fields: id (incremented), name and email. The following is the SQL command to create the table:

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL
);

Install the mysql module

Before using the mysql module, you first need to install it. You can install the mysql module using npm (Node.js package manager). Open a terminal window and run the following command:

npm install mysql

Connect to a MySQL database

Here is the basic code on how to connect to a MySQL database:

const mysql = require('mysql');

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

connection.connect((err) => {
if (err) throw err;
console.log( 'Connected!');
});

In this example, we first import the mysql module, and then use the mysql.createConnection() function to create a connection object. To do this, we need to pass in some connection parameters, including hostname, username, password, and database name.

After creating a connection object, we can use the connection.connect() method to connect to the MySQL database. If the connection is successful, it will output a "Connected!" message. If the connection fails, an error will be thrown.

Query MySQL database

After connecting to the MySQL database, we can perform various queries and operations. Here is the basic code of how to query the data in the table:

const mysql = require('mysql');

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

connection.connect((err) => {
if (err) throw err;
console.log('Connected!');

connection.query('SELECT * FROM users', (error, results, fields) => {

if (error) throw error;
console.log(results);

});
});

In this example, we use the connection.query() method to execute a SQL query statement. In this example, we query all the data in the users table and print the results to the console. The query results will be returned in the form of an array. Each row is an object containing the value of each field. For example, for our example, each object will have id, name and email properties.

Insert data into MySQL database

The following is the basic code on how to insert data into the table:

const mysql = require('mysql');

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

connection.connect((err) => {
if (err) throw err;
console.log('Connected!');

const sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@gmail.com')";
connection.query(sql, (error, result) => {

if (error) throw error;
console.log("1 record inserted");

});
});

In this example, we use the SQL INSERT statement to insert a new record into the users table. In the connection.query() method, we pass it the SQL statement as the first parameter, and then we use a callback function to handle the response.

Update MySQL database

The following is the basic code of how to update the data in the table:

const mysql = require('mysql');

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

connection.connect((err) => {
if (err) throw err;
console.log('Connected!');

const sql = "UPDATE users SET email='john.doe@gmail.com' WHERE name='John Doe'";
connection.query(sql, (error, result) => {

if (error) throw error;
console.log(result.affectedRows + " record(s) updated");

});
});

In this example, we use the SQL UPDATE statement to update the data in the users table. We use the WHERE clause to specify the rows to be updated. In the callback function of the connection.query() method, we output the number of affected rows.

Delete data in MySQL database

The following is the basic code on how to delete data from the table:

const mysql = require('mysql');

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

connection.connect((err) => {
if (err) throw err;
console.log('Connected!');

const sql = "DELETE FROM users WHERE name='John Doe'";
connection.query(sql, (error, result) => {

if (error) throw error;
console.log(result.affectedRows + " record(s) deleted");

});
});

在这个例子中,我们使用SQL DELETE语句从users表中删除数据。我们使用WHERE子句来指定要删除的行。在connection.query()方法的回调函数中,我们输出受影响的行数。

总结

在本文中,我们介绍了如何使用Node.js连接和操作MySQL数据库。我们使用Node.js的mysql模块来连接到数据库,执行各种查询和操作。使用这些知识,您可以创建强大和高效的Web应用程序和API,以及进行其他有关MySQL数据库的编程任务。

The above is the detailed content of How to use mysql in nodejs. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What is useEffect? How do you use it to perform side effects?What is useEffect? How do you use it to perform side effects?Mar 19, 2025 pm 03:58 PM

The article discusses useEffect in React, a hook for managing side effects like data fetching and DOM manipulation in functional components. It explains usage, common side effects, and cleanup to prevent issues like memory leaks.

Explain the concept of lazy loading.Explain the concept of lazy loading.Mar 13, 2025 pm 07:47 PM

Lazy loading delays loading of content until needed, improving web performance and user experience by reducing initial load times and server load.

What are higher-order functions in JavaScript, and how can they be used to write more concise and reusable code?What are higher-order functions in JavaScript, and how can they be used to write more concise and reusable code?Mar 18, 2025 pm 01:44 PM

Higher-order functions in JavaScript enhance code conciseness, reusability, modularity, and performance through abstraction, common patterns, and optimization techniques.

How does currying work in JavaScript, and what are its benefits?How does currying work in JavaScript, and what are its benefits?Mar 18, 2025 pm 01:45 PM

The article discusses currying in JavaScript, a technique transforming multi-argument functions into single-argument function sequences. It explores currying's implementation, benefits like partial application, and practical uses, enhancing code read

How does the React reconciliation algorithm work?How does the React reconciliation algorithm work?Mar 18, 2025 pm 01:58 PM

The article explains React's reconciliation algorithm, which efficiently updates the DOM by comparing Virtual DOM trees. It discusses performance benefits, optimization techniques, and impacts on user experience.Character count: 159

What is useContext? How do you use it to share state between components?What is useContext? How do you use it to share state between components?Mar 19, 2025 pm 03:59 PM

The article explains useContext in React, which simplifies state management by avoiding prop drilling. It discusses benefits like centralized state and performance improvements through reduced re-renders.

How do you prevent default behavior in event handlers?How do you prevent default behavior in event handlers?Mar 19, 2025 pm 04:10 PM

Article discusses preventing default behavior in event handlers using preventDefault() method, its benefits like enhanced user experience, and potential issues like accessibility concerns.

What are the advantages and disadvantages of controlled and uncontrolled components?What are the advantages and disadvantages of controlled and uncontrolled components?Mar 19, 2025 pm 04:16 PM

The article discusses the advantages and disadvantages of controlled and uncontrolled components in React, focusing on aspects like predictability, performance, and use cases. It advises on factors to consider when choosing between them.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment