MySQL中定义数据字段的类型对你数据库的优化是非常重要的
MySQL支持多种类型,大致分为三类:数组、日期/时间和字符串(字符)类型。
MySQL数据类型选择原则
注意:一定能使用小的尽量使用小的,为什么呢?我们公司的数据库设计很乱大部分都是使用bigint和varchar多表查询(5-8个表)的时候慢的一批,而且还占用服务器资源,亲测!!!
更小的通常更好:一般情况下选择可以正确存储数据的最小数据类型。越小的数据类型通常更好,占用磁盘,内存和cpu缓存更小。
简单就好:简单的数据类型的操作通常需要更少的CPU周期。例如:整形比字符操作代价要小得多,因为字符集和校对规则(排序规则)使字符比整型比较更复杂
避免使用NULL:尽量制定列为NOT NULL,除非真的需要NULL类型的值。因为可能为NULL列使用索引,索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理
为什么MySQL数据库尽量避免使用NULL?
在Mysql中很多表都包含可为NULL(空值)的列,即使应用程序并不需要保存NULL也是如此,这是因为可为NULL是列的默认属性。但我们常在一些Mysql性能优化的书或者一些博客中看到观点:在数据列中,尽量不要用NULL 值,使用0,-1或者其他特殊标识替换NULL值,除非真的需要存储NULL值,那到底是为什么?如果替换了会有什么好处?同时又有什么问题呢?那么就看下面:
(1)如果查询中包含可为NULL的列,对MySQL来说更难优化,因为可为NULL的列使得索引,索引统计和值比较都更复杂。
(2)含NULL复合索引无效.
(3)可为NULL的列会使用更多的存储空间,在MySQL中也需要特殊处理。
(4)当可为NULL的列被索引时,每个索引记录需要一个额外的字节,在MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列的索引)变成可变大小的索引。
数值类型
MySQL支持所有标准SQL数值类型
这些类型包括严格数值类型(integer、smallint、declmal和numeric),以及近似数值数据类型(float、real和double precision)。
关键字int是integer的同义词,关键字dec是decimal的同义词
bit数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表
作为SQL标准的扩展,MySQL也支持整数类型tinyint、mediumint和bigint。下面的表显示需要的每个整数类型的存储和范围
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT(tinyint) | 1字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT(smallint) | 2字节 | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT(mediumint) | 3字节 | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT(int) | 4字节 | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 |
INTEGER(integer) | 4字节 | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 |
BIGINT(bigint) | 8字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极 大整数值 |
FLOAT(float) | 4字节 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE(double) | 8字节 | 省略...,如有需要请自行百度 | 省略...,如有需要请自行百度 | 双精度 浮点数值 |
DECLMAL(declmal) | M+2/D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为datetime、date、timestamp、time和year
每个时间类型有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值时使用“零”值
timestamp类型有专有的自动更新特性,将在后面描述
类型 | 大小 | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE(date) | 3字节 | 1000-01-01/ 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME(time) | 3字节 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR(year) | 1字节 | 1901/2155 | YYYY | 年份值 |
DATETIME (datetime) | 8字节 | 1000-01-01 00:00:00/ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP (timestamp) | 4字节 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
字符串类型指char、varchar、binary、varbinary、blob、text、enum和set。
类型 | 大小(字节) | 用途 |
---|---|---|
CHAR(char) | 0-255 | 定长字符串 |
VARCHAR(varchar) | 0-65535 | 变长字符串 |
TINYBLOB(tinyblob) | 0-255 | 不超过255个字符的二进制字符串 |
TINYTEXT(tinytext) | 0-255 | 短文本字符串 |
BLOB(blob) | 0-65535 | 二进制形式的长文本数据 |
TEXT(text) | 0-65535 | 长文本数据 |
MEDIUMBLOB(mediumblob) | 0-16777215 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT(mediumtext) | 0-16777215 | 中等长度文本数据 |
LONGBLOB(longblob) | 0-4294967295 | 二进制的形式的极大文本数据 |
LONGTEXT(longtext) | 0-4294967295 | 极大文本数据 |
char和varchar类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
binary和varbinary类类似于char和varchar,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值
blob是一个二进制大对象,可以容纳可变数量的数据,有四种blob类型:tinyblob、blob、mediumblob和longblob,它们只是可容纳值的最大长度不同。
有4中text类型:tinytext、text、mediumtext和longtext。这些对应4种blob类型,有相同的最大长度和存储需求