专业级的MySQL开发设计规范及SQL编写规范

在团队开发过程中为了项目的稳定，代码的高效，管理的便捷制定内部种开发设计规范是必不可少的，

这里分享一份我们定义MySQL开发设计规范包括表设计规范，字段设计规范，SQL编写规范

数据库对象命名规范

数据库对象

命名规范的对象是指数据库SCHEMA、表TABLE、索引INDEX、约束CONSTRAINTS等的命名约定

数据库对象命名原则

命名使用具有意义的英文词汇，词汇中间以下划线分隔

命名只能使用英文字母、数字、下划线

避免用MySQL的保留字如：call、group等

所有数据库对象使用小写字母

数据库命名规范

数据库名不能超过30个字符

数据库命名必须为项目英文名称或有意义的简写

数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8(已迁移dumbo的使用utf8mb4)

命名应使用小写

表命名规范

同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义

多个单词以下划线（_）分隔

表名不能超过30个字符

普通表名以t_开头，表示为table，命名规则为t_模块名（或有意义的简写）_+table_name

临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则：加上tmp前缀和8位时间后缀（tmp_test_user_20181109）

备份表（DBA备份用作保存历史数据的中间表）命名规则：加上bak前缀和8位时间后缀（bak_test_user_20181109）

命名应使用小写

字段命名规范

字段命名需要表示其实际含义的英文单词或简写，单词之间用下划线（_）进行连接

各表之间相同意义的字段必须同名

字段名不能超过30个字符

用户命名规范

生产使用的用户命名格式为 code_应用

只读用户命名规则为 read_应用

数据库对象设计规范

存储引擎的选择

如无特殊需求，必须使用innodb存储引擎

字符集的选择

如无特殊要求，必须使用utf8或utf8mb4

表设计规范

不同应用间所对应的数据库表之间的关联应尽可能减少，不允许使用外键对表之间进行关联，确保组件对应的表之间的独立性，为系统或表结构的重构提供可能性

表设计的角度不应该针对整个系统进行数据库设计，而应该根据系统架构中组件划分，针对每个组件所处理的业务进行数据库设计

表必须要有PK

一个字段只表示一个含义

表不应该有重复列

禁止使用复杂数据类型(数组,自定义等)

需要join的字段(连接键),数据类型必须保持绝对一致，避免隐式转换

设计应至少满足第三范式,尽量减少数据冗余。一些特殊场景允许反范式化设计，但在项目评审时需要对冗余字段的设计给出解释

TEXT字段必须放在独立的表中,用PK与主表关联。如无特殊需要，禁止使用TEXT、BLOB字段

需要定期删除(或者转移)过期数据的表,通过分表解决

单表字段数不要太多，建议最多不要大于50个

MySQL在处理大表时，性能就开始明显降低，所以建议单表物理大小限制在16GB，表中数据控制在2000W内

如果数据量或数据增长在前期规划时就较大，那么在设计评审时就应加入分表策略

无特殊需求，严禁使用分区表

字段设计规范

INT：如无特殊需要，存放整型数字使用UNSIGNED INT型。整型字段后的数字代表显示长度

DATETIME：所有需要精确到时间(时分秒)的字段均使用DATETIME,不要使用TIMESTAMP类型

VARCHAR：所有动态长度字符串 全部使用VARCHAR类型,类似于状态等有限类别的字段,也使用可以比较明显表示出实际意义的字符串,而不应该使用INT之类的数字来代替；VARCHAR(N)，N表示的是字符数而不是字节数。比如VARCHAR(255)，可以最大可存储255个字符（字符包括英文字母，汉字，特殊字符等）。但N应尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，且存储字符个数由所选字符集决定。如UTF8存储一个字符最大要3个字节，那么varchar在存放占用3个字节长度的字符时不应超过21845个字符。同时，在进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。(如无特殊需要，原则上单个varchar型字段不允许超过255个字符)

TEXT:仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类数据,因为所有MySQL数据库都会使用UTF8字符集。所有使用TEXT类型的字段必须和原表进行分拆,与原表主键单独组成另外一个表进行存放。如无特殊需要，严禁开发人员使用MEDIUMTEXT、TEXT、LONGTEXT类型

对于精确浮点型数据存储，需要使用DECIMAL，严禁使用FLOAT和DOUBLE

如无特殊需要，严禁开发人员使用BLOB类型

如无特殊需要，字段建议使用NOT NULL属性，可用默认值代替NULL

自增字段类型必须是整型且必须为UNSIGNED，推荐类型为INT或BIGINT，并且自增字段必须是主键或者主键的一部分

