MySQL-语法-字段类型

字段类型

¶ 整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

¶ 浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

int(10)代表的含义：

int(10)的意思是假设有一个变量名为id，它的能显示的宽度能显示10位。

在使用id时，假如我给id输入10，那么mysql会默认给你存储0000000010。

当你输入的数据不足10位时，会自动帮你补全位数。

假如我设计的id字段是int(20)，那么我在给id输入10时，mysql会自动补全18个0，补到20位为止。

总结：

int(M)的作用于int的范围明显是无关的，int(M)只是用来显示数据的宽度，我们能看到的宽度。

当字段被设计为int类型，那么它的范围就已经被写死了，与M无关。

¶ 字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

字符串类型选择策略

当我们为字符串类型的字段选取类型的时候，判断该选取VARCHAR还是CHAR，我们可以从以下几个方面来考虑：

• 该字段数据集的平均长度与最大长度是否相差很小，若相差很小优先考虑CHAR类型，反之，考虑VARCHAR类型。

• 若字段存储的是MD5后的哈希值，或一些定长的值，优先选取CHAR类型。

• 若字段经常需要更新，则优先考虑CHAR类型，由于CHAR类型为定长，因此不容易产生碎片。

• 对于字段值存储很小的信息，如性别等，优先选取CHAR类型，因为VARCHAR类型会占用额外的字节保存字符串长度信息。

总之一句话，当我们能够选取CHAR类型的时候，或者说空间消耗相对并不是影响因素的重点时，尽量选取CHAR类型，因为在其他方面，CHAR类型都有着或多或少的优势。而当空间消耗成为了很大的影响因素以后，我们则考虑使用VARCHAR类型

varchar(50)代表的含义：

varchar(50)中50的涵义最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by xxx采用fixed_length计算xxx长度。

早期MySQL版本中50表示字节数，现在表示字符数

¶ BLOB 和 TEXT

BLOB和TEXT都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储。

与其他类型不同，MySQL把每个BLOB和TEXT值当做一个独立的对象去处理。当BLOB和TEXT值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

MySQL对BLOB和TEXT列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引。

¶ 时间和日期

MySQL 提供了两种相似的日期时间类型: DATETIME 和 TIMESTAMP。

¶ 1. DATETIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

¶ 2. TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

¶ 选择表示符（identifier）

整数类型通常是标识列的最佳选择，因为它们很快并且可以使用AUTO_INCREMENT。 如果可能，应该避免使用字符串类型作为标识列，因为它们很耗空间，并且比数字类型慢。 对于完全随机的字符串也需要多加注意，例如MD5(),SHA1()或者UUID()产生的字符串。这些函数生成的新值会任意分布在很大的空间内，这会导致INSERT以及一些SELECT语句变得很慢：

• 因为插入值会随机的写入到索引的不同位置，所以使得INSERT语句更慢。这会导致叶分裂、磁盘随机访问。

• SELECT语句会变的更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方。

• 随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的局部性原理失效。

¶ 选择优化的数据类型

• 更小的通常更好；更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；

• 简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；

• 尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列；