MySQL数据类型总结与使用

一、数据类型概述

二、tinyint

我们使用tinyint来验证一下mysql对数值大小的限制：

mysql> create database db charset=utf8 collate utf8_general_ci;//创建数据库db
mysql> create table t1(
    -> num tinyint
    -> );//创建表t1
mysql> insert into t1 (num) values(100);//成功
mysql> insert into t1 (num) values(127);//成功
mysql> insert into t1 (num) values(-100);//成功
mysql> insert into t1 (num) values(-128);//成功
mysql> insert into t1 (num) values(128);//失败，因为超出了范围
mysql> insert into t1 (num) values('1234');//失败，因为类型不匹配
1
2
3
4
5
6
7
8
9
10

但是在C语言中，char=1234就不会报错。我们可以得出一个初步结论：
如果mysql有不符合要走的插入操作，mysql就会被终止，即所谓的"约束"，通过对数据类型和大小来约束插入合法数据。mysql的约束很强，因为它就是用来管理数据的。

三、bit

bit(M)，M的范围为1~64，如果没加M，则默认为64。M表示的是比特位个数，但我们插入的是一个整数。当我们插入的整数转换为二进制的位数大于M时就会发生插入错误。

mysql> create table t2(
    -> a bit(1)
    -> );//创建一个包含bit的表
mysql> insert into t2 (a) values(0);//成功
mysql> insert into t2 (a) values(1);//成功
mysql> insert into t2 (a) values(2);//2表示为10需要两个比特位，因此插入失败
mysql> select* from t2;//显示表
+------+
| a    |
+------+
|      |
|     |
+------+
1
2
3
4
5
6
7
8
9
10
11
12
13

当我们查看表的时候，发现里面啥也没有，但不是已经插入0和1了吗？
这是因为bit类型在mysql中显示是以ascii码的值来显示的，0和1在ascii表中对应的内容不可显示。
我们可以创建一个大点的bit的表，然后插入65这个值，此时显示的就是a了。

四、float和decimal

1.float

float(M,D)，其中M表示的是整个数据的最多数字个数，D表示的是最多小数数字个数。注意，float会进行四舍五入的。

mysql> create table t4(
    -> num float(4,2)//四位数字，小数两位
    -> );
mysql> insert into t4 (num) values(99.99);//成功
mysql> insert into t4 (num) values(99.999);//失败，小数有三位
mysql> insert into t4 (num) values(-99.99);//成功
mysql> insert into t4 (num) values(-99.995);//失败，四舍五入后多一位
mysql> insert into t4 (num) values(97.999);//成功，四舍五入后98.00
mysql> insert into t4 (num) values(-99.994);//成功，四舍五入
mysql> insert into t4 (num) values(99.9);//成功99.90
1
2
3
4
5
6
7
8
9
10

2.decimal

decimal与float的用法一模一样，但是比float更加精确：

mysql> create table t5(
    -> num1 float(10,8),
    -> num2 decimal(10,8)
    -> );
mysql> insert into t5 (num1,num2) values(23.12345612,23.12345612);
1
2
3
4
5

此时再查看表中内容会发现，float的精度比decimal低：

五、char与varchar

1.char

char(L)：固定字符串，L是可以存储的长度，单位为字符。
注意对字符的理解，它可以是一个字母，也可以是一个汉字：

mysql> create table t6(
    -> a char(2)
    -> );
mysql> insert into t6 (a) values('ab');//成功，2个字符
mysql> insert into t6 (a) values('abc');//失败，三个字符
mysql> insert into t6 (a) values('中国');//成功，2个字符
mysql> insert into t6 (a) values('中国人');//失败，三个字符
1
2
3
4
5
6
7

2.varchar

varchar(L)：用法和char相同，是一个可变长度字符串，L表示的是字符长度，最大长度可以为65535字节。

mysql> create table t7(
    -> varchar(5)
    -> );
mysql> insert into t7 (a) values('abcde');//成功
mysql> insert into t7 (a) values('abcdef');//失败，6个字符
mysql> insert into t7 (a) values('我是中国人');//成功
mysql> insert into t7 (a) values(('我不是中国人');//失败
1
2
3
4
5
6
7

varchar的L到底有多大，是和表的编码密切相关的：

• varchar的长度可以指定为0-65535之间的值，但是有1-3个字节用于记录数据的大小，所以有效字节数是65532。
• 当我们的表的编码为utf8时，varchar(n)最大值为65532/3=21844[因为utf中，一个字符占用3个字节]，如果是gbk，varchar(n)的参数n的最大值时65532/2=32766(在gbk中，一个字符占用两个字节)。

3.char和varchar的区别

char与varchar的区别表现在开辟的空间上，char(N)表示直接开辟N个字符的空间，varchar(N)表示用多少开多少，但是不能超过N。
有点像数组和malloc的区别。默认在utf8中，一个字符3个字节。

六、日期和时间类型

date：年月日
datetime：年月日 时分秒
timestamp：时间戳，从1970年开始

mysql> create table t8( t1 date, t2 datetime, t3 timestamp );
1

我们可以创建一个类型然后查看一下：

发现时间戳已经有一个默认值了，这个默认值为系统的默认时间，每一次对这一行数据进行更改的时候，如果不改变时间戳，它会自动同步系统的时间。

mysql> insert into t8 (t1,t2) values('1991-1-1','2001-1-1 12:1:1');
1

由于时间戳有默认值了，因此我们可以不对其进行插入，可以查看一下：

如果我们更新这一行的时间数据，时间戳也会被自动更新：

mysql> update t8 set t1='2022-9-26';
1

七、enum和set

1.enum

enum表示一个枚举类型，该设定提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际上存储的是"数学"，因为这些选项的每一个选项值对应如下数字：1,2,3,4…等最多65535个。当我们添加枚举值时，也可以添加对应的数字编号。

mysql> create table t9(
    -> votes enum('男','女','未知')
    -> );
mysql> insert into t9 (votes) values('男');//成功
mysql> insert into t9 (votes) values('女');//成功
mysql> insert into t9 (votes) values('男','女');//失败，不能这么搞
mysql> insert into t9 (votes) values(3);//插入的是'未知'
mysql> insert into t9 (votes) values(0);//失败，0没有意义
1
2
3
4
5
6
7
8

2.set

但是有一些场景，需要插入多个元素的时候，就不能使用enum了，而是要使用set。

mysql> create table t10(
    -> hobby set('打球','游泳','看书','揍你')
    -> );
mysql> insert into t10 (hobby) values('打球','看书');//失败，格式错误
mysql> insert into t10 (hobby) values('打球,看书');
mysql> insert into t10 (hobby) values('打球,看书,揍你');//成功
1
2
3
4
5
6

如果使用普通的查找，比如要查找看书的爱好

mysql> select * from t10 where hobby='看书';
1

此时只能看到只有看书的那个选项。
我们希望将带有看书的都找出来：
此时需要使用一个名为find_in_set的函数：

mysql> select * from t10 where find_in_set('看书',hobby);
1

此时就将所有看书的都找出来了。

八、总结

数据类型本质就是一种约束，一旦不满足条件，直接进行终止，给表中的数据提供尽可能很高的确定性，MySQL对自己的数据完整性和一致性要求非常的高，因此对用户进行了强约束。