MYSQL基础之数据类型

已于 2022-02-16 14:35:42 修改
阅读量978 收藏
点赞数
分类专栏： MYSQL 文章标签： mysql VARCHAR CHAR DATETIME 数据类型
于 2022-01-06 17:42:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011863822/article/details/122348906
版权

现在开始聊一些数据库中的数据类型，毕竟前面查询的时候很少涉及到这个，而对于无论创建数据库还是数据库的增改都会涉及到一些数据库数据类型，这个就像是其它编程语言一样。

毕竟存放数据库中的数据可能不一样，比如数字，日期以及字符串等，那么在存储的时候，自然不会将其当作一类数据进行存储，毕竟不同的数据类型，存储在MYSQL中自然也会有不同的类型，这样方便在查询的时候用不同的数据类型的方法，这个感觉又像是像是JAVA中的数据类型，虽然一些方法可以同时，使用一些有各自有差异。

前面聊了查询，先不了其如何更新数据以及插入数据，而是聊其基本的数据类型。这样就更加容易理解。

当然mysql也是分数字，浮点以及字符串等类型，现在依次进行介绍。，具体的类型如下：

类型类型举例
整数类型TINYINT,SMALLINT,MEDIUMINT,INT(INTEGER),BIGINT
浮点类型FLOAT,DOUBLE
定点数类型DECIMAL
位类型BIT
日期时间类型YEAR,TIME,DATE,DATATIME,TIMESTAMP
枚举类型ENUM
集合类型SET
二进制字符串类型BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
JSON类型JSON对象，JSON数组
空间数据类型单值类型：GEOMETRY,POINT,LINESTRING,POLYGON
集合类型: MULTIPOINT,MULTILINESTRING,MULTIPOLYGON,GEOMETRYCOLLECTION

类型其实本身又有其属性，如下：

属性作用
NULL数据列包含NULL
NOT NULL数据列不允许包含NULL值
DEFAUT设置默认值
PRIMARY KEY主键
AOTU_INCREMENT自动递增，适用于整数类型
UNSIGNED无符号，只能存储正数
CHARACTER SET name指定字符集

准备

下面的数据类型进行体验的时候，需要在数据库中体验，所以现在简单补充一些DCL知识，当然后面单独细聊这些知识。

-- 创建一个数据库
CREATE DATABASE 数据库名;
-- 创建表
CREATE TABLE 表名{
字段....
}

因为会用到，所以先了解一下，后面单独会聊创建数据库相关的SQL。

整数类型

整数类型一共由5个：INTYINT,SMALLINT,MEDIUMINT,INT(INTEGER)和BIGINT。都代表正数，其取值范围还是不同，如下：

