案例一:用户表设计

先贴出我的建表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建表操作演示:


Current Time 0:00
/
Duration Time 0:00
Progress: NaN%

辅助的设计工具

推荐使用官方的mysql workbrench。
下载地址: 传送门


整数类型

(可以使用无符号整数类型,提高存储范围)(类似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.99999.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是可以分割成namemobile两个字段的。

第二范式(2NF)

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

第三范式(3NF)

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

逆范式

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



成为你想看到的世界变革力量

创建者:万乐荣
最后更新时间 : 2018年9月4日 17:38

评论

墨云翟丘  2019年10月4日 16:32
建表的sql代码中,UNIQUE KEY `INDEX_NICK` (`nick`)应该更改为UNIQUE KEY `INDEX_NICK` (`nickname`)