索引设计规范

索引必须创建在索引选择性选择性较高的列上,选择性的计算方式为: select count(distinct(col_name))/count(*) from tb_name;如果结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

组合索引的首字段,必须在where条件中，对于确定需要组成组合索引的多个字段,建议将选择性高的字段靠前放

禁止使用外键

Text类型字段如果需要创建索引，必须使用前缀索引

单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引

ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

尽量使用Btree索引，不要使用其它类型索引

约束设计规范

PK应该是有序并且无意义的,尽量由开发人员自定义,且尽可能短，使用自增序列。

表中除PK以外,还存在唯一性约束的,可以在数据库中创建以“uidx_”作为前缀的唯一约束索引。

PK字段不允许更新。

禁止创建外键约束,外键约束由应用控制。

如无特殊需要，所有字段必须添加非空约束，即not null。

如无特殊需要，所有字段必须有默认值。

SQL编写规范

尽量避免使用select *，join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数

严禁使用select * from table而不加任何where条件

MySQL中的text类型字段存储的时候不是和由其他普通字段类型的字段组成的记录存放在一起,而且读取效率本身也不如普通字段块。如果不需要取回text字段，又使用了select *,会让完成相同功能的sql所消耗的io量大很多,而且增加部分的io效率也更低下

在取出字段上可以使用相关函数，但应尽可能避免出现now(),rand(),sysdate(),current_user()等不确定结果的函数，在Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数

所有连接的SQL必须使用Join ... On ...方式进行连接，而不允许直接通过普通的Where条件关联方式。外连接的SQL语句,可以使用Left Join On的Join方式，且所有外连接一律写成Left Join，而不要使用Right Join

分页查询语句全部都需要带有排序条件,除非应用方明确要求不要使用任何排序来随机展示数据

WHERE条件中严禁在索引列上进行数学运算或函数运算

用in()/union替换or，并注意in的个数小于300

严禁使用%前缀进行模糊前缀查询:如：select id,val from table where val like ‘%name';可以使用%模糊后缀查询如：select id,val from table where val like ‘name%'

严禁使用INSERT ON DUPLICATE KEY UPDATE、REPLACE INTO、INSERT IGNORE

本文做个抛砖引玉，每个团队都有自己的开发设计规范，Mysql开发设计规范不单单只有这些，希望本文对您有所启发

赞 (0)

