数字类型

整形

在整数类型中,按照取值范围和存储方式不同,分为tinyint、smallint、mediumint、int和bigint这 5个类型。 

  • MySQL可以为整形指定宽度,例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度(数字1 显示为00001),而且要配合ZEROFILL属性使用,否则是没有意义的。
  • 其实对大多数应用是没有意义的,它不会限制合法的范围,只规定了MySql的交互工具(如命令行客户端)用来显示字符的个数,存储和计算来说INT(20)与INT(1)是一样的。
  • SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

浮点/定点型

 

单精度的FLOAT类型及双精度的DOUBLE类型型都是非精确的类型,经过一些操作后并不能保证运算的正确性。 

定点数DECIMAL在MySQL内部以字符串形式存放(每4个字节存9个数字,最多65个数字),比浮点数更精确,适合用来表示货币等精度高的数据,也可以使用DECIMAL存储比BIGINT还大的整数。 

定点数可以用类型名称后加“(M,D)”的方式来进行表示,“(M,D)”表示该值一共显示M位数字(整数位+小数位),其中D位位于小数点后面;DECIMAL相比浮点数会占用更多的空间,DECIMAL(18,9)小数点前有9位数字(4字节),小数点后有9位数字(4字节),加上小数点本身一共需要占用9个字节)。

日期和时间类型

MySQL数据库中有五种与日期和时间有关的数据类型:

类型

占用空间

最小值

最大值

DATETIME

8字节

1000-01-01 00:00:00

9999-12-31 23:59:59

DATE

4字节

1000-01-01

9999-12-31

TIME

3字节

-838:59:59

838:59:59

YEAR

1字节

1901

2055

TIMESTAMP

4字节

 

 

  • TIMESTAMP和DATETIME显示的结果是一样的,都是固定的“YYYY-MM-DD HH:MM:SS”的形式。
  • TIMESTAMP占用4字节,显示的范围为“1970-01-01 00:00:00”UTC到“2038-01-19 03:14:07”UTC。其实际存储的内容为“1970-01-01 00:00:00”到当前时间的毫秒数。TIMESTMAP保存的是时间戳,所以是和时区有关的。
  • FROM_UNIXTIME()函数把Unix时间戳转换为日期,UNIX_TIMESTAMP()函数把 日期转换为Unix时间戳。
  • 列为TIMESTAMP的日期类型可以设置一个默认值并且在记录更新时设置TIMESTAMP自动更新为当前时间(DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP),表中的第一个 TIMESTAMP类型默认会拥有此属性

字符类型

字符集与编码

常见字符集包括:ASCII字符集、LATIN1字符集、GB2312字符集、GBK字符集、GB18030字符集、Unicode字符集等。 

ASCII是基于罗马字母表的一套字符集,它采用1个字节的低7位表示字符,高位始终为0。LATIN1字符集相对于ASCII字符集做了扩展,仍然使用一个字节表示字符,但启用了高位,扩展了字符集的表示范围。GB2312、GBK、GB18030字符集是支持中文的字符集,字符集范围GB2312<gbk< gb18030。<="" p="" style="box-sizing: border-box; font-size: 18px; line-height: 30px; overflow-wrap: break-word;"></gbk<>

Unicode字符集是计算机科学领域里的一项业界标准,支持了所有国家的文字字符。Unicode字符集有好几种编码方式,比如常见的utf-8,utf-16,utf-32等。Utf8采用1-4个字节表示字符,utf-16采用固定的2个字节,utf-32则采用4个字节存储。

  • 建库时,若未明确指定字符集,则采用character_set_server(mysql.ini文件中的mysqld.character_set_server字段)指定的字符集。
  • 建表时,若未明确指定字符集,则采用当前库所采用的字符集。
  • 新增、修改表字段时,若未明确指定字符集,则采用当前表所采用的字符集。

排序规则(Collation)是指对指定字符集下不同字符的比较规则。 

utf8字符集默认的排序规则是utf8_general_ci(_ci结尾表示大小写不敏感),此时’a’ 与 ‘A’是一样的。

mysql> SET NAMES CHARACTER_SET utf8 COLLATE utf8_bin; 

将排序规则指定为utf8_bin,则表示希望区分大小写字符。

CHAR和VARCHAR

