mysql--数据类型

目录

搞定所有数据类型

一、常见数据类型分类

二、数值类型

1、bit类型

2、float类型

​编辑3、decimal类型

4、字符类型

（1）char

（2）varchar

（3）varchar和char有甚区别？

（4）char varchar如何选择？

5、日期和时间类型

​编辑

6、Enum 和 set类型

比特位存储

7、Enum 和 set类型查找

find_in_set(xxx，filed_name)函数

---

一、常见数据类型分类

分类	数据类型	描述	示例
数值类型	tinyint	1 字节，范围从 -128 到 127	tinyint(3)
	smallint	2 字节，范围从 -32,768 到 32,767	smallint
	mediumint	3 字节，范围从 -8,388,608 到 8,388,607	mediumint
	int / integer	4 字节，范围从 -2,147,483,648 到 2,147,483,647	int
	bigint	8 字节，范围从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807	bigint
	float	4 字节，单精度浮点数	float(7,4)
	double	8 字节，双精度浮点数	double
	decimal / numeric	精确数值，可以指定精度和小数位数	decimal(10,2)
日期和时间类型	date	日期，格式为 'yyyy-mm-dd'	date
	datetime	日期和时间，格式为 'yyyy-mm-dd hh:mm:ss'	datetime
	timestamp	时间戳，自动初始化和更新	timestamp
	time	时间，格式为 'hh:mm:ss'	time
	year	年，格式为 'yyyy'	year(4)
字符串类型	char	固定长度字符串，最大 255 字节	char(10)
	varchar	可变长度字符串，最大 65,535 字节	varchar(255)
	tinytext	最多 255 字节的文本	tinytext
	text	最多 65,535 字节的文本	text
	mediumtext	最多 16,777,215 字节的文本	mediumtext
	longtext	最多 4,294,967,295 字节的文本	longtext
	binary	固定长度的二进制数据	binary(10)
	varbinary	可变长度的二进制数据	varbinary(255)
	tinyblob	最多 255 字节的二进制大对象	tinyblob
	blob	最多 65,535 字节的二进制大对象	blob
	mediumblob	最多 16,777,215 字节的二进制大对象	mediumblob
	longblob	最多 4,294,967,295 字节的二进制大对象	longblob
json类型	json	存储 json 格式的数据	json

二、数值类型

以tinyint为例

只有数据合法才允许插入，否则不给插入
保证了整体数据库的规范性
这个叫做约束，整个约束反过来倒逼程序员要正确的输入数据

1、bit类型

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

如果是1位：范围是0-1
如果是2位：范围是0-3
如果是3位：范围是0-5
以此类推，其实就是二进制
超过位数的值不能被插入

示例：

create table table_name (
id bit(2)
);

2、float类型

float(m,d) [unsigned]

m：指定显示长度（超过显示长度，四舍五入）
d：小数位数
unsigned：将负数部分砍掉
小数点精度最大是7位

create table table_name (
id decimal(4,2)
);

例如：float(4,2)表示的范围是-99.99 - 99.99

3、decimal类型

decimal (m,d) [unsigned];

和float一样，但精度更高
float精度7位；decimal整数56位，小数30位

如果d被省略，默认为0；m被省略，默认为10

区别：对更小的数据，float可能会修改部分数据；但decimal不会，而保持原值

4、字符类型

（1）char

char（L）：固定长度字符串，L是可以存储的长度，单位是字符，最大是255个字符
mysql中的字符，对应一个符号，而非字节，字符可是字母 / 汉字
‘ab’是两个字符；‘你好’是两个字符；‘45’也是两个字符

create table table_name(
name char(4)
);

（2）varchar

varchar（L）：变长字符串，L表示长度，最大长度65535字节

utf标准3个1字节表示一个字符，因此65535/3=21845个字符
gbk标准2个字节表示一个字符
同时，在varchar中，会有一个1-3个字节记录字符串的长度
因此，其实utf标准下，最大长度其实是21844

create table table_name (
name varchar(32)
);

（3）varchar和char有甚区别？

varchar（L）中的L表示上限，
而char（L）中的L表示固定长度
举个例子：

varchar(6)
如果数据是 'a' ，数据长度为1
如果数据是 'ab'，数据长度为2
如果数据为 'abc'，数据长度为3
....
用多少，分配多少，但不能超过6

