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;
对应的是比特位图形式