引言： 继前面的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();
}
})();