06-MySQL的数据类型--数据类型（列类型）+数值型+字符串型+日期时间型+MySQL记录长度+字符与字节

一、数据类型（列类型）

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 = 1026个字节

b). UTF8环境下：varchar(4) = 4 * 3 + 1 = 13个字节，varchar(512) = 512 * 3 + 2 = 1538个字节

（3）文本字符串text在记录中占10个字节，且不分字符集

eg：

a). GBK环境下：text占10个字节

b). UTF8环境下：text占10个字节

3、tinyint使用1个字节存储，null也使用1个字节存储

4、MySQL中规定，任何一条记录最长不能超过65535个字节