MySQL数据类型详解

MySQL数据类型大致可以分为三类:数值、日期和时间、字符串类型。

数值类型

数值类型又可以分为整数类型、浮点数型、定点型。整型主要用于存储整数值。整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,INT是最常用整数类型。

  • 整型分类及其存储范围

MySQL数据类型详解


为什么要了解整数类型所占的字节数?因为根据数据类型所占字节数就可以算出该类型的取值范围。以int类型为例,int类型占用字节数为4byte(B),而比字节更小的单位是bit(b),一个位就代表一个0或1,8个位组成一个字节,一般字节用大写B来表示byte, 位用小写b来表示bit,根据计算机存储单位的换算:1B=8b 1KB=1024B 1MB=1024KB。可以换算出int UNSIGNED(无符号)类型的能存储的最小值为0, 最大值为4294967295。(即4B=32b, 最大值即为32个1组成,而十进制4294967295换算成二进制则是32个1)

  • 整型存储范围测试

mysql> create table test(col1 tinyint,col2 smallint,col3 mediumint,col4 int,col5 bigint);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test G;
*************************** 1. row ***************************
      Table: test
Create Table: CREATE TABLE `test` (
 `col1` tinyint(4) DEFAULT NULL,
 `col2` smallint(6) DEFAULT NULL,
 `col3` mediumint(9) DEFAULT NULL,
 `col4` int(11) DEFAULT NULL,
 `col5` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> insert into test values (1234,123456,12345678,12345678901,12345678901234567890);
Query OK, 1 row affected, 5 warnings (0.00 sec)

mysql> insert into test values (-1234,-123456,-12345678,-12345678901,-12345678901234567890);
Query OK, 1 row affected, 5 warnings (0.01 sec)

mysql> select * from test;
+------+--------+----------+-------------+----------------------+
| col1 | col2   | col3     | col4       | col5                 |
+------+--------+----------+-------------+----------------------+
|  127 |  32767 |  8388607 |  2147483647 |  9223372036854775807 |
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+------+--------+----------+-------------+----------------------+
2 rows in set (0.009 sec)

从上述测试中我们可以看出:有符号时,各种整型类型最大的存储范围,当存储数字大小不在存储范围时,MySQL会产生告警,但数字可以插入,默认截取为可存储的最大值或最小值。

  • int(M)中M的含义与zerofill的使用

int(M)中的M代表最大显示宽度,例如:int(11) 11 代表的并不是长度,而是字符显示宽度在字段类型为 int 时,无论你显示宽度设置为多少,int 类型能存储的最大值和最小值永远都是固定的。即int(10)和int(11)可存储的范围一样。

整型字段有个ZEROFILL属性(0填充),在数字长度不够的数据前面填充0,以达到设定的长度。加上ZEROFILL后M才表现出不同,当使用ZEROFILL时,默认会自动加unsigned(无符号)属性。比如 INT(3) ZEROFILL,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0,下面我们来测试下:

mysql> create table test(col1 int(5) zerofill, col2 int zerofill,col3 int(5)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.053 sec)

mysql> show create table test G;
*************************** 1. row ***************************
      Table: test
Create Table: CREATE TABLE `test` (
 `col1` int(5) unsigned zerofill DEFAULT NULL,
 `col2` int(10) unsigned zerofill DEFAULT NULL,
 `col3` int(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.002 sec)

mysql> insert into test values (12,12,12);
Query OK, 1 row affected (0.015 sec)

mysql> select * from test;
+-------+------------+------+
| col1 | col2       | col3 |
+-------+------------+------+
| 00012 | 0000000012 |   12 |
+-------+------------+------+
1 row in set (0.000 sec)

从上个例子我们可以得出以下几个结论:

1)字段设置无符号和填充零属性时,在数字长度不够的数据前面填充0,以达到设定的长度。

2)设置字段的显示宽度并不限制字段存储值的范围,例如:int(5),仍然可以存储 1234567890 这个 10 位数字。

3)设置的字符宽度只对数值长度不满足宽度时有效,如 d 字段 int(5),插入 1 时,长度不足 5,因此在左边补充 4 个零直到 5 位,但是插入 1234567890 时超过了 5 位,这时的显示宽度就起不了作用了。

zerofill的应用场景比较常用的应该是月份或日期前补0,这样显示的会规范些。

