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操作结果。