而char(6):
如果数据是 ‘a’，数据长度为6
如果数据是‘ab’，数据长度为6
...
即大小固定

（4）char varchar如何选择？

如果数据确定长度都一样，用char，例如身份证、学号、身高、体重
（应该没有人的身高突破4位数吧？比如1700cm？）
如果数据有变化，用varchar，例如路程、地址
定长的磁盘空间比较浪费，但是效率高
变长的磁盘空间比较节省，但是效率低

5、日期和时间类型

常用三种日期类型：date、datetime、timestamp

据类型	存储大小	描述
date	3 字节	存储日期，格式为 YYYY-MM-DD，范围：1000-01-01 到 9999-12-31。
datetime	8 字节	存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS，范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59。
timestamp	4 字节	存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS，范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。
time	3 字节	存储时间，格式为 HH:MM:SS，范围：'-838:59:59' 到 '838:59:59'。
year	1 字节	存储年份，格式为 YYYY，范围：1901 到 2155。

 示例
创建一个时间类型表：

create table time_table(
d1 date,
d2 datetime,
d3 timestamp,
d4 time,
d5 year
);

插入时间：

insert into time_table(d1, d2, d3, d4, d5) values(
'2024-10-14','2024-10-14 15:30:00',current_timestamp, 
'12:30:00', 2024
);

6、Enum 和 set类型

特性	enum	set
定义	用于存储单一值，值从预定义的字符串集合中选择一个	用于存储多个值，值从预定义的字符串集合中选择多个
语法	enum('value1', 'value2', ...)	set('value1', 'value2', ...)
值的数量	只能有一个值，最大值为 65535	可以有多个值，最大值为 64
存储	占用一个或两个字节	占用一个或多个字节，具体取决于存储的值的数量
使用场景	适合用于选择单一选项，如性别、状态等	适合用于选择多个选项，如兴趣、标签等
空值	可以设置为空值	可以设置为空值
排序	根据定义的顺序排序	按位排序
限制	不允许重复值	允许重复值

enum：枚举。“单选”类型
enum（选项1，‘选项2’，‘选项3’...）；
给多个选项，但只能选一个，单选

set：集合，“多选”类型；
set（’选项1'，'选项2'，'选项3'....）;
给多个选项，可选多个选项，多选
上述两个类型：只能插入所给的选项

比特位存储

数据类型	定义例子	存储值	比特位
ENUM	ENUM('small', 'medium', 'large')	'small' 存储为 1	01
		'medium' 存储为 2	10
		'large' 存储为 3	11
SET	SET('music', 'sports', 'reading', 'traveling')	'music,sports' 存储为 3	0000 0011
		'reading' 存储为 8	0000 1000

set：二进制倒序，按顺序一个比特位对应一个选项

insert into table_name values set_name (n);#

对n:

0 = 0000,空串

1 = 0001，选择第一个

2 = 0010，选择第二个

3 = 0011，选择第一、第二个

7 = 0111，选择第一、第二、第三个

示例：

1、建立示例表

create table set_table(
id int,
sports set('唱','跳','rap')
);

2、插入数据看现象

​
insert into set_table values (1, 1);​#1=01，对应第一个选项

​
insert into set_table values (2, 2);​#2=01，对应第二个选项

insert into set_table values (3, 3);
#3=011，对应第一、第二选项​

insert into set_table values (7,7);
#7=0111,对应前三个选项

enum：索引从1开始,从左到右，不是二进制

insert into enum_table valuse (n);

对n值：

n=0，空串

n=1，选择第一个

n=2，选择第二个

n=3，选择第三个

• 每个 ENUM 值都有一个从 1 开始的索引。例如，如果定义 ENUM('small', 'medium', 'large')，那么 small 的索引为 1，medium 为 2，large 为 3。

7、Enum 和 set类型查找

查找某一个条件：严格匹配

select * from table_name where filed_name='xxx...';

find_in_set(xxx，filed_name)函数

查找所有匹配的条件：

select * from table_name where find_in_set('xxx'，filed_name);

查找多个条件组合：

select * from table_name where find_in_set('xxx'，filed_name) and find_in_set('yyy'，filed_name) ;

也可以使用数字匹配：
select * from table_name where filed_name=num;
对应的是比特位图形式