数据类型用来指定一定的存储格式、约束和有效范围。MySQL支持的数据类型非常多,主要包括数值型、字符串类型、日期和时间类型。
选择正确的数据类型对于获得高性能至关重要。
一、数值类型
1.整数类型
整数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
TINYINT | 1 | 有符号-128、无符号0 | 有符号127 无符号255 |
SMALLINT | 2 | 有符号-32768、无符号0 | 有符号32767 无符号65535 |
MEDIUMINT | 3 | 有符号-8388608、无符号0 | 有符号8388607、无符号1677215 |
INT、INTEGER | 4 | 有符号-2147483648、无符号0 | 有符号2147483647、无符号4294967295 |
BIGINT | 8 | 有符号-9223372036854775808、无符号0 | 有符号9223372036854775807、无符号18446744073709551615 |
- 每种整数类型都有取值范围,超出类型范围的操作会导致"out of range"错误提示。
- 整型数据,MySQL还支持在类型名称后面的小括号内指定显示宽度,如int(5)表示当数值宽度小于5时在数字前面填满宽度,如果不显示指定宽度则默认为int(11)。一般配合zerofill使用,顾名思义,zerofill就是用"0"填充的意思,在数字位数不够的空间用字符"0"填满。宽度格式不会对实际数据有任何影响,还是按照类型的实际精度进行保存。
- 如果一个列指定为zerofill,则MySQL自动为该列添加UNSIGNED属性。
2.浮点数类型
对于小数的表示, MYSQL分为两种方式:浮点数和定点数。浮点数包括 float(单精度)和 double(双精度),而定点数则只有decimal一种表示。定点数在内部以字符串形式存放,比浮点数更精确,适合用来表示货币等精度高的数据。
浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示,“(M,D)”表示该值一共显示M位数字(整数位+小数位),其中D位位于小数点后面,M和D又称为精度和标度。例如,定义为 float(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入,因此如果在float(7,4) 列内插入9.00009,近似结果是999.0001。值得注意的是,浮点数后面跟“(M,D)”的用法是非标准用法,如果要用于数据库的迁移,则最好不要这么使用。 float和 double在不指定精度时,默认会按照实际的精度由实际的硬件和操作系统决定)来显示,而 decimal在不指定精度时,默认的整数位为10,默认的小数位为0。
浮点数类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 |
DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 |
DEC(M,D), DECIMAL(M,D) | M+2 | 最大范围与double相同,给定decimal的有效取值范围由M和D决定 |
3.位类型
BIT(M)类型,用于存放位字段值,BIT(M)可以用来存放多位二进制数,M范围从1~64,如果不写则默认为1位。
位类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
BIT(M) | 1~8 | BIT(1) | BIT(64) |
二、日期时间类型
日期和时间类型 | 字节 | 零值表示 | 最小值 | 最大值 |
---|---|---|---|---|
DATE | 4 | 0000-00-00 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 0000-00-00 00:00:00 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 4 | 00000000000000 | 19700101080001 | 2038年的某个时刻 |
TIME | 3 | 00:00:00 | -838:59:59 | 838:59:59 |
YEAR | 1 | 0000 | 1901 | 2155 |
三、字符串类型
字符串类型 | 字节 | 描述及存储需求 |
---|---|---|
CHAR(M) | M | M为0~255之间的整数 |
VARCHAR(M) | M为0~65536之间的整数,值得长度+1个字节 | |
TINYBLOB | 允许长度0~255字节,值得长度+1个字节 | |
BLOB | 允许长度0~65535字节,值得长度+2个字节 | |
MEDUIMBLOB | 允许长度0~167772150字节,值得长度+3个字节 | |
LONGBLOB | 允许长度0~4294967295,值得长度+4个字节 | |
TINYTEXT | 允许长度0~255字节,值得长度+2个字节 | |
TEXT | 允许长度0~65535字节,值得长度+2个字节 | |
MEDIUMTEXT | 允许长度0~167772150字节,值得长度+3个字节 | |
LONGTEXT | 允许长度0~4294967295字节,值得长度+4个字节 | |
VARBINARY(M) | 允许长度0~M个字节的边长字节字符集,值得长度+1个字节 | |
BINARY(M) | M | 允许长度0~M个字节的定长字节字符集 |
- char和varchar很类似,都用来保存MySQL中较短的字符串。
- char列的长度固定为创建表时声明的长度,长度可以是0~255的数值。
- char列删除尾部的空格,varchar则保留尾部的空格。
- char适合存储很短的字符串,或者所有值都接近同一个长度。对于经常变更的数据char也比varchar更好,因为定长的char不容易产生碎片。
- varchar适合于:字符串列的最大长度比平均长度大很多;列的更新很少所以碎片问题可忽略;使用了像UTF-8这样复杂的字符集,每个字符使用不同的字节数进行存储。