MySQL常见数据类型知识点举例详解_Mysql_、刘德华、刘亦菲、谌洪

1.整数类型

整数类型默认都是有符号整数

类型名称	字节数	类型说明
tinyint	1	带符号的范围-128127，无符号范围0255
smallint	2	带符号的范围-2^15 ~ 2^15 - 1，无符号 2^16 - 1
mediumint	3	带符号的范围-2^23 ~ 2^23 - 1，无符号 2^24 - 1
int	4	带符号的范围-2^31 ~ 2^31 - 1，无符号 2^32 - 1
bigint	8	带符号的范围-2^63 ~ 2^63 - 1，无符号 2^64 - 1

整数类型的可选属性有三个：

M : 表示显示宽度，M的取值范围是(0, 255)。例如，int(6)：当数据宽度小于6位的时候在数字前面需要用字符填满宽度。该项功能需要配合“ ZEROFILL ”使用，表示用“0”填满宽度，否则指定显示宽度无效。
UNSIGNED : 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。
ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。也就是说，int(M)，必须和UNSIGNED ZEROFILL一起使用才有意义。如果整数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充

使用示例：

创建test表

create table test(num int(6) zerofill);

插入数据：

insert into test values(22);

查看数据：

select num from test;

结果：

mysql> select num from test
    -> ;
+--------+
| num    |
+--------+
| 000022 |
+--------+
1 row in set (0.00 sec)

2.浮点数类型

浮点数和定点数类型的特点是可以处理小数，你可以把整数看成小数的一个特例,因此，浮点数和定点数的使用场景，比整数大多了。 MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL

类型名称	字节数	类型说明
float	4	表示单精度浮点数
double	8	表示双精度浮点数
real	8/4	默认就是 double。如果把 SQL 模式设定为启用“ REAL_AS_FLOAT ”，那么，MySQL 就认为real 是 float

使用示例：

语法：

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

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

#创建表
create table tt6(id int, salary float(4,2));
#插入数据
insert into tt6 values(100, -99.99);
insert into tt6 values(101, -99.991); #多的这一点被拿掉了
#查询数据
select id, salary from tt6;
#结果
+------+--------+
| id | salary |
+------+--------+
| 100 | -99.99 |
| 101 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

3.定点数类型

MySQL中的定点数类型只有 DECIMAL 一种类型。

语法：

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

decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99 decimal和float很像，但是有区别:float和decimal表示的精度不一样
0<=M<=65，0<=D<=30，D

DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。定点数在MySQL内部是以字符串的形式进行存储，这就决定了它一定是精准的。当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理

建议：如果希望小数的精度高，推荐使用decimal

4.bit类型

基本语法：

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

使用示例：

#创建表
create table tt4(id int, a bit(8));
#插入数据
insert into tt4 values(10, 10);
#查询数据
select * from tt4; 
#结果
+------+------+
| id | a 	  |
+------+------+
| 10 | 		  |
+------+------+
1 row in set (0.00 sec)
#发现很怪异的现象，a的数据10没有出现

bit使用的注意事项：

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

#插入数据
insert into tt4 values(65, 65);
#查询结果
select * from tt4;
+------+------+
| id | a      |
+------+------+
| 10 |    	  |
| 65 | A 	  |
+------+------+

5.字符串类型

5.1char和varchar类型

类型名称	类型说明
char	固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255
varchar	可变长度字符串，L表示字符长度，最大长度65535个字节

char语法：

char(L)

char使用示例：

#创建表
create table tt9(id int, name char(2));
#插入数据
insert into tt9 values(100, 'ab');
insert into tt9 values(101, '中国');
#查询结果
mysql> select * from tt9;
+------+--------+
| id | name 	|
+------+--------+
| 100 | ab 		|
| 101 | 中国 	   |
+------+--------+

说明：

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

varchar语法：

varchar(L)

varchar使用示例：

#创建表
create table tt10(id int ,name varchar(6)); #表示这里可以存放6个字符
#插入数据
insert into tt10 values(100, 'hello');
insert into tt10 values(100, '我爱你，中国');
#查询结果
mysql> select * from tt10;
+------+--------------------+
| 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字节）

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

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

5.2日期类型和时间类型

常用的日期有如下三个：

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

使用示例：

#创建表
create table birthday (t1 date, t2 datetime, t3 timestamp);
#插入数据
insert into birthday(t1,t2,t3) values('1997-7-1','2024-12-1 12:1:1','2024-12-1 12:1:1');
#查询结果
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-01 | 2024-12-01 12:01:01 | 2024-12-01 12:01:01 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

TIMESTAMP类型通常占用4个字节的存储空间，而DATETIME类型则需要8个字节。TIMESTAMP类型的精度可以指定，范围从0到6，默认精度为6。这意味着TIMESTAMP可以存储到微秒级别的时间戳，而DATETIME类型则不能指定精度

5.3enum和set类型

类型名称	类型说明
enum：枚举	单选类型
set：集合	多选类型

ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。其所需要的存储空间由定义ENUM类型时指定的成员个数决定。当ENUM类型包含1～255个成员时，需要1个字节的存储空间；当ENUM类型包含256～65535个成员时，需要2个字节的存储空间；ENUM类型的成员个数的上限为65535个
SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64 。设置字段值时，可以取取值范围内的 0 个或多个值。当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的。SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同

使用示例：

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

#创建表

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

#插入数据

insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2);

#查询

select * from votes where gender=2;

#查询结果

+----------+---------------+--------+
| username | hobby 		   | gender |
+----------+---------------+--------+
| Juse 	   | 登山,武术       |女 	    |
+----------+---------------+--------+

假如表中有这些数据，想查找所有喜欢登山的人：

+-----------+---------------+--------+
| username  | hobby         | gender |
+-----------+---------------+--------+
| 雷锋       | 登山,武术      | 男     |
| Juse      | 登山,武术       | 女     |
| LiLei     | 登山           | 男     |
| LiLei     | 篮球           | 男     |
| HanMeiMei | 游泳           | 女     |
+-----------+---------------+--------+

使用如下查询语句：

select * from votes where hobby='登山';

结果：

+----------+--------+--------+
| username | hobby  | gender |
+----------+--------+--------+
| LiLei    | 登山    | 男     |
+----------+--------+--------+

可以发现并不能查询出所有喜欢登山的人

集合查询使用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 | hobby         | gender |
+----------+---------------+--------+
| 雷锋 	  | 登山,武术      |  男     |
| Juse     | 登山,武术       | 女     |
| LiLei    | 登山           | 男     |
+----------+---------------+--------+