【MySQL】常见数据类型

MySQL常见数据类型 1.整数类型2.浮点数类型3.定点数类型4.bit类型5.字符串类型 5.1char和varchar类型5.2日期类型和时间类型5.3enum和set类型 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<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

固定长度字符串，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 | 登山 | 男 | +----------+---------------+--------+

MySQL的常用数据类型，就分享到这里了，如有错误还望指出，886！！！