MySQL数据类型

linkindly

于 2023-06-23 22:17:17 发布

阅读量792

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_65307907/article/details/131293053

版权

前言

hello,各位小伙伴大家好！本篇文章为大家详细介绍MySQL的数据类型，在之前的文章中，创建表的时候会在创建属性字段的时候加上相应的数据类型，在之前大家可能不理解不同的数据类型代表的含义，相信看完本章内容，就可以认识每一种数据类型表示的是什么含义了。

1.数据类型分类

下面带大家先来看一张表，这张表中总结了MySQL的不同数据类型以及具体说明了每种数据类型所代表的含义，让我们对数据类型有一个整体和宏观的认识！

说明：在这张表中标红的重点需要掌握的，建表创建属性字段时常用的类型。

有了上面对MySQL数据类型整体的认识之后，下面我们就具体对每一种数据类型进行测试说明。

2.数值类型

2.1bit类型

语法：

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

举例：创建一个bit类型的属性字段，并且插入值

mysql> create table t1(a bit(8));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(10);
Query OK, 1 row affected (0.01 sec)

mysql> select* from t1;
+------+
| a    |
+------+
|     |
| 
    |
+------+
2 rows in set (0.00 sec)

上面我们创建了一个bit类型的字段a,并且插入了值1和10，但是当我们在查询表中的内容的时候发现没有数据，此时有小伙伴就感到特别好奇，插入的值到哪里去了？

这是因为bit值在显示的时候，是以ASCII值显示的，1和10对字符属于不可见字符，所以我们觉得在表中没有数据。我们知道字符A的ASCII值是65，下面我们插入65来验证一下：

mysql> insert into t1 values(65);
Query OK, 1 row affected (0.00 sec)

mysql> select* from t1;
+------+
| a    |
+------+
|     |
| 
    |
| A    |
+------+
3 rows in set (0.00 sec)

此时，可以证明上面所说的bit类型是以ASCII值的形式显示的

了解了如何bit类型之后，下面可能会有小伙伴有疑问说，为什么会有bit类型，以及什么时候使用bit类型。

是因为在数据库中存储数据的时候，如果只是存放0或者1，此时可以定义bit(1),这样做可以节省空间，毕竟在数据库中存储的数据如果是上亿条的时候，空间的利用率就显得格外重要了。

2.2tinyint类型

默认在不指定的时候是有符号类型范围是[-128,127],指定为无符号的时候范围为[0,255]。

下面通过两个案例，分别创建两张表，第一张表为有符号类型，第二张表为无符号类型，分别测试数值范围。

案例1：

mysql> create table t2(num tinyint);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t2 values(-128);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(127);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

通过案例1可以观察到当数值范围在[-128,127]以外的时候，插入显示失败。

案例2：

