概述
MySQL 支持很多数据类型, 以便我们能在复杂的业务场景中支持各种各样的数据格式, 存储适当的数据内容. 我们在设计数据库时, 正确的使用数据库类型对整个数据库的整洁和高效, 会有很大的帮助.
目前常用的数据类型大致上可以分为 4 大类: 整数类型, 浮点数类型, 字符串 (字符) 类型, 日期 / 时间类型. 详细如下
4 大类 | 主要类型 |
整数类型 | tinyint 、 smallint 、 mediumint 、 int 、 bigint |
浮点数类型 | float 、 double 、 decimal |
字符串 (字符) 类型 | char 、 varchar 、 tinyblob 、 blob 、 mediumblob 、 longblob 、tinytext 、 text 、 mediumtext 、 longtext |
日期 / 时间类型 | Date 、 DateTime 、 TimeStamp 、 Time 、 Year |
数值类型
MySQL 支持所有标准 SQL 数值数据类型, 包括严格数值数据类型(INTEGER,SMALLINT,DECIMAL 和 NUMERIC), 还有近似数值数据类型(FLOAT,REAL 和 DOUBLE PRECISION), 所以 MySQL 中数据类型是丰富且完整的.
而作为 SQL 标准的扩展, MySQL 也支持整数类型 TINYINT,MEDIUMINT 和 BIGINT. 在下面的表格中, 我们总结了 5 个整数类型的存储大小和取值范围.
类型 | 字节数 | 有符号值范围 | 无符号值范围 | 作用 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT 或 INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
我们创建表赋予的类型默认是有符号类型的, 无符号的需要在类型需要后面跟上 unsigned 标记, 上面的数据范围是根据字节对应二进制换算, 比如一个字节转化为十进制最大为 255(11111111), 最小为 0(00000000), 具体可以去查下二进制表示相关资料.
测试一下:
- MySQL> create table tinyintdemo(
- tiny TINYINT,
- tinyunsi TINYINT UNSIGNED
- );
- Query OK, 0 rows affected
- MySQL> insert into tinyintdemo values(-128,-128);
- 1264 - Out of range value for column 'tinyunsi' at row 1
- MySQL> insert into tinyintdemo values(-128,0);
- Query OK, 1 row affected
- MySQL> insert into tinyintdemo values(255,255);
- 1264 - Out of range value for column 'tiny' at row 1
- MySQL> insert into tinyintdemo values(127,255);
- Query OK, 1 row affected
上面我们故意输入不正确的值, 无符号下我们输入 - 128, 默认有符号情况下我们输入 255, 都会提示 Out ofrange, 超出范围.
- MySQL> select * from tinyintdemo;
- +------+----------+
- | tiny | tinyunsi |
- +------+----------+
- | -128 | 0 |
- | 127 | 255 |
- +------+----------+
- 2 rows in set
另外需注意的点: 我们经常在创建表的时候定义字段为 int(n), 比如 int(11) , 或者 bitint(19) , 其实这不是描述字段长度的意思, 在 int(n)中, int 占据的字节是固定 4 个, 所以他的范围也固定是在 (-2 147 483 648,2 147 483 647) 之间,
并不会因为 n 的长度是多少而改变, 而 n 的目的是为了表实显示宽度用的, 在显示宽度不足的时候补充 0.
再测试一下:
- MySQL> CREATE TABLE lengthdemo (
- `nor` int,
- `norlen` int(8),
- `norfill` int(8) zerofill
- );
- Query OK, 0 rows affected
- MySQL> insert into lengthdemo values(12345,12345,12345);
- Query OK, 1 row affected
- MySQL> select * from lengthdemo;
- +-------+--------+---------+
- | nor | norlen | norfill |
- +-------+--------+---------+
- | 12345 | 12345 |00012345 |
- +-------+--------+---------+
- 1 row in set
- MySQL> show create table lengthdemo;
- +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | Table | Create Table |
- +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- | lengthdemo | CREATE TABLE `lengthdemo` (
- `nor` int(11) DEFAULT NULL,
- `norlen` int(8) DEFAULT NULL,
- `norfill` int(8) unsigned zerofill DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
- +------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set
从这个执行结果中, 我们可以总结如下:
1,n 的值不管填写多少, int 类型只占据 4 个字节.
2, 如果设置了 n 的显示宽度之后, 再加上 unsigned zerofill, 显示宽度不足的用 0 补足, 超过则直接显示完整数值.
3, 加 zerofill 属性的字段, 会默认为 unsigned 无符号类型.
浮点类型
float 数值类型表示单精度浮点数值, double 数值类型表示双精度浮点数值, float 和 double 都是浮点型, 而 decimal 是定点型. 浮点型和定点型可以用类型名称后加 (M,D) 来表示, M 表示该值的总共长度, D 表示小数点后面的长度, M 和 D 又称为精度和标度.
float 和 double 在不指定精度时, 默认会按照实际的精度来显示, 而 DECIMAL 在不指定精度时, 默认整数为 10, 小数为 0.
类型 | 大小 | 有符号值范围 | 无符号值范围 | 作用 |
---|---|---|---|---|
FLOAT | 4 bytes | (-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) | 单精度 < br ztid="230" ow="0" oh="0">浮点数值 |
DOUBLE | 8 bytes | (-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) | 双精度 < br ztid="237" ow="0" oh="0">浮点数值 |
DECIMAL | 对 DECIMAL(M,D) ,如果 M>D,为 M+2 否则为 D+2 | 依赖于 M 和 D 的值 | 依赖于 M 和 D 的值 | 小数值 |
浮点型和定点型可以用类型名称后加 (M,D) 来表示, M 表示该值的总共长度, D 表示小数点后面的长度, M 和 D 又称为精度和标度.
float 和 double 在不指定精度时, 默认会按照实际的精度来显示, 而 DECIMAL 在不指定精度时, 默认只取整数, 小数丢弃.
同样的, 我们来测试下三个类型的使用:
- MySQL> insert into accuracy values (1,1,1),(2.124,2.124,2.124),(3.125,3.125,3.125),(4.115,4.115,4.115),(5.136,5.136,5.136);
- Query OK, 5 rows affected, 4 warnings (0.07 sec)
- Records: 5 Duplicates: 0 Warnings: 4
- MySQL> select * from accuracy;
- +------+------+------+
- | a | b | c |
- +------+------+------+
- | 1.00 | 1.00 | 1.00 |
- | 2.12 | 2.12 | 2.12 |
- | 3.12 | 3.12 | 3.13 |
- | 4.12 | 4.12 | 4.12 |
- | 5.14 | 5.14 | 5.14 |
- +------+------+------+
- 5 rows in set (0.00 sec)
从这个执行结果, 总结如下:
1,c 是 decimal 类型, 采用的是四舍五入
2,a 和 b 分别为 float 和 double, 采用的是四舍六入五成双
说明下四舍六入五成双: 5 以下舍弃, 5 以上进位, 遇到 5 的时候, 如果 5 后面还有不为 0 的任何数字, 进位, 如果没有, 需要检查 5 前面的数字, 奇数进位, 偶数丢弃, 观察结果中 3.125 和 4.115, 可以得到规律.
我们再将浮点类型的 (M,D) 精度和标度都去掉, 结果如下:
- MySQL> create table accuracy2(a float,b double,c decimal);
- Query OK, 0 rows affected
- MySQL> insert into accuracy2 values (1,1,1),(1.23,1.23,1.23);
- Query OK, 2 rows affected
- Records: 2 Duplicates: 0 Warnings: 1
- MySQL> select * from accuracy2;
- +------+------+---+
- | a | b | c |
- +------+------+---+
- | 1 | 1 | 1 |
- | 1.23 | 1.23 | 1 |
- +------+------+---+
- 2 rows in set
总结如下:
如果浮点数 float,double 如果不写精度和标度, 则会按照实际的结果显示, 而 decimal 会将小数四舍五入, 并且插入时发出警告信息.
float,double 等非标准类型, 在 DB 中保存的是近似值, 而 decimal 则以字符串的形式保存准确的数值, 比如银行, 金融系统之类的对统计精度要求比较高的, 建议采用 decimal.
日期和时间类型
表示时间值的日期和时间类型为 DATETIME,DATE,TIMESTAMP,TIME 和 YEAR. 每个时间类型有一个有效值范围和一个 "零" 值, 当指定不合法的 MySQL 不能表示的值时使用 "零" 值.
TIMESTAMP 类型有专有的自动更新特性, 将在后面描述.
类型 | 大小 | 值范围 | 格式 | 作用 |
---|---|---|---|---|
DATE | 3 bytes | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 bytes | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 bytes | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 bytes | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 bytes | 1970-01-01 00:00:00/2038 结束时间是第 & nbsp;2147483647 秒,北京时间 & nbsp;2038-1-19 11:14:07,格林尼治时间 2038 年 1 月 19 日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
字符串类型指 CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM 和 SET.
类型 | 大小 | 作用 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意点:
1,CHAR(N) 和 VARCHAR(N) 中 N 代表字符的个数, 但并不代表字节个数, 比如 CHAR(10) 代表可以存储 10 个字符.
2,CHAR 和 VARCHAR 类型类似, 但它们保存和检索的方式不同, 最大长度和是否尾部空格被保留等方面也不同, 同时在存储或检索过程中不进行大小写转换.
如果存放的数据为固定长度的建议使用 char 类型, 如: 手机号码, 身份证等固定长度的信息.
3,BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR, 不同的是它们包含二进制字符串而不要非二进制字符串. 它们包含字节字符串而不是字符字符串. 这说明它们没有字符集, 并且排序和比较基于列值字节的数值.
4,BLOB 是一个二进制大对象, 可以容纳可变数量的数据, 包含 4 种类型: TINYBLOB,BLOB,MEDIUMBLOB 和 LONGBLOB, 区别是可容纳存储范围不同.
5,TEXT 类型: TINYTEXT,TEXT,MEDIUMTEXT 和 LONGTEXT. 对应的这 4 种 BLOB 类型, 可存储的最大长度不同, 可根据实际业务场景的选择.
MySQL,Java 类型对照
MySQL Type Name | Return value ofGetColumnClassName | Returned as Java Class |
BIT(1) (new in MySQL-5.0) | BIT | java.lang.Boolean |
BIT( > 1) (new in MySQL- 5.0) | BIT | byte[] |
TINYINT | TINYINT | java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not. |
BOOL, BOOLEAN | TINYINT | See TINYINT, above as these are aliases forTINYINT(1), currently. |
SMALLINT[(M)] [UNSIGNED] | SMALLINT [UNSIGNED] | java.lang.Integer (regardless if UNSIGNED or not) |
MEDIUMINT[(M)] [UNSIGNED] | MEDIUMINT [UNSIGNED] | java.lang.Integer, if UNSIGNED java.lang.Long |
INT,INTEGER[(M)] [UNSIGNED] | INTEGER [UNSIGNED] | java.lang.Integer , if UNSIGNED java.lang.Long |
BIGINT[(M)] [UNSIGNED] | BIGINT [UNSIGNED] | java.lang.Long , if UNSIGNED java.math.BigInteger |
FLOAT[(M,D)] | FLOAT | java.lang.Float |
DOUBLE[(M,B)] | DOUBLE | java.lang.Double |
DECIMAL[(M[,D])] | DECIMAL | java.math.BigDecimal a |
DATE | DATE | java.sql.Date Ja |
DATETIME | DATETIME | java.sql.Timestamp |
TIMESTAMP[(M)] | TIMESTAMP | java.sql.Timestamp |
TIME | TIME | java.sql.Time |
YEAR[(2|4)] 公 | YEAR 众 | If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short . If set to true (the default) then an object of type java.sql.Date (with the date set to January 1st, at midnight). |
CHAR(M) | CHAR | java.lang.String (unless the character set for the column is BINARY, then byte[] is returned. |
VARCHAR(M) [BINARY] | VARCHAR | java.lang.String (unless the character set for the column is BINARY, then byte[] is returned. |
BINARY(M) | BINARY | byte[] |
VARBINARY(M) | VARBINARY | byte[] |
TINYBLOB | TINYBLOB | byte[] |
TINYTEXT | VARCHAR | java.lang.String |
BLOB | BLOB | byte[] |
MySQL Type Name | Return value ofGetColumnClassName | Returned as Java Class |
TEXT | VARCHAR | java.lang.String |
MEDIUMBLOB | MEDIUMBLOB | byte[] |
MEDIUMTEXT | VARCHAR | java.lang.String |
LONGBLOB | LONGBLOB | byte[] |
LONGTEXT | VARCHAR | java.lang.String |
ENUM('value1','value2',...) | CHAR | java.lang.String |
SET('value1','value2',...) | CHAR | java.lang.String |
使用建议
不建议选择较大的数值范围, 尽量选择合适的数据范围, 越小的数据类型会更快, 占用磁盘, 内存和 CPU 缓存也更小.
越简单的类型执行的成本越低, 比如整型比字符类型操作代价要小得多, 很多固定范围的文本内容字段可以用整型表示.
尽量制定列为 NOT NULL, 有 NULL 的列值会使得索引, 索引统计和值比较更加复杂.
浮点类型的建议统一选择 decimal, 精度会好很多.
记录时间的建议使用时间戳格式, 存储方便, 索引高效
来源: https://www.cnblogs.com/wzh2010/p/13842989.html