数字类型

整形

在整数类型中，按照取值范围和存储方式不同，分为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；