mysql> create table t3(num tinyint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(255);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t3 values(256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

通过案例2可以观察到指定为tinyint为无符号类型的时候，数值范围在[0,255]以外的时候，插入显示失败。

表示整型除了上面bit类型和tinyint类型之外还包括int,bigint等，它们使用的方式都是相同的，不同的区别在与使用不同的类型所占字节数大小不同即表示数值范围大小不同，对于不同的类型可以根据不同的使用场景进行选择。

2.3float类型

说明：在生活中，除了整型常用之外,还会经常使用浮点数，在MySQL中也包含浮点数类型，其中一种类型就是float.

语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

案例：

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

mysql> create table t4(id int,salary float(4,2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t4 values(100,-99.99);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t4 values(100,99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(100,-100);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t4 values(100,100);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t4 values(100,-9.9999);   #采用四舍五入
Query OK, 1 row affected (0.00 sec)

mysql> select* from t4;
+------+--------+
| id   | salary |
+------+--------+
|  100 | -99.99 |
|  100 |  99.99 |
|  100 | -10.00 |
+------+--------+
3 rows in set (0.00 sec)

在默认不指定的情况下，float是有符号数，float(4,2)数值范围是[-99.99,99.99],指定为无符号数，float(4,2) unsigned数值范围是[0,99,99],细心的小伙伴可能发现了，对于浮点数来说，无符号数是有符号数正数的一半。

2.4decimal

说明：与float类型相同也是可以用来表示浮点数。

语法：

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

例：

decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99

float和decimal的区别：表示的精度不一样

案例：

mysql> create table t5(id int,salary1 float(10,8),salary2 decimal(10,8));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t5 values(10,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select* from t5;
+------+-------------+-------------+
| id   | salary1     | salary2     |
+------+-------------+-------------+
|   10 | 23.12345695 | 23.12345612 |
+------+-------------+-------------+
1 row in set (0.00 sec)

通过上面这个案例可以发现，decimal的精度是比float的精度更高，所以在以后使用的时候，根据具体场景，如果需要存储的数据精度要求比较高，可以采用decimal

注：

float表示的精度大约是7位。
decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，
默认是10。

除此之外表示浮点数类型的还有double类型，与float不同的是，float占用四个字节，double占八个字节，即double表示比float精度更大的小数，根据具体场景进行选择。

3.字符串类型

说明：在生活中，除了数值类型之外，还包括字符串类型，例如人的姓名，性别等都是字符串类型，下面一起来看看在MySQL中是如何表示字符串类型的。

3.1char类型

语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

案例

mysql> create table t6(name char(2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t6 values ('ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 values ('中国');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t6 values ('中国人');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select* from t6;
+--------+
| name   |
+--------+
| ab     |
| 中国   |
+--------+
2 rows in set (0.00 sec)

说明：

char(2):表示可以存放两个字符，可以是字母或汉字，但是不能超过两个。

注：char类型字符长度最多只能是255个

mysql> create table tt10(id int ,name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use
BLOB or TEXT instead

3.2varchar类型

语法

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

案例：

mysql> create table t7(id int ,name varchar(6)); --表示这里可以存放6个字符
mysql> insert into t7 values(100, 'hello');
mysql> insert into t7 values(100, '我爱你，中国');
mysql> select * from t7;
+------+--------------------+
| id   | name               |
+------+--------------------+
|  100 | hello              |
|  100 | 我爱你，中国        |
+------+--------------------+

说明：
关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：
varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字
节数是65532。
当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占
用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符
占用2字节）。

mysql> create table t8(name varchar(21845))charset=utf8; --验证了utf8确实是不
能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. You have to change some columns to
TEXT or BLOBs
mysql> create table t8(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

了解了varchar类型之后，可能有小伙伴会问，为什么varchar类型会有1-3个字节用于记录数据的大小，下面关于这个问题通过char类型和varchar类型的对比进行解答。

3.3char和varchar的对比

既然char可以表示字符类型，为什么还要有varchar呢？它们之间有什么区别呢？

varchar被称为可变字符类型，因为varchar类型在存储数据的时候，不是根据指定的大小开辟空间，而是根据存储的数据的大小，动态开辟，所以也就解释了为什么会存在1-3个字节用来记录数据的大小了。

下面根据一张具体的表来对比一下char和varchar的区别：

了解了char和varchar的区别与联系之后，下面来看看什么时候应该选择char什么时候应该选择varchar:
如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费，但是效率高。
变长的磁盘空间比较节省，但是效率低。
定长的意义是，直接开辟好对应的空间
变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

3.4日期和时间类型

常用的日期有如下三个：

date :日期 'yyyy-mm-dd' ，占用三字节
datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节
timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用
四字节

案例：

mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into birthday values('1997-7-1','2008-8-8 12:1:1');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into birthday(t1,t2) values ('1997-7-1','2008-8-8 12:1:1');
Query OK, 1 row affected (0.01 sec)

mysql> select* from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2023-06-21 17:00:21 | #添加数据，时间戳自动补上当前时间
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update birthday set t1 = '2002-2-28';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select* from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2002-02-28 | 2008-08-08 12:01:01 | 2023-06-21 17:01:28 | 
#更新数据，时间戳会自动更新为当前时间
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

3.5enum和set

语法：
enum：枚举，“单选”类型；
enum('选项1','选项2','选项3',...);
该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型；
set('选项值1','选项值2','选项值3', ...);
该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，....最多64个。

上面就是关于enum和set的定义介绍，可能有小伙伴看完之后可能有点懵，上面说了什么？

不要着急，下面我们就通过具体的案例进行分析，介绍enum和set的特性：

案例：
有一个调查表votes，需要调查人的喜好，比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]

a.创建表：

mysql> create table votes(
    -> username varchar(30),
    -> gender enum('男','女'),
    -> hobby set('篮球','足球','写代码','跑步'));
Query OK, 0 rows affected (0.03 sec)

b.插入数据：

方式1：

按照set和enum中的值向表中插入数据：

ysql> insert into votes values('张三','男','篮球');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('李四','未知','篮球'); #不能插入枚举中以外的值
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into votes values('李四','女','篮球,写代码'); #set可以一次插入一个值，也可以同时插入多个值
Query OK, 1 row affected (0.01 sec)
mysql> select* from votes;
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 张三     | 男     | 篮球             |
| 李四     | 女     | 篮球,写代码      |
+----------+--------+------------------+

方式2：

按照“下标”的形式进行：

好奇的小伙伴可能会问，为什么下标打了双引号，因为这里的下标不是C/C++中对应的下标，对于enum类型，下标是指第一个值对应的下标为1，第二个值对应的下标是2，依次类推；对于set类型数值代表的是位图结构，bit位为1的时候，向表中插入对应的值

mysql> desc votes;
+----------+---------------------------------------------+------+-----+---------+-------+
| Field    | Type                                        | Null | Key | Default | Extra |
+----------+---------------------------------------------+------+-----+---------+-------+
| username | varchar(30)                                 | YES  |     | NULL    |       |
| gender   | enum('男','女')                             | YES  |     | NULL    |       |
| hobby    | set('篮球','足球','写代码','跑步')            | YES  |     | NULL    |       |
+----------+---------------------------------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> insert into votes values('王五','1','5'); #位图：101 对应->篮球和写代码
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes values('赵六','2','3'); #位图：11  对应->篮球和足球
Query OK, 1 row affected (0.01 sec)

mysql> select* from votes;
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 张三     | 男     | 篮球             |
| 李四     | 女     | 篮球,写代码      |
| 王五     | 男     | 篮球,写代码      |
| 赵六     | 女     | 篮球,足球        |
+----------+--------+------------------+
4 rows in set (0.00 sec)

注：set插入数据的时候允许为空

mysql> insert into votes values('赵六','1','');
Query OK, 1 row affected (0.01 sec)

mysql> select* from votes;
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 张三     | 男     | 篮球             |
| 李四     | 女     | 篮球,写代码      |
| 王五     | 男     | 篮球,写代码      |
| 赵六     | 女     | 篮球,足球        |
| 赵六     | 男     |                  |
+----------+--------+------------------+
5 rows in set (0.00 sec)

上面介绍了enum和set插入数据的方式，如果想要查找enum和set中的值的时候该如何查找呢？

从enum中查找数据，因为enum插入数据的特点是多个数据中插入其中的唯一一个值，所以在查找的时候可以采用严格匹配的方式，使用where子句进行匹配查找：

mysql> select* from votes where gender='男';
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 张三     | 男     | 篮球             |
| 王五     | 男     | 篮球,写代码      |
| 赵六     | 男     |                  |
+----------+--------+------------------+
3 rows in set (0.01 sec)

从set中查找数据，因为set插入数据的特点是是多个数据插入其中的一个值或插入多个值，所以想要查找包含某个值的字段时不能进行严格匹配查找：

解决方式：find_in_set函数进行查找

函数说明：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；
str_list 用逗号分隔的字符串。

案例：查找爱好中包含篮球的数据

mysql> select* from votes where find_in_set('篮球',hobby);
+----------+--------+------------------+
| username | gender | hobby            |
+----------+--------+------------------+
| 张三     | 男     | 篮球             |
| 李四     | 女     | 篮球,写代码      |
| 王五     | 男     | 篮球,写代码      |
| 赵六     | 女     | 篮球,足球        |
+----------+--------+------------------+