mysql> create table test(  
 `year` year(4) DEFAULT NULL,  
 `month` int(2) unsigned zerofill DEFAULT NULL,  
 `day` int(2) unsigned zerofill DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.040 sec)

mysql> insert into test values (2019,6,5);
Query OK, 1 row affected (0.006 sec)

mysql> insert into test values (2019,10,1);
Query OK, 1 row affected (0.015 sec)

mysql> insert into test values (2019,11,11);
Query OK, 1 row affected (0.010 sec)

mysql> select * from test;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2019 |    06 |   05 |
| 2019 |    10 |   01 |
| 2019 |    11 |   11 |
+------+-------+------+
3 rows in set (0.000 sec)
  • 类型选取

存储字节越小,占用空间越小。类型选择上遵循最小化存储的原则,当然是能选TINYINT不选SMALLINT,能选MEDIUMINT不选INT了,不过一切都要满足业务的前提下尽量选取占用字节更少的类型。对于确定只存储正整数的字段,可以加上unsigned属性,这样会使存储范围更大,比如当字段有AUTO_INCREMENT属性时,我们可以为int类型加上unsigned属性。存储一些状态值或人的年龄可以用 tinyint ;主键列,无负数,建议使用 int unsigned 或者 bigint unsigned,预估字段数字取值会超过 42 亿,使用 bigint 类型。

浮点数类型包括 FLOAT 和 DOUBLE。

MySQL数据类型详解


如果存储小数数据,可以选择FLOAT和DOUBLE类型。当需要精确到小数点后10位以上,就需要选择DOUBLE类型。float 和 double 平时用的不太多。

定点数类型为 DECIMAL。

MySQL数据类型详解


FLOAT类型和DECIMAL类型区别:

mysql> create table test(a float(38,30),b double(38,30),c decimal(38,30));
Query OK, 0 rows affected (0.054 sec)

mysql> insert into test values(123450.000000000000000000000000000001, 123450.000000000000000000000000000001, 123450.000000000000000000000000000001);
Query OK, 1 row affected (0.016 sec)

mysql> select * from test G;
*************************** 1. row ***************************
a: 123450.000000000000000000000000000000
b: 123450.000000000000000000000000000000
c: 123450.000000000000000000000000000001
1 row in set (0.000 sec)

FLOAT、DOUBLE数据类型存储数据时存储的是近似值,而DECIMAL存储的是字符串,因此提高了精度。当要求小数数据精确度非常高时,则可选择DECIMAL,它的精确度比DOUBLE类型还要高。在需要表示金额等货币类型时优先选择DECIMAL数据类型。

日期和时间类型

日期和时间类型包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

  • 日期和时间类型分类及其存储范围

MySQL数据类型详解


DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’,其中 YYYY 表示年,MM 表示月,DD 表示日。在给 DATE 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATE 的日期格式即可。

TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。TIME 类型的取值范围为 -838:59:59~838:59:59,小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间(必须小于 24 小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可大于 24 小时,或者甚至为负)。

YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR。

DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH:MM:SS’,其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。在给 DATETIME 类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,只要符合 DATETIME 的日期格式即可。

TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 ‘1970-01-01 00:00:01’UTC~’2038-01-19 03:14:07’UTC。在插入数据时,要保证在合法的取值范围内。

TIMESTAMP 与 DATETIME 区别:

  • 存储字节和支持的范围不同

  • DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;

  • 而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。

总结:如果要存储年月日时分秒,并且年份的取值可能比较久远,最好使用datetime类型,而不是timestamp类型,因为datetime类型的日期范围要长一些,如果存储的日期需要让不同时区的用户使用,则可以使用timestamp,因为只有该类型能够与实际时区相对应。


日期和时间类型的使用方法:

mysql> create table test(date date,datetime datetime,timestamp timestamp,time time,year year);
Query OK, 0 rows affected (0.038 sec)

mysql> insert into test values(curdate(),now(),now(),time(now()),year(now()));
Query OK, 1 row affected (0.013 sec)

mysql> select * from test G;
*************************** 1. row ***************************
    date: 2022-09-17
datetime: 2022-09-17 21:59:30
timestamp: 2022-09-17 21:59:30
    time: 21:59:30
    year: 2022
1 row in set (0.006 sec)

字符串类型

包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

  • 字符串类型分类及其存储范围

MySQL数据类型详解


