MySQL 数据类型详情

这篇文章主要介绍了MySQL 数据类型，数值类型分类又分严格数值类型和近似数值数据类型，下面文章围绕MySQL 数据类型展开内容,需要的朋友可以参考一下

目录

1、数值类型

1.1、数值类型分类

  • 严格数值类型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC）
  • 近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）

MySQL中数值类型：

整数类型 字节　 最小值 最大值

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

浮点数类型

字节

 最小值 最大值

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

决定

位类型 

字节

 最小值 最大值

BIT(M)

1～8 

BIT(1)

BIT(64) 

1.1.1、浮点数

浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

1.1.2、位类型

BIT(位)类型：范围从1-64，如果不写，默认为1位，对于此字段，直接使用select命令将不会看到结果，可以用bin()(显示为二进制格式)或者hex()（显示为16进制格式）函数进行读取

例：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

mysql> desc t2;

+-------+--------+------+-----+---------+-------+

| Field | Type   | Null | Key | Default | Extra |

+-------+--------+------+-----+---------+-------+

| id    | bit(1) | YES  |     | NULL    |       |

+-------+--------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql> insert into t2 values(1);

Query OK, 1 row affected (0.05 sec)

mysql> select * from t2;

+------+

| id   |

+------+

|     |

+------+

1 row in set (0.00 sec)

mysql> select bin(id),hex(id) from t2;

+---------+---------+

| bin(id) | hex(id) |

+---------+---------+

| 1       | 1       |

+---------+---------+

1 row in set (0.03 sec)

bit类型数据插入时，首先将值转换为二进制，如果允许，则进行插入，如果位数小于实际定义的位数，则插入失败。

例：如果在刚刚的那张表中插入2，实际转换为二进制为10，超出了bit(1)的实际定义位数，就会报异常，将id定义为bit(2),在插入，就会成功

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

mysql> insert into t2 values(2);

ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> alter table t2 modify id bit(2);

