04、数据类型
一、介绍：mysql常用数据类型概览
#1. 数字：
整型：tinyinit int bigint
小数：
float ：在位数比较短的情况下不精准
double ：在位数比较长的情况下不精准
0.000001230123123123
存成：0.000001230000
decimal：（如果用小数，则用推荐使用decimal）
精准
内部原理是以字符串形式去存
#2. 字符串：
char（10）：简单粗暴，浪费空间，存取速度快
root存成root000000
varchar：精准，节省空间，存取速度慢
sql优化：创建表时，定长的类型往前放，变长的往后放
比如性别 比如地址或描述信息
>255个字符，超了就把文件路径存放到数据库中。
比如图片，视频等找一个文件服务器，数据库中只存路径或url。
#3. 时间类型：
最常用：datetime
#4. 枚举类型与集合类型
二、数值类型
1、整型
tinyint 1bytes
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127
无符号：
0 ～ 255
smallint 2bytes
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-32768 ～ 32767
无符号：
0 ～ 65535
int 4bytes
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
bigint 8bytes
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
'''不同类型的int能够存储的数字范围不一样'''
2、验证
# 验证不同类型的int是否会空出一个存储正负号（有符号）
mysql> create table t2(id tinyint);
mysql> insert into t2 values(256),(-129);
mysql> select * from t2;
+------+
| num |
+------+
| 127 |
| -128 | # 取值范围在[-128,127]
+------+
2 rows in set (0.00 sec)
# 得出结论:tinyint默认就会空出一位存储正负号
"""其实所有的int类型默认都是空出一位存储正负号"""
# （无符号）
mysql> create table t3(id tinyint unsigned);
mysql> insert into t3 values(-129),(0),(255),(256);
mysql> select * from t3;
+------+
| num |
+------+
| 0 |
| 0 |
| 255 | # # 取值范围在[0,255]
| 255 |
+------+
# 用zerofill测试整数类型的显示宽度
mysql> create table t4(num int(4) zerofill);
mysql> insert into t4 values(1),(11),(111),(1111),(11111);
mysql> select * from t4;
+-------+
| num |
+-------+
| 0001 |
| 0011 |
| 0111 |
| 1111 | # 不足4位补零，超过宽度限制仍然可以存
| 11111 |
+-------+
'''数字在数字类型中并不是用来限制存储长度 而是用来控制展示长度'''
结论:以后遇到数字类型不要指定数字 让他使用自带的即可
create table t15(id int);
3、浮点型
float(255,30) [UNSIGNED] [ZEROFILL]
总共255位,小数位占30位
double(255,30) [UNSIGNED] [ZEROFILL]
总共255位,小数位占30位
decimal(65,30) [UNSIGNED] [ZEROFILL]
总共65位,小数占30位
'''
三者精确度不同
decimal > double > float
具体使用结合实际情况
'''
4、验证
mysql> create table t5(id float(255,30));
mysql> create table t6(id double(255,30));
mysql> create table t7(id decimal(65,30));
mysql> insert into t5 values(1.111111111111111111111111);
mysql> insert into t6 values(1.111111111111111111111111);
mysql> insert into t7 values(1.111111111111111111111111);
mysql> select * from t5;
+----------------------------------+
| num |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
mysql> select * from t6;
+----------------------------------+
| num |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
mysql> select * from t7;
+----------------------------------+
| num |
+----------------------------------+
| 1.111111111111111111111111000000 |
+----------------------------------+
三、字符串类型
1、字符类型
#char类型：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
存储：
存储char类型的值时，会往右填充空格来满足长度
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
#varchar类型：变长，精准，节省空间，存取速度慢
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8
存储：
varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来
强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
2、验证
char(10) # 定长
最多存储10个字符 超出了则报错 没超出则用空格填充
varchar(10) # 变长
最多存储10个字符 超出了则报错 没超出有几个存几个
mysql> create table t8 (num int,name char(10));
mysql> create table t9 (num int,name varchar(10));
mysql> insert into t8 values(1,'abcde');
mysql> insert into t9 values(1,'abcde');
mysql> select name,char_length(name) from t8;
+-------------+-------------------+
| name | char_length(name) |
+-------------+-------------------+
| abcde | 10 |
+-------------+-------------------+
mysql> select name,char_length(name) from t9;
+-------+-------------------+
| name | char_length(name) |
+-------+-------------------+
| abcde | 5 |
+-------+-------------------+
3、解决SQL_MODE问题
# 1.模糊查询
mysql> show variables like '%mode%'; # 查看数据库配置中变量名包含mode的配置参数
+----------------------------+------------------------+
| Variable_name | Value |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR |
| block_encryption_mode | aes-128-ecb |
| gtid_mode | OFF |
| innodb_autoinc_lock_mode | 1 |
| innodb_strict_mode | OFF |
| pseudo_slave_mode | OFF |
| slave_exec_mode | STRICT |
| sql_mode | NO_ENGINE_SUBSTITUTION |
+----------------------------+------------------------+
# 2.修改安全模式
当存入的数据超出其数据类型的宽度，就会报错；若不报错，而是显示丢失，则需要修改配置
set session # 只在当前操作界面有效（临时修改）
set global # 全局有效
# 方法1：命令行直接修改
mysql> set global sql_mode = 'strict_trans_tables'
退出客户端重新进入
# 方法2：直接修改配置文件
sql_mode = 'strict_trans_tables'
# 3.由于mysql内部底层优化，在检索时char会将浪费的空间给删除，而varchar则不会
# 处理方式
mysql> set global sql_mode = 'pad_char_to_full_length';
4、char与varchar差异
1.先取消底层优化操作
mysql> set global sql_mode='strict_trans_tables,pad_char_to_full_length'
2.统计某个字段数据对应的长度
char_length() # 查看字符数
# 1.验证char定长特性
create table t10(id int,name char(5));
insert into t10 values(1,'tom');
create table t11(id int,name varchar(5));
insert into t11 values(1,'tom');
mysql> select name,char_length(name) from t10;
+-------+-------------------+
| name | char_length(name) |
+-------+-------------------+
| tom | 5 |
+-------+-------------------+
mysql> select name,char_length(name) from t11;
+-------+-------------------+
| name | char_length(name) |
+-------+-------------------+
| tom | 3 |
+-------+-------------------+
# 2.到底哪个好
char
优势:整存整取 速度快
劣势:浪费存储空间
varchar
优势:节省存储空间
劣势:存取数据的时候都需要先考虑报头 速度较于char慢
char(6) varchar(6)
1bytes+jason1bytes+tony1bytes+jack1bytes+kevin
四、日期类型
1、日期
date 年月日 YYYY-MM-DD datetime 年月日时分秒 YYYY-MM-DD HH:MM:SS time 时分秒 HH:MM:SS year 年 YYYY
2、验证
mysql> create table t10(
id int comment '编号',
name varchar(255) comment '姓名',
reg_time datetime comment '注册时间',
birthday date comment '生日',
study_time time comment '学习时间',
born_year year comment '年份'
);
mysql> insert into t10 values(1,'jason','2000-11-11 11:11:11','2000-11-11','11:11:11','2000');
mysql> select * from t10;
+------+------+---------------------+------------+------------+-----------+
| id | name | reg_time | birthday | study_time | born_year |
+------+------+---------------------+------------+------------+-----------+
| 1 | momo | 2021-11-11 11:11:11 | 2021-11-11 | 11:11:11 | 2021 |
+------+------+---------------------+------------+------------+-----------+
"""
字段也可以加类似于注释的说明
comment
"""
五、枚举与集合
1、枚举
# 多选一 enum
'''提前定义好数据之后 后续录入只能录定义好的内容之一'''
mysql> create table t11(
-> id int,
-> name varchar(32),
-> gender enum('male','female')
-> );
mysql> desc t11;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| gender | enum('male','female') | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
mysql> insert into t11 values(1,'momo','male');
mysql> select * from t11;
+------+------+--------+
| id | name | gender |
+------+------+--------+
| 1 | momo | male |
+------+------+--------+
mysql> insert into t11 values(1,'lily','女'); # 报错
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
2、集合
# 多选多(包含多选一) set
mysql> create table t12(
-> id int,
-> name varchar(32),
-> hobbies set('play','music','read')
-> );
mysql> desc t12;
+---------+----------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| hobbies | set('play','music','read') | YES | | NULL | |
+---------+----------------------------+------+-----+---------+-------+
mysql> insert into t12 values
-> (1,'jason','play'),
-> (2,'kevin','play,read'),
-> (3,'tom','music');
mysql> select * from t12;
+------+-------+-----------+
| id | name | hobbies |
+------+-------+-----------+
| 1 | jason | play |
| 2 | kevin | play,read |
| 3 | tom | music |
+------+-------+-----------+