相关推荐

  • MySQL 使用规范总结

    1.必须使用InnoDB存储引擎 有更好的CPU和IO性能,更好的备份和锁表机制,提高统计和调试效率. 另外,作为一 个系统,InnoDB支持多种关键功能,其中最重要的是事务日志和行级锁.事务日志记录真正的数据库事务,但更重要的是数据崩溃恢复和回滚. 基于 InooDB方式的IO,能给予更安全数据保护和更好性能表现.另外,在大多数的情况下,行级锁可以提供更高的并发性能,因为用户只锁定他们正在写的数据,而读数据永远不会被阻塞 . 2.数据表.数据字段必须加入中文注释 方便日后新人小哥,更快理解熟悉

  • MYSQL 数据库命名与设计规范

    1.设计原则 1) 标准化和规范化 数据的标准化有助于消除数据库中的数据冗余.标准化有好几种形式,但Third Normal Form(3NF)通常被认为在性能.扩展性和数据完整性方面达到了最好平衡.简单来说,遵守3NF 标准的数据库的表设计原则是:"One Fact in One Place"即某个表只包括其本身基本的属性,当不是它们本身所具有的属性时需进行分解.表之间的关系通过外键相连接.它具有以下特点:有一组表专门存放通过键连接起来的关联数据. 举例:某个存放客户及其有关定单的3

  • Mysql建表与索引使用规范详解

    一. MySQL建表,字段需设置为非空,需设置字段默认值.二. MySQL建表,字段需NULL时,需设置字段默认值,默认值不为NULL.三. MySQL建表,如果字段等价于外键,应在该字段加索引.四. MySQL建表,不同表之间的相同属性值的字段,列类型,类型长度,是否非空,是否默认值,需保持一致,否则无法正确使用索引进行关联对比.五. MySQL使用时,一条SQL语句只能使用一个表的一个索引.所有的字段类型都可以索引,多列索引的属性最多15个.六. 如果可以在多个索引中进行选择,MySQL通常

  • mysql数据库开发规范【推荐】

    最近一段时间一边在线上抓取SQL来优化,一边在整理这个开发规范,尽量减少新的问题SQL进入生产库.今天也是对公司的开发做了一次培训,PPT就不放上来了,里面有十来个生产SQL的案例.因为规范大部分还是具有通用性,所以也借鉴了像去哪儿和赶集的规范,但实际在撰写本文的过程中,每一条规范的背后无不是在工作中有参照的反面例子的.如果时间可以的话,会抽出一部分或分析其原理,或用案例证明. 一. 命名规范 1.库名.表名.字段名必须使用小写字母,并采用下划线分割 (1)MySQL有配置参数lower_cas

  • MySQL数据库使用规范总结

    导读: 关于MySQL数据库规范,相信大家多少看过一些文档.本篇文章给大家详细分类总结了数据库相关规范,从库表命名设计规范讲起,到索引设计规范,后面又给出SQL编写方面的建议.相信这些规范适用于大多数公司,也希望大家都能按照规范来使用我们的数据库,这样我们的数据库才能发挥出更高的性能. 关于库: 1.[强制]库的名称必须控制在32个字符以内,英文一律小写. 2.[强制]库的名称格式:业务系统名称_子系统名. 3.[强制]库名只能使用英文字母,数字,下划线,并以英文字母开头. 4.[强制]创建数据

  • MySQL开发规范与使用技巧总结

    1.命名规范 1.库名.表名.字段名必须使用小写字母,并采用下划线分割. a)MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为 0,即库表名以实际情况存储,大小写敏感.如果是1,以小写存储,大小写不敏感.如果是2,以实际情况存储,但以小写比较. b)如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱. c)字段名显示区分大小写,但实际使⽤用不区分,即不可以建立两个名字一样但大小写不一样的字段. d)为了统一规范, 库名

  • MySQL数据库命名规范及约定

    一.[操作规范]1. 如无备注,则表中的第一个id字段一定是主键且为自动增长:2. 如无备注,则数值类型的字段请使用UNSIGNED属性:3. 如无备注,排序字段order_id在程序中默认使用降序排列:4. 如无备注,所有字段都设置NOT NULL,并设置默认值:5. 如无备注,所有的布尔值字段,如is_hot.is_deleted,都必须设置一个默认值,并设为0:6. 所有的数字类型字段,都必须设置一个默认值,并设为0:7. 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预

  • 老鸟带你开发专业规范的MySQL启动脚本

    每一个合格的Linux运维人员都应该做到熟练或精通Shell脚本编程,因为Shell脚本语言差不多是所有编程语言里最简单的语言,如果Shell脚本不行,意味着运维之路可能还没开始就将要终结.--老男孩老师 #!/bin/bash # chkconfig: 2345 64 36 #配置系统自启动 # description: A very fast and reliable SQL database engine. #########################################

  • 超详细MySQL使用规范分享

    最近涉及数据库相关操作较多,公司现有规范也不是太全面,就根据网上各路大神的相关规范,整理了一些自用的规范用法,万望指正. 数据库环境 dev: 开发环境 开发可读写,可修改表结构.开发人员可以修改表结构,可以随意修改其中的数据但是需要保证不影响其他开发同事. test: 测试环境 开发可读写,开发人员可以通过工具修改表结构. online: 线上环境 开发人员不允许直接在线上环境进行数据库操作,如果需要操作必须找DBA进行操作并进行相应记录,禁止进行压力测试. 重点的问题,各个环境的mysql服

  • 专业级的MySQL开发设计规范及SQL编写规范

    在团队开发过程中为了项目的稳定,代码的高效,管理的便捷制定内部种开发设计规范是必不可少的, 这里分享一份我们定义MySQL开发设计规范包括表设计规范,字段设计规范,SQL编写规范 数据库对象命名规范 数据库对象 命名规范的对象是指数据库SCHEMA.表TABLE.索引INDEX.约束CONSTRAINTS等的命名约定 数据库对象命名原则 命名使用具有意义的英文词汇,词汇中间以下划线分隔 命名只能使用英文字母.数字.下划线 避免用MySQL的保留字如:call.group等 所有数据库对象使用小写

  • php+mysql开发中的经验与常识小结

    本文总结了php+mysql开发中的经验与常识.分享给大家供大家参考,具体如下: 一.基础规范 (1)尽量使用 InnoDB 存储引擎 支持事务.行级锁.并发性能更好,CPU 及内存缓存页优化得当,资源利用率更高 (2)必须使用 UTF8 字符集 无需转码,无乱码风险 (3)数据表.数据字段必须加入中文注释 N 年后谁知道这个 r1,r2,r3 字段是干嘛的 (4)尽量不要使用存储过程.视图.触发器.Event 高并发大数据的互联网业务,架构设计思路是"解放数据库 CPU,将计算转移到服务层&q

  • MySQL中一条SQL查询语句是如何执行的

    MySQL中一条SQL查询语句是如何执行的

    目录 前言 1. 处理连接 1.1 客户端和服务端的通信方式 1.1.1 TCP/IP协议 1.1.2 UNIX域套接字 1.1.3 命名管道和共享内存 1.2 权限验证 1.3 查看MySQL连接 2. 解析与优化 2.1 查询缓存 2.2 解析器 & 预处理器(Parser & Preprocessor) 2.2.1 词法解析 2.2.2 语法分析 2.2.3 预处理器 2.3 查询优化器(Optimizer)与查询执行计划 2.3.1 什么是查询优化器? 2.3.2 优化器究竟做了什

  • apache php mysql开发环境安装教程

    apache php mysql开发环境安装教程

    apache php mysql开发环境搭建教程,分享给大家,供大家参考 0.资源链接: 360云盘:https://yunpan.cn/c6wpzYwbfmLJY (提取码:0190) 1.首先安装编译器环境 即如下文件: 2.安装apache+php+mysql 即如下文件: 2.1.安装apache 2.1.1安装 只说关键几点 2.1.1.1 2.1.1.2 2.1.1.3 2.1.1.4.在浏览器地址栏输入:localhost ,若出现以下页面,说明您已成功安装apache 2.1.2

  • MySql批量插入优化Sql执行效率实例详解

    MySql批量插入优化Sql执行效率实例详解 itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志. updateInsertSql.AppendFormat("UPDATE itemcontractprice AS p INNER JOIN foreigncurrency AS f ON p.ForeignCurrencyId = f.ContractPriceId SET p.RemainPrice = f.RemainPrice * {0},

  • php+mysql开发的最简单在线题库(在线做题系统)完整案例

    php+mysql开发的最简单在线题库(在线做题系统)完整案例

    本文实例讲述了php+mysql开发的最简单在线题库.分享给大家供大家参考,具体如下: 题库,对于教育机构,学校,在线教育,是很有必要的,网上也有不少的第三方在线题库系统,但是本次案例,会让有需要的人了解题库的开发思路,其实很简单,无非就是一个表单验证,数据库验证. 1.先构建表单数据 2.把表单数据通过get或者post方式提交到表单验证页面,和数据库进行匹配 3.返回结果,答案正确或者错误 构建表单: index.php <!DOCTYPE html> <html> <h

  • MySql开发之自动同步表结构

    MySql开发之自动同步表结构

    开发痛点 在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致. 而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全.人手工去和qa环境对字段又特别繁琐,容易遗漏. 解决之道 于是笔者就写了一个能够自动比较两个数据库的表结构,并生成alter语句的程序.同时还可以进行配置从而自动这行这些alter语句.详情见github 原理 同步新增的表 如果rd环境新增的表,而qa环境没有,此程序可以直接输出create table语句.原理如下: 用到的sql主要有

  • python+django+mysql开发实战(附demo)

    python+django+mysql开发实战(附demo)

    开发工具:pycharm 环境:python3.7.4(例子中用的3.6)下载安装pycharm:http://www.jetbrains.com/pycharm/download/#section=windows分为社区版和专业版,一个免费一个收费Pycharm2021破解版下载: https://www.jb51.net/softs/598504.html下载安装python:https://www.python.org/downloads/windows/安装好开发工具和python环境后

  • IDEA连接MySQL数据库并执行SQL语句使用数据图文详解

    目录 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 二.使用数据库的数据 (一)新建Java类 Test (二)下载MySQL驱动Jar包 (三)返回IDEA,新建文件夹lib (四)非常重要(添加到库) (五)编写Test类 (六)运行主函数,得到数据 三.总结 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 1.点击右侧DataBase 2.点击加号,找到MySQL,添加数据库 3.输入用户名和密码,点击Test Co

  • Linux下通过python访问MySQL、Oracle、SQL Server数据库的方法

    本文档主要描述了Linux下python数据库驱动的安装和配置,用来实现在Linux平台下通过python访问MySQL.Oracle.SQL Server数据库. 其中包括以下几个软件的安装及配置: unixODBC FreeTDS pyodbc cx_Oracle 欢迎转载,请注明作者.出处. 作者:张正 QQ:176036317 如有疑问,欢迎联系. 本文档主要描述了Linux下python数据库驱动的安装和配置,用来实现在Linux平台下通过python访问MySQL.Oracle.SQ

随机推荐