property-docs/规范/数据库/数据库设计规范.md

数据库设计规范

######版本号：V0.0.1，更新日期：2019-01-16

基本规范

• 数据库统一使用MySQL，数据库存储引擎统一使用InnoDB
• 字符集统一使用utf8mb4，排序规则统一使用utf8mb4_general_ci
• 数据表、数据字段必须加入中文注释
• 禁止使用存储过程，视图，触发器，Event
• 文件或图片统一存储在文件系统中，URI存储文件的相对路径
• 数据库连接优先使用内网域名，尽量避免使用IP连接
• 数据表必须设置主键，主键数据类型必须为递增的整数
• 不使用组合索引
• 所有字段必须是NOT NULL，并提供默认值
• 不适用ENUM枚举，使用tinyint替代，通过程序实现枚举功能
• 单表索引字段数建议不要超过5个
• 禁止使用SELECT *，只获取必要的字段
• 禁止使用属性隐式转换，如SELECT id FROM user_user WHERE mobile=13812345678，而是SELECT id FROM user_user WHERE mobile='13812345678'
• 禁止使用负向查询，如NOT，!=, <>, !<, !>, NOT IN, NOT LIKE等
• 建议对于同一个字段的OR查询改成IN查询，如SELECT id FROM user_user WHERE status=1 OR status=2，改成SELECT id FROM user_user WHERE status IN (1,2)
• 如非必要，尽量不使用联合查询，采用分步查询的方式实现更好
• 应用程序必须捕获SQL异常，并做处理，必须避免将SQL异常信息直接呈现给前端用户。
• 布尔类型的数值使用tinyint存储，0表示false，1表示true
• 涉及货币的数据存储统一用整型存储，出库计算时通过程序移位完成。
• 重要隐私信息、金融数据必须加密存储，如银行卡号。
• 密码必须使用不可逆加密方式存储，不得存储明文。
• 数值类型除非有特殊说明，否则默认设置为无符号整数。

命名规范

• 数据库名称统一使用{域名}.{租户名称}-{从库备份库标识}.{数据库环境}的方式命名。

• 域名不包括.com,.cn这种尾缀，如www.huiguanjia.cn，则域名为huiguanjia。

• 租户名称为租户唯一标识，若无租户则使用产品名称，如数据库为从库或备份库，则加上-slave或-backup。

• 数据库环境分为线上环境，标识为online；开发环境，标识为dev；测试环境，标识为test。

• 示例数据库名称如下：

  • huiguanjia.xch.online：绘管家新成海线上数据库
  • huiguanjia.xch.dev：绘管家新成海开发数据库
  • huiguanjia.xch.test：绘管家新成海测试数据库
  • huiguanjia.xch-salve.online：绘管家新成海线上从数据库
  • huiguanjia.xch-backup.online：绘管家新成海线上备份数据库

• 数据表字段统一使用小写字母，多个单词采用下划线分隔。单词不允许使用拼音，单个单词字符在15字符以内不允许缩写。

• 数据表名采用模块名_实体名的方式命名，，中间表两个实体时间通过to连接，表名称长度不得超过60个字符(MySQL最大表名称长度为64，Oracle为30)，建议表名在30个字符以内，示例数据表名如下：

  • service_feedback：物业服务反馈表
  • service_feedback_detail： 物业服务反馈详情表
  • charge_standard_to_house: 收费系统收费标准关联房屋表

• 数据表字段名禁用编程语言关键字，如abstract, int, string, public等。

常用字段

常用字段要求必须使用下表的字段，不允许重复新增新的字段名。

名称	数据库字段名	数据类型	最小长度	最大长度	备注
主键	id	String	32	32	数据表主键,字符均为阿拉伯数字
姓名	name	varchar	16	64	推荐长度设置为64
性别	sex	varchar	1	10	male=男，female=女
生日	birthday	datetime	-	-	格式：YYYY-MM-dd hh:mm:ss
手机号	mobile	varchar	16	32	推荐长度设置为20
电话号码	telephone	varchar	16	32	推荐长度设置为16
标题	title	varchar	64	128	推荐长度设置为128
摘要	summary	varchar	64	128	推荐长度设置为64
备注	remark	varchar	64	128	推荐长度设置为64
创建时间	created_at	datetime	-	-	格式：YYYY-MM-dd hh:mm:ss
更新时间	updated_at	datetime	-	-	格式：YYYY-MM-dd hh:mm:ss
创建人	created_by	String	32	32	存储数据创建者ID,字符均为阿拉伯数字
更新人	updated_by	String	32	32	存储最近一次更新者ID,字符均为阿拉伯数字
省份名	province_name	varchar	16	32	推荐长度设置为32
省份编号	province_id	int	9	11	推荐长度设置为10
城市名	city_name	varchar	16	32	推荐长度设置为32
城市编号	city_id	int	9	11	推荐长度设置为10
地区名	district_name	varchar	16	32	推荐长度设置为32
地区编号	district_id	int	9	11	推荐长度设置为10
经度	longitude	decimal	10,6	10,6
纬度	latitude	decimal	10,6	10,6
单价	price	decimal	8,2	16,6	推荐使用12,6，如果是参与计算的单价建议保留6位小数
删除标记	deleted	tinyint	1	1	1=已删除，0=正常
数据可见性	visible	tinyint	1	1	0=不可见，1=可见，仅适用于针对C端用户
是否启用	enable	tinyint	1	1	0=禁用，1=启用
排序号	sort	smallint	2	4	推荐使用2位，数值越大排序越靠前（即降序排列）

##业务表必备字段 业务表是指关系产品具体业务逻辑实现的数据表，不包括诸如日志，配置等表。业务表要求必须有以下字段：

• id：主键
• created_at：创建时间
• created_by: 创建人（如非后台创建的数据设置为'0'）
• updated_at: 最近一次更新时间(创建时等于创建时间)
• updated_by: 最近一次更新人（如非后台更新，设置为'0'，创建时等于创建人）
• deleted: 删除标记（默认为0，即正常状态）
• remark: 备注(默认为空字符串)