MySql

[[toc]]

MySql

启动mysql

使用 docker 启动

# 启动容器
docker run --name efe-server \      # 容器名字                                                            
  -e MYSQL_ROOT_PASSWORD=6328158Rnnn \ # 密码
  -p 3306:3306 \ # 端口 默认 3306
  -d mysql:8.3.0 

# 进入容器
docker exec -it efe-server   mysql -uroot -p

使用 MySql

进入 MySQL 命令行界面后，你可以执行各种数据库管理和操作任务。以下是一些常见的操作示例：

1. 查看现有数据库

查看所有数据库：

SHOW DATABASES;

2. 创建一个新数据库

创建一个名为 testdb 的数据库：

CREATE DATABASE efe_db;

3. 使用某个数据库

选择并使用一个数据库：

USE efe_db;

4. 创建一个新表

在 testdb 数据库中创建一个名为 users 的表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sfm_files (
  id INT NOT NULL AUTO_INCREMENT COMMENT '主键',
  file_name varchar(20) DEFAULT NULL COMMENT '文件名',
  project_id INT DEFAULT NULL COMMENT '关联项目id',
  tos_key varchar(128) DEFAULT NULL COMMENT 'tos上存储的key',
  create_user_id varchar(13) DEFAULT NULL COMMENT '创建项目的',
  create_user_name varchar(20) DEFAULT NULL COMMENT '创建者的全名汉字',
  created_at datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  origin_size INT DEFAULT NULL COMMENT '原始尺寸',
  tinified_size INT DEFAULT NULL COMMENT '压缩后的尺寸',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='静态资源管理-全量文件表';

5. 插入数据

向 users 表中插入数据：

INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');

6. 查询数据

从 users 表中查询数据：

SELECT * FROM users;

7. 更新数据

更新 users 表中的数据：

UPDATE users SET email = 'john.doe@newdomain.com' WHERE name = 'John Doe';

8. 删除数据

删除 users 表中的数据：

DELETE FROM users WHERE name = 'John Doe';

9. 删除表

删除 users 表：

DROP TABLE users;

10. 删除数据库

删除 testdb 数据库：

DROP DATABASE testdb;

11. 创建新用户并授予权限

创建一个新用户并授予权限：

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;

12. 查看用户

查看所有用户：

SELECT user, host FROM mysql.user;

Egg-Sequelize实践

https://github.com/eggjs/egg-sequelize

egg-sequelize 是一个用于在 Egg.js 框架中集成 Sequelize ORM 的插件。Egg.js 是一个基于 Koa 的企业级 Node.js 框架，而 Sequelize 是一个基于 Promise 的 Node.js ORM（对象关系映射）库，用于与关系型数据库（如 MySQL、PostgreSQL、SQLite 和 MSSQL）进行交互。

如何使用 egg-sequelize

安装

首先，你需要在你的 Egg.js 项目中安装 egg-sequelize 和 sequelize 以及相应的数据库驱动，例如 MySQL：

yarn add egg-sequelize mysql2

配置

在项目的 config/plugin.js 文件中启用 egg-sequelize 插件：

exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
};

然后，在 config/config.default.js 文件中进行数据库配置：

config.sequelize = {
  dialect: 'mysql', // 使用的数据库类型
  host: 'localhost', // 数据库地址
  port: 3306, // 数据库端口
  database: 'efe_db', // 数据库名称
  username: 'root', // 数据库用户名
  password: '6328158Rnnn', // 数据库密码
  timezone: '+08:00', // 设置时区为东八区
  dialectOptions: {
    dateStrings: true, // 确保日期和时间以字符串形式返回
    typeCast: function (field, next) {
      // 自定义转换逻辑
      if (field.type === 'DATETIME' || field.type === 'TIMESTAMP') {
        return field.string(); // 返回字符串格式的日期时间
      }
      return next(); // 对于其他类型，使用默认转换
    }
  },
  define: {
    // 配置模型的全局选项
    timestamps: false, // 禁用自动添加时间戳字段
    freezeTableName: true, // 使用自定义表名，禁用表名复数化
  },
};

定义模型 model

egg-sequelize会自动将sequelize实例挂载到app.model上面，然后静态方法和属性则会直接被绑定到app上，通过app.Sequelize进行获取。

model层作为MVC的最底层，需要注意到数据模型的pure，model文件也应该是纯净的，这个文件里面应该是和数据库中的表一一对应，一个model文件对应一个DB中的表，这个文件中不应该包含任何和逻辑相关的代码，应该完全是数据模型的定义。

在 app/model 目录下创建模型文件，例如 user.js：

