目录
数据类型
数值型
int和numeric
数值列的扩展属性
在使用AUTO_INCREMENT时，应注意以下几点：
null是什么意思
字符串类型
char和varchar
大对象类型Lob
两者之间的区别：
日期时间类型
特殊的时间是时间戳类型
系统函数now()可以获取系统的当前时间
类型说明
集合和枚举类型
位类型
总结
数据类型
存储数据是处理数据的第一步 。只有正确地把数据存储起来，才能进行有效的处理和分析。否则只能是 一团乱麻，无从下手。那么，怎样才能把用户各种经营相关的、纷繁复杂的数据，有序、高效地存储起来呢？ 在MySQL 中，一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。
要先创建一个数据库，而不是直接创建数据表呢？
- 因为从系统架构的层次上看，MySQL数据库系统从大到小依次是【数据库服务器、数据库、数据表、数据表的行与列】
数据类型主要给列上添加域完整性约束。开发者设置列的数据类型后， DBMS 会对添加的数据进行合法性验证，如果不合法则报错，拒绝添加
数值型
- 5种整型 tinyint、smallint、mediumint、int和bigint，主要区别就是取值范围不同，还可以在类 型前添加一个限制词unsigned，不允许添加负数
- 3种浮点型：不能精确存放float和double，可以精确存放decimal和numeric
类型名称 | 说明 |
tinyint | 1B，取值范围-128到127 |
smallint | 2B ，取值范围为 -32768 到 32767 |
mediumint | 3B ，取值范围为 -8388608 到 8388607 |
int 或者 Integer | 4B ，取值范围为 2e9 |
bigint | 8B ，取值范围为 9e18 |
float | 4B ，单精度浮点型，取值范围 3.4e38 |
double | 8B ，双精度浮点型，取值范围 1.7e308 ，总位数不超过 15 位 |
decimal和 numeric | 采用定点存放浮点数，具体字节数取决于定义时设置的参数 |
unsigned 设置列为无符号型，只能存放大于等于 0 的数据，没有负数。当使用无符号类型时取值范围由于没有负数部分，从而导致上限扩大一倍
create table t3(id int unsigned);
mysql> insert into t3 value(-10); -- 不允许存储负数
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t3 value(255); -- 存储数据的上线扩大一倍
可以在类型名后添加括号，其中包含一个正整数，例如 int(5) ，这里的含义并不是要求只能存放 5 位长度的整数；含义是当进行查询时自动使用空格填充到5 个长，如果真实数据长度大于 5 ，则按实际输出
decimal 和 numeric 作为字符串存储浮点数，可以实现浮点数的精确存储，并不是 float 和 double 种使用二进制浮点数存储。使用方法numeric( 总位宽，小数位数 ) ，小数位数必须小于等于总位宽；小数位数最大值30 ，总位宽最大值为 65 ，注意可能存储的数据会超出范围，其中的符号位和小数点不占位宽，
mysql> insert into t5 values(-99.999); -- 位宽统计中符号位不算
mysql> insert into t5 values(100); -- 整数超出范围报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t5 values(9.1235); -- 小数位数多余则自动四舍五入int和numeric
- int类型不能保存小数位，存储小数时会自动进行四舍五入
- numeric 可以保存小数位，如果小数位数为 0 ，可以模拟得到 int 类型的存储效果。采用的实际存储方式为字符串。查询效率远低于int
数值列的扩展属性
- auto_increment一般用于主键，可以实现该列的自动生成连续整数值
mysql> insert into t8 values(null,'yan1');-- 插入数据时不指定插入的值或者设置插入值
为null则会自动添加一个自增值
mysql> insert into t8 values(99,'yan1'); -- 如果插入数据时不指定对应的值，则自增长
max(id)+1;如果指定对应的值，则自增效果失效在使用AUTO_INCREMENT时，应注意以下几点：
1 、 AUTO_INCREMENT 是数据列的一种属性，只适用于整数类型数据列。
2 、设置 AUTO_INCREMENT 属性的数据列应该是一个正数序列，所以应该把该数据列声明为
UNSIGNED ，这样序列的编号个可增加一倍。
3 、 AUTO_INCREMENT 数据列必须有唯一索引，以避免序号重复 ( 即是主键或者主键的一部分 ) 。
AUTO_INCREMENT 数据列必须具备 NOT NULL 属性。
4 、 AUTO_INCREMENT 数据列序号的最大值受该列的数据类型约束，如 TINYINT 数据列的最大编号是127, 如加上 UNSIGNED ，则最大为 255 。一旦达到上限， AUTO_INCREMENT 就会失效。
- 当auto_incement达到上限后，再次申请下一个id时，得到的值保持不变。主键值重复报错
5 、当进行全表删除时， MySQL AUTO_INCREMENT 会从 1 重新开始编号。这是因为进行全表操作
时， MySQL 实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。
如果想删除所有的数据行又想保留序列编号信息，可这样用一个带 where 的 delete 命令以抑制
MySQL 的优化： delete from table_name where 1;
可用 last_insert_id() 获取刚刚自增过的值。
- unsigned 禁用负值
- zerofill如果查询显示时，实际数据小于指定位宽，则自动添加 0 值
- default 用于设置默认值
mysql> insert into t10 values(11,'yan1');-- 指定对应的数据，则default无效
mysql> insert into t10 values(null,'yan1'); -- 指定对应的数据，则default无效，即
使设置的值为null
mysql> insert into t10(name) values('yan1'); -- 只有不针对这个列进行数据插入时
default才能生效null是什么意思
null的意思为未知的数据，它既不是空字符串，不是任何一个具体的值；不能把任何值与一个null值进行比较。因为null的意思是不确定的值。真正的null值比较必须使用特殊的运算符is null或者is not null
mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)字符串类型
char(n) 定长字符串， n 值用于表示所允许的最大长度，取值范围 0-255
- 长度限制不区分中英文，表示是允许最多存储多少个字符
- 使用length函数获取的是字节数，不是字符数。在utf-8编码种一个汉字占用3个字节
varchar(n)可变长度的字符串，n值取值范围为0-65535
- 实际存储多少个字符就占多少个位置，每个列所占用的长度根据具体内容变化
char和varchar
- char属于定长，varchar属于变长。选择最基本的依据就是定长使用char，变长使用varchar
- char允许的最大长度为255，varchar允许的最大长度为65535
- 如果存储的字符串长度变化不大，推荐优先考虑使用char，例如针对口令进行md5加密，固定长度的行不容易产生碎片；对于长度很短的列，char的性能优于varchar，是因为varchar需要引入额外一个或者两个字节存储字符串长度
- char在计算种会自动去除空格；varchar不会去掉空格，但是进行字符串比较时，会去除空格后进行比较；在插入数据时系统会自动去除字符串默认多余的空格
大对象类型Lob
大对象类型可以分为 2 大类：文本类型和二进制类型
- BLOB是一个二进制大对象，可以容纳可变长度的数据，其中分为tinyblob、blob、 mediumblob、longblob。一般用于存储二进制数据，例如声音、图像、视频等
- Text是一个文本大对象，可以容纳可变长度的数据，其中分为tinytext、text、mediumtext、 longtext，一般用于存储文本类型数据，例如小说、商品简介
两者之间的区别：
- blob是二进制类型，可以容纳可变长度的数据，区分大小写；text是一个不区分大小写的blob
类型名称 | 允许的最大长度 | 说明 |
tinytext | 0-255 | 短文本字符串 |
text | 0-65535 | 长文本字符串 |
mediumtext | 16M | 中等长度的长文本字符串 |
longtext | 4G | 极大文本数据 |
tinyblob | 0-255 | 不超过 255 个长的二进制串 |
blob | 0-65535 | 二进制类型的长文本字符串 |
mediumblob | 16M | 中等长度的二进制长文本字符串 |
longblob | 4G | 极大二进制数据 |
日期时间类型
create table dt1(id date);
insert into dt1 values('1989-2-3');特殊的时间是时间戳类型
create table dt2(id timestamp default current_timestamp,name varchar(20));
mysql> insert into dt3(name) values('yan1');
mysql> select * from dt3;
+---------------------+------+
| id | name |
+---------------------+------+
| 2022-04-17 15:07:33 | yan1 |
+---------------------+------+
时间戳字段定义主要影响两类操作：
1. 插入记录时，时间戳字段包含 DEFAULT CURRENT_TIMESTAMP ，如插入记录时未指定具体时间数
据则将该时间戳字段值设置为当前时间
2. 更新记录时，时间戳字段包含 ON UPDATE CURRENT_TIMESTAMP ，如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
3. 当字段定义为 timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP ，表示该字段在插入或更新时未指定值，则被赋值为当前时间
系统函数now()可以获取系统的当前时间
select now();类型说明
类型名称 | 说明 | 格式 |
date | 日期类型，有效范围为 1000-9999 年，不包含时分秒 | 3B ， yyyy-MM-dd |
time | 时间类型，有效范围为 -838 到 838 小时范围内，一般用 于表示时间值或者持续时间。不包含年月日 | 3B ， HH:mm:ss |
year | 年份类型，有效范围 1901 到 2155 年 | 1B ， yyyy |
datetime | 日期时间类型，其中包含日期和时间，可以精确到秒，有效范围为1000 到 9999 年。具体存储时和时区无关。 从 MySQL5.6.4 之后可以包含秒后的小数部分，精度最高为微妙(6 位 ) | 非小数部分5B+ 小数 部分 0-3B ，如果有小 数部分则会随着小数 位数变化， yyyy-MM dd |
timestamp | 时间戳类型，可以精确到秒，有效分为是 1970 到 2038年，同时支持自动存储修改的时间，存储有时区数据，实际存储毫秒数。在MySQL 5.6.4 及之后版本，可以将时间戳类型数据最高精确微秒，timestamp(N)N 取值范围为0-6 | 非小数部分 4B+ 小数 部分 0-3B ，默认显示 格式 YYYY-MM-dd HH:mm:ss |
集合和枚举类型
- set集合类型，可以设置多个集合成员，允许从多个集合成员种选择多个数据进行插入
create table set1(id int,hobby set('抽烟','喝酒','烫头'));
insert into set1 values(1,'烫头,抽烟'); -- 没问题
insert into set1 values(1,'烫头,抽烟,扣脚'); -- 报错，因为其中一个值不在set定义种
insert into set1 values(1,'抽烟,抽烟');-- 实际上只插入一个值，也就是会自动去除重复值- enum枚举类型，可以设置允许选用的多个值，列只能赋予某个枚举成员值
create table enum1(id int,sex enum('男','女','不确定'));
insert into enum1 values(1,'男');
insert into enum1 values(2,'男,女'); -- 报错位类型
bit(1) 表示可以存储一个二进制位，只能存储 0 或者 1 ，其中 n 的最大上限值为 64
bit(8) 表示可以存储 8 个二进制位的值，取值范围为 0 到 255
总结
- 定义数据类型就是定义列，数据类型决定数据的特性【域完整性】
- 数据类型主要分为字符串类型、定点数和浮点数类型、日期类型、大对象类型【慎用】
- 数据类型在不同的存储引擎上表现不同
- 数据所采用的数据类型是根据业务需求和对应的数据类型特征进行选择，不建议使用过于复杂的数据类型
