【MySql】MySql的数据类型

文章目录

数据类型分类

对于数据类型分类，这里简单分为数值类型（如BIT,BOOL,INT），文本、二进制类型（如CHAR,VARCHAR），时间日期（DATE），String类型（如ENUM类型），这里简单了解一下即可

数值类型

不同的类型所占字节数是不一样的。如TINYINT所占字节数为1，而INT所占字节数为4.同时数值范围也是有区别的，这些我们都需要去注意。

tinyint类型

用tinyint数据类型创建表t1:

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 (0);
Query OK, 1 row affected (0.00 sec)

超出范围就不可以了：

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

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

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

此时默认是无符号的unsigned:范围是0-255：插入数值超过范围就会直接ERROR

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

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

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

对于MySql，如果我们向mysql特定的类型中插入不合法的数据，MySql一般都是直接拦截，不然我们做对应的操作！不会截断数据。

反过来，我们如果已经有数据被插入到MySql中了，一定是插入的时候是合法的

**所以MySql中一般而言，数据类型本身也是一种

约束

，约束程序员尽可能进行正确的插入，约束使用者，如果你不是一个很好的使用者，MySql也能保证数据插入的合法性。就能保证数据库中的数据是可预期，完整的**

MySql表中建立属性列：列名称 类型在后，如num tinyint unsigned;

这是tinyint类型我们所需要注意的，同时，尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

bit类型

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

注意数值范围，超出了会ERROR：

mysql> create table if not exists t4(
    -> id int,
    -> online bit(65)
    -> );
ERROR 1439 (42000): Display width out of range for column 'online' (max = 64)
mysql> 

创建含有bit数据类型的表t3:

