缘由

之前已经将接口测试参数化做好了,踩了一些坑,现在基本上搞定了。
接下来的计划是做一个接口测试服务,计划的方案是:
前端:VUE+axios ==> 完全没接触到,从头学起
后端:Spring boot + mybatis + REST
数据库:Mysql

所以按着之前的接口测试参数化方案写了几张表

sql

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id,自增主键',
    `account` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名',
    `password` varchar(64) NOT NULL DEFAULT '' DEFAULT '0' COMMENT '密码',
    `email` varchar(256) DEFAULT '' COMMENT '邮箱',
    `role` varchar(16) DEFAULT 'USER' COMMENT '用户权限',

    PRIMARY KEY (`user_id`),
    UNIQUE KEY `UNIQUE_account` (`account`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

DROP TABLE IF EXISTS `tb_project`;
CREATE TABLE `tb_project` (
    `project_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `project_name` varchar(64) NOT NULL DEFAULT '' COMMENT '项目名',
    `project_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '项目描述',

    PRIMARY KEY (`project_id`),
    UNIQUE KEY `UNIQUE_project_name` (`project_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试项目表';

DROP TABLE IF EXISTS `tb_project_global_host`;
CREATE TABLE `tb_project_global_host` (
    `global_host_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `project_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '项目id',
    `global_host_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '全局地址描述',
    `global_host_url` varchar(128) NOT NULL DEFAULT '' COMMENT '全局地址',

    PRIMARY KEY (`global_host_id`),
    KEY `idx_project_id` (`project_id`),
    UNIQUE KEY `UNIQUE_global_host_desc` (`global_host_desc`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='项目全局地址表';

DROP TABLE IF EXISTS `tb_project_global_parameters`;
CREATE TABLE `tb_project_global_parameters` (
    `global_parameters_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `project_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '项目id',
    `global_parameters_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '全局参数描述',
    `global_parameters` varchar(512) NOT NULL DEFAULT '' COMMENT '全局参数',

    PRIMARY KEY (`global_parameters_id`),
    KEY `idx_project_id` (`project_id`),
    UNIQUE KEY `UNIQUE_global_parameters_desc` (`global_parameters_desc`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='项目全局参数表';

DROP TABLE IF EXISTS `tb_project_global_asserts`;
CREATE TABLE `tb_project_global_asserts` (
    `global_asserts_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `project_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '项目id',
    `global_asserts_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '全局断言描述',
    `global_asserts` varchar(512) NOT NULL DEFAULT '' COMMENT '全局断言',

    PRIMARY KEY (`global_asserts_id`),
    KEY `idx_project_id` (`project_id`),
    UNIQUE KEY `UNIQUE_global_asserts_desc` (`global_asserts_desc`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='项目全局断言表';

DROP TABLE IF EXISTS `tb_feature`;
CREATE TABLE `tb_feature` (
    `feature_id` int(11) NOT NULL AUTO_INCREMENT,
    `feature_name` varchar(64) NOT NULL DEFAULT '' COMMENT '模块名',
    `feature_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '模块描述',
    `project_id` int(11) NOT NULL DEFAULT '0' COMMENT '项目id',

    PRIMARY KEY (`feature_id`),
    UNIQUE KEY `UNIQUE_feature_name` (`feature_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试模块表';

DROP TABLE IF EXISTS `tb_api_test_data`;
CREATE TABLE `tb_api_test_data` (
    `data_id` int(11) NOT NULL AUTO_INCREMENT,
    `data_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '数据描述',
    `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',

    PRIMARY KEY (`data_id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试数据表';

DROP TABLE IF EXISTS `tb_api_test_data_values`;
CREATE TABLE `tb_api_test_data_values` (
    `value_id` int(11) NOT NULL AUTO_INCREMENT,
    `data_id` int(11) NOT NULL DEFAULT '0' COMMENT '数据id',
    `data_value` text NOT NULL COMMENT '数据内容',

    PRIMARY KEY (`value_id`),
    KEY `idx_data_id` (`data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试数据内容表';

DROP TABLE IF EXISTS `tb_api_test_case`;
CREATE TABLE `tb_api_test_case` (
  `case_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `project_id` int(11) NOT NULL DEFAULT '0' COMMENT '项目id',
  `feature_id` int(11) DEFAULT '0' COMMENT '模块id',
  `case_name` varchar(512) NOT NULL DEFAULT '' COMMENT '用例名',
  `case_desc` varchar(512) NOT NULL DEFAULT '' COMMENT '用例描述',
  `url` varchar(512) NOT NULL DEFAULT '' COMMENT '接口地址',
  `req_content_type` varchar(256) NOT NULL DEFAULT '' COMMENT '请求参数类型,默认json',
  `req_body` text NOT NULL COMMENT '请求体',
  `res_content_type` varchar(256) NOT NULL DEFAULT 'json' COMMENT '响应参数类型,默认json',
  `method` tinyint(1) NOT NULL DEFAULT '0' COMMENT '请求类型,0表示post,1表示get',
  `case_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '用例状态,0表示在使用,1表示已废弃',
  `case_comment` varchar(512) NOT NULL DEFAULT '' COMMENT '备注信息',
  `update_time` int(10) NOT NULL DEFAULT '0' COMMENT '用例更新时间',
  `res_assert` varchar(2048) NOT NULL DEFAULT '' COMMENT '响应断言',
  `sql_assert` varchar(2048) NOT NULL DEFAULT '' COMMENT 'sql断言',
  `batch_data` varchar(512) NOT NULL DEFAULT '' COMMENT '批量数据',
  `batch_data_use_one` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否每次使用批量数据中的一个',

  PRIMARY KEY (`case_id`),
  UNIQUE KEY `case_name` (`case_name`),
  KEY `idx_project_id` (`project_id`),
  KEY `idx_feature_id` (`feature_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试用例表';

DROP TABLE IF EXISTS `tb_api_test_sql`;
CREATE TABLE `tb_api_test_sql` (
    `sql_id` int(11) NOT NULL AUTO_INCREMENT,
    `case_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口测试用例ID',
    `column_name` varchar(32) NOT NULL DEFAULT '' COMMENT '如果是查询语句,需要获取的列名',
    `local_json_path` varchar(256) NOT NULL DEFAULT '' COMMENT '接口测试用例的jsonpath',
    `db_url` varchar(512) NOT NULL DEFAULT '' COMMENT '数据库地址',
    `db_user` varchar(64) NOT NULL DEFAULT '' COMMENT '数据库用户名',
    `db_password` char(8) NOT NULL DEFAULT '' COMMENT '数据库密码',
    `sql_type` enum('delete','update','insert','select') NOT NULL DEFAULT 'select' COMMENT 'sql语句类型',
    `sql_statement` varchar(512) NOT NULL DEFAULT '' COMMENT 'sql语句',
    `is_before` tinyint(1) NOT NULL DEFAULT '0' COMMENT '前置sql还是后置sql,0表示前置,1表示后置',

    PRIMARY KEY (`sql_id`),
    KEY `idx_case_id` (`case_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试用例需要执行的sql表';

DROP TABLE IF EXISTS `tb_api_test_reference_case`;
CREATE TABLE `tb_api_test_reference_case` (
    `rec_id` int(11) NOT NULL AUTO_INCREMENT,
    `case_id` int(11) NOT NULL DEFAULT '0' COMMENT '接口测试用例ID',
    `reference_case_id` int(11) NOT NULL DEFAULT '0' COMMENT '依赖的接口测试用例ID',
    `reference_body_type` tinyint(1) NOT NULL DEFAULT '0' COMMENT '依赖的接口测试用例请求,还是响应,0表示响应,1表示请求',
    `remote_json_path` varchar(256) NOT NULL DEFAULT '' COMMENT '依赖的接口测试用例的jsonpath',
    `local_json_path` varchar(256) NOT NULL DEFAULT '' COMMENT '接口测试用例的jsonpath',

    PRIMARY KEY (`rec_id`),
    KEY `idx_case_id` (`case_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试用例与依赖用例关系表';

DROP TABLE IF EXISTS `tb_api_test_suite`;
CREATE TABLE `tb_api_test_suite` (
    `suite_id` int(11) NOT NULL AUTO_INCREMENT,
    `suite_name` varchar(512) NOT NULL DEFAULT '' COMMENT '测试用例集名称',
    `test_object` varchar(512) NOT NULL DEFAULT '' COMMENT '测试目的',
    `project_id` int(11) NOT NULL DEFAULT '0' COMMENT '项目id',

    PRIMARY KEY (`suite_id`),
    UNIQUE KEY `UNIQUE_suite_name` (`suite_name`),
    KEY `idx_project_id` (`project_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试用例集表';

DROP TABLE IF EXISTS `tb_api_test_suite_case_relation`;
CREATE TABLE `tb_api_test_suite_case_relation` (
    `rec_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '测试用例集与用例关系id',
    `suite_id` int(11) NOT NULL DEFAULT '0' COMMENT '测试用例集id',
    `case_id` int(11) NOT NULL DEFAULT '0' COMMENT '测试用例id',

    PRIMARY KEY (`rec_id`),
    KEY `idx_suite_id` (`suite_id`),
    KEY `idx_case_id` (`case_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试用例集与用例关系表';

DROP TABLE IF EXISTS `tb_api_test_task`;
CREATE TABLE `tb_api_test_task` (
    `task_id` int(11) NOT NULL AUTO_INCREMENT,
    `task_name` varchar(512) NOT NULL DEFAULT '' COMMENT '接口测试任务名称',
    `suite_id` int(11) NOT NULL DEFAULT '0' COMMENT '测试用例集id',
    `global_parameters_id` int(11) NOT NULL DEFAULT '0' COMMENT '全局参数id',
    `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',

    PRIMARY KEY (`task_id`),
    UNIQUE KEY `UNIQUE_task_name` (`task_name`),
    KEY `idx_suite_id` (`suite_id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_global_parameters_id` (`global_parameters_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='接口测试任务表';

DROP TABLE IF EXISTS `tb_api_test_result`;
CREATE TABLE `tb_api_test_result` (
  `result_id` int(11) NOT NULL AUTO_INCREMENT,
  `suite_id` int(11) NOT NULL DEFAULT '0',
  `case_id` int(11) NOT NULL DEFAULT '0',
  `result` enum('Pass','Fail','Skip','') NOT NULL DEFAULT '' COMMENT '用例测试结果',
  `error_info` mediumtext NOT NULL COMMENT '异常堆栈信息',
  `res_body` longtext NOT NULL COMMENT '接口响应内容',
  `case_start_time` bigint(13) unsigned NOT NULL COMMENT '用例开始时间',
  `case_end_time` bigint(13) unsigned NOT NULL COMMENT '用例结束时间',
  `case_run_time` int(13) unsigned NOT NULL COMMENT '用例执行用时',

  PRIMARY KEY (`result_id`),
  KEY `idx_resultId_caseRunTime` (`result_id`,`case_id`,`result`,`case_run_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='接口测试用例结果表';

DROP TABLE IF EXISTS `tb_feedback`;
CREATE TABLE `tb_feedback` (
    `feedback_id` int(11) NOT NULL AUTO_INCREMENT,
    `feedback_desc` text NOT NULL COMMENT '反馈描述',
    `feedback_status` enum('solved','unsolved','not-solve') NOT NULL DEFAULT 'unsolved'  COMMENT '反馈状态',
    `solution` text COMMENT '解决方案', 
    `user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',

    PRIMARY KEY (`feedback_id`),
    KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='反馈表';

DROP TABLE IF EXISTS `tb_image_flow`;
CREATE TABLE `tb_image_flow` (
    `flow_id` int(11) NOT NULL AUTO_INCREMENT,
    `url` text NOT NULL COMMENT '图片链接',
    `frame_len` int(11) NOT NULL DEFAULT '0' COMMENT '流量消耗',
    `image_size` int(11) NOT NULL DEFAULT '0' COMMENT '图片大小',
    `response_time` int(11) NOT NULL DEFAULT '0' COMMENT '响应时间',
    `test_time` int(10) NOT NULL DEFAULT '0' COMMENT '测试时间',

    PRIMARY KEY (`flow_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='app图片大小统计表';

问题

自问自答

问:用户 id 和用户名都是唯一的,那么用 user_name 作主键不就好了?
答:1. user_name 在某些场景可能允许修改
2. user_name 是 varchar 型的,判断是否重复需要遍历每个字符,效率上肯定不如 int 型的

其他 mysql 相关知识:

  1. 通用数字就别用字符串,最小的数字占 8 位(tinyint),最大的 64 位(bigint)
  2. 定长或修改较少的列使用 char
  3. BLOB 使用二进制存储字符串;TEXT 使用字符存储,存储的字符很长时,可以按字符的前面多少位来排序,ORDER BY SUSTRING(col, len),或修改配置:max_sort_length

范式

范式 :英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪 70 年代提出关系数据库模型后总结出来的,范式是关系数据库理论的基础,也是我们在设计数据库结构过程 中所要遵循的规则和指导方法。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式,才能设计出高效率、优雅的数据库,否则可能会设 计出错误的数据库。目前有迹可寻的共有 8 种范式,依次是:1NF,2NF,3NF,BCNF,4NF,5NF,DKNF,6NF。
满足最低要求的叫第一范 式,简称 1NF。在第一范式基础上进一步满足一些要求的为第二范式,简称 2NF。其余依此类推。通常所用到的只是前三个范式,即:第一范式(1NF),第 二范式(2NF),第三范式(3NF)

第一范式

第一范式是指数据库表中的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。

如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。第一范式的模式要求属性值不可再分裂成更小部分,即属性项不能是属性组合或是由一组属性构成。

简而言之,第一范式就是无重复的列。

第二范式

第二范式 (2NF) 是在第一范式 (1NF) 的基础上建立起来的,即满足第二范式 (2NF) 必须先满足第一范式 (1NF)。第二范式 (2NF) 要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。

如果关系模型 R 为第一范式,并且 R 中的每一个非主属性完全函数依赖于 R 的某个候选键,则称 R 为第二范式模式 (如果 A 是关系模式 R 的候选键的一个属性,则称 A 是 R 的主属性,否则称 A 是 R 的非主属性)。

例如,在选课关系表 (学号,课程号,成绩,学分),关键字为组合关键字 (学号,课程号),但由于非主属性学分仅依赖于课程号,对关键字 (学号,课程号) 只是部分依赖,而不是完全依赖,因此此种方式会导致数据冗余以及更新异常等问题,解决办法是将其分为两个关系模式:学生表 (学号,课程号,分数) 和课程表 (课程号,学分),新关系通过学生表中的外关键字课程号联系,在需要时进行连接。

第三范式

如果关系模型 R 是第二范式,且每个非主属性都不传递依赖于 R 的候选键,则称 R 是第三范式的模式。

以学生表 (学号,姓名,课程号,成绩) 为例,其中学生姓名无重名,所以该表有两个候选码 (学号,课程号) 和 (姓名,课程号),故存在函数依赖:学号——>姓名,(学号,课程号)——>成绩,唯一的非主属性成绩对码不存在部分依赖,也不存在传递依赖,所以属性属于第三范式。

BC 范式

它构建在第三范式的基础上,如果关系模型 R 是第一范式,且每个属性都不传递依赖于 R 的候选键,那么称 R 为 BCNF 的模式。
假设仓库管理关系表 (仓库号,存储物品号,管理员号,数量),满足一个管理员只在一个仓库工作;一个仓库可以存储多种物品,则存在如下关系:

(仓库号,存储物品号)——>(管理员号,数量)

(管理员号,存储物品号)——>(仓库号,数量)

所以,(仓库号,存储物品号) 和 (管理员号,存储物品号) 都是仓库管理关系表的候选码,表中唯一非关键字段为数量,它是符合第三范式的。但是,由于存在如下决定关系:

(仓库号)——>(管理员号)

(管理员号)——>(仓库号)

即存在关键字段决定关键字段的情况,因此其不符合 BCNF。把仓库管理关系表分解为两个关系表仓库管理表 (仓库号,管理员号) 和仓库表 (仓库号,存储物品号,数量),这样这个数据库表是符合 BCNF 的,并消除了删除异常、插入异常和更新异常。

反范式

反范式是通过增加冗余数据或数据分组来提高数据库读性能的过程,主要是通过空间来换时间。
在某些情况下, 反范式有助于掩盖关系型数据库软件的低效。关系型的范式数据库即使做过优化, 也常常会带来沉重的访问负载。

范式与反范式的比较

  1. 查询记录时,范式模式往往要进行多表连接,而反范式只需在同一张表中查询,当数据量很大的时候,显然反范式的效率会更好。
  2. 反范式有很多重复的数据,会占用更多的内存,查询时可能会较多地使用 DROUP BY 或 DISTINCT 等耗时耗性能的关键字。
  3. 当要修改更新数据时,范式更灵活,而反范式要修改全部的数据,且易出错。

参考:

https://www.zhihu.com/question/24696366
数据大小定义


↙↙↙阅读原文可查看相关链接,并与作者交流