持久化MySQL

node.js中实现持久化的多种方法：文件系统 fs、数据库。其中数据库又可以分为：

• 关系型数据库-mysql
• 文档型数据库-mongodb
• 键值对数据库-redis

本文只介绍如何用node去操作MySQL，深入学习MySQL可以参考MySQL教程。

一、Node.js原生驱动操作MySQL

使用mysql模块

原生驱动操作mysql，安装mysql模块：npm i mysql --save。

const mysql = require("mysql");
// MySQL的连接配置
const cfg = {
  host: "localhost",
  user: "root", 
  password: "", // 修改为你的密码
  database: "test" // 请确保数据库存在
};
// 创建连接对象
const conn = mysql.createConnection(cfg);

// 连接MySQL服务
conn.connect(err => {
  if (err) {
    throw err;
  } else {
    console.log("连接成功！");
  }
});

// 创建表sql
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test (
                    id INT NOT NULL AUTO_INCREMENT,
                    message VARCHAR(45) NULL,
                    PRIMARY KEY (id))`;
// 插入数据SQL
const INSERT_SQL = `INSERT INTO test(message) VALUES(?)`;
// 查询数据SQL
const SELECT_SQL = `SELECT * FROM test`;
conn.query(CREATE_SQL, err => {
  if (err) {
    throw err;
  }
  // 插入数据
  conn.query(INSERT_SQL, "hello,world", (err, result) => {
    if (err) {
      throw err;
    }
    console.log(result);
    conn.query(SELECT_SQL, (err, results) => {
        console.log(JSON.stringify(results));
        conn.end(); // 若query语句有嵌套，则end需在此执行
    })
  });
});

打印结果如下

连接成功！
OkPacket {
  fieldCount: 0,
  affectedRows: 1,  // 影响的行数，可用于判断有没有插入成功
  insertId: 1,  // 所插入的数据的id
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }
[{"id":1,"message":"hello,world"}]  // 查询出来的结果

使用mysql2模块

使用mysql2，就可以使用promise的写法了，结合 ES2017 async+await。改造代码如下

(async () => {
    const mysql = require('mysql2/promise')
    // 连接配置
    const cfg = {
        host: "localhost",
        user: "root",
        password: "", // 修改为你的密码
        database: "test", // 请确保数据库存在
        charset: 'utf8mb4' // 这里字符集用utf8mb4，这样就可以支持emoji表情字符了。
    }
    // 创建连接
    const connection = await mysql.createConnection(cfg)
    // 创建test表
    let ret = await connection.execute(`
        CREATE TABLE IF NOT EXISTS test (
            id INT NOT NULL AUTO_INCREMENT,
            message VARCHAR(45) NULL,
        PRIMARY KEY (id))
    `)
    console.log('create', ret)
    // 插入记录
    ret = await connection.execute(`
            INSERT INTO test(message)
            VALUES(?)
    `, ['ABC'])
    console.log('insert:', ret)
    // 查询记录
    const [rows] = await connection.execute(`SELECT * FROM test`)
    console.log('select:',rows)  // 返回结果，是一个数组
    connection.end()
})()    

二、Sequelize 操作MySQL

Sequelize是基于Promise的ORM(Object Relation Mapping)，支持多种数据库、事务、关联等。安装： npm i sequelize -S。参考。

基本使用

(async () => {
  const Sequelize = require("sequelize");
  // 建立连接，参数分别是数据库名，用户名，密码
  const sequelize = new Sequelize("test", "root", "", {
      host: "localhost",
      dialect: "mysql",  //数据库类型：'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql'
      timezone: '+08:00' //时区转换
  });

  // 定义Fruit模型，类似于定义表的结构
  const Fruit = sequelize.define("Fruit", {
      // uuid
      id: {
          type: Sequelize.DataTypes.UUID,
          defaultValue: Sequelize.DataTypes.UUIDV1,
          primaryKey: true
      },
      name: { type: Sequelize.STRING(20), allowNull: false },
      price: { type: Sequelize.FLOAT, allowNull: false },
      stock: { type: Sequelize.INTEGER, defaultValue: 0 }
  }, { tableName: 'fruit' });  // 指定表名为fruit

  let ret = await Fruit.sync({ force: false });
  const Op = Sequelize.Op;

  //创建一张表，force: true则会删除已存在表，为false则不会删除已存在表
  ret = await Fruit.sync({ force: false }); 
  console.log(ret);

  // 创建一条数据
  ret = await Fruit.create({
      name: "香蕉",
      price: 3.5
  })
  console.log('create', ret)
})();

增删改查

sql、orm对应关系

sql	    orm
-----------------------
select	findAll,findOne,findById,findOrCreate,findAndCountAll
-----------------------
update	update
-----------------------
insert	create
-----------------------
delete	destroy
-----------------------

• 数据查询

// 根据id查询
ret = await Fruit.findByPk(1);  // 根据id查询
console.log(ret);
// 查询所有数据
ret = await Fruit.findAll();
console.log("findAll", JSON.stringify(ret,'','\t'));
// 按条件查询
ret = await Fruit.findAll({
    where: {
        name: '香蕉'
    }
});
console.log(ret)
// 按条件范围查询(4<=price<=5)，这里Op.lte是小于等于，Op.gte是大于等于的意思
ret = await Fruit.findAll({
    where: { price: { [Op.lte]: 5, [Op.gte]: 4 } }  // 4<=price<=5
});
console.log(ret)
// 通过属性查询
Fruit.findOne({ where: { name: "香蕉" } }).then(fruit => {
    // fruit是首个匹配项，若没有则为null
    console.log(fruit.get());
});
// 查询结果结果只包含某些字段
Fruit.findOne({ attributes: ['name'] }).then(fruit => {
    // fruit是首个匹配项，若没有则为null
    console.log(fruit.get());
});
// 查询结果结果过滤某些字段，过滤name总字段
Fruit.findOne({ attributes: { exclude: ['name'] } }).then(fruit => {
    // fruit是首个匹配项，若没有则为null
    console.log(fruit.get());
});
// 获取数据和总条数
Fruit.findAndCountAll().then(result => {
    console.log(result)
    console.log(result.count);  // result.count总条数
    console.log(result.rows.length); // result.rows.length总条数
});
// 或语句
Fruit.findAll({
    // where: { [Op.or]:[{price: { [Op.lt]:4 }}, {stock: { [Op.gte]: 100 }}] }  // price<4||stock>=100
    where: { price: { [Op.or]: [{ [Op.gt]: 3 }, { [Op.lt]: 2 }] } } // price>3||price<2
}).then(fruits => {
    console.log(fruits[0].get());
});
// 分页
Fruit.findAll({
    offset: 0,
    limit: 2,
})
// 排序
Fruit.findAll({
    order: [['price', 'DESC']]  // 安装价格降序排列
})
// 聚合
Fruit.max("price").then(max => {
    console.log("max", max); // 找出价格最大的price
});
Fruit.sum("price").then(sum => {
    console.log("sum", sum); // 把price进行求和
});

• 数据更新

更新一条

Fruit.findOne({ where: { name: '香蕉' } }).then(fruit => {
    // 方式1
    fruit.price = 4;
    fruit.save().then(() => console.log('update!!!!'));
});

更新多条

Fruit.findAll({ where: { name: '香蕉' } }).then(fruit => {
    fruit.map(t => {
        t.price = 5;
        t.save().then(() => console.log('update!!!!'));
    });
});

使用update方法会自动更新一条或多条，即满足条件的都会更新，推荐使用。

Fruit.update({ price: 6 }, { where: { name: '香蕉' } }).then(r => {
    console.log(r[0]); // 影响的行数
    console.log('update!!!!')
})

• 删除

// 方式1，只能删除1条
Fruit.findOne({ where: { id: 1 } }).then(r => r.destroy()); 
// 方式2，删除满足条件的都会删除
Fruit.destroy({ where: { id: 1 } }).then(r => console.log(r));

sequelize 的op模块

const Op = Sequelize.Op

[Op.and]: {a: 5}           // 且 (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 或 a = 6)
[Op.gt]: 6,                // id > 6
[Op.gte]: 6,               // id >= 6
[Op.lt]: 10,               // id < 10
[Op.lte]: 10,              // id <= 10
[Op.ne]: 20,               // id != 20
[Op.eq]: 3,                // = 3
[Op.not]: true,            // 不是 TRUE
[Op.between]: [6, 10],     // 在 6 和 10 之间
[Op.notBetween]: [11, 15], // 不在 11 和 15 之间
[Op.in]: [1, 2],           // 在 [1, 2] 之中
[Op.notIn]: [1, 2],        // 不在 [1, 2] 之中
[Op.like]: '%hat',         // 包含 '%hat'
[Op.notLike]: '%hat'       // 不包含 '%hat'
[Op.iLike]: '%hat'         // 包含 '%hat' (不区分大小写)  (仅限 PG)
[Op.notILike]: '%hat'      // 不包含 '%hat'  (仅限 PG)
[Op.regexp]: '^[h|a|t]'    // 匹配正则表达式/~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.notRegexp]: '^[h|a|t]' // 不匹配正则表达式/!~ '^[h|a|t]' (仅限 MySQL/PG)
[Op.iRegexp]: '^[h|a|t]'    // ~* '^[h|a|t]' (仅限 PG)
[Op.notIRegexp]: '^[h|a|t]' // !~* '^[h|a|t]' (仅限 PG)
[Op.like]: { [Op.any]: ['cat', 'hat']} // 包含任何数组['cat', 'hat'] - 同样适用于 iLike 和 notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG数组重叠运算符)
[Op.contains]: [1, 2]      // @> [1, 2] (PG数组包含运算符)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG数组包含于运算符)
[Op.any]: [2,3]            // 任何数组[2, 3]::INTEGER (仅限PG)
[Op.col]: 'user.organization_id' // = 'user'.'organization_id', 使用数据库语言特定的列标识符, 本例使用 PG

其它

• 强制同步：创建表之前先删除已存在的表

Fruit.sync({force: true})

• 避免自动生成时间戳字段

const Fruit = sequelize.define("Fruit", {}, {
  timestamps: false
});

• 时区转换

const sequelize = new Sequelize("test", "root", "", {
    host: "localhost",
    dialect: "mysql",
    timezone: '+08:00' //时区转换
});

• 指定表名： freezeTableName: true 或 tableName:'xxx'

设置前者则以modelName作为表名；设置后者则按其值作为表名。蛇形命名 underscored: true,默认驼峰命名。

const Fruit = sequelize.define("Fruit", {}, {
  tableName: 'fruit'   // 指定表名为fruit
});

• UUID-主键

UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。在UUID的算法中，可能会用到诸如网卡MAC地址，IP，主机名，进程ID等信息以保证其独立性。

id: {
    type: Sequelize.DataTypes.UUID,
    defaultValue: Sequelize.DataTypes.UUIDV1,
    primaryKey: true
}

不要使用自增id，有安全隐患，要使用UUID。uuid优缺点如下：

优点：

能够保证独立性，程序可以在不同的数据库间迁移，效果不受影响。 保证生成的ID不仅是表独立的，而且是库独立的，这点在你想切分数据库的时候尤为重要。

缺点：

比较占地方，和INT类型相比，存储一个UUID要花费更多的空间。 使用UUID后，URL显得冗长，不够友好。

• Getters & Setters：可用于定义伪属性或映射到数据库字段的保护属性

// 定义为属性的一部分
name: {
    type: Sequelize.STRING,
    allowNull: false,
    get() {
        const fname = this.getDataValue("name");
        const price = this.getDataValue("price");
        const stock = this.getDataValue("stock");
        return `${fname}(价格：￥${price} 库存：${stock}kg)`;
    }
}
// 定义为模型选项
// options中
{
    getterMethods:{
        amount(){
            return this.getDataValue("stock") + "kg";
        }
    },
    setterMethods:{
        amount(val){
            const idx = val.indexOf('kg');
            const v = val.slice(0, idx);
            this.setDataValue('stock', v);
        }
    }
}

// 通过模型实例触发setterMethods
Fruit.findAll().then(fruits => {
    console.log(JSON.stringify(fruits));
    // 修改amount，触发setterMethods
    fruits[0].amount = '150kg';
    fruits[0].save();
});

• 校验：可以通过校验功能验证模型字段格式、内容，校验会在 create 、 update 和 save 时自动运行。

price: {
    validate: {
        isFloat: { msg: "价格字段请输入数字" },
        min: { args: [0], msg: "价格字段必须大于0" }
    }
},
stock: {
    validate: {
        isNumeric: { msg: "库存字段请输入数字" }
    }
}

• 模型扩展：可添加模型实例方法或类方法扩展模型

// 添加类级别方法
Fruit.classify = function(name) {
    const tropicFruits = ['香蕉', '芒果', '椰子']; // 热带水果
    return tropicFruits.includes(name) ? '热带水果':'其他水果';
};
// 添加实例级别方法
Fruit.prototype.totalPrice = function(count) {
    return (this.price * count).toFixed(2);
};
// 使用类方法
['香蕉','草莓'].forEach(f => console.log(f+'是'+Fruit.classify(f)));
// 使用实例方法
Fruit.findAll().then(fruits => {
    const [f1] = fruits;
    console.log(`买5kg${f1.name}需要￥${f1.totalPrice(5)}`);
});

三、关联查询

关联查询参考

1:1  belongsTo + hasOne
1:N  belongsTo + hasMany
N:N  belongsToMany      trough中间表

关于外键设在哪张表

hasOne、hasMany 可理解为拥有，belongsTo 可理解为属于（被拥有），外键都是设在被拥有的那一方。例如：

A.hasMany(B) 外键设在B里

B.belongsTo(A) 外键设在B里

一对一关联

一对一关联是通过单个外键连接两个模型的关联。

BelongsTo

BelongsTo 关联是在 source model 上存在一对一关系的外键的关联。一个简单的例子是 Player 通过 player 的外键作为 Team 的一部分。

const Player = this.sequelize.define('player', {/* attributes */});
const Team  = this.sequelize.define('team', {/* attributes */});

Player.belongsTo(Team); // 将向 Player 添加一个 teamId 属性以保存 Team 的主键值, teamId 就是 Player 的外键

外键

默认情况下，将从目标模型名称和目标主键名称生成 belongsTo 关系的外键。(上面的例子中，Team就是目标模型)

默认的样式是 camelCase，但是如果源模型配置为 underscored: true ，那么 foreignKey 将是snake_case。

const User = this.sequelize.define('user', {/* attributes */})
const Company  = this.sequelize.define('company', {/* attributes */});

User.belongsTo(Company); // 将 companyId 添加到 user

const User = this.sequelize.define('user', {/* attributes */}, {underscored: true})
const Company  = this.sequelize.define('company', {
  uuid: {
    type: Sequelize.UUID,
    primaryKey: true
  }
});

User.belongsTo(Company); // 将 company_uuid 添加到 user

在已定义 as 的情况下，将使用它代替目标模型名称。

const User = this.sequelize.define('user', {/* attributes */})
const UserRole  = this.sequelize.define('userRole', {/* attributes */});

User.belongsTo(UserRole, {as: 'role'}); // 将 role 添加到 user 而不是 userRole

目标键

目标键是源模型上的外键列指向的目标模型上的列。 默认情况下，belongsTo 关系的目标键将是目标模型的主键。 要定义自定义列，请使用 targetKey 选项。

const User = this.sequelize.define('user', {/* attributes */})
const Company  = this.sequelize.define('company', {/* attributes */});

User.belongsTo(Company, {foreignKey: 'fk_companyname', targetKey: 'name'}); // 添加 fk_companyname 到 User

HasOne 和 BelongsTo

下是一个示例，说明了 BelongsTo 和 HasOne 的用法。

const Player = this.sequelize.define('player', {/* attributes */})
const Coach  = this.sequelize.define('coach', {/* attributes */})
const Team  = this.sequelize.define('team', {/* attributes */});

假设我们的 Player 模型有关于其团队的信息为 teamId 列。 关于每个团队的 Coach 的信息作为 coachId 列存储在 Team 模型中。 这两种情况都需要不同种类的1：1关系，因为外键关系每次出现在不同的模型上。

• 当关于关联的信息存在于 source 模型中时，我们可以使用 belongsTo。 在这种情况下，Player 适用于 belongsTo，因为它具有 teamId 列。

Player.belongsTo(Team) // `teamId` 将被添加到 Player / Source 模型中

• 当关于关联的信息存在于 target 模型中时，我们可以使用 hasOne。 在这种情况下， Coach 适用于 hasOne ，因为 Team 模型将其 Coach 的信息存储为 coachId 字段。

Coach.hasOne(Team) // `coachId` 将被添加到 Team / Target 模型中

一对多关联

一对多关联将一个来源与多个目标连接起来。 而多个目标接到同一个特定的源。

const User = sequelize.define('user', {/* ... */})
const Project = sequelize.define('project', {/* ... */})

好。 现在，事情变得更加复杂（对用户来说并不真实可见）。首先我们来定义一个 hasMany 关联

Project.hasMany(User, {as: 'Workers'})

这将添加属性 projectId 或 project_id 到 User。 Project 的实例将获得访问器 getWorkers 和 setWorkers。 我们让它保持原样，让它成为单向关联。 但是我们想要更多！ 让我们在下一节中以其他方式定义并创建一个多对多的关联：

有时您可能需要在不同的列上关联记录，您可以使用 sourceKey 选项：

const City = sequelize.define('city', { countryCode: Sequelize.STRING });
const Country = sequelize.define('country', { isoCode: Sequelize.STRING });

// 在这里，我们可以根据国家代码连接国家和城市
Country.hasMany(City, {foreignKey: 'countryCode', sourceKey: 'isoCode'});
City.belongsTo(Country, {foreignKey: 'countryCode', targetKey: 'isoCode'});

例子

(async () => {
    // 1:N关系
    const Sequelize = require("sequelize");

    // 建立连接
    const sequelize = new Sequelize("kaikeba", "root", "", {
        host: "localhost",
        dialect: "mysql",
        operatorsAliases: false
    });
    const Player = sequelize.define('player', { name: Sequelize.STRING });
    const Team = sequelize.define('team', { name: Sequelize.STRING });
    Player.belongsTo(Team); // N端建立关系
    Team.hasMany(Player); // 1端建立关系

    // 同步数据库，force: true则会删除已存在表
    sequelize.sync({ force: true }).then(async () => {
        await Team.create({ name: '火箭' });
        await Player.bulkCreate([{ name: '哈登', teamId: 1 }, { name: '保罗', teamId: 1 }]);

        // N端关联查询  
        const players = await Player.findAll({ include: [Team] });
        console.log(JSON.stringify(players, null, 2));

        // 1端关联查询
        const team = await Team.findOne({ where: { name: '火箭' }, include: [Player] });
        console.log(JSON.stringify(team, null, 2));

        const team1 = await Team.findByPk(1);   // 查找id为1的team，会返回Team实例
        console.log(await team1.getPlayers());  // 实例包含了getPlayes和setPlayers方法
    });
})()

这里 Team.findOne({ where: { name: '火箭' }, include: [Player] }) 使用 include 语法表示用关联查询。

多对多关联

多对多关联用于将源与多个目标相连接。 此外，目标也可以连接到多个源。

Project.belongsToMany(User, {through: 'UserProject'});
User.belongsToMany(Project, {through: 'UserProject'});

这将创建一个名为 UserProject 的新模型，具有等效的外键projectId和userId。 属性是否为camelcase取决于由表（在这种情况下为User和Project）连接的两个模型。

定义 through 为 required。 Sequelize 以前会尝试自动生成名称，但并不总是导致最合乎逻辑的设置。

这将添加方法 getUsers, setUsers, addUser,addUsers 到 Project, 还有 getProjects, setProjects, addProject, 和 addProjects 到 User.

有时，您可能需要在关联中使用它们时重命名模型。 让我们通过使用别名（as）选项将 users 定义为 workers 而 projects 定义为t asks。 我们还将手动定义要使用的外键：

User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId' })
Project.belongsToMany(User, { as: 'Workers', through: 'worker_tasks', foreignKey: 'projectId' })

foreignKey 将允许你在 through 关系中设置 source model 键。

otherKey 将允许你在 through 关系中设置 target model 键。

User.belongsToMany(Project, { as: 'Tasks', through: 'worker_tasks', foreignKey: 'userId', otherKey: 'projectId'})

当然你也可以使用 belongsToMany 定义自我引用：

Person.belongsToMany(Person, { as: 'Children', through: 'PersonChildren' })
// 这将创建存储对象的 ID 的表 PersonChildren。

如果您想要连接表中的其他属性，则可以在定义关联之前为连接表定义一个模型，然后再说明它应该使用该模型进行连接，而不是创建一个新的关联：

const User = sequelize.define('user', {})
const Project = sequelize.define('project', {})
const UserProjects = sequelize.define('userProjects', {
    status: DataTypes.STRING
})
 
User.belongsToMany(Project, { through: UserProjects })
Project.belongsToMany(User, { through: UserProjects })

要向 user 添加一个新 project 并设置其状态，您可以将额外的 options.through 传递给 setter，其中包含连接表的属性

user.addProject(project, { through: { status: 'started' }})

默认情况下，上面的代码会将 projectId 和 userId 添加到 UserProjects 表中， 删除任何先前定义的主键属性 - 表将由两个表的键的组合唯一标识，并且没有其他主键列。 要在 UserProjects 模型上强添加一个主键，您可以手动添加它。

const UserProjects = sequelize.define('userProjects', {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  status: DataTypes.STRING
})

使用多对多你可以基于 through 关系查询并选择特定属性。 例如通过 through 使用findAll

User.findAll({
  include: [{
    model: Project,
    through: {
      attributes: ['createdAt', 'startedAt', 'finishedAt'],
      where: {completed: true}
    }
  }]
});

实例

(async () => {
    // N:N关系
    const Sequelize = require("sequelize");

    // 建立连接
    const sequelize = new Sequelize("kaikeba", "root", "", {
        host: "localhost",
        dialect: "mysql",
    });

    const Fruit = sequelize.define("fruit", { name: Sequelize.STRING });
    const Category = sequelize.define("category", { name: Sequelize.STRING });
    Fruit.belongsToMany(Category, {
        through: "FruitCategory"
    });
    Category.belongsToMany(Fruit, {
        through: "FruitCategory"
    });

    // 插入测试数据
    sequelize.sync({ force: true }).then(async () => {
        await Fruit.create(
            {
                name: "香蕉",
                categories: [{ id: 1, name: "热带" }, { id: 2, name: "温带" }]
            },
            {
                include: [Category]
            }
        );
        // 多对多联合查询
        const fruit = await Fruit.findOne({
            where: { name: "香蕉" }, // 通过through指定条件、字段等
            include: [{ model: Category, through: { attributes: ['id', 'name'] } }]
        });
        console.log(fruit)
    })
})()

四、连接池

数据库连接是一种关键的、有限的、昂贵的资源，这一点在多用户的网页应用程序中体现得尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。

连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时，还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。

设置pool选项即创建了连接池。

const sequelize = new Sequelize("test", "root", "example", {
    host: "localhost",
    dialect: "mysql",
    // operatorsAliases: false,
    pool: {
        max: 10,  // 设置最大并发数
        min: 0,
        idle: 30000
    }
});

参考：sequelize教程