MySQL数据类型详解
引言
mysql中定义数据字段的类型对你数据库的优化是非常重要的。
mysql支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型,如下脑图所示:
数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
tinyint | 1 字节 | (-128,127) | (0,255) | 小整数值 |
smallint | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
mediumint | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
int或integer | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
bigint | 8 字节 | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
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 | 8 字节 | (-1.797 693 134 862 315 7 e+308,-2.225 073 858 507 201 4 e-308),0,(2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) | 0,(2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) | 双精度 浮点数值 |
decimal | 对decimal(m,d) ,如果m>d,为m+2否则为d+2 | 依赖于m和d的值 | 依赖于m和d的值 | 小数值 |
其中:
这些类型,是定长的,其容量是不会随着后面的数字而变化的,比如int(11)和int(8),都是一样的占4字节。tinyint(1)和tinyint(10)也都占用一个字节。
那么后面的11和8,有啥用呢。
数据类型(m)中的m不是表示的数据长度,而是表示数据在显示时显示的最小长度。tinyint(1) 这里的1表示的是 最短显示一个字符。tinyint(2) 这里的2表示的是 最短显示两个字符。
当字符长度(m)超过对应数据类型的最大表示范围时,相当于啥都没发生;
当字符长度(m)小于对应数据类型的表示范围时,就需要指定拿某个字符来填充,比如zerofill(表示用0填充),
设置tinyint(2) zerofill 你插入1时他会显示01;设置tinyint(4) zerofill 你插入1时他会显示0001。
即使你建表时,不指定括号数字,mysql会自动分配长度:int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。
decimal(m,d)表示列可以存储d
位小数的m位数。十进制列的实际范围取决于精度和刻度。例如decimal(5,2) -- 取值范围是 -999.99 到 999.99
-
整数的位数必须小于等于m-d,不然报错。小数的位数可以大于d位。多出d位时会做四舍五入,截取到d位。
-
以上均不包括小数点、符号的位数。数字的总长度是m位,保存后的小数位最多是d位。如果保存后是整数,小数位不会补0。
日期和时间类型
类型 | 大小 (字节) |
范围 | 格式 | 用途 |
---|---|---|---|---|
date | 3 | 1000-01-01/9999-12-31 | yyyy-mm-dd | 日期值 |
time | 3 | '-838:59:59'/'838:59:59' | hh:mm:ss | 时间值或持续时间 |
year | 1 | 1901/2155 | yyyy | 年份值 |
datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | yyyy-mm-dd hh:mm:ss | 混合日期和时间值 |
timestamp | 4 |
1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 |
yyyymmdd hhmmss | 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
---|---|---|
char | 0-255字节 | 定长字符串 |
varchar | 0-65535 字节 | 变长字符串 |
tinyblob | 0-255字节 | 不超过 255 个字符的二进制字符串 |
tinytext | 0-255字节 | 短文本字符串 |
blob | 0-65 535字节 | 二进制形式的长文本数据 |
text | 0-65 535字节 | 长文本数据 |
mediumblob | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
mediumtext | 0-16 777 215字节 | 中等长度文本数据 |
longblob | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
longtext | 0-4 294 967 295字节 | 极大文本数据 |
char的列长度是固定的,char的长度可选范围在0-255字符之间。也就是char最大能存储255个字符。
varchar的列长度是可变的,在mysql5.0.3之前varchar的长度范围为0-255字符,mysql5.0.3之后varchar的长度范围为0-65535个字节。
char(m)定义的列的长度为固定的,m取值可以为0-255之间,当保存char值时,在它们的右边填充空格以达到指定的长度。当检索到char值时,尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。char存储定长数据很方便,char字段上的索引效率级高,比如定义char(10),那么不论你存储的数据是否达到了10个字符,都要占去10个字符的空间,不足的自动用空格填充。
varchar(m)定义的列的长度为可变长字符串,varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节。varchar值保存时不进行填充。当值保存和检索时尾部的空格仍保留,符合标准sql。varchar存储变长数据,但存储效率没有char高。如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字符,把它定义为 varchar(10)是最合算的。varchar类型的实际长度是它的值的实际长度+1。为什么"+1"呢?这一个字节用于保存实际使用了多大的长度。从空间上考虑,用varchar合适;从效率上考虑,用char合适,关键是根据实际情况找到权衡点。
简单来说:varchar(m)里面表示的是长度,例如:varchar(5)表示最大可存储5个中文或5个英文字母。
总结
数据库作为程序的核心,设计的好坏,直接影响系统的性能,因此要不断总结学习,设计符号标准的、稳定的、可靠的数据库。
上一篇: 天猫这6大改动,对卖家是流量口还是坑?
推荐阅读