mysql表结构设计

案例一：用户表设计

先贴出我的建表sql
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nickname` varchar(100) NOT NULL,
`type` smallint(5) unsigned NOT NULL DEFAULT '0',
`password` char(32) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`created` int(10) unsigned NOT NULL DEFAULT '0',
`last_login` int(10) unsigned DEFAULT NULL,
`mobile` varchar(40) DEFAULT NULL,
`bak1` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `INDEX_NICK` (`nick`),
UNIQUE KEY `INDEX_EMAIL` (`email`),
UNIQUE KEY `mobile` (`mobile`),
KEY `INDEX_TYPE` (`type`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';
  • id是主键， 给它int类型，unsigned非负数, 自增
  • 然后，nick是昵称，给它最大为100个字符的varchar类型
  • type 用户的类型，因为可选的类型并不多，所以smallint即可，注意默认值为0
  • password 密码，由于使用md5加密，所以直接用定长的char(32)
  • email 邮箱，给它最大为100个字符的varchar类型
  • created，创建时间，我们这里使用int类型存储时间戳，可以节省存储空间。
  • last_login 上次登陆的时间，同上。
  • mobile 手机号，给它最大为40个字符的varchar类型
  • bak1，保留字段，备用。

mysql workbrench建表操作演示：

📎
视频附件未知大小

辅助的设计工具

推荐使用官方的mysql workbrench。
下载地址: 传送门
5分钟入门MySQL Workbench

整数类型

（可以使用无符号整数类型，提高存储范围）(类似int(2)不会减少其真正存储空间，1只是表示显示的长度为1位),如下：
列类型
存储空间
取值范围
tinyint
1字节
signed：-128~127 unsigned： 0~255
smallint
2字节
signed: -32768~32767 unsigned: 0~65535
mediumint
3字节
signed: -800万~800万 unsigned:0~1600万
int
4字节
signed: -21亿~21亿 unsigned：0~42亿
bigint
8字节
922亿亿，注意是两个亿，总之很大就是了。。。

字符类型

列类型
字符数
用途
char
0-255
定长字符串
例如经常用char(32)来存储md5加密后的密码。
varchar
0-65535
变长字符串
TEXT
0-65535
长文本数据
MEDIUMTEXT
0-16 777 215
中等长度文本数据
LONGTEXT
0-4 294 967 295
极大文本数据
char(32)括号里表示的是字符数，32指示要存储的最大字符数。
一个汉字，一个数字，一个中文，都是一个字符。但是对于不同的编码方式而言，存储每个字符所需要的字节数是不一样的。

实数类型

列类型
说明
DECIMAL
5表示总位数，2表示小数点后的位数
DECIMAL(5,2)能够存储五位数和两位小数的任何值
因此可以存储在值范围为-999.99至 999.99。
decimal是精确值,
经常用于存金额
float
近似数字数据值,四个字节的单精度值.
从0到23的精度将产生一个4字节的单精度浮点数列。
FLOAT(7,4) 显示时看起来像-999.9999 当存储值时 mysql执行四舍五入,
所以如果将999.00009插入到浮点（7,4）列中，则近似结果为999.0001。
DOUBLE
近似数字数据值,八个字节的双精度值
从24到53的精度导致一个8字节的双精度双列。

日期类型 

列类型
存储空间
日期格式
取值范围
datetime
8 字节（bytes）
YYYY-MM-DD HH:MM:SS
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp
4 字节（bytes）
YYYY-MM-DD HH:MM:SS
1970-01-01 00:00:01 ~ 2038
date
3 字节（bytes）
YYYY-MM-DD  
1000-01-01 ~ 9999-12-31
year
1 字节（bytes）
YYYY 
1901 ~ 2155
一般情况下，倾向于使用 datetime 日期类型。timestamp容易所支持的范围比timedate要小。 并且容易出现超出的情况

数据库命名规范

  • 数据库名使用小写英文以及下划线组成.比如: my_db
  • 备份数据库名使用正式库名加上备份时间组成,如: dbname_20070403
  • 不得超过 30 个字符

数据库表命名规范

  • 数据表名使用小写英文以及下划线组成
  • 备份数据表名使用正式表名加上备份时间组成,如:
  • 不得超过 30 个字符
info_user_20070403
system_destination_20070403

字段设计规范

  • 在一个系统中，保持字段名和类型的一致性。表与表之间的相关联字段要用统一名称。
  • 字段名不宜过长
  • 用尽量少的存储空间来存数一个字段的数据。能用 varchar(20)的就不用 varchar(255)，能用tinyint的就不用int。
  • 尽量避免空（NULL），要尽可地把字段定义为NOT NULL。因为MySQL难以优化了使用了可空列的查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要在MySQL内部进行特殊处理。

其他设计技巧

  • 避免使用触发器。触发器的功能通常可以用其他方式实现.在调试程序时触发器可能成为干扰. 假如你确实需要采用触发器,你最好集中对它文档化.
  • 避免使用存储过程
  • 使用常用英语(或者其他任何语言)而不要使用拼音首字母缩写

数据库设计之范式与冗余设计

先提一点，三范式可以很好的保证表结构的原子性，但是实际应用的时候，也常常使用逆范式的设计。因为面对大量数据时，连表是致命的。

第一范式(1NF)

  • 概念 数据表的每个字段(属性)必须是唯一的、不可分割的。
  • 唯一性 比如：在一张学生信息表里不能有两个名称都是name的字段。
  • 不可分割性 比如：在一张学生信息表不能出现类似name_mobile这样的字段，很明显name_mobile是可以分割成name和mobile两个字段的。

第二范式(2NF)

  • 概念 数据表的每条记录必须是唯一的(主键约束)，且非主键字段只依赖于主键。
  • 唯一性 比如说：不能同时存在id ＝ 1的记录（id为主键）。
  • 依赖性 比如说：在一张学生信息表（student_id为主键），不应该出现course_name(课程名称，依赖于course_id)这样的字段，因为，如果有一天，《心理健康教育》课程名要改成《心理健康教育杂谈》，就得改课程表和学生信息表的课程名称了。

第三范式(3NF)

  • 概念 数据表中不应该存在多余的字段，也就是说每个字段都不能由其他字段推理得到。
  • 例子 比如说：学生信息表里不能同时存在province_id(省份ID)、city_id(城市ID)这两个字段，因为province_id可以由city_id推理得到

逆范式

  • 概念 就是不按照标准的范式去设计数据库
  • 逆 在数据库的实践过程中，我们可能遇到数据量非常大的数据表，这时候去做join查询是非常损耗性能的，甚至导致数据库连接超时、挂掉等问题。所以呢，有时候就需要数据库多冗余设计，对一些字段做冗余，以避免大表之间的join。