module.exports = app => {
  const { STRING, INTEGER } = app.Sequelize;

  const User = app.model.define('user', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(30),
    age: {
      type: INTEGER,
      allowNull: true // 允许为 NULL ; false:不允许为 NULL
      defaultValue: 0, // 默认值
      // 可以重写某个字段的字段名
      field: 'db_create_user',
    }
  },{
     timestamps: false, // 禁用自动添加时间戳字段  禁用了 createdAt 和 updatedAt 字段。
      freezeTableName: true, // 禁用表名复数化
    	tableName: 'user', // 强制指定表名
      underscored: true // 启用下划线命名法
  });

  // 定义关联关系
  User.associate = () => {
    // 定义多对多关联
    User.belongsToMany(app.model.Groups, {
      // 中间表的model
      through: app.model.groupUser,
      // 进行关联查询时，关联表查出来的数据模型的alias
      as: 'project',
      // 是否采用外键进行物理关联
      constraints: false,
    });
    // 这里如果一个模型和多个模型都有关联关系的话，关联关系需要统一定义在这里
  };
  
  return User;
};

使用模型 controller

在控制器或服务中使用模型进行数据操作：

// app/controller/user.js
const Controller = require('egg').Controller;

class UserController extends Controller {
  async index() {
    const ctx = this.ctx;
    const users = await ctx.model.User.findAll();
    ctx.body = users;
  }

  async create() {
    const ctx = this.ctx;
    const { name, age } = ctx.request.body;
    const user = await ctx.model.User.create({ name, age });
    ctx.body = user;
  }
}

module.exports = UserController;

service

egg官方文档对于service的描述是这样的：

简单来说，Service 就是在复杂业务场景下用于做业务逻辑封装的一个抽象层，提供这个抽象有以下几个好处：

• 保持 Controller 中的逻辑更加简洁。
• 保持业务逻辑的独立性，抽象出来的 Service 可以被多个 Controller 重复调用。
• 将逻辑和展现分离，更容易编写测试用例。

也就是controller中要尽量保持clean，然后，可以复用的业务逻辑被统一抽出来，放到service中，被多个controller进行复用。

我们将CRUD操作，全部提取到service中，封装成一个个通用的CRUD方法，来提供给其他service进行嵌套的时候调用，或者提供给controller进行业务逻辑调用。

比如：读取用户信息的过程：

// app/service/user.js
module.exports = class UserService extends Service {
    // 通过id获取用户信息
  async getUserById = ({id }) => {
    const { ctx } = this;
    let userInfo = {};
    try {
      userInfo = await ctx.model.User.findAll({
        where: {id},
        // 查询操作的时候，加入这个参数可以直接拿到对象类型的查询结果，否则还需要通过方法调用解析
        raw: true,
      });
    } catch (err) {
      ctx.logger.error(err);
    }

    return userInfo;
  }
}

sequelize事务

之前有说到，在建立模型的时候，我们建立了User和Group之间的关联关系，并且通过了一个关联表进行两者之间的关联。

由于我们没有建立两者之间的外键关联，所以在写入的时候，我们要进行逻辑的关联写入。

如果我们需要新建一个用户，并且为这个用户新建一个默认的group，由于组和用户有着多对多的关系，所以这里我们采用belongsToMany来建立关系。一个用户可以属于多个组，并且一组也可以包含多个用户。

在建立的时候，需要按照一定的顺序，写入三张表，一旦某个写入操作失败之后，需要对于之前的写入操作进行回滚，防止DB中产生垃圾数据。这里需要用到事务机制进行写入控制，并且人工保证写入顺序。

// app/service/user.js

module.exports = class UserService extends Service {
    async setUser = ({
        name,
    }) => {
        const { ctx } = this;
        let transaction;

        try {
            // 这里需要注意，egg-sequelize会将sequelize实例作为app.model对象
            transaction = await ctx.model.transaction();

            // 创建用户
            const user = await ctx.model.User.create({
                name,
            }, {
                transaction,
            });

            // 创建默认组
            const group = await ctx.model.Group.create({
                name: 'default',
            }, {
                transaction,
            });

            const userId = user && user.getDataValue('id');
            const groupId = group && group.getDataValue('id');

            if (!userId || !groupId) {
                throw new Error('创建用户失败');
            }

            // 创建用户和组之间的关联
            const associate = await ctx.mode.GroupUser.create({
                user_id: userId,
                group_id: groupId,
            }, {
                transaction,
            });

            await transaction.commit(); // 提交事务

            return userId;
        } catch (err) {
            ctx.logger.error(err);
            await transaction.rollback(); // 回滚事务
        }
    }
}

通过sequelize提供的事务功能，可以将串联写入过程中的错误进行回滚，保证了每次写入操作的原子性。