MySQL常见数据类型知识点举例详解

目录

• 1.整数类型
• 2.浮点数类型
• 3.定点数类型
• 4.bit类型
• 5.字符串类型
• 5.1char和varchar类型
• 5.2日期类型和时间类型
• 5.3enum和set类型
• 总结 

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[(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中的定点数类型只有 DECIWebNdYtySrMAL 一种类型。

语法：

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<M

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语法：

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类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选javascript取多个值。其所需要的存储空间由定义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 |
+-------android---+---------------+--------+
| 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    | 登山           | 男     |
+----------+---------------+--------+

总结 

到此这篇关于MySQL常见数据类型的文章就介绍到这了,更多相关MySQL常见数据类型内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！