Query OK, 1 row affected (0.67 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(2);

Query OK, 1 row affected (0.03 sec)

mysql> select bin(id),hex(id) from t2;

+---------+---------+

| bin(id) | hex(id) |

+---------+---------+

| 1       | 1       |

| 10      | 2       |

+---------+---------+

2 rows in set (0.00 sec)

1.1.3、时间日期类型

1.1.3、时间日期类型

日期和时间类型 字节 最小值 最大值

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 
  • DATE：用来表示年月日
  • DATETIME：用来表示年月日时分秒（支持插入格式：2007-9-3 12:10:10'、'2007/9/3 12+10+10'、'20070903121010'、20070903121010 等）
  • TIME：只用来表示时分秒
  • TIMESTAMP：经常插入或者更新日期为当前系统时间
  • YEAR：表示年

例1：创建时间表（dt），字段分别为 date、time、datetime 三种日期类型，并插入数值，查看显示结果

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql> create table dt(d date,t time,dt datetime);

Query OK, 0 rows affected (0.23 sec)

mysql> insert into dt values(now(),now(),now());

Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from dt;

+------------+----------+---------------------+

| d          | t        | dt                  |

+------------+----------+---------------------+

| 2021-05-13 | 10:14:07 | 2021-05-13 10:14:07 |

+------------+----------+---------------------+

1 row in set (0.00 sec)

 例2：创建测试表t，字段id1为TIMESTAMP类型，插入空值，并显示

1

2

3

4

5

6

7

8

9

10

11

12

13

mysql> create table t(id1 timestamp);

Query OK, 0 rows affected (0.22 sec)

mysql> insert into t values(null);

Query OK, 1 row affected (0.05 sec)

mysql> select * from t;

+---------------------+

| id1                 |

+---------------------+

| 2021-05-13 10:18:05 |

+---------------------+

1 row in set (0.00 sec)

可以发现，系统给 id1 自动创建了默认值 CURRENT_TIMESTAMP（系统日期）。（注意，MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值）

例3：解释如上说明

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> alter table t add column id2 timestamp;

Query OK, 0 rows affected (0.48 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t \G;

*************************** 1. row ***************************

       Table: t

Create Table: CREATE TABLE `t` (

  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR:

No query specified

1.1.4、字符串类型

字符串类型 字节 描述及存储需求

CHAR（M） 

 M

M 为 0～255 之间的整数

VARCHAR（M）

M 为 0～65535 之间的整数，值的长度+1 个字节

TINYBLOB

允许长度 0～255 字节，值的长度+1 个字节

BLOB 

允许长度 0～65535 字节，值的长度+2 个字节

MEDIUMBLOB

允许长度 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：可变长字符串，在检索时，会保留这些空格

例：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

mysql> create table varc(v varchar(4),c char(4));

Query OK, 0 rows affected (0.20 sec)

mysql> insert into varc values('abc ','abc ');

Query OK, 1 row affected (0.03 sec)

mysql> select length(v),length(c) from varc;

+-----------+-----------+

| length(v) | length(c) |

+-----------+-----------+

|         4 |         3 |

+-----------+-----------+

1 row in set (0.01 sec)

mysql> select concat(v,'+'),concat(c,'+') from varc;

+---------------+---------------+

| concat(v,'+') | concat(c,'+') |

+---------------+---------------+

| abc +         | abc+          |

+---------------+---------------+

1 row in set (0.00 sec)
  •  BINARY：类似于char，但他是二进制字符串
  • VARBINARY：类似于varchar，单他是存储二进制字符串

例：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> create table bina(c binary(3));

Query OK, 0 rows affected (0.22 sec)

mysql> insert into t set c='a';

ERROR 1054 (42S22): Unknown column 'c' in 'field list'

mysql> insert into bina set c='a';

Query OK, 1 row affected (0.05 sec)

mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from bina;

+------+--------+-------+---------+-----------+

| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |

+------+--------+-------+---------+-----------+

| a    | 610000 |     0 |       0 |         1 |

+------+--------+-------+---------+-----------+

1 row in set (0.00 sec)

可以发现，当保存 BINARY 值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。从上例中看出，对于一个 BINARY(3)列，当插入时'a'变为'a\0\0'

1.1.5、ENUM 类型

ENUM：对 1～255 个成员的枚举需要 1 个字节存储；对于 255～65535 个成员，需要 2 个字节存储。

例：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

mysql> create table gend(gender enum('M','F'));

Query OK, 0 rows affected (0.20 sec)

mysql> insert into gend values('M'),('F'),('1'),(null);

Query OK, 4 rows affected (0.03 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from gend;

+--------+

| gender |

+--------+

| M      |

| F      |

| M      |

| NULL   |

+--------+

4 rows in set (0.00 sec)

由此可以看出，枚举类型的插入值得时候，是忽略大小写的，初始下标从1开始；查询的时候会转换为大写，也可以插入空（null）

1.1.6、SET类型

SET：字符串对象，里面可以包含64个成员，成员不同，存储上也有所不同

1～8 成员的集合，占 1 个字节。
9～16 成员的集合，占 2 个字节。
17～24 成员的集合，占 3 个字节。
25～32 成员的集合，占 4 个字节。
33～64 成员的集合，占 8 个字节。
Set和EMUN区别在于，Set一次可以选则多个成员，而ENUM则只能选择一个

例：

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

mysql> create table st(col set('a','b','c','d'));

Query OK, 0 rows affected (0.20 sec)

mysql> insert into st values('a,b'),('a,d,a'),('a,c'),('a');

Query OK, 4 rows affected (0.03 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into st values('f');

ERROR 1265 (01000): Data truncated for column 'col' at row 1

mysql> insert into st values(null);

Query OK, 1 row affected (0.03 sec)

mysql> select * from st;

+------+

| col  |

+------+

| a,b  |

| a,d  |

| a,c  |

| a    |

| NULL |

+------+

5 rows in set (0.00 sec)

由此可以看出，set是可以插入多个成员的，也可以插入null，插入不存在的定义列时，会报异常插入失败。

分享不易，如果觉得分享对大家有帮助，老规矩，点赞、关注、留言支持哦！

全部评论

相关推荐

头像
肱二头肌上岸了
05-10 09:05
Python
点赞 评论 收藏
转发
头像
喜欢唱跳rap做题家想交流
昨天 13:24
已编辑
武汉理工大学 计算机类
点赞 评论 收藏
转发
头像
yu77
03-20 10:53
广州理工学院
点赞 评论 收藏
转发
头像 会员标识 头像
牛客296407561号
04-28 00:49
中山大学 数学类
点赞 评论 收藏
转发
头像
温和kind
05-11 16:07
杭州电子科技大学 计算机类
在腾讯云智的巴适实习生活
团队氛围重庆不光人很巴适，生活上也很巴适。在这段日子里，组内已经出去聚餐团建过2次了，每次都很开心，吃的也满足，当下的幸福值直接拉满。还有2次别的地方的同事，请喝奶茶和饮料，拍照分享后，朋友都羡慕急了直接。公司内部还有很多娱乐活动，篮球，足球，还有桌游，几乎每周都有，工作之余完全不用担心去哪里放松玩耍。甚至每周都很期待周四晚上的桌游活动。都说爱上一座城市不光要看这座城市本身的魅力，更要看日常生活和工作以及人与人的魅力。显然，我已经爱上了重庆这个城市，虽然短短两个多月，但是自己可以很明显的感受到工作和生活在这里，是多么幸福。难道真的是，少不入川，老不入蜀？？？个人提升工作中将会有一对一导师细致辅...
投递腾讯云智研发等公司8个岗位 >
点赞 评论 收藏
转发
点赞 收藏 评论
分享

全站热榜

正在热议
# 牛客帮帮团来啦！有问必答 #
711663次浏览 11530人参与
# 许愿池 #
77276次浏览 1542人参与
# 通信硬件人笔面经互助 #
107921次浏览 2179人参与
# 你的秋招进展怎么样了 #
501394次浏览 13426人参与
# 找工作时遇到的神仙HR #
177805次浏览 1745人参与
# 如何写一份好简历 #
259553次浏览 3920人参与
# 铜五铁六真的存在吗？ #
27411次浏览 293人参与
# 找工作，你会甘心进小厂还是猛冲大厂 #
35112次浏览 352人参与
# 产品实习，你更倾向大公司or小公司 #
35970次浏览 548人参与
# 非技术岗是怎么找实习的 #
73908次浏览 1385人参与
# 市场营销面经 #
4574次浏览 125人参与
# 互联网公司评价 #
79669次浏览 1087人参与
# 通信硬件薪资爆料 #
196497次浏览 1761人参与
# 你的秋招进行到哪一步了 #
353392次浏览 6269人参与
# 硬件兄弟们 甩出你的华为奖状 #
27526次浏览 180人参与
# 无实习如何秋招上岸 #
224895次浏览 3518人参与
# 投了多少份简历才上岸 #
56772次浏览 947人参与
# 面试中的破防瞬间 #
82643次浏览 1015人参与
# 通信/硬件的薪资开多少，才值得去？ #
10754次浏览 140人参与
# 产品人求职现状 #
50619次浏览 747人参与
牛客网
牛客企业服务