一、数据类型（列类型）
1、MySQL将数据类型分成了三大类：数值型（整数型+小数型）、字符串型和日期时间型
二、数值型
1、数值型数据都是数值。系统将数值型分为整数型和小数型
2、整数型
（1）整数型：存放整型数据。在SQL中，因为更多要考虑如何节省磁盘空间，所以系统将整型又细分成了五类：
a). tinyint：迷你整型。使用1个字节存储（1个字节=8位），可以表示256个状态，最大能表示的数值是255
注：默认是有符号的，其值为-128~127。无符号的值为0~255
b). smallint：小整型。使用2个字节存储，最大能表示的数值是65535
c). mediumint：中整型。使用3个字节存储
d). int：标准整型。使用4个字节存储
e). bigint：大整型。使用8个字节存储
注：整型中，使用较多的是tinyint和int
（2）插入数据时，只能插入整型数据，且必须在范围内
-- 创建整型表
create table my_int(
int_1 tinyint,
int_2 smallint,
int_3 int,
int_4 bigint
)charset utf8;
-- 插入数据：只能插入整型数据，且必须在范围内
insert into my_int values (100, 100, 100, 100); -- 有效数据
insert into my_int values ('a', 'b', '199', 'f'); -- 无效数据：类型限定（数据类型不正确）
insert into my_int values (255, 10000, 10000, 1000000); -- 错误：超出范围（数值类型默认有符号）（3）SQL中的数值类型默认都是有符号的（分正负）。如果需要使用无符号的数值，需要给数据类型加限定unsigned
（4）无符号：unsigned。用unsigned限定的字段存储的数据只有正数，没有负数（从0开始）
-- 无符号：unsigned
... 数据类型 unsigned;
-- 给my_int表增加一个无符号unsigned的tinyint型字段
alter table my_int add int_5 tinyint unsigned; -- 无符号unsigned
（5）查看表结构，发现每个字段的数据类型之后都会自带一个括号，里面指定某个数字，表示显示宽度
（6）显示宽度
-- 指定显示宽度为1
alter table my_int add int_6 tinyint(1) unsigned;a). 显示宽度：指数据（整型）在显示的时候，到底可以显示多长位。即 数据最终显示的位数
eg：-123是4位显示宽度（包含符号），255是3位显示宽度
tinyint(3) unsigned：无符号的tinyint默认显示宽度为3。unsigned说明只能是正数，正数的tinyint范围是0~255，永远不会超过三个长度
tinyint(4)：有符号的tinyint默认显示宽度为4。-128~127，-128是4位
b). 修改显示宽度不会改变数据本身的大小，也不会对数据显示有任何影响（没有零填充时显示宽度无效）
c). 显示宽度只是代表了数据是否可以达到指定的长度，但是不会自动满足到指定的长度。如果想要数据不够显示宽度时，自动变成对应的显示宽度，需要给字段增加一个属性zerofill
（7）零填充zerofill
a). 零填充：zerofill。从左侧开始填充0，因为左侧不会改变数值大小（负数不能使用零填充）
b). 不管数据的大小，保证填充的位数（不够时在前面补0，超出时不管。即 只保证最少有xxx位显示宽度）
c). 零填充的意义是保证数据格式（eg：年月日中保证月份是01~12，格式相同）
注：
a). 零填充zerofill会导致数值自动变成无符号unsigned的（一旦使用零填充zerofill，自动是无符号unsigned的）
b). 如果没有零填充，显示宽度无效
-- 零填充+显示宽度为2（不够2位时，在前面补0；超出2位时，不管）
-- 一旦使用零填充zerofill，自动是无符号unsigned的
alter table my_int add int_7 tinyint(2) zerofill;
-- 零填充+显示宽度的效果
insert into my_int values (1, 1, 1, 1, 1, 1, 1);
insert into my_int values (100, 100, 100, 100, 100, 100, 100);
3、小数型
（1）小数型：带有小数点或者范围超出整型的数值类型，专门用来存储小数的。在SQL中，将小数型分成两种：
a). 浮点型：小数点浮动。精度有限，且会丢失精度（超出精度范围就四舍五入，数据不准确）
b). 定点型：小数点固定。精度固定，不会丢失精度（不会四舍五入）
（2）数据存储方式：浮点型之所以能够存储较大的数值（不精确），是因为浮点型是利用存储数据的位来存储指数
a). 整型：tinyint（无符号）的最大值是255，所有位都为1
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
b). 浮点型：有部分用于存储数据，有部分用于存储指数
eg：前三位转换成十进制之后用作10的指数，后面几位是数据值。下面表格的值为：10^7次方 * 数据值
1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
（3）浮点型：浮点型数据是一种精度型数据。超出指定范围后，会丢失精度（自动四舍五入）。浮点型分为两种精度：
a). float：单精度。占用4个字节存储数据，但能表示的数据范围比整型大的多，为10^38次方。精度范围为7位左右，即 只能保证7个左右的精度（如果数据在7位数以内，基本是准确的；如果超过7位数，就是不准确的）
b). double：双精度。占用8个字节存储数据，表示的范围更大，为10^308次方。但精度范围只有15位左右
（4）浮点数的使用方式
a). float：表示不指定小数位的浮点数。如果float没有小数部分，则小数部分会被忽略，显示为整数
b). float(M, D)：表示一共存储M个有效数字，其中小数部分占D位（M代表总长度，D代表小数部分长度，整数部分长度为M-D）。如果指定了小数部分长度为D，则小数部分不够长度时会自动补0
（5）浮点型插入数据，可以是直接小数，也可以是科学计数法
-- 创建浮点数表
create table my_float(
f1 float,
f2 float(10, 2),
f3 float(6, 2)
)charset utf8;
-- 查看表结构
desc my_float;
-- 插入数据
insert into my_float values (1000.10, 1000.10, 1000.10); -- 符合条件
-- 1234567890：（1）float没有小数部分，则小数部分会被忽略，显示为整数
-- （2）float精度范围为7位左右，会四舍五入
-- 12345678.90：float精度范围为7位左右。1234567是准确的，从8开始不准确（四舍五入）
insert into my_float values (1234567890, 12345678.90, 1234.56); -- 符合条件
-- 浮点型可以采用科学计数法来存储数据
-- float：插入时为科学计数法，显示时也为科学计数法（保持原样）
-- float(M, D)：（1）插入时为科学计数法，显示时为浮点数，且小数位要补D个0
-- （2）插入时为科学计数法，但转换成浮点数后，如果整数部分超出指定长度，也会报错
insert into my_float values (3.01e38, 3.01e7, 1234.56);
-- 报错，f2超出指定长度
insert into my_float values (3.01e38, 3.01e8, 1234.56);
-- 系统进位导致整数部分超出指定长度，不会报错
insert into my_float values (9999999999, 99999999.99, 9999.99); -- 插入的最大值
（6）浮点型数据，整数部分不能超出指定长度，但小数部分可以超出指定长度（系统会自动四舍五入）。浮点数如果因为系统进位导致整数部分超出指定长度，那么系统也允许成立
-- 超出指定长度插入数据：小数部分超出指定长度（四舍五入）
insert into my_float values (123456, 1234.12345678, 123.9876543); -- 无问题
-- 超出指定长度插入数据：整数部分超出指定长度
insert into my_float values (123456, 1234.12, 12345.56); -- 报错：f3整数部分超出指定长度（7）浮点数的应用：浮点数通常用来保存那些数量特别大，大到可以不用那么精确的数据
（8）定点型：定点型数据保证整数部分一定精确，绝对不会被四舍五入（不会丢失精度）。理论上小数部分也不会丢失精度，但实际上，小数部分有可能丢失精度，超出指定长度会四舍五入
（9）定点型数据，系统自动根据存储的数据来分配内存空间（动态分配），大概每9个数就会分配4个字节来进行存储。同时，小数和整数部分是分开的
（10）decimal(M, D)：M表示总长度，最大值不能超过65。D表示小数部分长度，最长不能超过30
-- 创建定点数表
create table my_decimal(
f1 float(10, 2),
d1 decimal(10, 2)
)charset utf8;
-- 查看表结构
desc my_decimal;
-- 插入数据
insert into my_decimal values (12345678.90, 12345678.90); -- 有效数据
-- 浮点型数据小数部分可以超出指定长度，系统会自动四舍五入
-- 定点型数据小数部分超出指定长度，不会报错，但会产生一个警告
insert into my_decimal values (1234.123456, 1234.123456);
-- 查看警告
show warnings;
-- 插入最大数据
-- 浮点型数据整数部分不能超出指定长度。但因为系统进位导致整数部分超出指定长度，没有问题
-- 定点型数据保证整数部分一定精确
insert into my_decimal values (99999999.99, 99999999.99);
-- 浮点型数据整数部分不能超出指定长度。超出指定长度，插入失败
insert into my_decimal values (99999999.999, 99999999.99); -- 报错
-- 定点型数据如果因为系统进位导致整数部分超出指定长度，也会报错
insert into my_decimal values (99999999.99, 99999999.999); -- 报错
（11）定点型数据，整数部分不能超出指定长度，否则报错。小数部分超出指定长度，不会报错，但会产生一个警告。如果因为进位导致整数部分超出指定长度，系统也会报错
注：定点数一般不会进位
（12）定点数的应用：一般涉及到钱，用定点数来保证数据的准确性（通常不会使用浮点数）
注：涉及到钱，也有可能用"分"来存储。将数值*100，去掉小数部分。因为钱最小的单位是分，100元表示为10000分，而不表示为100.00元
三、字符串型
1、在SQL中，将字符串类型分成了6类：定长char、变长varchar、文本字符串（text、blob）、（枚举enum、集合set）
2、char：定长字符串。二维表在定义结构时，就已经确定了最终数据的存储长度，为指定的长度（不管放不放数据，都是这么多空间）
（1）char(L)：L代表length，可以存储的长度，单位为字符。最大长度值可以为255个字符（0`255）
eg：char(4)：在utf8环境下，需要4*3=12个字节；在gbk环境下，需要4*2=8个字节
3、varchar：变长字符串。变长字符串在分配空间时，按照最大空间分配。但实际使用多少，根据具体数据确定
（1）varchar(L)：L表示字符长度，理论长度是65535个字符，但是会多出1到2个字节（L的值小于256，用1个字节；L的值大于256，用2个字节）来确定存储的实际长度。实际上，如果长度超过255，既不用定长，也不用变长，而是使用文本字符串text
eg：varchar(10)：在utf8环境下，需要10*3+1=31个字节(bytes)
4、定长char和变长varchar的区别
（1）char一定会使用指定的空间，而varchar是根据数据来确定空间
（2）char的数据查询效率比varchar高。因为varchar需要通过实际的记录数来计算
5、如何选择定长或变长字符串
（1）定长char：比较浪费磁盘空间，但效率高。如果数据长度基本相同，就使用定长。eg：身份证号、手机号码等
（2）变长varchar：比较节省磁盘空间，但效率低。如果数据长度不确定（不同数据长度有变化），就使用变长。eg：姓名、地址等
（3）如果数据长度超过255个字符，无论是否固定长度，都不使用char和varchar，而是使用text（varchar太长了，意味着它占用的空间会比较多。而text占用的空间较少）
6、文本字符串：如果数据量非常大，通常超过255个字符就会使用文本字符串。文本字符串根据存储数据的格式分为两类：
（1）text：存储普通的字符文本
（2）blob：存储二进制文本（图片、文件）。一般不使用blob存储文件本身（文件本身太大了），而是使用一个链接指向对应的文件本身，这样就可以节省很多空间
注：二进制数据实际上都是存储路径
7、系统中提供的4种text
（1）tinytext：使用1个字节来保存对应数据所在的位置（地址+长度），不是保存数据。实际能够存储的数据为：(2^8次方+1) 个字符
（2）text：使用2个字节保存，实际能够存储的数据为 (2^16次方+2) 个字符
（3）mediumtext：使用3个字节保存，实际能够存储的数据为 (2^24次方+3) 个字符
（4）longtext：使用4个字节保存，实际能够存储的数据为 (2^32次方+4) 个字符
注：
（1）在选择文本字符串存储文本时，通常用text即可。不用刻意选择text的类型，系统会自动根据实际存储的数据长度来选择合适的text类型
（2）如果数据超过255个字符，就使用text存储，不用varchar（varchar也可以存储，但varchar数据越长，效率越低。如果超过255个字符，通常认为text的效率比对应varchar的效率高，而且text能够节省更多空间）
（3）实际开发中，新闻、文章等内容，通常使用text进行存储
8、enum：枚举字符串。如果确定某个字段只有几个固定的值，就可以使用枚举。系统在设定字段的时候将所有可能出现的结果都设计好，实际存储的数据就必须是规定好的数据中的一个
（1）枚举的使用方式
a). 定义：enum(值1, 值2, ... 可能出现的元素列表); --eg：enum('男', '女', '保密');
b). 使用：存储数据，只能存储上面定义好的数据
-- 创建枚举表
create table my_enum(
gender enum('男', '女', '保密')
)charset utf8;
-- 查看表结构
desc my_enum;
-- 插入数据
insert into my_enum values ('男'), ('保密'); -- 有效数据
-- 插入的数据只能是规定好的数据中的一个
insert into my_enum values ('male'); -- 错误：没有该元素（2）系统提供了1~2个字节来存储枚举数据。通过计算enum列举的具体值（数据值列表）来选择实际的存储空间。如果数据值列表在255个以内，使用1个字节（1个字节能表示的最大值是255）；如果数据值列表超过255但小于65535，使用2个字节
注：数据值列表在255个以内，指的是选项的个数，而不是选项的内容。选项的内容存储在别的地方
（3）枚举enum的存储原理
a). 实际上，字段所存储的值并不是真正的字符串，而是字符串对应的下标。在系统设定枚举类型的时候，会给枚举中的每个元素定义一个下标，这个下标是从1开始
b). 枚举在进行数据定义的时候，系统会自动建立一个数字与枚举元素的对应关系（下标），该关系放到日志中。在进行数据插入时，系统自动将字符转换成对应的数字存储。在进行数据提取时，系统自动将数值转换成对应的字符串显示
注：因为枚举实际存储的是数值，所以可以直接插入数值
-- 枚举元素中插入数值
insert into my_enum values (1), (2);
-- 将字段结果（数据）取出来进行+0运算，证明字段存储的数据是数值而不是字符串
select gender+0, gender from my_enum;
（4）枚举的好处（作用）
a). 规范数据格式。限定插入的数据只能是规定好的数据中的一个
b). 节省存储空间。枚举实际存储的是数值（从1开始），而不是字符串本身（枚举有个别名：单选框）
（5）枚举的弊端：虽然节省了内存空间，但降低了效率。因为要进行转换
注：所有需要转换的东西，都会导致效率的降低
9、set：集合字符串。是一种可以将多个数据选项同时保存的数据类型，本质是将指定的项按照对应的二进制位来进行控制，1表示该选项被选中，0表示该选项没有被选中。集合跟枚举很类似，实际存储的是数值（用二进制），而不是字符串（集合是多选）
（1）集合的使用方式
a). 定义：set(值1, 值2, ... 元素列表)
注：值1、值2是字符串类型。set中最多只能放64个元素（选项个数）
b). 使用：可以使用元素列表中的多个元素，用逗号分隔
-- 创建集合表
create table my_set(
hobby set('篮球', '足球', '乒乓球', '羽毛球', '排球', '台球', '网球', '棒球')
)charset utf8;
-- 查看表结构
desc my_set;（2）系统为set提供了多个字节进行保存，但系统会自动计算来选择具体的存储单元
a). 1个字节 = 8位(bit)，只能存放8个选项。所以，set只能有8个选项
b). 2个字节，set只能有16个选项
c). 3个字节，set只能表示24个选项
d). 8个字节，set可以表示64个选项
注：set和enum一样，最终存储到数据字段中的是数字而不是真实的字符串
（3）插入数据时，可以使用多个元素字符串组合（用逗号将选项隔开），也可以直接插入数值
注：
a). 数值插入的前提是对应的二进制位上都有对应的数据项
b). 通常不会用插入数值这种方式。因为插入数值要算准，还要知道哪个数据该选，哪个数据不该选
-- 插入数据（可以使用多个元素字符串组合，也可以直接插入数值）
-- 插入多个元素字符串组合
insert into my_set values ('足球,台球,网球');
-- 直接插入数值
insert into my_set values (3);（3）查看数据：数值+数据 查看
-- 查看集合数据
select hobby+0, hobby from my_set;（4）集合中每个元素都对应一个二进制位，被选中为1，没有被选中为0。将得到的二进制数反过来（reverse），再转换成十进制，此计算结果就是系统存储的数值（255 = 2^8次方-1：表示所有的选项都被选中）
注：要将得到的二进制反过来，是因为如果选项值没有8个，后面几位是0，这样计算得到的数值会很大。反过来就变成高几位是0，计算得到的数值会变小
（5）集合中，数据插入的顺序与数据选项中数据的顺序无关。系统会按照创建数据选项的顺序自动排序，最终都会变成数据选项中对应的顺序（一开始设计的顺序就是最终保存的顺序）
-- 插入数据
-- 颠倒插入元素的顺序，但最终的插入结果一样
insert into my_set values ('网球,台球,足球');
insert into my_set values ('足球,台球,网球');（6）集合的强大之处在于能够规范数据、节省空间，但因效率很低，且增加维护成本，所以实际很少使用集合（给一个数值，要先将其转成二进制，再把结果颠倒过来，然后再进行匹配）
四、日期时间型
1、日期时间类型
（1）date：日期。系统使用3个字节来存储数据，格式为：YYYY-mm-dd（就是datetime中的date部分）。能表示的范围是从1000-01-01到9999-12-12，初始值为0000-00-00
（2）time：时间或时间段。系统使用3个字节来存储数据，格式为：HH:ii:ss。MySQL中的time类型能够表示的时间范围很大（超出24个小时），从-838:59:59~838:59:59。在MySQL中，time具体的用途是用来描述时间段，-时间~+时间（从过去的某个时间到现在，以及从现在到未来的某个时间）
（3）datetime：日期时间。格式为：YYYY-mm-dd HH:ii:ss，有0值（0000-00-00 00:00:00）
（4）timestamp：时间戳。格式为：YYYY-mm-dd HH:ii:ss（与datetime完全一致），可以判断记录何时被更新
（5）year：年份。占用一个字节来保存，能表示1900~2155年。year有两种形式：
a). year(2)：0~99
b). year(4)：四位数的具体年（默认的显示宽度为4）
2、时间戳：timestamp
（1）时间戳类型不能为空
（2）有默认值，为当前时间戳对应的时间
（3）当记录被修改时，timestamp字段的值自动更新为当前最新的时间（YYYY-mm-dd HH:ii:ss）
（4）数据库多用时间戳timestamp来存储时间
3、time的特殊性
（1）用来表示时间区间，能表示的范围比较大
（2）在插入时间类型的数据时，可以使用一个简单的日期代替时间。在时间格式之前加一个空格，然后指定一个数字（可以是负数），系统会自动将该数字转换成：天数*24小时，再加上后面的时间
-- 创建表
create table my_time(
t1 time
)charset utf8;
-- 插入数据
-- 5 12:12:12：5 * 24 + 12 = 120 + 12 = 132（小时）--> 132:12:12
-- -2 12:12:12：-(2 * 24 + 12) = -(48 + 12) = -60（小时）--> -60:12:12
-- time为负数，表示过去的时间段
insert into my_time values ('12:12:12'), ('5 12:12:12'), ('-2 12:12:12');
-- 查看数据
select * from my_time;
4、year的特殊性：可以使用2位数，也可以使用4位数（默认）。year(2)有一个区间划分，临界点为69和70。如果输入的值是69及以下，匹配系统时间为20+数字；如果输入的值是70及以上，匹配系统时间为19+数字
-- 创建表
create table my_year(
y1 year(2),
y2 year
)charset utf8;
-- 插入数据，临界点为69和70
insert into my_year values (68, 68), (69, 69), (70, 70), (71, 71);
-- 查看数据
select * from my_year;
5、unix_timestamp()函数，是mysql自己的时间戳，用整型存储
-- mysql的时间戳，用整型存储
select unix_timestamp();五、MySQL记录长度
1、MySQL中规定，任何一条记录最长不能超过65535个字节
注：
（1）varchar永远达不到理论长度值。因为varchar能够存储的理论值是65535个字符，但varchar除了存储数据本身要占用空间，还需要额外的空间来保存记录长度（字符在不同的字符集下可能占用多个字节）
（2）0会有一个单独的应用。因为MySQL中没有布尔类型，0通常作为布尔类型false的判断
2、varchar的实际存储长度能达到多少，看字符集编码
（1）utf8下varchar的实际顶配：21844字符
gbk下varchar的实际顶配：32766字符
（2）求出varchar在utf8和gbk下的实际最大值的完整过程
（3）想用完整的65535个字节长度，增加一个tinyint字段即可
（4）MySQL记录中，如果有任何一个字段允许为空，那么系统会自动从整个记录中保留1个字节来存储null。如果想释放null所占用的字节，必须保证所有字段都不允许为空
注：即便有多个字段可以为空，最终占1个字节存储null即可
3、MySQL中，文本字符串text占据记录中的10个字节，用来保存数据的地址及长度（数据保存在别的地方，有地址和长度就可以确定数据）
注：
（1）文本字符串text在记录中占10个字节，且不分字符集。即 在gbk、utf8等环境下，都是占10个字节（与gbk、utf8等字符集无关）
（2）超过varchar的gbk、utf8计算的字符，用text存储
六、字符与字节
1、汉字/字符与字节的转换
（1）GBK环境下：1个汉字/1个字母 = 1个字符 = 2个字节
（2）UTF8环境下：1个汉字/1个字母 = 1个字符 = 3个字节
2、char与varchar的最大值
（1）char(L)：L的单位为字符，最大值为255个字符
eg：
a). GBK环境下：char(4) = 4 * 2 = 8个字节
b). UTF8环境下：char(4) = 4 * 3 = 12个字节
（2）varchar(L)：L的单位为字符，理论上最大值为65535个字符，但实际会多出1-2个字节来确定存储的实际长度（L<256，用1个字节；L>256，用2个字节）
eg：
a). GBK环境下：varchar(4) = 4 * 2 + 1 = 9个字节，varchar(512) = 512 * 2 + 2
b). UTF8环境下：varchar(4) = 4 * 3 + 1 = 13个字节，varchar(512) = 512 * 3 + 2
（3）文本字符串text在记录中占10个字节，且不分字符集
eg：
a). GBK环境下：text占10个字节
b). UTF8环境下：text占10个字节
3、tinyint使用1个字节存储，null也使用1个字节存储
4、MySQL中规定，任何一条记录最长不能超过65535个字节