mysql> create table if not exists t3(
    -> id int,
    -> online bit(1)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> 

此时只有一个比特位，只能插入0或者插入1,：

此时查看表t3:

我们发现online是位类型的，并没有显示，我们可以理解为当前online的按照ASCII值显示，如果想看到：在插入一条数据：

mysql> insert into t3 (id,online) values (123,0);
Query OK, 1 row affected (0.01 sec)

**select id,hex(online) from t3;**这时候就显示的看到0和1了

如果插入’a’或者97:

mysql> insert into t3 (id,online) values (123,'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 (id,online) values (123,97);
Query OK, 1 row affected (0.01 sec)

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

小数类型

float

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

举个例子:

mysql> create table if not exists t5 (
    -> id int,
    -> salary float(4,2)
    -> );
Query OK, 0 rows affected (0.02 sec)

此时要求浮点数小数位数是两位:

mysql> insert into t5 (id,salary) values (1,99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (id,salary) values (1,-99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 (id,salary) values (1,-10.0);
Query OK, 1 row affected (0.00 sec)

虽然插入的是-10.0,但是mysql插入的是-10.00，遵守小数是两位的规则。

mysql> insert into t5 (id,salary) values (1,23.456);
Query OK, 1 row affected (0.00 sec)

插入的如果是，23.456，会变成23.46（采用四舍五入）

插入的如果是，99.993是可以的，而如果是99.995那就不行了：(这个也可以理解，四舍五入之后不在合法)

mysql> insert into t5 (id,salary) values (1,99.993);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 (id,salary) values (1,99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> 

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

现在，我们来看看无符号的浮点数：

mysql> create table if not exists t6(
    -> id bigint,
    -> salary float(4,2) unsigned
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t6 (id,salary) values (1,99.99);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t6 (id,salary) values (1,99.996);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 (id,salary) values (1,-99.99);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 (id,salary) values (1,-0.01);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> 

负数此时是插入不了的了。

decimal

decimal也是mysql中的浮点数类型，float存储数据时，小数比较大，或者小数位点比较多存储的就不太准确了,这与浮点数存储规则有关（mysql中的float浮点数精度为是7）。

mysql> create table if not exists t7 (
    -> f1 float(10,8),
    -> f2 decimal (4,2)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql中的float浮点数精度为是7,如果我们设置为8会不会起效果呢：

mysql> insert into t7 (f1,f2) values (10.0,99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t7 (f1,f2) values (10.0,99.999);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1

mysql> insert into t7 (f1,f2) values (10.0,23.935);
Query OK, 1 row affected, 1 warning (0.01 sec)

进行修改:

mysql> alter table t7 modify f2 decimal(10,8);
Query OK, 2 rows affected (0.30 sec)
Records: 2  Duplicates: 0  Warnings: 0

同样插入相同的数据：

虽然float设置的是8位精度，但是此时存储的已经与插入的差别比较大了，而decimal插入什么就存储什么

float在精度过大会做一些动作，而decimal不会。

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

字符串类型

char

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

mysql> create table if not exists t8(
    -> id int,
    -> name char(2)
    -> );
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into t8 (id,name) values (1,'中');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 (id,name) values (1,'中国');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 (id,name) values (1,'中国人');
ERROR 1406 (22001): Data too long for column 'name' at row 1

而我们这里插入2个汉字能够成功，这说明对于char单位为字符，mysql的字符与C/C++语言不同，mysql的字符代表的是符号，所以能够插入成功！

总结就是char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255

mysql> create talbe if not exists t8 (
    -> id int,
    -> address char(256)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'talbe if not exists t8 (
id int,
address char(256)
)' at line 1
mysql> 

varchar

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节（mysql存储char类型，utf8默认是3字节，65535/3 = 21845）

举个例子:

mysql> create table if not exists t9(
    -> id int,
    -> name varchar(6)
    -> );
Query OK, 0 rows affected (0.02 sec)

关于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字节）

• char和varchar比较

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

日期和时间类型

常用的日期有如下三个：

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 if not exists t11 (
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp
    -> );
Query OK, 0 rows affected (0.02 sec)

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

mysql> insert into t11 (t1,t2) values ('2000-10-01','1949-10-01 08:00:00');
Query OK, 1 row affected (0.00 sec)

此时更新t1:

mysql> update t11 set t1='1999-01-01';
Query OK, 1 row affected (0.01 sec)

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

而date的意义在于记录某一天即可。结合具体的场景即可。

enum和set

语法：

• enum：枚举，“单选”类型； enum(‘选项1’,‘选项2’,‘选项3’,…);

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

• set：集合，“多选”类型； set(‘选项值1’,‘选项值2’,‘选项值3’, …);

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

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

举个例子👇

mysql> create table if not exists votes (
    -> username varchar(30),
    -> gender enum('男','女'),
    -> hobby set('代码','乒乓球','羽毛球','足球')
    -> );
Query OK, 0 rows affected (0.03 sec)

现在插入数据:

mysql> insert into votes values('李四','','代码');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into votes values('李四','女','代码');
Query OK, 1 row affected (0.00 sec)

mysql> insert into votes values('王五','unknow','代码');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

性别只能插入男或女，枚举类型提供了类型约束，只能插入枚举的类型。插入常量的下标也是可以的：下标数字从1开始，代表第一个

mysql> insert into votes values('坤坤',1,'代码');
Query OK, 1 row affected (0.01 sec)

当然，也可以插入多个hobby:

mysql> insert into votes values ('招八',1,'乒乓球,足球,羽毛球');
Query OK, 1 row affected (0.01 sec)

插入的hobby不能在集合中不存在！如果是多个用逗号分开即可。

可以插入NULL的集合

mysql> insert into votes (username) values ('曹操');
Query OK, 1 row affected (0.00 sec)

插入set下标为0:

mysql> insert into votes value ('流星',1,0);
Query OK, 1 row affected (0.00 sec)

NULL 与’ ‘是不一样的，没有是NULL，’ '是有，只不过是空串。

对于hobby这个set集合，数字代表的不是下标，对应的是

对于插入1:00000---------->00001，代表代码这个hobby

对于插入3:00000----------->00011,代表的是代码和乒乓球这两个hobby

对于插入7:00000------------>001111,代表的是代码乒乓球羽毛球这三个hobby

代表的是位图，从左到右。

• 进行查找

枚举进行查找：

mysql> select * from votes where gender='男';

mysql> select * from votes where gender=2;

当然用数字查找也是可以的：

set进行查找:

mysql> select * from votes where hobby='羽毛球';

但是有的人是有多种爱好的，包括了羽毛球，却没有显示出来，只是显示了爱好只有羽毛球的:

所以mysql提供了find_in_set函数：

mysql> select * from votes where find_in_set('羽毛球',hobby);

如果想查找hobby有足球和代码的呢：

mysql> select * from votes where find_in_set('代码',hobby) and find_in_set('足球',hobby);

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

举个例子:

find_in_set的作用是查对应的一个元素是否在一个集合里面。0表示假，非0表示真

如上就是在集合中的查找