在设计数据库表时,其中一项重要步骤便是选择表中字段的数据类型。
如果你选择的是mysql作为数据库,必须要清楚mysql到底支持哪些数据类型呢?
总体来说包括数值型、字符串类型、日期和时间类型这么几大类。
1、数值类型
数值类型中又细分为整数类型、浮点数类型、定点数类型和位类型。下面分别述之。
整数类型
整数类型 | 占用字节 | 范围 |
TINYINT | 1 | 有符号:[-128,127] 或无符号:[0,255] |
SMALLINT | 2 | 有符号:[-32768,32767]或无符号:[0,65535] |
MEDIUMINT | 3 | 有符号:[-8333608,8388607]或无符号:[0,1677215] |
INT、INTEGER | 4 | 有符号:[-21亿多,21亿多]或无符号:[0,42亿多] |
BIGINT | 8 | 很大,19位数字的范围 |
记住mysql的整数类型有5种,分别是tinyint、smallint、mediumint、int和bigint。
另外还有两个特性:
- 所有整数类型都有一个可选的属性UNSIGNED(无符号),此时上限取值是原来的2倍。
- 还有一个属性是AUTO_INCREMENT(自增),该属性只能用于整数类型。
浮点数类型
浮点数类型 | 字节 | 范围 |
FLOAT | 4 | [1.175494351E-38,3.402823466E+38] |
DOUBLE | 8 | [2.2…E-308,1.7…E+308] |
定点数类型
定点数类型 | 字节 | 范围 |
DECIMAL(M,D) | M+2 | 最大取值范围与DOUBLE相同,有效范围由M,D决定 |
位类型
位类型 | 字节 | 范围 |
BIT(M) | 1-8 | 最小值BIT(1),最大值BIT(64) |
浮点数和定点数都可以用(M,D)的方式来进行表示。
(M,D)表示该值一共显示M位数字(整数位+小数位),其中D位位于小数点后面。
- M:精度
- D:标度
注意在一些表述货币等精确数字的场景必须使用DECIMAL类型。
BIT类型用来存放多为二进制数,数据在插入BIT类型字段时,首先会转换成二进制数。因此,直接使用SELECT命令将不会看到结果。可以用bin()或hex()函数进行读取。
2、字符串类型
MySQL中提供了多种对字符数据的存储类型,不同版本可能有所差异,本文以5.0版本为例。
字符串类型 | 描述 |
CHAR(M) | M为0-255之间的整数 |
VARCHAR(M) | M为0-65535之间的整数 |
TINYBLOB | 允许0-255字节 |
BLOB | 允许0-65535字节 |
MEDIUBLOB | 允许0-167772150字节 |
LONGBLOB | 允许0-4294967295字节 |
TINYTEXT | 允许0-255字节 |
TEXT | 允许0-65535字节 |
MEDIUMTEXT | 允许0-167772150字节 |
LONGTEXT | 允许0-4294967295字节 |
VARBINARY(M) | 允许长度0-M个字节的边长字节字符串 |
BINARY(M) | 允许0-M个字节的定长字节字符串 |
CHAR和VARCHAR很类似,都用来保存mysql中较短的字符串。二者的主要区别在于存储方式不同:
- CHAR列的长度固定为创建时表明的长度;
- 而VARCHAR列中的值为可变长字符串。
比如存储字符串“abc”,CHAR(5)占用了5个字节,而VARCHAR(5)占用3个字节。
3、日期时间类型
分类:
- 表示年月日,用DATE
- 表示年月日时分秒,用DATETIME
- 表示时分秒,用TIME
日期时间类型 | 字节 | 范围 |
DATE | 4 | 1000-01-01到9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00到9999-12-31 23:59:59 |
TIMESTAMP | 4 | 19700101080001到2038年某个时刻 |
TIME | 3 | -838:59:59到838:59:59 |
YEAR | 1 | 1901到2155 |
注意TIMESTAMP有一个特点就是和时区相关。当插入日期时,会先转换为本地时区后存放;而从数据库里面取出时,也同样需要将日期转换为本地时区后显示。
查看当前时区的SQL:show variables like 'time_zone';