Node.js （三）——操作MySQL
2018-03-28 本文已影响43人
120c06518fa0
mysql
mysql设置
注意设置成utf-8编码！默认引擎设置成InnoDB。
注意，如果MySQL的版本≥5.5.3，可以把编码设置为utf8mb4，utf8mb4和utf8完全兼容，但它支持最新的Unicode标准，可以显示emoji字符。
mysql.ini设置如下：
[mysqld]
# 设置MySQL安装目录
basedir=D:/Program Files/Oracle/mysql-5.5.20
# 设置MySQL数据存放目录，路径最后的文件夹必须是data
datadir=E:/oracle/mysql/mysql-5.5.20/data
# 设置MySQL服务器字符集
character_set_server=utf8
# 设置允许的最大连接数
max_connections=200
# 设置端口
port=3306
[client]
# 设置MySQL客户端的字符集
default-character-set=utf8
# 设置端口
port=3306
设置后需要重启。
登录mysql命令：mysql --host localhost -u root -p。
查看mysql环境变量设置，使用命令show variables，可以配合%模糊搜索。
例如，执行命令：show variables like '%char%';：
+--------------------------+------------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | D:\Program Files\Oracle\mysql-5.5.20\share\charsets\ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)
已经成功的设置了utf-8编码。
执行命令show variables like '%engine%';：
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| default_storage_engine | InnoDB |
| engine_condition_pushdown | ON |
| storage_engine | InnoDB |
+---------------------------+--------+
3 rows in set (0.00 sec)
使用Sequelize ORM框架
注意，mysql官方（Oracle）的Node.js驱动仅支持mysql 5.7以上版本。
目前使用最广泛的MySQL Node.js驱动程序是开源的mysql，可以直接使用npm安装。
如果直接使用mysql包提供的接口，我们编写的代码就比较底层，例如，查询代码：
connection.query('SELECT * FROM users WHERE id=?', ['1'], function (err, rows) {
if (err) {
// error
} else {
for (let row in rows) {
processRow(row);
}
}
});
数据库是一个二维表，每一行实际上可以用JavaScript的对象来表示。
这就是ORM（Object-Relational Mapping）技术：把关系数据库的表结构映射到对象上。
因此需要ORM框架来完成转换。
这里选择Node的ORM框架Sequelize来操作数据库。
用Sequelize查询pets表，代码像这样：
Pet.findAll()
.then(function (pets) {
for (let pet in pets) {
console.log(`${pet.id}: ${pet.name}`);
}
})
catch(function (err) {
// error
});
Sequelize返回的对象是Promise，所以我们可以用then()和catch()分别异步响应成功和失败。
但是用then()和catch()仍然比较麻烦。可以用ES7的await来调用任何一个Promise对象，await有个限制，就是必须在async函数中调用，因此代将代码包装成函数并立即调用：
(async () => {
var pets = await Pet.findAll();
})();
由于Koa2的处理函数都是async函数，只要Sequelize API返回的是Promise，我们都可以直接在Koa2的处理函数中，使用await调用数据库API了。
增删改查
首先创建一个表来测试。我们可以在test数据库中创建一个pets表。test数据库是MySQL安装后自动创建的用于测试的数据库。
在项目目录新建init.sql。
-- 创建MySQL的用户名和口令，均为www，并赋予操作test数据库的所有权限。
GRANT ALL PRIVILEGES ON test.* to 'www'@'%' IDENTIFIED BY 'www';
USE test;
CREATE TABLE pets (
id VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
gender BOOL NOT NULL,
birth VARCHAR(10) NOT NULL,
createdAt BIGINT NOT NULL,
updatedAt BIGINT NOT NULL,
version BIGINT NOT NULL,
PRIMARY KEY (id)
) engine=innodb;
执行上面的命令，使用新用户www登录测试下：mysql --host localhost -u www -p。
添加新的依赖包：
"sequelize": "3.24.1",
"mysql": "2.11.1"
mysql是驱动，我们不直接使用，是提供给sequelize用。接着使用npm install安装。
在项目目录新建数据库配置文件config.js，实际上也是一个模块：
var config = {
database: 'test',
username: 'www',
password: 'www',
host: 'localhost',
port: 3306
};
module.exports = config;
下面编写app.js。
使用Sequelize操作MySQL需要先做两件准备工作：
一是创建一个sequelize对象实例：
const Sequelize = require('sequelize');
const config = require('./config');
var sequelize = new Sequelize(config.database, config.username, config.password, {
host: config.host,
dialect: 'mysql',
pool: [
max: 5,
min: 0,
idle: 30000
]
});
二是定义模型Pet，告诉Sequelize如何映射数据库表
var Pet = sequelize.define('pet', {
id: {
type: Sequelize.STRING(50),
primaryKey: true
},
name: Sequelize.STRING(100),
gender: Sequelize.BOOLEAN,
birth: Sequelize.STRING(10),
createdAt: Sequelize.BIGINT,
updatedAt: Sequelize.BIGINT,
version: Sequelize.BIGINT
}, {
timestamps: false // 附加的选项没有去显示地指定tableName！
});
用sequelize.define()定义Model时，传入名称pet，默认的表名就是pets（自动加上s）！第二个参数指定列名和数据类型，如果是主键，需要更详细地指定。第三个参数是额外的配置，我们传入{ timestamps: false }是为了关闭Sequelize的自动添加timestamp的功能。所有的ORM框架都有一种很不好的风气，总是自作聪明地加上所谓“自动化”的功能，但是会让人感到完全摸不着头脑。
向表中添加记录可以使用Promise的方式或者await的方式。
Promise写法：
var now = Data.now();
Pet.create({
id: 'g-' + now,
name: 'Tim',
gender: false,
birth: '2010-01-02',
createdAt: now,
updatedAt: now,
version: 0
})
.then(function (p) {
console.log('created. ' + JSON.stringify(p));
})
.catch(function (err) {
console.log('failed. ' + err);
});
执行结果：
Executing (default): INSERT INTO `pets` (`id`,`name`,`gender`,`birth`,`createdAt`,`updatedAt`,`version`) VALUES ('g-1502514140829','Tim',false,'2010-01-02',1502514140829,1502514140829,0);
created. {"id":"g-1502514140829","name":"Tim","gender":false,"birth":"2010-01-02","createdAt":1502514140829,"updatedAt":1502514140829,"version":0}
(async () => {
var p = await Pet.create({
id: 'g-' + now,
name: 'Tim',
gender: false,
birth: '2010-01-02',
createdAt: now,
updatedAt: now,
version: 0
};
console.log('created. ' + JSON.stringify(p));
})();
执行结果：
Executing (default): INSERT INTO `pets` (`id`,`name`,`gender`,`birth`,`createdAt`,`updatedAt`,`version`) VALUES ('g-1502514301771','Tim',false,'2010-01-02',1502514301771,1502514301771,0);
created. {"id":"g-1502514301771","name":"Tim","gender":false,"birth":"2010-01-02","createdAt":1502514301771,"updatedAt":1502514301771,"version":0}
查询的await写法：
(async () => {
var pets = await Pet.findAll({
where: {
name: 'Tim'
}
});
console.log(`find ${pets.length} pets`);
for (let p of pets) {
console.log(JSON.stringify(p));
}
})();
执行结果：
Executing (default): SELECT `id`, `name`, `gender`, `birth`, `createdAt`, `updatedAt`, `version` FROM `pets` AS `pet` WHERE `pet`.`name` = 'Tim';
find 4 pets
{"id":"g-1502514140829","name":"Tim","gender":false,"birth":"2010-01-02","createdAt":1502514140829,"updatedAt":1502514140829,"version":0}
{"id":"g-1502514301771","name":"Tim","gender":false,"birth":"2010-01-02","createdAt":1502514301771,"updatedAt":1502514301771,"version":0}
{"id":"g-1502514363827","name":"Tim","gender":false,"birth":"2010-01-02","createdAt":1502514363827,"updatedAt":1502514363827,"version":0}
{"id":"g-1502514439228","name":"Tim","gender":false,"birth":"2010-01-02","createdAt":1502514439228,"updatedAt":1502514439228,"version":0}
更新的await写法，调用save()：
(async () => {
var pets = await Pet.findAll({
where: {
name: 'Tim'
}
});
var pet = pets[0];
pets.gender = true;
pet.updatedAt = Date.now();
pet.version++;
await pet.save();
})();
执行结果：
Executing (default): SELECT `id`, `name`, `gender`, `birth`, `createdAt`, `updatedAt`, `version` FROM `pets` AS `pet` WHERE `pet`.`name` = 'Tim';
Executing (default): UPDATE `pets` SET `updatedAt`=1502514949830,`version`=1 WHERE `id` = 'g-1502514140829'
删除的await写法，调用destroy()：
(async () => {
var pets = await Pet.findAll({
where: {
name: 'Tim'
}
});
var pet = pets[0];
await pet.destroy();
})();
执行结果：
Executing (default): SELECT `id`, `name`, `gender`, `birth`, `createdAt`, `updatedAt`, `version` FROM `pets` AS `pet` WHERE `pet`.`name` = 'Tim';
Executing (default): DELETE FROM `pets` WHERE `id` = 'g-1502514140829' LIMIT 1
可以看到，Sequelize每次都会打印出一个对应操作的SQL语句，便于我们核查。
建立Model
直接使用Sequelize存在一些问题。比如有些人喜欢自己加上timestamp（例如前面的createdAt和updataedAt），有些人喜欢加上自增长（autoIncrement: true）。Model不统计可能导致代码难以复用。因此需要一个统一的模型，强迫所有Model都遵守同一个规范，这样不但实现简单，而且容易统一风格。
建立Model的规范：
- 首先所有的Model必须存放在
models文件夹内，并以Model名字命名； - 统一主键，名称必须是
id，类型必须是STRING(50)； - 主键可以自己指定，也可以由框架自动生成（如果为
null或undefined）； - 所有字段默认为
NOT NULL，除非显式指定； - 统一timestamp机制，每个Model必须有
createdAt、updatedAt和version，分别记录创建时间、修改时间和版本号。其中，createdAt和updatedAt以BIGINT存储时间戳，最大的好处是无需处理时区，排序方便。version每次修改时自增。
此外，不要直接使用Sequelize的API，而是通过db.js间接地定义Model。
db.js的作用就是统一Model的定义：
const Sequelize = require('sequelize');
const uuid = require('node-uuid');
const config = require('./config');
console.log('init sequelize...');
function generateId() {
return uuid.v4();
}
var sequelize = new Sequelize(config.database, config.username, config.password, {
host: config.host,
dialect: config.dialect,
pool: {
max: 5,
min: 0,
idle: 10000
}
});
const ID_TYPE = Sequelize.STRING(50);
function defineModel(name, attributes) {
var attrs = {};
for (let key in attributes) {
let value = attributes[key];
if (typeof value === 'object' && value['type']) {
value.allowNull = value.allowNull || false;
attrs[key] = value;
} else {
attrs[key] = {
type: value,
allowNull: false
};
}
}
attrs.id = {
type: ID_TYPE,
primaryKey: true
};
attrs.createdAt = {
type: Sequelize.BIGINT,
allowNull: false
};
attrs.updatedAt = {
type: Sequelize.BIGINT,
allowNull: false
};
attrs.version = {
type: Sequelize.BIGINT,
allowNull: false
};
return sequelize.define(name, attrs, {
tableName: name, // 显式地指定tableName！
timestamps: false,
hooks: {
beforeValidate: function (obj) {
let now = Date.now();
if (obj.isNewRecord) {
console.log('will create entity...' + obj);
if (!obj.id) {
obj.id = generateId();
}
obj.createdAt = now;
obj.updatedAt = now;
obj.version = 0;
} else {
console.log('will update entity...');
obj.updatedAt = now;
obj.version++;
}
}
}
});
}
const TYPES = ['STRING', 'INTEGER', 'BIGINT', 'TEXT', 'DOUBLE', 'DATEONLY', 'BOOLEAN'];
var exp = {
defineModel: defineModel,
sync: () => {
// only allow create ddl in non-production environment:
// console.log(`process.env.NODE_ENV = ${process.env.NODE_ENV}`);
if (process.env.NODE_ENV !== 'production') {
console.log('sequelize.sync start...');
return sequelize.sync({ force: true }); // 返回Promise！自动建表
} else {
throw new Error('Cannot sync() when NODE_ENV is set to \'production\'.');
}
}
};
for (let type of TYPES) {
exp[type] = Sequelize[type];
}
exp.ID = ID_TYPE;
exp.generateId = generateId;
module.exports = exp;
可以看到，id、createdAt、updatedAt和version应该自动加上，而不是每个Model都去重复定义。
Sequelize在创建、修改Entity时会调用我们指定的函数，这些函数通过hooks在定义Model时设定。我们在beforeValidate这个事件中根据是否是isNewRecord设置主键（如果主键为null或undefined）、设置时间戳和版本号。
下面可以方便地定义Model了，例如User.js：
const db = require('../db');
module.exports = db.defineModel('users',{
email: {
type: db.STRING(100),
unique: true
},
passwd: db.STRING(100),
name: db.STRING(100),
gender: db.BOOLEAN
});
数据库配置
下面把config.js拆成3个配置文件。
- config-default.js：存储默认的配置（开发环境）；
- config-override.js：存储特定的配置（生成部署环境）；
- config-test.js：存储用于测试的配置（测试环境）。
config-default.js：
var config = {
dialect: 'mysql',
database: 'nodejs',
username: 'www',
password: 'www',
host: 'localhost',
port: 3306
};
module.exports = config;
config-override.js：
var config = {
database: 'production',
username: 'www',
password: 'secret-password',
host: '10.2.3.4'
};
module.exports = config;
config-test.js：
var config = {
dialect: 'mysql',
database: 'test',
username: 'www',
password: 'www',
host: 'localhost',
port: 3306
};
module.exports = config;
于是，实际读取配置的时候，我们用config.js实现不同环境读取不同的配置文件：
const defaultConfig = './config-default.js';
// 可设定为绝对路径，如 /opt/product/config-override.js
const overrideConfig = './config-override.js';
const testConfig = './config-test.js';
const fs = require('fs');
var config = null;
console.log(`process.env.NODE_ENV = ${process.env.NODE_ENV}`);
if (process.env.NODE_ENV === 'test') {
console.log(`Load ${testConfig}...`);
config = require(testConfig);
} else {
console.log(`Load ${defaultConfig}...`);
config = require(defaultConfig);
try {
if (fs.statSync(overrideConfig).isFile()) {
console.log(`Load ${overrideConfig}...`);
config = Object.assign(config, require(overrideConfig)); // 合并、覆盖！
}
} catch (err) {
console.log(`Cannot load ${overrideConfig}【生产环境才有config-override.js】: ${err}`);
}
}
module.exports = config;
开发环境下，团队统一使用默认的配置，并且无需config-override.js。部署到服务器时，由运维团队配置好config-override.js，以覆盖默认设置。测试环境下，本地和CI服务器统一使用config-test.js，测试数据库可以反复清空，不会影响开发。
配置文件表面上写起来很容易，但是，既要保证开发效率，又要避免服务器配置文件泄漏，还要能方便地执行测试，就需要一开始搭建出好的结构，才能提升工程能力。
使用Model
写一个model.js，自动扫描并导入所有Model，这就是自动化。
const fs = require('fs');
const db = require('./db');
let files = fs.readdirSync(__dirname + '/models');
let js_files = files.filter((f) => {
return f.endsWith('.js');
}, files);
module.exports = {};
for (let f of js_files) {
console.log(`import model from file ${f}`);
let name = f.substring(0, f.length - 3);
module.exports[name] = require(__dirname + '/models/' + f);
}
module.exports.sync = () => {
return db.sync(); // 返回Promise以便then和catch！
};
注意到我们其实不需要创建表的SQL，因为Sequelize提供了一个sync()方法，可以自动创建数据库。这个功能在开发和生产环境中没有什么用，但是在测试环境中非常有用。测试时，我们可以用sync()方法自动创建出表结构，而不是自己维护SQL脚本。这样，可以随时修改Model的定义，并立刻运行测试。开发环境下，首次使用sync()也可以自动创建出表结构，避免了手动运行SQL的问题。
因此，创建一个init-db.js：
const model = require('./model.js');
// 返回Promise才能使用then().catch()，进而回显操作是否成功
model.sync().then(() => {
console.log('sync finished!');
console.log('init db ok.');
process.exit(0); // 需要放在then或者catch里面！
}).catch((err) => {
console.log(`sync failed: ${err}`);
process.exit(1); // 需要放在then或者catch里面！
});
从而避免了手动维护一个SQL脚本。该文件只在最开始执行一次，用于自动创建表，但是数据库得事先建立好！
最后编写app.js测试。
const model = require('./model');
let User = model.User;
(async () => {
var user = await User.create({
name: 'Tim',
gender: false,
email: 'tim-' + Date.now() + '@tim.com',
passwd: '123456'
});
console.log(`created. ${JSON.stringify(user)}`); // 没有显式地指定主键id，db.js会自动生成uuid主键的
})();
注意，不能写成下面的风格！因为sequelize的DB操作都是异步操作，立即打印将可能得不到完整结果。需要配合async和await，像写同步语句一样编写异步调用！
const model = require('./model');
let User = model.User;
var user = User.create({
name: 'Tim',
gender: false,
email: 'tim-' + Date.now() + '@tim.com',
passwd: '123456'
});
// 立即打印可能得不到完整的结果。因为是上一条语句异步操作！
// 打印结果：created. {"isFulfilled":false,"isRejected":false}
console.log(`created. ${JSON.stringify(user)}`);
项目的执行过程：
-
首先需要为www用户分配数据库操作权限：
开发环境下：GRANT ALL PRIVILEGES ON nodejs.* to 'www'@'%' IDENTIFIED BY 'www';。
测试环境下：GRANT ALL PRIVILEGES ON test.* to 'www'@'%' IDENTIFIED BY 'www';。 - 然后执行
node init-db.js，自动创建表。 - 最后执行
node app.js，测试DB操作结果。