CHAR(M) 为固定长度字符串,在定义时指定字符串列长。当保存时,在右侧填充空格以达到指定的长度。M 表示列的长度,范围是 0~255 个字符。例如,CHAR(4) 定义了一个固定长度的字符串列,包含的字符个数最大为 4。当检索到 CHAR 值时,尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串,M 表示最大列的长度,M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。例如,VARCHAR(50) 定义了一个最大长度为 50 的字符串,如果插入的字符串只有 10 个字符,则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

MySQL数据类型详解


字符串类型使用:

mysql> create table test(id int,name varchar(20));
Query OK, 0 rows affected (0.039 sec)

mysql> insert into test values(1,'bob'), (2,'petter'),(3,"a123456789123456789123");
Query OK, 3 rows affected, 1 warning (0.013 sec)
Records: 3 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 3 |
+---------+------+-------------------------------------------+
1 row in set (0.000 sec)

mysql> select * from test;
+------+----------------------+
| id   | name                 |
+------+----------------------+
|    1 | bob                 |
|    2 | petter               |
|    3 | a1234567891234567891 |
+------+----------------------+
3 rows in set (0.001 sec)

以上实例可以发现如果插入的字符串的长度超过字符串定义的长度,字符串会被截断并显示警告信息。

总结下CHAR与VARCHAR字段类型的适用场景:

CHAR适合存储很短的字符串,或者所有值都接近同一个长度。例如,CHAR非常适合存储密码的MD5值,因为这是一个定长的值。对于经常变更的数据,CHAR也比VARCHAR更好,因为定长的CHAR类型不容易产生碎片。对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值,如果采用单字节字符集只需要一个字节,但是VARCHAR(1)却需要两个字节,因为还有一个记录长度的额外字节。

下面这些情况下使用VARCHAR是合适的:字符串很长或者所要存储的字符串长短不一,差别很大;字符串列的最大长度比平均长度大得多;列的更新很少,所以碎片不是问题。

额外说明下,我们在定义字段最大长度时应该按需分配,提前做好预估。特别是对于VARCHAR字段,有人认为反正VARCHAR数据类型是根据实际的需要来分配长度的,还不如给大一点呢。但事实不是这样的,比如现在需要存储一个地址信息,根据评估,只要使用100个字符就可以了,我们可以使用VARCHAR(100)或VARCHAR(200)来存储,虽然它们用来存储90个字符的数据,其存储空间相同,但是对于内存的消耗是不同的。更长的列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值,尤其是使用内存临时表进行排列或者操作时会特别糟糕。所以我们在分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度,然后选择一个最长的字段来设置字符长度。如果为了考虑冗余,可以留10%左右的字符长度。千万不能认为VARCHAR是根据实际长度来分配存储空间,而随意的分配长度,或者说干脆使用最大的字符长度。

TEXT 列保存非二进制字符串,如文章内容、评论等。当保存或查询 TEXT 列的值时,不删除尾部空格。TEXT 类型分为 4 种:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。根据存储字符的长度来选择允许长度最小的TINYTEXT字符串类型,还是选择允许长度最大的LONGTEXT字符串类型。同样都是存储字符串,对比 varchar ,text 类型有以下特点:

  • text 类型无须指定长度。

  • 若数据库未启用严格的 sqlmode ,当插入的值超过 text 列的最大长度时,则该值会被截断插入并生成警告。

  • text 类型字段不能有默认值。

  • varchar 可直接创建索引,text 字段创建索引要指定前多少个字符。

  • text 类型检索效率比 varchar 要低。

BLOB 是一个二进制大对象,可以容纳可变数量的数据(存储电影等视频文件)。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。例如:保存图片base64数据时根据大小选择,BLOB类型,最大长度64K,适合存储小图片,存储大图片可以使用MEDIUMBLOB(最大16M)或者LONGBLOB类型(4G) 。不过数据库并不适合直接存储图片,如果有大量存储图片的需求,请使用对象存储或文件存储,数据库中可以存储图片路径来调用。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串。如果需要存储少量二进制数据,则可以选择BINARY和VARBINARY类型,如果是经常发生变化可以选择VARBINARY类型,否则选择BINARY类型。

总结: 

本篇文章主要介绍了 MySQL 中常用的字段类型,平时用到的字段类型基本都在这里了,以一张思维导图总结如下:

MySQL数据类型详解



原文始发于微信公众号(面试技术):MySQL数据类型详解

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/187145.html

(0)
小半的头像小半

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站,欢迎收藏到浏览器,订阅我们!