整数类型字节有符号范围无符号范围
INTYINT1-128----127（-27-27）0-255 (0-28)
SMAILINT2-32768----32767(-215-215）0-65535(0-216)
MEDIUMINT3-8388608----8388607(-223-223）0-16777215 (0-224)
INT(INTEGER)4-2147483648----2147483647(-231-231）0-4294967295(0-232)
BIGINT8-9223372036854775808----9223372036854775807-263-263）0-18443744073709551615(0-264)

为什么上面会说有符号和无符号的范围，在说属性的时候提到：UNSIGNED。毕竟符号也会占一个二进制的位置。

现在用上面的字段创建一个新的表：

CREATE TABLE table_test(
a TINYINT,
b SMALLINT,
c MEDIUMINT,
d INT,
e BIGINT
);

创建这个表，然后用命令DESC table_test在8.0版本以及5.7版本中看。

8.0版本如下：

在这里插入图片描述

5.7版本如下：

在这里插入图片描述

可以看出8.0的时候整数没有对位数进行限制，而5.7版本却有位数限制。所以以5.7版本进行讲解，毕竟对于有位数限制的理解了，其8.0就更容易理解了。

这个时候就需要了解整数类型三个可选属性。

位数（宽度）

位数的取值范围是0-255。例如int(3):表示显示的宽度位3位，这个表示的是显示的数据有几位，而不是数据只能存储几位。不过这个功能需要佩服ZEROFILL使用，表示会用0填满宽度，否则指定显示的位数无效。

这个先创建一个表；

CREATE TABLE table_1(
a INT(3)
);
-- 插入数据
INSERT INTO table_1 VALUES ('123');
INSERT INTO table_1 VALUES ('12345');
INSERT INTO table_1 VALUES ('2147483647');
INSERT INTO table_1 VALUES ('2147483648');

在这里插入图片描述

看见这个神奇不，在存储的整数的时候，明明显示宽度最长的长度为3，但是下面还可以存储大于3位的数字，不过这个时候又可以看出如果整数超过最大值的时候，只能存储该类型最大的数字。所以在存储数字的时候，要根据自己的数据中数字长度选择不同的类型，防止数据存储失真。

因为显示的宽度于数据类型可以存储的值范围无关（该数据最大值以内），所以在MYSQL8.0开始，整数数据类型不推荐使用显示宽度属性。

当然不指定的话系统默认其宽度值。

前面聊了ZEROFILL这个属性，现在加入这个

CREATE TABLE table_2(
a INT(3) ZEROFILL
);
INSERT INTO table_2 VALUES ('1');
INSERT INTO table_2 VALUES ('12345');
INSERT INTO table_2 VALUES ('2147483647');
INSERT INTO table_2 VALUES ('2147483648');
INSERT INTO table_2 VALUES ('4294967295');
INSERT INTO table_2 VALUES ('4294967296');

在这里插入图片描述

这个可以看出如果不足三位，显示的时候前面直接补充0，所以这个就有点要说，既然前面补充0所以只能存储整数，所以最大值是4294967295。如果超过三位，是什么数据就显示什么数据。

UNSIGNED

无符号，其实本意就是这个数据不存储负数。

CREATE TABLE table_3(
a INT,
b INT UNSIGNED
)
INSERT INTO test.table_3 VALUES('-1', '-1');
INSERT INTO test.table_3 VALUES('2147483648', '4294967296');

在这里插入图片描述

可以看出只能存无符号最小只能存储值为0，所以可以看出其最大值的不同。

ZEROFILL

其实这个聊位数的时候已经演示了，所以不在演示，其是定义显示整数宽度，如果不足前面会补充0，以及将整数变成无符号的整数。

整数类型使用场景

毕竟都是存储整数，不过不同的数据类型占据的存储空间不一样的，所以有效的选取整数类型会减少数据存储的空间。

  • TINYINT:一般用于枚举数据，比如用户数字0，1代替性别，以及其它取值范围很小的场景。
  • SMALLINT:用于存储较小的统计数，比如一些工厂的固定库存等信息
  • MEDIUMINT:用户较大的整数计算，比如车站的每日客流量。
  • INT:取值范围足够大了，一般情况下不用考虑超出范围的问题，用到最多的整数类型。
  • BIGINT:只有存储或处理巨大的整数的时候才会用到，比如号称爱腾优所谓的视频总播放量以及双十一的交易量等。

因为在防止数据存储失真，也就是如果超过某类型数据的最大值后数据只能存储该数据的最大值，引起数据库存储的数据没有意义，所以优先考虑数据的存储值，然后再考虑存储成本，毕竟存储的数据如果没有意义，就算节省了空间，又有什么意义呢？

浮点类型

浮点类型，简单的说就是带有小数的数字，而在MYSQL中有两种方式处理小数浮点类型和定点数类型。很多时候浮点数和定点数据的使用频率要比整数更多。在MYSQL中浮点类型有三个：FLOT,DOUBLE,REAL。

  • FLOAT 表示单精度浮点数。

  • DOUBLE 表示双精度浮点数。

  • REAL 其实其默认就是DOUBLE类型，如果把SQL模式设定为REAL_AS_FLOAT，那么MSYQL就是认为REAL是FLOAT。如果要启动REAL_AS_FLOAT，如下：

    SET sql_mode="EAL_AS_FLOAT";
整数类型字节有符号范围无符号范围
FLOAT4(-3.402823466E+38 - -1.175494351E-38),0,(1.175494351E-38 - 3.402823466E+38)0,(1.175494351E-38 - 3.402823466E+38)
DOUBLE8(-1.7976931348623157E+308 - -2.2250738585072014E-308,0,2.2250738585072014E-308),0 ,(2.2250738585072014E-308- 1.7976931348623157E+308)0,(2.2250738585072014E-308 - 1.7976931348623157E+308)

可以看出DOUBEL比FLOAT存储的数据长度或者值更广，但是其占用的存储空间相对也会变大。

不过这个时候细心的人会发现为什么整数中无符号的最大值是有符号最大值的近2倍，而浮点类型中其实将有符号的数据中的负数取消只取正数范围呢？

这个首先需要了解一下MYSQL浮点类型的存储格式：符号（S），尾数（M）和阶码（E）,所以无论是否有符号，MSYQL的浮点类型都会存储符号部分，因此无符号的取值范围其实就是有符号取值范围大于或等于0的部分了。

浮点精度

对于浮点类型，在MYSQL中单精度使用4个字节，双精度使用8个字节。

  • 在MYSQL允许使用非标准语法，也就是其它数据库未必支持，因此如果涉及数据迁移，最好不要这样用：FLOAT(M,D)或者DOUBLE(M,D)。M为精度M的值=整数位数+小数位，D为标度，D为小数位数其值的范围D<=M<=255,0<=D<=30。

    这个无论老版本还是8.0版本如果创建的时候设置了这个精度，再用DESC 表都会显示这个精度的。

  • FLOAT 和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由硬件和操作系统决定）来显示。用DESC 表这个精度不会显示。

  • 前面说过取可以添加无符号属性UNSIGNED，但是不会改变取值范围。

  • 不管是否显示设置了精度（M,D）,MYSQL处理方案如下：

    • 如果存储时候，整数部分超过了范围，就会报错，不允许这样存在这样的值。但是这个需要说明一下，在8.0和5.7版本中报错，但是在5.0版本等老版本中其和整数一样存储数据的时候变成这个定义精度后的最大值。
    • 如果存储时候小数部分若超出范围：
      • 若四舍五入整数没有超出范围（在8.0版本中却五舍六入），则只会警告，但能成功操作并四舍五入删除多余的小数位后存储。
      • 若四舍五入整数没有超出范围（在8.0版本中却五舍六入），则MYSQL会报错，拒绝插入数据。
CREATE TABLE table_1(
a FLOAT,
b FLOAT(5,2),
c DOUBLE,
d DOUBLE(5,2)
);

INSERT table_1 VALUES (123.145,123.145,123.145,123.145);
INSERT table_1 VALUES (123.145,123.146,123.145,123.146);

使用DESC table_1

在这里插入图片描述

然后看5.7版本中查看数据：

在这里插入图片描述

在8.0版本中查看数据：

在这里插入图片描述

所以可以看出在数据中不一定是四舍五入，这个需要注意一下。

现在插入整数超过限制长度：

INSERT table_1 VALUES (NULL,1123.145,NULL,NULL);

在5.0版本中：

在这里插入图片描述

在8.0和5.7版本中会报错：

在这里插入图片描述

不过前面聊这样多，现在说个可能被骂街的事情：从MYSQL8.0.17开始，FLOAT(M,D)和DOUBLE(M,D)用法在官方文档中已明确不推荐使用，估计以后可能会被移除，自然取浮点类型中的无符号也不推荐使用了。

精度误差

浮点类型有一个缺陷，那就是不精确，这个不精确不是四舍五入或者五舍六入导致的的，现在做一个实验：

CREATE TABLE table_2(
a DOUBLE
);

INSERT INTO table_2 VALUES (0.57);
INSERT INTO table_2 VALUES (0.44);
INSERT INTO table_2 VALUES (0.19);

1.2=0.57+0.44+0.19，但是用SUM函数计算的时候，显示却是1.199999999。当然这个还是看电脑配置，如果配置不同可能显示不同，有的电脑仍然会显示1.2，但是不代表其它的精度数据不会有误差。

不过DOUBLE的误差要比FLOAT误差更小一些。误差是如何存在的呢？因为存储数据采用的二进制方式存储数据，如果位数不是5或者0的时候，就无法用一个二进制数准确表达，静儿只好在取值允许的范围内进行四舍五入，因为i浮点类型时不准确的，所以避免使用=来判断两个值是否相等。同样如果在一些对精度要求很高的项目中，也不要使用浮点数，不然会导致结果错误。下面聊下面顶点数据类型。

定点数据类型

MYSQL中的定点类型数据只有DECIMAL一种类型。

数据类型字节涵义
DECIMAL(M,D)M+2有效范围由M和D决定。当然0<=M<=65,0<=D<=30.

  • 其中M被称为精度，D被称为标度。其最大的取值范围于DOUBlLE类型一样，但是有效的数据范围由M和D决定。DECIMAL的存储空间不是固定的，由精度M决定，总共占用的存储空间是M+2个字节，所以在对精度要求不高的场景中同样的占用的空间其存放的值小于DOUBLE数据类型的值。如果使用DESC 表名，会显示其精度和标度。

  • 其实定点数在MYSQL内部存储时以字符串形式存储的，所以其精确度要高。

  • 如果DECIMAL没有设置精度和标度，那么其默认时DECIMAL(10,0)。当数据的精度超出了定点数据类型的精度范围时，同样会四舍五入。

    现在进行演示：

    CREATE TABLE table_1 ( 
a DECIMAL (5,2)
);

INSERT INTO table_1 VALUES (123.45);
INSERT INTO table_1 VALUES (123.455);
INSERT INTO table_1 VALUES (123.456);
INSERT INTO table_1 VALUES (1123.456);

    如果是5.0版本：

在这里插入图片描述

如果是5.7以及8.0版本（整数超过就会报错）：

在这里插入图片描述

在这里插入图片描述

定点数与浮点数区别

  • 浮点数相对于定点数是同样的存储空间，存储的值范围更大，但是其不精确，适用于与范围大但是对数值精确度要求不高的场景，比如分子建模，计算化学等。
  • 定点数同样的存储空间，相对于浮点来说其数值要小，但是其数值精确，所以适用于很多涉及到金钱计算的地方，比如交易额等。

总结：其实由于DECIMAL数据类型精确，所以在项目中除了所有数据比为整数，比如商品序列号等以外，其它项目都是用DECIMAL存储。因为很多时候项目都是围绕着钱来开发的。

位类型

其实这个就是BIT类型数据，其存储的就是二进制，有点像是由0和1组成的数据类似

数据类型长度空间
BIT(M)M （1<=M<=64）约为（M+1）/8 个字节

BIT类型，如果没有指定M，默认就是1位，表示只能存储1位二进制的值，这个M表示的是二进制的位数。

CREATE TABLE table_1 ( 
a BIT (5)
);
-- 因为插入的是是十进制，所以最多只能插入31
INSERT INTO  table_1 VALUES (1);
INSERT INTO  table_1 VALUES (0);
INSERT INTO  table_1 VALUES (31);

在这里插入图片描述

显示的时候为什么数据前面会由一个b呢？因为SQLyog工具的原因，告诉用户这个是一个BIT类型数据。

读取的时候也可以适用HEX和BIN()函数进行读取。如果看十进制的话，那就直接+0即可；

SELECT a+0,BIN(a),HEX(a) FROM  table_1

在这里插入图片描述

其实位类型一般很少用，这个了解即可。

日期和时间类型

这个数据类型几乎是最常用的类型之一，几乎所有的数据库都会用到，毕竟很多时候需要了解某条数据的时间标签。从而进行数据查询，统计和处理。

主要支持的日期和时间类型主要由：YEAR类型,TIME类型,DATE类型,DATETIME类型,TIMESTAMP类型。

  • YEAR : 通常用来表示年
  • DATE： 类型通常来表示年月日
  • TIME： 类型通常用来表示时分秒
  • DATETIME： 通常用来表示年月日时分秒
  • TIMESTAMP：通常用来表示市区的年月日时分秒
类型名称字节格式取值范围
YEAR年1YYYY或YY1901至2155
TIME时间3HH:MM:SS-838:59:59至 838:59:59
DATE日期3YYYY-MM-DD1000-01-01 至 9999-12-03
DATETIME日期时间8YYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 至 9999-12-03 23:59:59
TIMESTAMP带时区日期时间4YYYY-MM-DD HH:MM:SS 时区19701970-01-01 00:00:00 UTC 至 2038-12-03 23:59:59 UTC

所以根据自己的需求选择不同的日期和时间格式，这个时候会发现一个时间TIME，既然保存时分秒，为什么会超过24小时，甚至还为负数，因为MYSQL在涉及time格式的时候不光只一天之内的时间，同时还会表示两个时间直接的间隔。

YEAR类型

YEAR类型用来表示年份，在所有的日期格式中存储空间最小，只需要一个字节的存储空间。不过在存储的格式由两种：

  • 年份以4位字符串或数字YEAR类型，其格式时YYYY。这个很好理解，其范围是 1901至2155。

  • 年份以2位字符串或数字YEAR类型，其格式时YY。其最小值是0最大值是99。但是这个年不是有小到大的排序。

    • 当取值位01到69是，表示2001到2069.
    • 当取值位70到99是，表示1970到1999.
    • 当取值整数位0或00添加的话，那么是0000年
    • 当取值是日期/字符串的‘0’添加的话是2000年

    这个可以看出YY表示的年份有时候会很绕，所以从5.5.27开始，2位格式的YEAR已经不推荐适用了，YEAR默认的格式就是YYYY,所以没有必要写YEAR(4）。

DATE类型

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD，其中依次是年月日，需要3个字节的存储。在向DATE类型的字段插入数据时，同样也需要满足一定的格式条件。

  • 以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期，其中最小为1000-01-01最大为9999-12-03.如果是YYYYMMDD也会转换为YYYY-MM-DD。
  • 以YY-MM-DD的话就会遵守YEAR中两位数字表示的年份一样。这种不推荐适用。
CREATE TABLE table_1 ( 
a DATE
);
INSERT INTO table_1 VALUES ('2000-11-01');
-- 这里会涉及隐式转换
INSERT INTO table_1 VALUES (20001101);
INSERT INTO table_1 VALUES ('70-11-01');
INSERT INTO table_1 VALUES ('55-11-01');
INSERT INTO table_1 VALUES (NOW());

在这里插入图片描述

TIME类型

TIME 类型用来表示时间，不包含日期部分，当然这个也是会用3个字节存储，一般的格式HH:MM:SS,依次为时分秒。

TIME类型数据插入的时候，也会由不同的几种格式。

  • 可以使用带有冒号的字符串，比如D HH:MM:SS，HH:MM:SS，D HH:MM，HH:MM，D HH，SS等格式，都能被插入到数据库中。其中D表示天，其最小值为0，其最大值为34。如果带有D的字符串插入到TIME中会转换为小时。如果不带有D的字符串表示时间，是表示当天的时间，顺便说一下，其时间11:11插入的TIME后是11:11:00，而不是00:11:11。

  • 如果不带冒号的话比如HHMMSS,插入的时候会隐式转换 HH:MM:SS格式。如果是是1111，插入的TIME后是00:11:11，而不是11:11:00。

    
CREATE TABLE table_1 ( 
a TIME
);
INSERT INTO table_1 VALUES ('1 11:11:11');
INSERT INTO table_1 VALUES ('11:11:11');
INSERT INTO table_1 VALUES (111111);
INSERT INTO table_1 VALUES ('11:11');
INSERT INTO table_1 VALUES (1111);
INSERT INTO table_1 VALUES (NOW());

DATETIME

DATETIME类型所有的日期时间类型中占用的存储空间最大，总共需要8个字节存储空间。在格式上为DATE和TIME两个类型的组合，可以表示为：YYYY-MM-DD HH:MM:SS 分别是年月日时分秒。

其年份自然的条件和DATE类型几乎一样，也是分两位和四位，就不单独在列举了。

还是那句话2位表示年知道就好，用的话还是四位吧，毕竟可读性高，以及更好记。

CREATE TABLE table_1 ( 
a DATETIME
);


INSERT INTO table_1 VALUES ('2021-11-11 11:11:11');
INSERT INTO table_1 VALUES ('20211111111111');
INSERT INTO table_1 VALUES (20211111111111);
INSERT INTO table_1 VALUES ('22-11-11 11:11:11');
INSERT INTO table_1 VALUES ('221111111111');
INSERT INTO table_1 VALUES (221111111111);
-- 如果用@进行分隔，也可以，不过这个知道就好没多少意义
INSERT INTO table_1 VALUES ('2028@11@11 11@11@11');

在这里插入图片描述

TIMESTAMP类型

TIMESTAMP存储的格式和DATETIME一样，其也是可以表示年月日时分秒，格式也是相同：YYYY-MM-DD HH:MM:SS。但是TIMESTAMP存储的空间小，其只是4个字节。当然其存储的日期时间的跨度也是会小：1970-01-01 00:00:00 UTC 至 2038-12-03 23:59:59 UTC。其中UTC 表示的是世界统一时间，也叫作标准时间。

存储数据的时候，需要对当前时间所在的时区进行转换，查询时再将时间转换位当前的时区，因此使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。其插入也时需要满足格式的，同意的年2位和4位和前面的一样，不再说了。

CREATE TABLE table_1 ( 
a TIMESTAMP
);


INSERT INTO table_1 VALUES ('2021-11-11 11:11:11');
INSERT INTO table_1 VALUES ('20211111111111');
INSERT INTO table_1 VALUES (20211111111111);
INSERT INTO table_1 VALUES ('22-11-11 11:11:11');
INSERT INTO table_1 VALUES ('221111111111');
INSERT INTO table_1 VALUES (221111111111);
-- 如果用@进行分隔，也可以，不过这个知道就好没多少意义
INSERT INTO table_1 VALUES ('2028@11@11 11@11@11');

在这里插入图片描述

看着似乎和DATATIME 没什么区别，现在演示：

CREATE TABLE table_2 ( 
a DATETIME,
b TIMESTAMP
);


INSERT INTO table_2 VALUES ('2021-11-11 11:11:11','2021-11-11 11:11:11');

INSERT INTO table_2 VALUES (NOW(),NOW());

在这里插入图片描述

现在看更是一样， 这个我们再设置一下时区。

SET time_zone='+9:00'

在这里插入图片描述

可以看TIMESTAMP的时间变了，而DATATIME却没有。

TIMESTAMP和DATATIME总结：

  • TIMESTAMP存储的空间比较小，表示的日期范围也是比较小的。
  • TIMESTAMP底层存储时毫秒值，举例1970-01-01 0:0:0 0毫秒开始计算
  • 如果比较日期或者计算日期的时候，TIMESTAMP比较快。
  • TIMESTAMP与时区有关，而DATATIME却不是，所以不同时区TIMESTAMP查询的结果是不一样的。

上面的日期时间格式最常用的是DATATIME格式，毕竟其支持年月日时分秒，同时范围也足够大。再根据日期处理数据的时候，也很方便，毕竟是一个字段可以全部表示。不过再有些时候比如注册时间以及商品发布时间的时候，就直接用时间戳即可，这样可以将其作为整数进行计算更方便。

文本字符串

说是文本类型，其实说白了就是字符串，其也是数据据中常用一种数据格式。

MySQL的字符串如下：

类型长度（单位是字节）占用空间（单位是字节）
CHAR(M)0<=M<=255M个字节
VARCHAR(M)0<=M<=65535实际长度+1个字节
TINYTEXT0至255实际长度+2个字节
TEXT0至65535实际长度+2个字节
MEDIUMTEXT0至16777215实际长度+3个字节
LONGTEXT0至4294967295实际长度+4个字节
ENUM0至655351或2个字节
SET0至641，2，3，4或8个字节

上面写长度时候说是字节，还强调说了，因为不同的编码会涉及到存储汉字占几个字节了，比如UTF-8一个汉字是三个字节。

CHAR

CHAR(M)类型一般需要预先定义字符串长度，如果不指定的话默认是1个字符。但是如果存储数据的时候，实际长度比CHAR类型声明的M长度小的话就会再右侧补充空格进而达到指定的长度。MYSQL检索CHAR类型的的数据时候，CHAR会去除尾部的空格。所以声明的M就是占的存储空间的字节数。

CREATE TABLE table_1 ( 
a CHAR(5)
);

INSERT INTO table_1 VALUES ('ab');
INSERT INTO table_1 VALUES ('ab ');
INSERT INTO table_1 VALUES ('a b');
INSERT INTO table_1 VALUES (' ab');
INSERT INTO table_1 VALUES ('  ab');
INSERT INTO table_1 VALUES ('abcd');
INSERT INTO table_1 VALUES ('我是中国人');

SELECT a,LENGTH(a),CHAR_LENGTH(a) FROM  table_1;

在这里插入图片描述

这个可以看出左侧的空格还会保留，但是右侧的空格却不会再搜索中体现处理，类似于右侧补充空格。当然前面说过了插入的时候，如果M其实本质上字符长度而不是字节。

INSERT INTO table_1 VALUES ('中国人真棒a');

如果5.0版本插入超过长度后会截取：

在这里插入图片描述

但是在5.7还有8.0中就会报错：

在这里插入图片描述

VARCAHR

​ VARCAHR(M)虽然存储的时候是实际长度+1，但是在声明的时候必须带上M，当然M不能超过21845(因为UTF-8方法中文占三个字节)，否则会报错。在MYSQL4.0版本以下的时候VARCHAR(20),如果存放UTF8汉字时，但是只能存6个，因为每个汉字占3个字节。但是MYSQL5.0版本以上VARCHAR(20)却指定时20个字符。

检索VARCHAR类型的字段数据时，会保留尾部的空格。VARCHAR类型的字符串占用的存储空间位字符串实际长度+1。

CREATE TABLE table_1 ( 
a VARCHAR(5)
);

INSERT INTO table_1 VALUES ('ab');
INSERT INTO table_1 VALUES ('ab ');
INSERT INTO table_1 VALUES ('a b');
INSERT INTO table_1 VALUES (' ab');
INSERT INTO table_1 VALUES ('  ab');
INSERT INTO table_1 VALUES ('abcd');
INSERT INTO table_1 VALUES ('我是中国人');

SELECT a,LENGTH(a),CHAR_LENGTH(a) FROM  table_1;

在这里插入图片描述

这个可以看出右侧的空格是保留的，这个和CHAR类型的区别的。

INSERT INTO table_1 VALUES ('中国人真棒a');

这个可以看出5.0版本的时候，也会截取：

在这里插入图片描述

但是在5.7或者8.0中会报错：

在这里插入图片描述

对比CHARHE 和VARCHAR

  • CHAR 固定长度，会浪费存储空间，但是其效率高所以适合存储不大但是对速度要求高的数据。

    比如门牌号的数据，毕竟本身数据就不长，而VARCHAR又会多出一个字节，也不一定减少存储空间。还有一些本身就固定长度的数，比如uuid等数据因为其长度都是一样的。如果某个字段会频繁改变那么用CHAR，有哪位VARCHAR每次存储都要有额外的计算得到长度等工作，如果操作过多会让MYSQL浪费很多精力在计算上，所以不如CHAR类型，毕竟CHAR是不需要这样额外计算的。

  • VARCHAR： 可变长度，节省空间，但是相对于CHAR来说效率低，适合用不适合CHAR类型的数据。

​

因MSYQL中的存储引擎是MyISAM和InnoDB(新版更多用此引擎)，二者又有一些不同：

  • MyIASM数据存储引擎最好使用固定长度的CHAR类型代替可以变长度的VARCAHR的数据列。这样使得真个表静态化从而是检索更快，用于空间换时间。
  • InnoDB存储引起，建议使用VARCHAR类型，因为对于InnoDB数据表，内部的行存储格式没有取反固定长度和可变长度（所有的数据都使用指向数据列值的头指针），而且主要影响因素是数据行使用的存储总量，由于CHAR平均占用的空间多于VARCHAR ，所以除了简短且固定的数据用CAHR其它建议用VARCAHR。这样节省空间，对磁盘I/O和数据存储总量比较好。

TEXT类型

在MYSQL中，TEXT用来存储文本类型的字符串，在向TEXT类型的的字段存储数据和查询时，系统自动按照实际的长度进行存储，不需要预先定义长度，这一点和VARCAHR类型有点类似，TEXT有4个类型：

类型长度（单位是字节）占用空间（单位是字节）
TINYTEXT0至255实际长度+2个字节
TEXT0至65535实际长度+2个字节
MEDIUMTEXT0至16777215实际长度+3个字节
LONGTEXT0至4294967295实际长度+4个字节

由于TEXT类型的存储长度不确定，所以MSYQL中不允许TEXT类型的字段作为主键。所以这个时候只能用CHAR或者VARCAHR了。

其实这个几个TEXT类型，如何抉择那就是看你存储的数据长短了。

CREATE TABLE table_1(
a TINYTEXT,
b TEXT,
c MEDIUMTEXT,
d LONGTEXT
)

INSERT INTO table_1 VALUES ('ab','ab','ab','ab');
INSERT INTO table_1 VALUES ('ab ','ab ','ab ','ab ');
INSERT INTO table_1 VALUES ('我说中国人','我说中国人 ','我说中国人 ','我说中国人');

在这里插入图片描述

因为TEXT文本类型，可以存储比较大的文本字段，所以其搜索速度会慢，如果不是大文本就不要用TEXT类型的，直接用VARCHAR 或者CAHR类型的即可。而且TEXT类型的也没必要加入默认值因为加了也不会用（后面讲解约束的时候聊）。而且TEXT类型和BLOB类型数据删除的时候容易导致空洞，让文件碎片很多，所以如果频繁使用的表最好不要用TEXT,可以将其单独弄一个表存储。

ENUM类型

ENUM类型也就是枚举类型，这个在很多变成语言中都有，比如Java中，既然都叫做枚举，所以其作用或者说适用也是很相似的。其取值范围需要在定义字段时进行指定，插入数据的时候，也只能从这些定义的范围中取出数据，单不能选择多个。

其存储的空间根据其划分的成员决定的。

类型长度（单位是字节）占用空间（单位是字节）
ENUM0至655351或2个字节

其实枚举占空间，有些神奇分类：

当类型包含1-255成员的时候，需要一个字节存储空间。如果包含256-65535个成员的时候，需要2个字节存储空间。

其创建表的时候，就将成员定义好了：

CREATE TABLE table_1(
grede ENUM('A','B','C','D')
);

INSERT INTO  table_1 VALUES ('A');
INSERT INTO  table_1 VALUES ('D');

在这里插入图片描述

如果插入非成员的数据，

INSERT INTO  table_1 VALUES ('E');

在5.0中，插入的数据不报错但数据毫无意义;

在这里插入图片描述

而5.7和8.0直接会报错：

在这里插入图片描述

无论5.0还是8.0都可以插入空：

INSERT INTO  table_1 VALUES (NULL);

在这里插入图片描述

这个也可以看出非成员在5.0中虽然可以插入，但是数据没有意义也不是NULL。

当然如果插入两个成员会报错：

INSERT INTO  table_1 VALUES ('A,B');
-- 当然在5.0中不报错数据没有意义，但是5.7和8.0版本中会报错不在截图。

也可以通过索引插入数据：

INSERT INTO  table_1 VALUES ('2');
INSERT INTO  table_1 VALUES (2);

在这里插入图片描述

SET 类型

SET表示一个字符串对象，可以包含0个或者多个成员，但是成员个数上限时64。设置字段的时，可以取值范围内的0个到多个。

其存储占用空间也是成员不同占用的空间不同

成员数占用空间
1-----81个字节
9-----162个字节
17—243个字节
25–324个字节
32–648个字节

其存储空间也是和成员数有关，其也是提前声明好成员范围，不过其可以放多个。

CREATE TABLE table_1(
it_language SET ('JAVA','JS','PYTHON','C')
);

INSERT INTO  table_1 VALUES ('JAVA');
INSERT INTO  table_1 VALUES ('JAVA,JS');

在这里插入图片描述

-- 如果重复写多一个呢
INSERT INTO  table_1 VALUES ('JS,JS,PYTHON');

在这里插入图片描述

可以看出会自动去重的的。

这个又涉及到如果插非成员的值呢？

INSERT INTO  table_1 VALUES ('JAVA,dfdfd');
-- 5.0 版本会将将非成员剔除插入
-- 5.7和8.0会报错，这个就不在截图了
-- 当然都可以插入NULL
INSERT INTO  table_1 VALUES (NULL);

二进制数据类型

MYSQL通过二进制存储有些图片，音频和视频数据。二进制的类型主要包括：BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB类型。

不过说一句题外话，一般的开发这样图片什么都直接存在硬盘上而不是存储在数据库中。

BINARY和VARBINARY

BINARY和VARBINARY有点像是CHAR和VARCHAR的对应关系，只不过其存储的是二级制字符串。

BINARY(M)存储固定长度的二进制字符串，M表示能存储多个字节数，取值范围是0-255个字节。如果未指定那就是存储1个。其长度M，如果数据不够其右侧会补充\0。

VARBINARY(M)是可变的二进制字符串，M表示可以存储的最大字节数。其最大值位65335。VARCHAR除了存储本身数据外，还需要1或2个字节来存储数据的字节数。其必须指定M，不然会报错。

这个就不在演示了，因为其存储和CHAR和VARCAHR的限制或者知识点太相似了。

BLOB

其实BINARY和VARBINARY存储的二进制字符串其实相对很多视频来说其还是不够大，所以有了BLOB这个类型，其实有点事像是TEXT类型于CHAR和VARCHAR类型的关系。

其有四个类型，当然其存储的范围也是不同的。

数据类型长度（字节）占用空间
TINYBLOB0-255实际长度+1个字节
BLOB0-265535（相当于64KB）实际长度+2个字节
MEDIUMBLOB0-16777215（相当于16MB）实际长度+3个字节
LONGBLOB0-4294967295（相当于4GB）实际长度+4个字节
CREATE TABLE table_1(
a BLOB
);

因为其插入的需要二级制，演示效果直接用SQLyog工具插入了：

在这里插入图片描述

在这里插入图片描述

适用BLOB类型需要注意：

  • 在TEXT类型中说过：TEXT类型和BLOB类型数据删除的时候容易导致空洞，让文件碎片很多，所以如果频繁使用的表最好不要用TEXT,可以将其单独弄一个表存储。
  • 因为TEXT和BLOB数据量很大，所以查询的时候不要适用*，非必要尽可能的不查询该字段。

不过一般图片类资源都会直接放在硬盘上，不会放在数据库中，所以了解即可。

JSON

JSON其在各中语言中都会讲解，其最为一个交互或者存储数据的一种格式，其本身就是郑一泓轻量级的数据交互格式。从5.7中已经有JSON数据类型了，在8.0版本以上JSON类型提供了可以加拿大进行自动验证的JSON文件和优化存储的结构。这样MYSQL中存储和处理JSON效率更好。

CREATE TABLE table_1(
a json
)
INSERT INTO table_1 VALUES ('{"name":"jakc","age":18,"car":{"brand":"bc"}}')

在这里插入图片描述

其查询还有用-> 和$查询：

SELECT a-> '$.name'  `NAME`,a->'$.car.brand' AS car_brand FROM table_1

在这里插入图片描述

空间类型

其实这一部分用的很少，所以不在列举，说句不挨打的话，学了也几乎不用，没必要费脑子。当然如果用的话，可能需要单独拉一个大篇幅介绍，所以知道这个类型即可。

小结

在定义数据类型的时候，如果确定是整数，那就直接用INT即可，如果是小数的话，直接选DECIMAL,如果日期和实际就用DATATIME。当然这些是一般情况，如果特例就需要当前的情况考虑了。

这样做的好处就是首先保证自己的数据不会出错，当然这个也是牺牲了一些效率的。

其实很多规范会参考阿里的《Java开发手册》之MYSQL:

  • 任何字符串如果位非负数，必须是UNSIGNED
  • 强制小数类型适用DECIMAL，禁止适用FLOAT和DOUBLE.
    • 因为二者都会有精度损失，所以在比值的时候有可能得到的结果不正确。
    • 如果小数类型超过了DECIMAL的存储范围，那就拆成两个字段分开存储。
  • 强制如果存储的字符串长度基础相等，那就直接用CHAR定义即可
  • 强制VARCHAR是可变长度字符串，不预先分配存储空间，长度不要超过5000，如果超过就用TEXT,并且单独一个表存储，避免影响其它字段的索引速度。
小猪弟
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYSQL基础：数据类型.docx
08-08
MySQL是一种常用的关系型数据库管理系统，广泛应用于各种应用程序和网站开发中。在MySQL中，数据类型是定义和存储数据的重要组成部分。本文将详细介绍MySQL支持的各种数据类型，以及它们的特点和用途
3.mysql基本数据类型
weixin_51476766的博客
04-15 1352
文章目录mysql基本数据类型数值类型bit类型浮点数floatdecimal字符类型charvarchar日期和时间类型enum和setenumset mysql基本数据类型 数值类型 tinyint[unsigned] 占1个字节，带符号的范围为-128~127，服务号范围为0~255，默认有符号 smallint[unsigned] 占2个字节，带符号范围为-2^15~2^15-1,无符号为2^16-1 int[unsigned] 占4个字节，带符号范围为-2^31~2^31-1,服务号是2^32
mysql基本数据类型
Startapi的博客
04-13 1440
概述 要想学好mysql，了解其支持的基本数据类型以及内部原理是极为重要的，只有这样，我们才能根据不同的业务要求来选择不同的数据类型，实现最佳的存储效果和查询性能，因而本文就着重总结一下mysql支持的数据类型以及内部的存储原理。 总体来说，mysql一共分成了四类：数值类型、日期和时间类型、字符串类型、二进制类型等。 数值类型 数值类型是最为基础的类型，在业务开发中存储递增主键ID、金额、数量...
mysql数据类型有几种_MySQL基本数据类型
weixin_35125159的博客
01-18 291
mysql中的数据类型1 整数类型大小tinyint1 字节smallint2 字节mediumint3 字节int4 字节bigint8字节1个字节就是8位，范围是有符号(-128, 127)，有符号(0, 255)，详情可见X位的有/无符号整数详细介绍注意：默认情况整数是有符号的设置为无符号的三种方式新建表的时候：create table 表名(字节名 tinyint unsigned);新建...
MySQL知识汇总：详解MySQL基本数据类型
热门推荐
七叔的博客
02-10 1万+
MySQL知识汇总：详解MySQL基本数据类型
MySql入门学习：数据类型
sssxlxwbwz的博客
02-07 508
数据类型是定义列中可以存储什么数据以及该数据实际怎样存储的基本规则，MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型 。 一、字符串数据类型 有两种基本的字符串类型，分别为定长字符串和变长字符串，定长串接受长度固定的字符串，其长度是在创建表时指定的。变长串存储可变长度的文本。有些变长数据类型具有最大的定长，而有些则是完全变长的。 数据类型 说明 char(n) ...
mysql8.0.19基础数据类型详解
12-14
mysql基础数据类型 mysql常用数据类型概览 ![1036857-20170801181433755-146301178](D:\笔记\mysql\复习\1036857-20170801181433755-146301178.png)1. 数字： 整型：tinyinit int bigint 小数： float ：在位数...
Mysql支持的数据类型(总结)
12-14
　Mysql支持所有标准SQL中的数值类型，其中包括严格数据类型(INTEGER，SMALLINT，DECIMAL，NUMBERIC)，以及近似数值数据类型(FLOAT，REAL，DOUBLE PRESISION)，并在此基础上进行扩展。 　扩展后增加了TINYINT，...
mysql数据类型
10-21
mysql数据类型 数据库基础知识适合初学者
MySQL学习6：MySQL基本数据类型
life is wonderful
05-07 3976
数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储方式，代表了不同的信息 类型。MySQL中常用的的数据类型包括：数值类型、日期和时间类型和字符串类型等。        一数值类型        MySQL支持所有标准SQL中的数值类型，其中包括严格数据类型(INTEGER、SMALLINT、DECIMAL、 NUMBERIC)，以及近似数值数据类型(FLOAT、
MySQL之基本数据类型及其应用
qq_37954088的博客
05-07 1140
数据类型1. 数值类型  在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。 可以通过UNSIGNED来说明某个字段是无符号的。注意越界情况；2. 文本类型    1. 基本使用：     char(L): 固定长度字符串，L是可以存储的长度，单位为字符，大长度值可以为255      varchar(L): 可变长度字符串，L表示字符长度，大长度65535个字节 测试char:注...
MySQL中的常用基本数据类型
春水碧于天的博客
04-13 4622
MySQL中常用的数据类型：varchar :变长字符串，使用时我们必须设定其长度，最大值”理论值”65535个，实际上最大只能存65533个，但考虑到存储的字符编码的不同，也会有进一步的减少。比如： 如果存储从中文gbk,则最多是65533/2个; 如果存储中文utf-8,则最多存储65533/3个 此类型的实际长度是存储内容决定，而设定的值是最多可存储的字符个数。注意：这里的存储限制，其实都来源
MySQL基础（五）常见数据类型
小皮球的博客
08-21 160
常见数据类型及约束一、常见数据类型1.数值型1.1 整型1.2 小数（浮点型、定点型）2.字符型2.1 较短的文本（char/varchar）2.2 较长的文本（text/blob）3.日期型 一、常见数据类型 1.数值型 1.1 整型 分类： tinyint、smallint、mediumint、int、bigint 类型 占用字节 范围(有符号) 范围(无符号) tinyint 1 (-128，127) (0，255) smallint 2 (-32,768，32,767) (0，6
MySQL数据库的基本数据类型及字段属性
weixin_49512993的博客
07-25 247
文章目录MySQL数据库的基本数据类型及字段属性1、数据类型数值类型：日期和时间：字符串类型：2、字段属性Unsigned ：Zerofill：**Auto_InCrement** 自增：**NOT NULL** 非空：**DEFAULT** 默认： MySQL数据库的基本数据类型及字段属性 1、数据类型 数值类型： 类型 大小 范围（有符号） 范围（无符号） 用途 TINYINT 1 byte (-128，127) (0，255) 小整数值 SMALLINT 2 bytes (-32
MYSQL变量的定义与使用
2301_81019087的博客
05-10 496
3.通过变量进行实际操作。
mybatis 跨库查询 mysql
zzhongcy的专栏
05-11 419
跨库，表关联的查询，实现起来很简单：selecta.uidfrom只要在表的前边加上库名即可。
MySql软件安装
最新发布
m0_62731842的博客
05-12 423
1.打开mysql官网网址 MySQL :: Download MySQL Community Server 2.本次针对版本8的图形化界面安装，下载成功后接下来对MySQL进行安装 3.图形化下载后有一个MSI文件 4.我们安装典型即可，选择第一个 5.选择数据库信息存放的路径，我默认放在C盘不做改变 6.MySQL平时作为一些应用的数据提供者，选择服务类型即可 7.端口号（暴露的端口号便于我们连接数据库，进行相关数据操作）可以修改，但我们平常使用的端口号是33
python pymysql怎么查询一列的数据
活到老学到老
05-11 193
要使用Python的pymysql库查询MySQL数据库中一列的数据，你需要首先安装pymysql库（如果尚未安装），然后建立与数据库的连接，并执行SQL查询语句。为你的实际数据库信息和查询目标。这段代码会连接到MySQL数据库，查询指定表的指定列，并打印出结果。
MySQL中数据类型的基本概念
07-08
了解MySQL中的数据类型是数据库设计和数据操作的基础。下面是MySQL中常见的数据类型及其基本概念： 1. 数值型（Numeric Types）： - 整数类型（INT、BIGINT、TINYINT等）：用于存储整数值，可以指定有符号或无符号...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值