【MySQL篇】数据类型

前言：

MySQL 是一个流行的开源关系型数据库管理系统。它支持多种数据类型，用于存储和管理数据。 MySQL提供了多种数据类型，合理选择数据类型对存储效率，查询性能和数据 准确性至关重要，本篇对MySQL 支持的主要数据类型分类及详细说明。

1，数据类型的分类

2 ，数值类型

2.1 tinyint类型

tinyint大小为一个字节，无符号数的取值范围在0~255，有符号数的取值范围在-128~127。

使用示例：

• 创建一个包含tinyint数据类型的表

create table if not exists t1( num tinyint);

• 向t1中插入数据

mysql> insert into t1 values(1); 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); Query OK, 1 row affected (0.00 sec)

• 打印t1表中的信息

select * from t1;

• 插入超出范围的数字

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>

将tiny设置为无符号类型，此时的数据范围就是0~255。

create table if not exists t2( num tinyint unsigned);

插入数据：

总结：

MySQL中，整型可以指定符号的，默认是有符号的，通过unsigned可以来标识字段类型为无符号类型。 MySQL也不支持 插入超出数据类型范围的数，否则直接拦截。所以 可以保证插入的时候数据一定是合法的。在MySQL中，数据类型本身就是一种约束，所以无论使用者是否可以熟练的使用MySQL，这种约束都可以很好的保证数据是可预期的，完整的，合法的。

2.2 bit类型

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

• 创建一个包含bit类型的数据表并插入数据

create table if not exists t3(id int,num bit);

insert into t3 values(1,0); insert into t3 values(2,1); insert into t3 values(3,2);//err，默认只能插入0或1

• 创建一个大小为8的bit类型数据表

create table if not exists t4( id int,num bit(8));

insert into t4 values(1,25); insert into t4 values(2,100); insert into t4 values(2,255); insert into t4 values(2,256);//err，同样不能越界

2.3 小数类型

2.3.1 float类型

float[(m,d)][unsigned]：m指定显示长度，d指定小数位数，占4个字节。

• 创建一个长度为4，小数部分长度为2的小数的数据表

create table if not exists t5(id int,num float(4,2));

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

• 插入数据

insert into t5 values(1,-99.99); insert into t5 values(2,0); insert into t5 values(3,99.99); insert into t5 values(3,99.991); insert into t5 values(3,59.885); insert into t5 values(3,100.00);//err insert into t5 values(3,59.886);

同理，如果将float定义为unsigned无符号类型，那么float(4,2)的取值范围时0~99.99.

2.3.2 decimal类型

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

• decimal(5,2)表示 的数据范围：-999.99~999.99
• decimal(5,2) unsigned表示的数据范围：0~999.99
• float和decimal很像，但是有区别，表示的精度不一样。

创建一个数据表，长度为10，小数部分为8，比较float和decimal。

create table if not exists t6( num1 float(10,8), num2 decimaimal(10,8) );

insert into t6 values (23.12345612,23.12345612); insert into t6 values (12.12345678,12.12345678);

 我们看到在数据一样的情况下，decimal的小数精度高于float。float表示的精度大约是7位。decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0，如果m被省略，默认是10。

3，字符串类型

3.1 char

char[(L)]：固定长度字符串，L是可以存储的长度，单位为字符，最大可以存储255个字符 。

• 创建包含长度为2的字符串的数据表

create table if not exists t7(id int,name char(2));

• 插入数据

insert into t7 values(1,'张三'); insert into t7 values(2,'李四'); insert into t7 values(3,'王五');

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

3.2 varchar

varchar[(L)]：可变长度字符，L表示字符长度，最大长度为65535个字节。

• 创建一个长度为6的字符串的数据表

create table if not exists t8(id int,name varchar(6));

varchar(6)表示可以存储6给字符。

• 插入数据

insert into t8 values(100,'hello'); insert into t8 values(100,'我爱你，中国'); insert into t8 values(100,'我爱敲代码！！');//err

关于varchar(len)，len到底是多大，和表的编码密切相关。

• varchar长度可以指定为0~65535之间的值，但是有1~3个字节用来存储数据的大小，所以说有效字节数是65532。
• 当我们表的编码是utf8时，varchar(n)的参数n的最大值是65532/3=21844（因为utf8中，一个字符占3个字节），如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占个字节）。

3.3 char与varchar的比较

如何选择定长或变长字符串：

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

3.4日期和时间类型

常见的日期有如下三个：

• date：日期'yyyy-mm-dd'，占用3个字节
• datetime：时间日期格式'yyyy-mm-dd HH:ii:ss'，表示范围从1000到9999，占用8个字节。
• timestamp：时间戳，从1970年开始的yyyy-mm-dd HH:ii:ss格式和datetime完全一致，占用4个字节。表示现在 的时间。
• 创建带时间的数据表

create table if not exists birthday(t1 date,t2 datetime,t3 timestamp);

• 插入数据

insert into birthday (t1,t2) values ('1997-7-1','2008-8-8 12:1:1');

3.5 enum和set

enum：枚举，单选类型

enum('选项1'，'选项2'，'选项3'，...）

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

set：集合，多选类型

set(’选项值1‘，‘选项值2’，‘选项值3’,...)

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

• 创建一个含有set的数据表

create table votes(username varchar(30), hobby set('登山','游泳','篮球','武术'),gender enum('男','女'));

• 插入数据

insert into votes values ('雷锋','登山,武术','男'); insert into votes values ('张三','登山,武术','男'); insert into votes values ('李四','游泳','女'); insert into votes values ('赵六','篮球','2'); insert into votes values ('王五','足球','男');//err

集合查询使用find_in_set函数

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；

str_list 用逗号分隔的字符的字符串。

select * from votes where find_in_set('登山',hobby);