实际上还有一类数字类型是通过二进制格式以字符串来存放的,如,DECIMAL(DEC)[(M[,D])],NUMERIC[(M[,D])],由于其存放长度主要通过其定义时候的M所决定,M定义多大,则实际就有多长。M代表整个位数的长度,D代表小数殿后的位数,默认M为10,D为0。一般考虑到这种数据完全可以变化形式以整数存放,所以这种数据类型()一般不用。但是,在mysql中float、double(或real)是浮点数,decimal(或numberic)是定点数。浮点数(float、double)存在误差问题;对货币等对精度敏感的数据,应该用定点数表示或存储;编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较;
另外IP地址字段的话,尽量设置为UNSIGNED INT(32无符号整形),查询时可以使用把一个字符串IP转成一个整形,并使用把一个IP转换成字符串IP。
时间存储格式,常用的是DATETIME,DATE,TIMESTAMP这三种,从存储空间看TIMESTAMP最少,4个字节,而其他两种都要8个字节,多了一倍。如果有需要存放早于1970年之前的时间,则要使用DATETIME类型,假如不需要,则最好使用TIMESTAMP来减少存储空间的占用。
字符存储类型:
mysql5.0.3之后的表示,VARCHAR[(M)]表示的字节数,最大限制和字符集有关,如果是gbk编码,最大长度为(65535-1-2)/2=32766,减1的原因是实际行存储从第二个字节开始,减2的原因是varchar头部的2个字节表示长度,除2因为是gbk编码;如果是utf8编码,最大长度为(65535-1-2)/3=21844。字段尽量选择固定长度。另外MySQL 5.0之后版本存储VARCHAR类型的时候会保留末尾空格,CHAR[(M)]都是以空格填补剩余的空间,所以,如果要保留结尾空格的话,一定要使用VARCHAR。
另外不要使用数字作为ENUM枚举常量!
尽量用char(1)来代替bit(1),尽量避免程序错误!
MyISAM表的话,尽量使用char()代替varchar();对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),主要的性能因素是数据行使用的存储总量,建议使用varcahr(),可以减少存储空间。
对于BLOB和TEXT字段,如果可以,就把这些字段分拆到另外一个表里面去。
4字符集选择
mysql的字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念。字符集是用来定义mysql存储字符串的方式,校对规则是定义了比较字符串的方式。
UTF-8:是用以解决国际上字符的一种多字节编码,它对英文使用8位(即一个字节),中文使用24位(三个字节)来编码。UTF-8包含全世界所有国家需要用到的字符,是国际编码,通用性强。
GBK: 是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的,即不论中、英文字符均使用双字节来表示,为了区分中文,将其最高位都设定成1。GBK包含全部中文字符,是国家编码,通用性比UTF8差,不过UTF8占用的数据库比GBK大。
为了避免所有乱码问题,应该采用UTF-8,将来要支持国际化也非常方便UTF-8可以看作是大字符集,它包含了大部分文字的编码。
5命名规范
1、数据库和表名应尽可能和所服务的业务模块名一致;
2、服务于同一子模块的一类表尽量以子模块名(或部分单词)为前缀或后缀;
3、表名应尽量包含与所存放数据相对应的单词;
4、字段名称也尽量保持和实际数据相对应
5、索引名称尽量包含所有的索引键字段名或者缩写,且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致,且尽量包含一个类似于idx或者ind之类的前缀或者后缀,以表名其对象类型是索引,同时还可以包含该索引所属表的名称;
6、约束等其他对象也应该尽可能包含所属表或其他对象的名称,以表名各自关系。
7、同一个SQL语句中必须统一大小写,不允许SELECT * FROM my_table
WHERE MY_TABLE.col=1;
6索引的设计
说明:MyISAM的主键索引(聚集索引)和普通索引(非聚集索引);InnoDB中索引分为聚集索引形式的主键索引和非聚集索引形式的普通索引。
·频繁的作为条件进行查询的字段应创建索引
·唯一性太差的字段不要创建索引
·频繁更新的字段不适合创建索引
·能建立组合索引的尽量建立组合索引
·索引键长不能过长(最大限制为1000个字节)
·join条件字段类型不一致的时候mysql无法使用索引
·如果一个字段设计成用来存储URL,则可以新增一个被索引的url_crc列,使用CRC32做哈希
·如果对较长的字段使用索引,可以考虑前缀索引(注意索引唯一性)
·避免使用重复索引(可以使用pt-duplicate-key-checker来检查重复索引)
·统计未使用的索引(可以使用pt-index-usage来查看)
·只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列
7外键问题
InnoDB中定义外键时需要注意的地方:
InnoDB有问题的方式
InnoDB正常的方式
Create
Table: CREATE TABLE `person` (
`id` smallint(5) unsigned NOT NULL
AUTO_INCREMENT,
`name` char(60) NOT NULL,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
CREATE
TABLE shirt (
id
SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style
ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color
ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner
SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY
KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8;
CREATE
TABLE person2 (
id
SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name
CHAR(60) NOT NULL,
PRIMARY
KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8;
CREATE
TABLE shirt2 (
id
SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style
ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color
ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner
SMALLINT UNSIGNED NOT NULL,
PRIMARY
KEY (id),
FOREIGN
KEY(owner) REFERENCES person2(id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8;
mysql>
show create table shirt\G
***************************
1. row ***************************
Table: shirt
Create
Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL
AUTO_INCREMENT,
`style` enum('t-shirt','polo','dress') NOT
NULL,
`color`
enu