【MySQL】MySQL数据类型

一、数据类型的分类

• 数值类型

二、tinyint类型

2.1 创建有符号数值

默认是有符号的。取值范围[-128,127]：

mysql> create table if not exists t1(-> num tinyint-> );

插入tinyint类型数值范围内的数据是可以的：

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

超出范围的数据会插入失败：

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

2.2 创建无符号数值

用tinyint unsigned数据类型创建表t2:

mysql> create table if not exists t2( num tinyint unsigned );
Query OK, 0 rows affected (0.02 sec)

我们知道tinyint unsigned类型的范围是【0 ~ 255】：

mysql> insert into t2 values (0);
Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values (255);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values (-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

如果我们向MySQL特定的类型中插入不合法的数据，MySQL一般都是直接拦截，不让我们做对应的操作！
反过来，我们如果已经有数据被插入到MySql中了，那么一定是合法的。
所以数据类型本身就是一种约束。

MySql表中建立属性列：列名称 类型在后

num tinyint unsigned;

三、bit类型

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

mysql> create table if not exists t3( -> num1 bit(1),-> num2 bit(64)-> );
Query OK, 0 rows affected (0.03 sec)

对于num1只能插入0或1。

mysql> insert into t3 (num1, num2) values (1, 256);
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 (num1, num2) values (0, 1024);
Query OK, 1 row affected (0.01 sec)

位类型显示的是ASCII码，如果想看到：可以使用十六进制打印。

select hex(num1),hex(num2) from t3;

验证一下：
如果插入’a’或者97:

mysql> insert into t3 (num1, num2) values(1, 97);
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 (num1, num2) values(1, 'a');
Query OK, 1 row affected (0.00 sec)

所以bit字段在显示时，是按照ASCII码对应的值显示。

三、浮点类型

3.1 float

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

例如：
float(5,2) 表示的范围是 -999.99 ~ 999.99
float(5,2) unsigned 表示的范围 0 ~ 999.99

创建表：

mysql> create table if not exists t5(-> num float(4, 3)-> );
Query OK, 0 rows affected (0.04 sec)

插入数据：

mysql> insert into t5 (num) values (9.999);
Query OK, 1 row affected (0.00 sec)mysql> insert into t5 values (-9.999);
Query OK, 1 row affected (0.00 sec)

插入的如果是 1.2345，会变成1.234（超过5进1）

mysql> insert into t5 values (1.2345);
Query OK, 1 row affected (0.01 sec)mysql> insert into t5 values (1.2346);
Query OK, 1 row affected (0.01 sec)

但是如果进位以后超过范围了就也无法插入成功。

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

如果是无符号浮点数就不能插入负数了，上限不变。

3.2 decimal类型

decimal(m, d) [unsigned] : M指定显示的总长度（整数+小数），d指定小数位数。

decimal和float很像，表示的数据范围一样，但是精度不一样。

创建表：

mysql> create table t7(-> num1 float(10, 8),-> num2 decimal(10, 8)-> );
Query OK, 0 rows affected (0.03 sec)

插入数据：

mysql> insert into t7 (num1, num2) values (10.0, 99.99);
Query OK, 1 row affected (0.01 sec)mysql> insert into t7 (num1, num2) values (23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

可以看到float类型存在精度丢失，decimal存什么就是什么。（精度高）

float在精度过大会做一些动作，而decimal不会。
float表示的精度大约是7位。decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。

建议：如果希望小数的精度高，推荐使用decimal或double而不是float。

四、字符串类型

4.1 char类型

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

创建表：

mysql> create table t6(-> num char(2)-> );
Query OK, 0 rows affected (0.06 sec)

插入数据：

mysql> insert into t6 values ('a');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('ab');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('abc');
ERROR 1406 (22001): Data too long for column 'num' at row 1

如果插入汉字：对于gbk编码一个占用2个字节,utf8编码一个汉字占用3个字节

mysql> insert into t6 values ('哈');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('哈哈');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('哈哈哈');
ERROR 1406 (22001): Data too long for column 'num' at row 1

mysql的字符代表的是符号，所以能够插入成功。

4.2 varchar类型

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

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

创建表：

mysql> create table t4(-> id int,-> name varchar(6)-> );
Query OK, 0 rows affected (0.03 sec)

插入数据跟char类型是一样的。

char和varchar的区别：

char是固定长度字符串，类似C/C++中的数组的概念，例如char(6)表示开辟6个字符的大小的空间，没用完就是浪费。
varchar是可变长度的字符串，例如varchar(6)表示最大长度为6个字符，如果用户仅使用了一个字符，那么varchar只会分配出一个字符的空间+额外1个字节（记录数据大小）用于存储。

五、日期和时间类型

常用的日期有如下三个：

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 t8(-> t1 date,-> t2 datetime,-> t3 timestamp-> );
Query OK, 0 rows affected (0.04 sec)

对于时间戳timestamp:创建表结构，插入数据是tinmestamp会自动更新。所以不需要更改

mysql> insert into t8 (t1, t2) values ('2002-07-31', '2023-07-31 08:00:00');
Query OK, 1 row affected (0.00 sec)

此时更新t1:

mysql> update t8 set t1='2003-07-31';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t8;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2003-07-31 | 2023-07-31 08:00:00 | 2023-08-03 17:17:45 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

t1和t3都会被更新，这也说明了时间戳timestamp会自动更新,意义在于记住时间的更新。

六、枚举和集合类型

6.1 enum的枚举值和set的位图结构

创建带有enum和set类型的表votes：

mysql> create table if not exists votes(-> username varchar(10),-> gander enum('男', '女'),-> hobby set('篮球', '足球', '跑步')-> );

插入数据：

mysql> insert into votes values ('张三', '男', '跑步');
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values ('李四', '女', '篮球');
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values ('赵五', 'a', '篮球');
ERROR 1265 (01000): Data truncated for column 'gander' at row 1

可以看到对于gander我们只能插入男或者女，不能插入其他字符。
但是可以插入1和2，也就是说枚举类型可以写常量也可以写常量的下标。

对于set也可输入数字插入，数字代表位图：

mysql> insert into votes values ('赵五', '男', '羽毛球');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1mysql> insert into votes values ('赵五', '男', '跑步,篮球')
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values ('赵五', '男', '跑步,篮球,足球');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes (username) values ('小明');
Query OK, 1 row affected (0.02 sec)mysql> insert into votes values ('小明', 1, 0);
Query OK, 1 row affected (0.00 sec)mysql> insert into votes values ('小明', 1, 1);
Query OK, 1 row affected (0.00 sec)

NULL表示什么都没有，而' '表示有东西但是是一个空串。

当然set是一个位图结构，如果是3，那么就是1 | 2（篮球，足球）。

enum：在所有枚举中选择一种进行插入。也可以直接使用枚举值，枚举值从1开始。
set：在集合中选择一种或多种存在的选项进行数据的插入。也可使用位图的形式进行数据插入。

6.2 查询集合find_in_set

如果我们想要查询爱好是篮球的：

mysql> select * from votes where hobby='篮球';
+----------+--------+--------+
| username | gander | hobby  |
+----------+--------+--------+
| 李四     | 女     | 篮球   |
| 小明     | 男     | 篮球   |
+----------+--------+--------+
2 rows in set (0.00 sec)

可以看到是精确筛选
下面介绍一个函数find_in_set：

mysql> select find_in_set('a', 'a,b,c');
+---------------------------+
| find_in_set('a', 'a,b,c') |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

这里的1表示true，说明’a’在集合’a,b,c’中。

这里只能查找一个元素是否在对应的集合中。

mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)mysql> select find_in_set('a,b', 'a,b,c');
+-----------------------------+
| find_in_set('a,b', 'a,b,c') |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

当一个要查找的字段位于集合中时，find_in_set函数会返回其下标（非0），反之返回0。所以我们就可以在集合中筛选出爱好带有“篮球”的人。

mysql> select * from votes where find_in_set ('篮球', hobby);
+----------+--------+----------------------+
| username | gander | hobby                |
+----------+--------+----------------------+
| 李四     | 女     | 篮球                 |
| 赵五     | 男     | 篮球,跑步            |
| 赵五     | 男     | 篮球,足球,跑步       |
| 小明     | 男     | 篮球                 |
| 小明     | 男     | 篮球,足球            |
+----------+--------+----------------------+
5 rows in set (0.00 sec)

也可以在集合中筛选出爱好既有“篮球”，又有“足球”人。使用and逻辑与进行筛选：

mysql> select * from votes where find_in_set ('篮球', hobby) and find_in_set('足球', hobby); 
+----------+--------+----------------------+
| username | gander | hobby                |
+----------+--------+----------------------+
| 赵五     | 男     | 篮球,足球,跑步       |
| 小明     | 男     | 篮球,足球            |
+----------+--------+----------------------+
2 rows in set (0.00 sec)