很多时候, int char bit set enum这几种类型都可以替换着用,下面对这几种类型的优劣稍作浅析
以在数据库中建立用户表的性别(gender)字段为例,分别用以下几种类型各有什么优缺点
1. int: gender tinyint unsigned not null
可以这样定义gender列,好处是储存空间小( 1 Byte ),可扩展性好(range 0~255)
坏处是没有强约束(可以在代码里控制),可读性不好,无从知道 0 1 2 到底什么意思
2. char(1): gender char(1) not null
优劣与int类似
3. bit(1): gender bit(1) not null
实际上bit在存储空间上没有什么优势,MyISAM会把bit做一个round,例如bit(17)会变成3 Bytes存储。而InnoDB和Memory则是会选择最小的能容下这个列的int型来存储他。
4. set: 此处没用
5. enum: gender enum("MALE", "FEMALE") not null
enum好处很明显,就是存储空间小而且有对应的字符串映射关系,他的数据存储的是2 Bytes大小的整数index,真正的字符串值存放在.frm文件里,每次查询和插入的时候做转换。
他的坏处就是扩展性不好,每次如果要扩充enum的值都要用到 ALTER TABLE
就gender这个问题而言,我认为最好的方法其实是建立代码表,将gender列独立成一个表,如下
create table gender (
id int unsigned not null primary key,
text varchar(8) not null
)
然后可以在user表里引用gender的id作为外键
将对gender表的查询做一个cache,例如
select * from gender;
select * from gender where id = ?;
...
等等,这样既能够很方便的查询出gender的信息(例如在生成页面的gender select option选项时)而不影响效率,扩展添加表项只需要insert或者update gender表即可,这相对于ALTER TABLE来说是好很多的,而且完全不会影响到程序代码(例如生成页面里的option的代码),达到代码和数据库解耦,只要在更新表项后flush一次cache就好。一般来讲ORM框架还会有延迟加载之类的,运用这些技术可以进一步提高效率。
如果说不考虑这些项目需要,我觉得最好还是用enum吧,但是如果在项目里需要去获取enum的值,程序代码确实是比较麻烦的一件事情,此处可以考虑在项目启动的时候将enum的值加载到项目里的静态变量去,每次更新值重启一下项目,但是这些动态生成表项的代码需要自己去编写,而且扩展enum较麻烦(ALTER TABLE)。
第二个例子是user表里的privilege列,这时候有如下两种选择:
1. set: privilege set("read", "write", "exe") not null
这种用法和enum类似,不同点在于set可以同时存在多个值,例如 read, write 或者 read, exe等等组合
缺点也和enum一样,扩展的时候需要用到ALTER TABLE,
2. int: privilege tinyint unsigned not null
这样的方法和linux里的权限控制是一样的,你可以将权限定义成二进制数,例如
read = 1 << 0;
write = 1 << 1;
exe = 1 << 2;
在判断权限的时候就很简单了,例如要有一个权限 p1 = 3,要知道他有哪些权限,只需要做 & 运算即可
if (p1 & read != 0) { ... }
即可知道p1有read权限。
结合在项目里来说,同样可以像gender那样,为privilege建表:
create table privilege (
id tinyint unsigned not null primary key,
text varchar(6) not null
)
使用起来和gender是一样的。