mysql 数据库表结构设计与规范

DDL(data difinition language)就是数据定义语言。

1.sql语句的界定符

[code]– 默认情况下” ; ” 代表sql语句的结束 delimiter 新的界定符 – 修改 // 为界定符 delimiter //

2.创建数据库

[code]#1.创建数据库 create database 数据库名字 [charset = uft8 ] [collate = 排序规则]

一般都写成这样

create database student charset=utf8; 可以用如下命令查看数据库支持的字符编码

[code]show charset;

用如下命令查看数据库排序规则：

show collation;

可以看到有197种排序规则，注意每种数据库的编码都有相应的排序规则可选，排序规则不是乱写。

3.修改数据库

[code]alter database 数据库名 charset 字符编码 collate 排序规则;

修改数据库的编码和排序规则

alter database mydb charset gbk collate gbk_chinese_ci; alter database mydb charset utf8 collate utf8_general_ci;

4.显示所有的数据库

[code]show databases; – s 莫忘记

5.显示一个数据库的创建语句

[code]show create database 数据库名;

6.使用某个数据库

[code]use 数据库名

7.删除数据库

[code]#if exists 居于“安全运行的考虑” ，如果数据库不存在，也不会报错,最好写上 drop database [if exists] 数据库名

8.命名和大小写问题

mysql中的各种系统关键字和命令名本身是不区分大小写的，命名规则如下：

可以自己命名的名字，称为标识符，包括：数据库名， 表名，字段名，视图名，函数名，过程名，变量名，用户名，等等。

可以命名标识符的字符比常规的语言多，但特别建议只用：字母数字和下划线，并不用数字开头。

非常规字符或系统关键字虽然可以作为标识符使用，但最好要包在反引号（数字1左边那个反撇 ` ）中，并且不推荐。

对数据库名，表名，和视图名，在window系统中不区分大小写，而其他系统中区分，建议全使用小写，并采用下划线分割法。

对其他自己命名的标识符（字段名，函数名，过程名），不区分大小写，但也建议全使用小写，并采用下划线分割法。

关于单引号和反引号

反引号是为了区分MySQL的保留字和普通字符,其他例如SQL语句用双引号

MySQL中反引号，是以对象为单位的，表，或者库等，不能把a.name都括起来，而是应该`a`.`name`，因为如果`a.name`的话，MySQL会认为你是以a.name为名字的，名字里面包含了.，而这个名字就是不存在的

mysql数据库使用的一些规范

通常来讲，各个互联网公司的数据库分为5个数据库环境：

dev : 开发环境, 开发可读写,可修改表结构; 常用的163的数据库表; 开发人员可以修改表结构, 可以随意修改其中的数据; 但是需要保证不影响其他开发同事

qa : 测试环境, 开发可读写, 开发人员可以通过工具修改表结构

sim: 模拟环境, 开发可读写, 通过web平台;发起上线请求时，会先在这个环境上进行预执行， 这个环境也可供部署上线演练或压力测试使用 可以读写

real: 生产数据库从库（准实时同步）,只读环境,不允许修改数据,不允许修改表结构; 供线上问题查找,数据查询等使用

online: 线上环境;开发人员不允许直接在线上环境进行数据库操作,如果需要操作必须找DBA进行操作并进行相应记录

这些环境的机器，一定要做到权限划分明确，读写帐号分离，并且有辨识度，能区分具体业务。例如用户名w_wap, r_wap 能看出来，读写帐号是wap应用的

数据库开发规范

开发规范本身也包含几部分：基本命名和约束规范，字段设计规范，索引规范，使用规范等

规范存在意义

保证线上数据库schema规范

减少出问题概率

方便自动化管理

规范需要长期坚持，对开发和DBA是一个双赢的事情

约束规范

表字符集选择UTF8 ，如果需要存储emoj表情，需要使用UTF8mb4(MySQL 5.5.3以后支持)

存储引擎使用InnoDB

变长字符串尽量使用VARCHAR VARBINARY

不在数据库中存储图片、文件

设计表的时候需要添加注释

单表数据量控制在1亿以下，单表物理大小不超过10GB，行平均长度不超过8KB

禁止在线上做数据库压⼒测试

禁止从测试、开发环境直连数据库

demo:

CREATE TABLE `employees` ( `emp_no` INT NOT NULL AUTO_INCREMENT COMMENT ‘员工的id’, `birth_date` DATE NOT NULL COMMENT ‘员工的生日’, `first_name` VARCHAR(14) NOT NULL COMMENT ‘员工的first name’, `last_name` VARCHAR(16) NOT NULL COMMENT ‘员工的last name’, `gender` CHAR(1) NOT NULL COMMENT ‘员工的性别’, `hire_date` DATE NOT NULL COMMENT ‘员工的入职日期’, PRIMARY KEY (emp_no) )ENGINE=InnoDB default charset = utf8;

基本命名规范

库名、表名、字段名禁止使用保留字

库名、表名、字段名、索引名使用小写字母，以下划线分割 ，需要见名知意

库名、表名、字段名、索引名不要设计过长，禁止超过32个字符，尽可能用最少的字符表达出表的用途

临时库、临时表名必须以tmp为前缀，并以日期为后缀

备份库、表必须以bak为前缀，并以日期为后缀

库名、表名、字段名、索引名使用名词作为数据库名称，并且只用英文，不用中文拼音

库名使用英文字母，全部小写，控制在3-7个字母以内

库名如果有多个单词，则使用下划线隔开，不建义驼峰命名

分表规范

禁止使用分区表

拆分大字段和访问频率低的字段，分离冷热数据

使用HASH进行散表，表名后缀使用十进制数，下标从0开始

按⽇期时间分表需符合YYYY[MM][DD][HH]格式

采用合适的分库分表策略

字段规范

所有字段均定义为NOT NULL ，除非你真的想存NULL，但是我想不到需要用Null的情况

字段类型在满足需求条件下越小越好，使用UNSIGNED存储非负整数 ，实际使用时候存储负数场景不多

使用TIMESTAMP存储时间，使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ，这种方式只能存储IPv4，存储不了IPv6

使用VARCHAR存储变长字符串 ，当然要注意varchar(M)里的M指的是字符数不是字节数；

使用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

尽可能不用BLOB TEXT

使用TINYINT来代替ENUM类型，将字符转化为数字

禁止在数据库中存储明文密码

使用VARBINARY存储大小写敏感的变⻓字符串

索引规范

单个索引字段数不超过5，单表索引数量不超过5，索引设计遵循B+ Tree索引最左前缀匹配原则

选择区分度高的列作为索引，区分度高的放在前面

对字符串使用前缀索引，前缀索引长度不超过8个字符

建议优先考虑前缀索引，必要时可添加伪列并建立索引

建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾

DML和order by和group by字段要建立合适的索引

避免索引的隐式转换

避免冗余索引

关于主键：表必须有主键 ；不使用更新频繁的列 ；不选择字符串列 ；不使用UUID MD5 HASH ；默认使用非空的唯一键 ，建议选择自增或发号器

重要的SQL必须被索引：UPDATE、DELETE语句的WHERE条件列；

核心SQL优先考虑覆盖索引

不在低基数列上建立索引，例如“性别”

不在索引列进行数学运算和函数运算

尽量不使外键 ，外键用来保护参照完整性，可在业务端实现；对父亲和子表的操作会相互影响，降低可用性 ；INNODB本身对online DDL的限制

不使%前导的查询，如like “