引言： 繼前面的NodeJS的Hello,World！我們還可以看到其他強大之處，NodeJS現在社區的火熱，以及大批工程師對它的支持之下，現在已經陸續的引出了大量的module出來了。
內容： 下面這個所演示的是NodeJS與Mysql 的交互。
這時需要為NodeJS加入Mysql 的Module了，這時前一章說到的npm（Node package manager）啟到作用了。
把Mysql Module裝到NodeJS中：
複製代碼代碼如下:
$npm install Mysql
JS腳本mysqlTest.js
複製代碼代碼如下:
// mysqlTest.js
//加載mysql Module
var Client = require('mysql').Client,
client = new Client(),
//要創建的數據庫名
TEST_DATABASE = 'nodejs_mysql_test',
//要創建的表名
TEST_TABLE = 'test';
//使用者名稱
client.user = 'root';
//密碼
client.password = 'root';
//創建連接
client.connect();
client.query('CREATE DATABASE '+TEST_DATABASE, function(err) {
if (err && err.number != Client.ERROR_DB_CREATE_EXISTS) {
throw err;
}
});
// If no callback is provided, any errors will be emitted as `'error'`
// events by the client
client.query('USE '+TEST_DATABASE);
client.query(
'CREATE TABLE '+TEST_TABLE+
'(id INT(11) AUTO_INCREMENT, '+
'title VARCHAR(255), '+
'text TEXT, '+
'created DATETIME, '+
'PRIMARY KEY (id))'
);
client.query(
'INSERT INTO '+TEST_TABLE+' '+
'SET title = ?, text = ?, created = ?',
['super cool', 'this is a nice text', '2010-08-16 10:00:23']
);
var query = client.query(
'INSERT INTO '+TEST_TABLE+' '+
'SET title = ?, text = ?, created = ?',
['another entry', 'because 2 entries make a better test', '2010-08-16 12:42:15']
);
client.query(
'SELECT * FROM '+TEST_TABLE,
function selectCb(err, results, fields) {
if (err) {
throw err;
}
console.log(results);
console.log(fields);
client.end();
}
);
執行腳本
複製代碼代碼如下:
node mysqlTest.js
效果如下:
// mysql-demo.js
const mysql = require('mysql2/promise'); // 使用支持Promise的mysql2
require('dotenv').config(); // 環境變量管理
// 使用連接池提升性能
const pool = mysql.createPool({
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'root',
password: process.env.DB_PASSWORD || 'root',
database: process.env.DB_NAME || 'nodejs_mysql_test',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
// 表結構定義（DDL管理）
const TABLE_SCHEMA = `
CREATE TABLE IF NOT EXISTS posts (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
content TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
`;
// 業務邏輯封裝
class PostDAO {
constructor(pool) {
this.pool = pool;
}
async initialize() {
await this.pool.execute(TABLE_SCHEMA);
console.log('Table verified/created');
}
async createPost(post) {
const [result] = await this.pool.execute(
`INSERT INTO posts (title, content)
VALUES (?, ?)`,
[post.title, post.content]
);
return { ...post, id: result.insertId };
}
async getPosts(page = 1, pageSize = 10) {
const offset = (page - 1) * pageSize;
const [rows] = await this.pool.execute(
`SELECT * FROM posts
ORDER BY created_at DESC
LIMIT ? OFFSET ?`,
[pageSize, offset]
);
return rows;
}
async transactionDemo() {
const conn = await this.pool.getConnection();
try {
await conn.beginTransaction();
// 事務操作1
await conn.execute(
`INSERT INTO posts (title, content)
VALUES (?, ?)`,
['Transaction 1', 'Atomic operation demo']
);
// 事務操作2
const [result] = await conn.execute(
`UPDATE posts SET content = ?
WHERE title = ?`,
['Updated content', 'Transaction 1']
);
await conn.commit();
return result;
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release();
}
}
}
// 執行示例
(async () => {
try {
const dao = new PostDAO(pool);
await dao.initialize();
// 創建新文章
const newPost = await dao.createPost({
title: 'Modern Node.js MySQL Guide',
content: 'Best practices for database operations'
});
console.log('Created post:', newPost);
// 分頁查詢
const posts = await dao.getPosts();
console.log('First page results:', posts);
// 事務演示
const txResult = await dao.transactionDemo();
console.log('Transaction completed:', txResult);
// 最終查詢
const finalPosts = await dao.getPosts();
console.log('Final posts:', finalPosts);
} catch (err) {
console.error('Application error:', err);
} finally {
await pool.end();
}
})();