CHAR和VARCHAR是最常使用的两种字符串类型。一般来说,CHAR(N)用来保存固定长度的字符串,VARCHAR(N)用来保存变长字符类型。对于CHAR类型,N的范围为0~255,对于VARCHAR类型,N的范围为0~65 535。CHAR(N)和VARCHAR(N)中的N都代表字符长度,而非字节长度

  • LENGTH函数返回的是字节长度,而不是字符长度。
  • 对于CHAR类型采用Unicode编码字符集的字段,数据库会保存最大可能的字节数。例如,对于CHAR(30),数据库可能存储90字节的数据。
  • CHAR和VARCHAR都会保留字符串最前面的空格,是VARCHAR可以保留字符串最后面的空格,CHAR类型不会保留字符串最后面的空格。但是当做字符串匹配时都会去掉首尾的空格。
  • 使用VARCHAR(5)和VARCHAR(200)存储’hello’的空间开销虽然是一样的,但是MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间。 
    CHAR VS VARHAR
  • 国内程序员设计数据库时默认都是utf-8字符集,对于utf-8字符集而言 char(10)会占用30个字节,比varchar浪费空间。
  • CHAR类型只有在表中所有字段都是固定长度的情况下会比使用VARCHAR速度上有优势,如果你的表中有其它字段为VARCHAR类型,那么都用VARCHAR吧。
  • 如果使用的是INNODB存储引擎,推荐使用VARCHAR。

BINARY和VARBINARY

BINARY(N)和VARBINARY(N)存储的是二进制的字节流,N指的是字节长度,而非字符长度

BLOB和TEXT

BLOB可以视为足够大的VARBINARY类型。 

TEXT可以视为足够大的VARCHAR类型。

  • 在BLOB和TEXT类型的列上创建索引时,必须制定索引前缀的长度。而VARCHAR和VARBINARY的前缀长度是可选的。
  • BLOB和TEXT类型的列不能有默认值。
  • 在排序时只使用列的前max_sort_length个字节。 
    因为Memory引擎不支持BLOB和TEXT类型,如果查询使用了BLOB或TEXT列并且需要使用隐式临时表,将不得不使用MyISAM磁盘临时表,这会导致严重的性能开销。尽量避免使用BLOB和TEXT类型,不要对BLOB、TEXT类型字段做 
    排序。

ENUM和SET类型

ENUM中文名称叫枚举类型,它的值范围需要是创建表时显示指定的字符串集合,枚举类型值在实际存储时会转换为集合中得下标(数字型,从1开始),所以可以节省大量的空间。

create table t (gender enum(‘MALE’,’FEMAIL’));

Value

Index

备注

NULL

NULL

ENUM类型可以为NULL

0

不在指定的枚举列表中得错误值

‘MALE’

1

从1开始

‘FEMALE’

2

 

当插入不在 ENUM 指定范围内的错误值时,mysql会保存下标0,查询出的结果会转换为空字符串。 

但是当SQL_MODE设置为strict_trans_tables时,如果插入一个不存在集合中的值会报错。

* If you store a number into an ENUM column, the number is treated as the index into the possible values, and the value stored is the enumeration member with that index.*

INSERT INTO t (gender) VALUES(1),(‘1’),(‘MALE’); 

//插入三条gender值为’MALE’的数据

ENUM优势与劣势 

优势:数据紧凑,存储的是整数,占用空间小,作为关联字段时,效率比varchar类型高很多; 

劣势: ENUM类型不容易扩展,字符串列表是固定的,添加或者删除字符串必须使用ALTER TABLE,如果添加的字符串不在列表末尾,则需要重建整个表完成修改。存储数字类型的字符串更是难以理解,ENUM是Mysql独有的类型不容易做数据迁移。建议谨慎使用ENUM。

SET和ENUM类似都是需要在创建表时指定一个范围集合,最主要的区别在于 SET 类型一次可以选取多个成员,而ENUM则只能选一个。

选择合适的数据类型

选择正确的数据类型对于获得高性能至关重要,数据类型选择可以有以下原则:

  • 更小的通常更好:因为它们占用更少的磁盘,内存,CPU缓存,处理时需要的 CPU周期也少。
  • 简单就好:简单的数据类型通常需要更少的CPU处理周期,比如整型比字符串类型操作代价更低,因为字符集和校对规则使字符比较比整理更复杂,比如:通常可以使用内建日期来代替字符串存储日期,整型来存储IP地址;
  • 尽量避免NULL: Null对于mysql来说更难优化,如果要列要加索引,尽量设置Not NULL;