Mysql 详解所有的数据类型

Mysql 详解所有的数据类型

---

每博一文案

你有没有这样的经历，无论自己有多么忙，只要别人提出请求和邀请，就会全部答应，
宁愿委屈了自己，也不愿意拒绝别人的要求。害怕这次拒绝影响了彼此之间的关系。
不想发生争执，便让自己妥协，不想伤害情谊，便选择了成全，可时间久了，
才明白靠委屈自己换来的感情，并不能长久，因为没有底线的接受别人的要求，
注定没有一个好结局。
很喜欢作家方舟的一句话，真正能够欣赏你的人，永远欣赏的是你的骄傲的样子，而不是你故作
谦卑的样子和故意讨好的样子。心里有你的人，无需讨好，心里没你的人讨无用，习惯性的讨好，
迎合别人，只会让自己的生活变得千疮百孔，我们很容易活在别人的期待里，从而迷失了真正的自己。
人生短短数十载，最要紧的是温暖自己，而不是去讨好他人。
一是浮生一刹那，一程山水一年华，我们好不容易才来人间一趟，怎能轻易辜负自己的一生，
懂得温暖自己的人，人生才能不纠结于生活，清醒而坦然。
无论如何，我们都要努力和不喜欢的生活方式告别，奔向自己向往的远方。
人生苦短，遇到喜欢的事情就坦然接受，遇到不喜欢的就果断拒绝。
                                 ——————   一禅心灵庙语
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

1. Mysql 数据类型

数据库中的表中只能输入各个列指定格式的数据，如：指定为“日期类型”的列中，就只能输入日期数据。这种数据的格式称为数据类型

数据类型具有降低输入错误数据的可能性，保证数据库整体可信赖性的特点

但是Mysql和其他RDBMS相比，数据输入的检查功能相对趋于宽松。因此，在很多情况下即使输入的数据不符合设置输入的数据不符合设置的数据类型也不会发生错误。例如：向数值类型的列中输入文字就不会发生错误，而是会输入 0 。这就导致输入的数据格式和用户想要的数据格式出现偏差。大家需要注意这种情况。

下面是Mysql当中的所有数据类型

有关数据类型的所有约束条件

想要具体了解约束的，朋友可以移步到 🔜🔜🔜 Mysql 讲解所有的约束类型

---

2. 整数类型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

它们之间的具体不同如下表

具体整形类型的使用实例

CREATE TABLE test_int1(
i1 TINYINT,
i2 SMALLINT,
i3 MEDIUMINT,
i4 INT,
i5 BIGINT
);

DESC test_int1; -- 查看表结构
1
2
3
4
5
6
7
8
9

---

INSERT INTO test_int1(i1)   -- 表中插入数值
VALUES(12),
      (-12),
      (-128),
      (127);
1
2
3
4
5

---

注意插入的数值，不可以超过其数值的存储范围，如下面：i1 TINYINT数值的存储范围是在：-128~127当我们插入 数值 128，超过了，其存储范围是会报错的 Out of range value for column 'i1' at row 1

INSERT INTO test_int1(i1)
VALUES (128);  -- 超过其数值的存储范围报错
1
2

---

2.1 可选属性：M

M 表示显示宽度，M的取值范围是(0,255)。 例如：int(5) 当数据宽度小于 5 位的时候在数字前面需要用，字符填满宽度 到 5 。该项功能需要配合 ZEROFLL 使用，表示不足时用0 填充宽度，否则指定显示的宽度是无效的。

如果设置了显示宽度，那么插入的数据宽度超过显示宽度限制，会不会发生截断或报错 ？？？

答案：是不会对插入的数据有任何影响，插入的数据超过了指定显示的宽度，不会截断或报错，而是按照实际情况数值的宽度插入数据。明白一点就好：在整形中：指定显示的宽度与类型可以存储的值范围无关 。

注意：从Mysql 8.0.17开始，整数数据类型就不推荐使用这种指定宽度的属性了。而是以默认宽度使用。 说了这么多，却发现，不推荐使用。呵呵

整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认 的宽度值。

注意 int(M), M = 11 是最大的，不可以超过，不然报错，int(11)

当我们在 Mysql 8.0 中指定宽度创建表时，会给予我们警告如：Integer display width is deprecated and will be removed in a future release.意思是：整数显示宽度已弃用，将在未来的版本中删除。

CREATE TABLE test_int01(
i1 INT,
i2 INT(5)
);
1
2
3
4

---

当我们插入的数值超过了，指定的数值宽度，依然可以将数值插入到字段中

INSERT INTO test_int01(i2)
VALUES (123456); -- 超过了 i2 int(5)个宽度的范围,依然可以将数值插入
1
2

---

2.2 UNSIGNED (无符号类型)

UNSIGNED: 无符号类型（非负数），所有的整数类型都有一个可选的属性就是 unsigend(无符号属性)，无符号整数类型的最小取值为 0 ，不可以小于 0 ，不然会报错。所以，如果需要在MySQL中数据库表中保存非负数整数值，就可以将整数类型设置为无符号类型。

int 类型默认显示宽度为 int(11),无符号 int 类型默认显示宽度为 int(10)

CREATE TABLE test_int3(
i1 INT UNSIGNED -- 无符号类型,默认是 int(10)
);

DESC test_int3; -- 显示表结构
1
2
3
4
5

---

UNSIGNED无符号类型,插入负数，超过了其存储范围，报错：

INSERT INTO test_int3(i1)
VALUES (-10);
1
2

---

2.3 ZEROFILL(填 0)

ZEROFILL ：整数类型指定宽度，不足以 0 在左边填充。

当某列是 ZEROFILL ,那么Mysql会自动为当前列添加上 UNSIGNED属性(因为当宽度不足时,是在数值的左边补 0 ,如 01，0123，就没有负数的可能了，就自动添加上了 UNSIGNED无符号类型)。

如果指定了 ZEROFILL 只是表示不够 指定的 M宽度位时，用 0 在左边填充，如果超过了 M宽度位，也可以将数值插入到字段中(只要不超过数据存储的范围就可以)。

原来，在int(M)中 M的值跟 int(M) 所占多少存储空间并无任何关系。无论是 int(2), int(3),int(8) 在磁盘上都是占用 4 bytes 的存储空间。也就是说，(intM),必须和 UNSIGNED ZEROFILL 一起使用才可以真正的节省空间，才有意义。如果整 数值超过M位，就按照实际位数存储。只是无须再用字符 0 进行填充。

CREATE TABLE test_int4(
i1 INT(5) ZEROFILL
);

DESC test_int4; -- 显示表结构
1
2
3
4
5

---

INSERT INTO test_int4(i1)
VALUES(123),  -- 不足指定宽度 5,左边以 0 填充
      (123456); -- 超过指定宽度,但没有超过存储宽度,插入数值
1
2
3

---

2.4 整数类型总结

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。

INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证 券公司衍生产品持仓等。

在决定用那种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：

一方面： 用占用字节空间少的整数类型可以节省存储空间，

另一方面： 要是为了节省存储空间，使用整数类型取值范围太小了，一旦遇到超过取值范围的情况，就可能引起系统错误，影响可靠性。

举个简单的例子 ：如果商品采用的数据类型是 int 。原因就是在于，客户门店中流通的商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断的迭代，日积月累，数据值会越来越大。而如果我们使用的是 SMALLINT 类型，虽然占用字节数比 INT 类型的少，换来的是数据的存储范围也小了。不能保证数据不会超过范围。

相反，我们一开始使用 INT ，就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。

你要注意的是：实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本 。因此，我建议你首先确保数据不会超过取值范围，在这个前提之下，再考虑如何节省存储空间。

3. 浮点类型

浮点数和定点数类型的特点是可以 处理小数 ，你可以把整数看成小数的一个特例。

因此，浮点数和定点 数的使用场景，比整数大多了。 MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

• FLOAT 表示单精度浮点数，占用 4 个字节数
• DOUBLE 表示双精度浮点数，占用 8 个字节数
• REAL 默认就是 DOUBLE 。如果你把 SQL模式 设定为 REAL_AS_FLOAT,那么，MySQL就认为REAL 是FLOAT 如果要启用REAL_AS_FLOAT ，可以通过以下SQL语句实现：

SET sql_mode = 'REAL_AS_FLOAT';
1

---

---

面试题1: FLOAT 和 DOUBLE 这两种数据类型的区别是啥呢？

FLOAT 占用字节数少 4 个，取值范围较小，DOUBLE 占用字节数 8 个，取值范围大。

面试题2 : 从上图上看，为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于 有符号数取值范围大于等于零的部分呢？

Mysql 存储浮点数的格式为: 符号(S) + 尾数(M) + 阶码(E)。

因此无论有没有符号，MySQL的浮点数都会存储表示符号的部分。

所以，所谓的无符号数取值范围，其实是有 0 表示符号的符号位的，因此：有符号数取值范围就只是 >= 0的部分。

---

3.1 浮点类型精度说明

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

Mysql 允许使用 非标准语法 (其他数据库未必支持，如果涉及到数据迁移，就不要用了)。

• 非标准语法 ：

FLOAT(M,D) / DOUBLE(M,D) 这里： M 称为精度，D 称为 标度。

(M,D) 中 M = 整数位 + 小数位 ，D = 小数位 。 D <= M <= 255，0 <= D <= 30。

例如: 定义为 FLOAT(5,2) 的一个列，可以存储显示的范围是 -999.99~999.99 。如果超过了这个范围会报错。

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

浮点类型也是可以加 UNSIGEND 无符号类型的，但是不会改变数据存储范围。例如: FLOAT(3,2) UNSIGNED 仍然只能表示 0 ~ 9.99的范围。

不管是否指明设置精度(M,D) ，Mysql的处理方案如下：

1. 如果存储时，整数部分超过了范围，mysql 就报错。
2. 如果存储时，小数点部分超过了范围，分如下情况
   1. 如果小数位经过四舍五入后，整数部分没有数值超过范围，将经过四舍五入后的数值插入进去，并给予警告。
   2. 如果小数位经过四舍五入后，整数部分超过数值存储范围，报错。
   3. 基本上只要整数部分没有超过范围，就可以插入，因为小数部分超过了会(四舍五入)给整数

从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可 能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。 所以说了这么多，还是不推荐使用，呵呵。

CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
);

DESC test_double1;  -- 查看表结构
1
2
3
4
5
6
7
8

---

例如: FLOAT(5,2) 数值 999.009 四舍五入 = 999.01,整数部分没有超过范围，插入并给予警告

INSERT INTO test_double1(f2)
VALUES(999.009);  -- 四舍五入
1
2

---

例如: FLOAT(5,2)列内插入 999.995 和 -999.995 都会报错。四舍五入后 1000.00,1000.00 超过了整数部分的存储范围，基本上只要整数部分没有超过范围，就可以插入，因为小数部分超过了会(四舍五入)给整数

INSERT INTO test_double1(f2)
VALUES(999.999),
      (-999.995);
1
2
3

---

3.2 浮点类型误差说明

浮点数类型有个缺陷，就是不精准。

下面我来重点解释一下为什么 MySQL 的浮点数不够精准。

比如，我 们设计一个表，有f1这个字段，插入值分别为0.47,0.44,0.19，我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询：

CREATE TABLE test_double2 (
f1 DOUBLE
);

DESC test_double2;  -- 查看表结构
1
2
3
4
5

插入数值 0.47,0.44,0.19

INSERT INTO test_double2(f1)
VALUES (0.47),
       (0.44),
       (0.19);
1
2
3
4

使用 SUM 函数计算和，应该时等于 0.47+0.44+0.19 = 1.1，可结果却是：1.0999999999999999

看到了吗？虽然误差很小，但确实有误差。 你也可以尝试把数据类型 改成 FLOAT，然后运行求和查询，得到的是， 1.0999999940395355。显然，误差更大了

SELECT SUM(f1)
FROM test_double2;
1
2

---

我们再判断 sum(f1)结果与 1.1 比较大小是否相等，理论上，应该是为 1(真) ，结果却是 0(假)

SELECT SUM(f1),SUM(f1) = 1, 1.1 = 1.1
FROM test_double2;
1
2

---

面试题：那么，为什么会存在这样的误差呢？

问题还是出在 MySQL 对浮点类型数据的存储方式上。

Mysql用 4 个字节存储 FLOATl 类型数据，用8个字节来存储DOUBLE类型数据。

无论是哪个，都是采用二进制的方式进行存储的。

比如9.625 ，用二进制来表示，就是 1001.101 ,或者表示成 1.001101 x 2^3 。如果尾数不是 0 或 5(比如 9.624), 你就无法用一个二进制数来精确表达。进而，就只好在取值允许的范围内进行四舍五入了。

在编程中 ，如果用到浮点数，要特别注意误差问题，因为浮点数不是准确的，所以我们要避免使用 = 来判断两个浮点数是否相等。同时，在一些对精度要求较高的项目中，千万不要使用浮点是，不然会导致结果错误，甚至是造成不可挽回的损失。

那么，Mysql有没有精确的数据类型呢。当然有了，就是我们下面说的 定点数类型 : DECIMAL

4. 定点数类型

Mysql 中的定点数只有一种就是 ：DECIMAL

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M 被称为精度，D 被称为是标度。 0 <= M <= 65，0 <= D <= 30，(M,D) 中 M = 整数位 + 小数位 ，D = 小数位

例如: DECIMAL(5,2) 的类型，表示该列值范围是 -999.99~999.99 。DECIMAL(M,D) 的最大范围与 DOUBLE类型一样，但是有效的数据范围是由 M 和 D 决定的。

DECIMAL 的存储空间并不是固定的，由精度值 M 决定，总共占用的存储空间为 M+2 个字节。

也就是说，在一些对于精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围是更大一些的。

定点数在Mysql内部是以字符串的形式进行存储的 ,这就决定了它一定是精准的。当 DECIMAL 类型不指定精度和标度时，默认认为时 DECIMAL(10,0 )。

当数据的精度超过了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

1. 如果存储时，整数部分超过了范围，mysql 就报错。
2. 如果存储时，小数点部分超过了范围，分如下情况
   1. 如果小数位经过四舍五入后，整数部分没有数值超过范围，将经过四舍五入后的数值插入进去，并给予警告。
   2. 如果小数位经过四舍五入后，整数部分超过数值存储范围，报错。
   3. 基本上只要整数部分没有超过范围，就可以插入，因为小数部分超过了会(四舍五入)给整数

---

例如：定义定点数类型

CREATE TABLE test_decimal1 (
f1 DECIMAL,    -- 未指定范围,默认是 decimal(10,0)
f2 DECIMAL(5,2)
);

DESC test_decimal1;
1
2
3
4
5
6

---

f2 插入数值 67.567 四舍五入。

INSERT INTO test_decimal1 (f2)
VALUES (67.567);  -- 四舍五入 67.57
1
2

---

f2 插入数值 999.995 四舍五入后 1000.00，整数部分超过了范围，报错，

INSERT INTO test_decimal1(f2)
VALUES (999.995);
1
2

---

f2 插入数值 1234.00 整数部分超过了范围，报错

INSERT INTO test_decimal1(f2)
VALUES(1234.00);
1
2

我这里报错了

---

我们在试试上面对于浮点数精度的等于测试，将 0.47,0.44,0.19 插入到 f1 当中去。

CREATE TABLE test_decimal2 (
f1 DECIMAL(5,2)
);

INSERT INTO test_decimal2(f1)
VALUES (0.47),
       (0.44),
       (0.19);
1
2
3
4
5
6
7
8

---

再使用函数 sum(f1) 计算 f1 的总和，理论是 0.47+0.44+0.19 =1.1 ,并将结果与 1.1比较判断相等

我们发现与 浮点数结果不同的是，没有精度的丢失，结果为 1.10, 比较判断上也都是 1(真)。

因为定点数类型，存储的小数的形式是字符串，不是浮点数的二进制 符号位 + 尾数 + 阶码；可以以字符的形式精确的表示出，小数值。

SELECT SUM(f1), SUM(f1) = 1.1 , 1.1 = 1.1
FROM test_decimal2;
1
2

---

4.1 浮点类型总结

浮点数 vs 定点数

浮点数: 相对于定点数的优点是在长度一定的情况下，浮点类型的取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如：计算化学，分子建模，流体动力学等等）

定点数: 取值范围相对小，但是精准，没有误差，适合于对于精度要求极高的场景（比如涉及金额计算的场景）

由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型 外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能 差。 ” ——来自某项目经理

---

5. 位类型

BIT类型中存储的是二级制值，类似 010110

BIT类型，如果没有指定(M)，默认是1位，这个 1位，表示只能存 1 位的二进制值。这里(M)是表示二进制的位数。位置最小值1，最大值为 64

注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。

定义一个二进制类型表

CREATE TABLE test_bit1(
b1 BIT(65)   -- 报错的,bit(M), 最大是 64
);
1
2
3

---

CREATE TABLE test_bit1(
b1 BIT,
b2 BIT(5),
b3 BIT(64)
);

DESC test_bit1;
1
2
3
4
5
6
7

---

b1 插入数值 2 ，报错，因为 2 转换为二进制是10 含有两个二进制的数值的,超过了 b1 默认的是 1个bit(1)的

INSERT INTO test_bit1(b1)
VALUES (2);
1
2

---

b2 插入数值 31 刚好是 二进制 11111 5个二进制

INSERT INTO test_bit1(b2)
VALUES(31);
1
2

---

显示的话，一般在Dos 中是十六进制显示的。

我们可以使用 BIN(b2) 以二进制的方式显示，HEX(b2) 十六进制显示， b2 +0 通过与十进制运算隐式转换为 十进制。

SELECT BIN(b2), HEX(b2), b2 + 0
FROM test_bit1;
1
2

---

6. 日期和时间类型

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都用得到。

原因是客户需要知道数据的，时间标签，从而进行数据查询，统计和处理。

MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL8.0版本支持的日期和时间 类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

YEAR 表示 年

DATE 表示 年，月，日

TIME 表示 时，分，秒

DATETIME 表示 年，月，日，时，分，秒

TIMESTAMP 表示带有时区的 年，月，日，时，分，秒

---

可以看到，不同数据类型表示的时间内容不同，取值范围不同，而且占用的字节数也不一样，你要根据实际需要灵活选取。

为什么时间类型 TIME的取值范围不是 -23:59:59 ~ 23:59:59呢 ？

原因是：Mysql设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

6.1 YEAR类型：年

YEAR 类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要 1 个字节的存储空间。

在MySQL中，YEAR 有以下几种存储格式：

• 以 4 位字符串或数字格式表示 YEAR类型, 其格式为 YYYY, 最小值为 1901,最大值为 2155
• **以 2 位字符串格式表示 YEAR 类型，最小值为 00，最大值为 99 **
  • 当取值为 01 到 69 时，表示 2001 到 2069
  • 当取值为 70 到 99 时，表示 1970 到 1999
  • 当取值整数的 0 或 00 添加的话，那么是 0000年
  • 当取值是日期/字符串的 ’0‘ 添加的话，是 2000年

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”，没必要写成YEAR(4)， 从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型

定义 YEAR 类型

CREATE TABLE test_year1(
f1 YEAR,
f2 YEAR(4)
);
 
DESC test_year1; -- 查看表结构
1
2
3
4
5
6

---

插入数值 2156 ，报错，超过了存储的范围

INSERT INTO test_year1(f1)
VALUES(2156);   -- 报错,超过范围 2155
1
2

---

可以使用 NOW() 或者 CURRENT_DATE() 函数，会插入当前系统的年

SELECT NOW(), CURRENT_DATE()  
FROM DUAL;
1
2

---

INSERT INTO test_year1(f1)
VALUES (NOW()),  -- 会自动截断到需要的年
       (CURRENT_DATE());  
1
2
3

---

6.2 DATE类型：日期 (年月日)

DATE 类型表示日期，没有时间部分，格式为 YYYY-MM-DD ，其中，YYYY 表示年份，DD表示日期。需要 3 个字节的存储空间。

在向 DATE 类型的字段插入数据时，同样需要满足一定的格式条件，

以 YYYY-MM-DD 格式或者 YYYYMMDD 格式，表示的字符串日期，其最小值取值为 1000-01-01,最大取值为 9999-12-03。 YYYYMMDD 格式会被转化为 YYYY-MM-DD格式。

以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足

YEAR 类型的格式条件为：

• 当年份取值为00到69时，会被转化为2000到2069
• 当年份取值为70到99时，会被转化为1970到1999

可以使用 CURRENT_DATE或者 NOW()函数,会插入当前系统的日期

创建数据表，表中只包含一个DATE类型的字段f1。

CREATE TABLE test_date1(
f1 DATE
);

DESC test_date1;  -- 查看表结构
1
2
3
4
5

---

插入日期

INSERT INTO test_date1(f1)
VALUES('2020-10-01'), -- 该格式推荐
      ('20201001'),
      (20201001);  -- 该格式不推荐
1
2
3
4

---

使用函数插入日期 CURDATE(),CURRENT_DATE(),NOW()

INSERT INTO test_date1(f1)
VALUES(CURDATE()),
      (CURRENT_DATE()),
      (NOW());
1
2
3
4

---

6.3 TIME类型：时间(时分秒)

TIME类型用来表示时间，不包含日期部分。

在Mysql中，需要 3 个字节的存储空间来存储 TIME类型的数据，可以使用 “HH:MM:SS” 格式来表示 TIME类型

其中，HH 表示小时，MM 表示分钟，SS 表示秒

在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。

• 可以使用带有冒号的字符串，比如D HH:MM:SS ，HH:MM:SS ，HH:MM ,D HH:MM ，D HH 或 SS 格式，都能被正确地插入 TIME类型的字段中。其中 D 表示 天，其最小值为 0,最大值为 34。

如果使用带有 D 格式的字符串 插入 TIME 类型的字段时，D 会被转化为小时，计算格式为 D*24 +HH 。当使用带有冒号并且不带 D 的字符串，表示时间时，表示当天的时间，比如 12 : 10 表示 12:10:00 ，而不是 00:12:10 。

• 可以使用不带冒号的字符串或者数字，格式为 HH:MM:SS 或 HH:MM:SS 。

如果插入一个不合法的字符串或者数字，Mysql在存储数据时，会将其自动转化为 00:00:00 进行存储。比如1210，MySQL会将最右边的两位解析成秒 ，表示 00:12:10,而不是 12:10 :00

使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。

---

创建数据表，表中包含一个TIME类型的字段f1。

CREATE TABLE test_time1(
f1 TIME
);

DESC test_time1;  -- 查看表结构
1
2
3
4
5

---

插入时间数据

INSERT INTO test_time1(f1)
VALUES ('2 12:30:29'),   -- 2*24+12 = 60
       ('12:35:36');
1
2
3

---

使用函数 NOW(), CURRENT_TIME(),CURTIME() 插入本地时间

INSERT INTO test_time1(f1)
VALUES (NOW()),
       (CURRENT_TIME()),
       (CURTIME());
1
2
3
4

---

6.4 DATETIME类型：日期时间(年月日 时分秒)

DATETIME 类型在所有的日期时间类型中占用的存储空间最大。总共需要 8 个字节的存储空间。

在格式上 为DATE 类型和 TIME 类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中 YYYY 表示年份，MM表示月份，DD表示日期，HH表示小时，MM 表示分钟，SS表示秒。

在向 DATETIME 类型的字段插入数据时，同样需要满足一定的格式条件。以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串，插入DATETIME 类型的字段时，最小值为 1000-01-01 00:00:00 ，最大值为 9999-12-03 23:59:59 。以YYYYMMDDDHHMMSS 格式的数字插入 DATETIME 类型的字段时，会被转化为 YYYY-MM-DD HH:MM:SS 格式。

使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和 时间。

---

创建数据表，表中包含一个DATETIME 类型的字段 dt

CREATE TABLE test_datetime1(
dt DATETIME
);

DESC test_datetime1; -- 查看表结构
1
2
3
4
5

---

插入日期时间数据

INSERT INTO test_datetime1(dt)
VALUES ('2021-01-01 06:50:30'),  -- 该格式推荐
       (20210101065030);         -- 该格式不推荐
1
2
3

---

使用函数 CURRENT_TIMESTAMP() 和 NOW() ，向DATETIME类型的字段插入系统的当前日期和 时间。

INSERT INTO test_datetime1(dt)
VALUES(NOW()),
      (CURRENT_TIMESTAMP());
1
2
3

---

6.5 TIMESTAMP类型：日期时间(年月日 时分秒)分时区

TIMESTAMP 类型也可以表示日期时间，其显示格式有DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要 4 个字节的存储空间。

但是 TIMESTAMP 存储的时间范围比 DARETIME 要小很多，只能存储到 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 之间的时间，其中 , UTC 表示世界统一时间，也叫作世界标准时间。

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回档期的时区。因此，使用TIMESTAMP 存储的同一个时间值，在不同的时区查询会显示不同的时间。

向 TIMESTAMP 类型的字段插入数据时，当插入的数据格式满足 YY-MM-DD HH:MM:SS 和 YYMMDDHHMMSS时，两位数值的年份同样需要符合 YEAR 类型的规则条件(但是不推荐使用)。只不过表示的时间范围要小很多。如果向 TIMESTAMP 类型的字段插入的时间超过了 TIMESTAMP 类型的范围，则 MySQL会抛出错误信息。

使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向TIMESTAMP类型的字段插入系统的当前日期和 时间。

---

创建数据表，表中包含了一个 TIMESTAMP 类型的字段 ts

CREATE TABLE test_timestamp1(
ts TIMESTAMP
);

DESC test_timestamp1;  -- 查看表结构
1
2
3
4
5

---

插入日期时间数据

INSERT INTO test_timestamp1(ts)
VALUES ('2021-01-1 20:9:04'),
       ('2021@01@1@20@9@04');
1
2
3

---

插入数据 2038-01-20 03:14:07 报错，超过了最大可以存储的日期时间范围：2038-01-19 03:14:07 UTC

INSERT INTO test_timestamp1(ts)
VALUES ('2038-01-20 03:14:07');
1
2

---

使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向TIMESTAMP类型的字段插入系统的当前日期和 时间

INSERT INTO test_timestamp1(ts)
VALUES (NOW()),
       (CURRENT_TIMESTAMP());
1
2
3

---

对比 DATETIME 和 TIMESTAMP

1. TIMESTAMP 存储空间比较小，表示的日期时间范围也比较小
2. 底层存储方式不同，TIMESTAMP 底层存储的时毫秒值，距离 1970 -01-01 00:00:00 毫秒的毫秒值。
3. 两个日期比较大小或日期计算时，TIMESTAMP 更方便，更快。
4. TIMESTAMP 和时区有关。TIMESTAMP 会根据用户的时区不同，显示不同的结果。而 DATETIME则只能反映出插入时的当地的时区，其他时区的人，查看数据必然会有误差的。因为不同的地方纬度不同，时间点也是不同的。

---

下面比较看看 TIMESTAMP 的时区更新

创建一个数据表，含有 DATETIME 类型的 d1, 和 一个 TIMESTAMP 类型的 d2

CREATE TABLE test_time(
d1 DATETIME,
d2 TIMESTAMP
);

DESC test_time;  -- 查看表结构
1
2
3
4
5
6

---

插入数据日期时间，两种类型，插入同样的时间点,查看时间是一样的

INSERT INTO test_time(d1,d2)
VALUES ('2022-09-15 16:55:06','2022-09-15 16:55:06');
1
2

---

使用该方式修改时区

SET time_zone = '+9:00';   -- 修改时区
1

再次查看表中的数据，发现相差了一个小时的时间

SELECT *
FROM test_time;
1
2

---

6.6 日期和时间类型总结

用得最多的日期时间类型，就是 DATEIME 。虽然 MySQL也支持 YEAR(年)，TIME(时间)，DATE(日期)，以及TIMESTAMP类型。

但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型 包括了完整的日期和时间信息，同时取值范围大，使用起来比较方便。

如果将日期时间信息分散在 好几个字段(DATE + TIME) ，很不容易记，而且查询的时候，SQL 语句也会更加复杂。此外，一般存注册时间，商品发布时间等，不建议使用 DATETIME 存储，而是使用 时间戳，因为DATETIME 虽然直观，但不便于计算。

---

7. 字符串类型

在实际的项目中，我们还经常遇到一种数据，就是字符串数据。

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET等类型。

7.1 CHAR 与 VARCHAR类型

CHAR和VARCHAR类型都可以存储比较短的字符串。

• CHAR类型：

CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是 1 个字符 。如果保存时，数据的实际长度比 CHAR 类型指定声明的长度小，则会在右侧填充空格以达到指定的长度。

当MySQL检索 CHAR 类型的数据时，CHAR 类型的字段会去除尾部的空格。定义CHAR类型字段时，声明的字段长度即为 CHAR 类型字段所占的存储空间的字节数。

---

创建数据表，包含一个默认 CHAR 类型 c1,以及一个指定 CHAR(5) 类型的 c2

CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);

DESC test_char1;  -- 查看表结构
1
2
3
4
5
6

---

插入数据字符，c1 插入字符 ab ，报错，超过了 CHAR 默认大小

INSERT INTO test_char1(c1)
VALUES('ab');   -- ab 两个字符超过了 char默认 char(1) 可以存储的一个字符大小
1
2

---

正常插入数据：

INSERT INTO test_char1(c1,c2)
VALUES ('a','ab  ');
1
2

---

MySQL检索时会自动取到尾部的空格计算，如上面我们 c2 添加的是 'ab ’ 在末尾添加了两个空格，我们使用 函数CHAR_LENGTH 查看其字段内容中的个数，结果是 2 把末尾的空格去了。

SELECT CHAR_LENGTH(c2)
FROM test_char1;
1
2

---

中间的空格不会去，会保留

INSERT INTO test_char1(c2)
VALUES ('a   b');

SELECT CHAR_LENGTH(c2)
FROM test_char1;
1
2
3
4
5

---

• VARCHAR 类型

注意：VARCHAR(M) 定义时，必须指定长度 M ，否则会报错。

MySQL4.0 版本以下，varchar(20): 指的是 20 个字符，如果存放的是 utf8 的汉字时，只能存 6 个(每个汉字 占 3 个字节)

MySQL5.0 版本以上，varchar(20): 指的是 20个子字符，检索 VARCHAR 类型的字段数据时，会保留数据尾部的空格 与 CHAR 删除尾部的空格不同 。

varchar类型的字段所占用的存储空间为 字符串实际长度 +1 个字节，因为需要额外占用一个字节存储用来存储字符串的长度，用于变化, utf8 一个汉字占用 3 个字符。所以VARCHAR(M) 实际的 M 最大是 65535 / 3= 21845

注意 `varchar(M) M 的最大值是 21845, 不是 65535,定义时超过了 21845 无法创建的，而是报错

---

创建数据表，包含 VARCHAR字符串类型 name

这里，VARCHAR 的定义没有指明长度，报错

CREATE TABLE test_varchar1( -- 报错, varchar 没有指定长度,无法创建
`name` VARCHAR   -- name 与关键字冲突,使用着重号区分
);
1
2
3

---

这里 varchar 定义的长度为 65535 ，报错,varchar 因为保存字符串的长度占用一个字节，所以实际存储的字符个数需要 65535 / 3 = 21845

CREATE TABLE test_varchar1(
`name` VARCHAR(65535)
);
1
2
3

---

正确创建如下

CREATE TABLE test_varchar1(
`name` VARCHAR(5)  -- name 与关键字冲突了,使用着重号区分
);

DESC test_varchar1;  -- 查看表结构
1
2
3
4
5

---

插入字符串，报错

INSERT INTO test_varchar1(`name`)
VALUES ('尚硅谷IT教育');  -- 报错超过了,varchar(5) 可以存储的 5 个字符个数
1
2

---

正确插入合理范围的字符串

INSERT INTO test_varchar1(`name`)
VALUES ('尚硅谷教育');

1
2
3

---

7.2 字符串总结：CHAR 或 VARCHAR 类型的选择

CHAR 类型，长度是固定的，不会根据实际存储的字符长，变化存储空间，但是在存储效率高，不需要预先计算字符串的长度，直接存储，

VARCHAR(M) 类型长度会根据实际存储的字符串长，变化存储空间，节省空间，但是存储效率不高，需要预先计算出字符串的长度，才能将字符串，存储进去。

• 情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个

  byte用于存储信息长度，本来打算节约存储的，结果得不偿失。

• 情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个 非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

• 从字符串内容角度上考虑

对于长度固定，存储字符串短的采用 CHAR 类型。

对于长度不断长短变化大的，可以考虑采用 VARCHAR(M) 类型

• 从具体存储引擎中的情况上考虑

MylSAM 数据存储引擎和数据列：MylSAM 数据表，最好使用固定长度 CHAR 的数据列代替可变长度 VARCHAR 的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。

MEMORY 存储引擎和数据列：MEMORY 数据表目前都使用固定长度的数据行存储，因此无论使用CHAR 或VARCHAR 列都没有关系，两者都是作为 CHAR 类型处理的。

**InnDB ** 存储引擎，建议使用 VARCHAR 类型。因为对于 InnoDB 数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于 char 平均占用的空间多余于 varchar ，所以除了简短并且固定长度的，其他考虑 varchar。这样节省空间，对磁盘 I/O 和数据存储总量比较好。

---

8. 文本类型

在MySQL中，TEXT用来保存文本类型的字符串，总共包含 4 种类型，分别为 TINYTEXT，TEXT, MEDIUMTEXT 和 LONGTEXT 类型。在向 TEXT 类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。

这一点和 VARCHAR 类型相同。每种 TEXT 类型保存的数据长度和占用的存储空间不同

具体情况如下:

---

注意: 由于实际存储的长度不确定，Mysql不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用 CHAR(M)，或者 VARCHAR(M) 来代替

创建数据表

CREATE TABLE test_text1(
tx TEXT
);

DESC test_text1;  -- 查看表结构
1
2
3
4
5

插入数据，存储的范围比较大，基本上没有什么超过范围的可能

INSERT INTO test_text1(tx)
VALUES ('atguigudb');
1
2

---

8.1 文本类型总结

TEXT 文本类型，可以存比较大的文本短，搜素速度稍慢，因此如果不是特别大的内容，建议使用 CHAR ，VARCHAR 来代替，还有 TEXT类型不用加默认值，加了也没用。

而且 text 和 blob 类型的数据删除后容易导致 ‘空洞’ ，使得文件碎片比较多，所以频繁使用的表不建议包含 TEXT 类型字段，建议可以单独分出去，单独用一个表。

---

9. ENUM类型 (枚举类型)

ENUM 类型也叫作枚举类型，ENUM 类型的取值范围需要在定义字段时进行指定。

设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值.

其所需要的存储空间由定义 ENUM 类型时，指定的成员个数决定。

• 当ENUM 类型包含 1~255个成员时，需要 1个字节的存储空间
• 当ENUM 类型包含 256~65535 个成员时，需要 2 个字节的存储空间。
• ENUM 类型的成员个数的上限为 65535 个。

---

创建数据表，含有 一个 enum 枚举数据类型的 season

CREATE TABLE test_enum (
season ENUM('春','夏','秋','冬','unknow')
);

DESC test_enum;  -- 查看表结构;
1
2
3
4
5

---

插入枚举类型中含有的数据

INSERT INTO test_enum(season)
VALUES ('春'),
       ('冬');
1
2
3

---

不可以插入枚举类型中没有包含的内容，不然，报错

INSERT INTO test_enum(season)
VALUES ('abc');    -- 不可插入枚举类型中没有包含到的内容，不然报错
1
2

---

枚举类型一次性只能插入枚举其中一个内容，不可以插入多个，不然报错

INSERT INTO test_enum(season)
VALUES ('夏','秋');
1
2

---

插入的数据在枚举上不区分大小写

INSERT INTO test_enum(season)
VALUES ('UNKNOW');
1
2

---

可以使用枚举内容上的第几个，索引进行枚举元素的调用

INSERT INTO test_enum(season)
VALUES (2),
       (3);
1
2
3

---

可以使用 NULL 前提是：没有限制非空的情况下，才可以使用

INSERT INTO test_enum(season)
VALUES (NULL);

1
2
3

---

10. SET类型 (集合类型:多个选择)

SET 集合类型：当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下：

SET集合类型在存储数据时成员个数越多，其占用的存储空间越大，

注意：SET 类型在选取成员时，可以一次选择多个成员，这一点与 ENUM 类型(只能选取一个)不同

---

创建数据表，包含一个 set 集合数据类型 为 s

CREATE TABLE test_set (
s SET ('A','B','C')
);

DESC test_set; -- 查看表结构
1
2
3
4
5

---

插入数据，可以一次性插入 1个或多个 集合类型中包含的内容，注意是：一个单引号下，包含了集合类型中的一个或多个内容(内容之间使用逗号分隔开)，不是多个单引号包含一个或多个内容，这样是会不然会报错的

INSERT INTO test_set(s)
VALUES ('A','B','C');  -- 报错,不是多个单引号包含多个内容，而是一单引号包含多个内容
1
2

---

INSERT INTO test_set(s)
VALUES ('A'),
       ('A,B,C');
1
2
3

---

当插入重复的 set 类型成时，MySQL会自动删除重复的成员

INSERT INTO test_set(s)
VALUES ('A,B,C,A,A');  -- 在set集合类型中出现重复的,MySQL会自动删除重复的成员
1
2

---

向 SET 类型中的字段插入 SET 成员中不存在的值时，MySQL会抛出错误

INSERT INTO test_set(s)
VALUES ('666');  -- 666 在set 中是没有的,插入 set 没有的数值，会报错
1
2

---

SET 类型中可以插入 NULL，前提是: 没有非空约束的限制才可以

INSERT INTO test_set(s)
VALUES (NULL);
1
2

---

举例子: ENUM 枚举类型 和 SET 集合类型配合一起使用

创建数据表，包含一个 ENUM枚举类型 gender 和 一个 SET 集合类型 hobby

CREATE TABLE temp_mul(
gender ENUM('男','女'),   -- 枚举
hobby SET ('吃饭','睡觉','工作','敲代码') -- 集合
);

DESC temp_mul;  -- 查看表结构
1
2
3
4
5
6

---

插入数据

INSERT INTO temp_mul(gender,hobby)
VALUES ('男','工作,敲代码'), -- 注意 set 类型是一个单引号多个值,逗号分隔,不是多个单引号,多个值报错
       ('女','睡觉');
1
2
3

SELECT *
FROM temp_mul;
1
2

---

11. 二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片，音频和视频等二进制数据。

MySQL中支持的二进制字符串类型主要包括 BINARY，VARBINARY，TINYBLOB，BLOB，MEDIUMBLOB 和 LONGBLOB 类型。

11.1 BINARY与VARBINARY类型

BINARY 和 VARBIMARY 类似于 CHAR 和 VARCHAR ，只是它们存储的是二进制字符串。

BINARY(M) 为固定长度的二进制字符串，M 表示最多能存储的字节数，取值范围是 0~255个字符。

如果未指定**(M)** ,表示只能存储 1 个字节。例如BINARY(8) ，表示最多能存储 8 个字节，如果字符不足**(M)** ，将在右边填充给**'\0'**(字符串结束标志)，以补齐指定长度。类似于 字符串类型中的 CHAR 类型

VARBINARY(M) 为可变长度的二进制字符串，M 表示最多能存储的字节数，总字节数不能超过行的字节长度限制为 65535 ,另外还有考虑额外字节开销，VARBINARY 类型的数据除了存储数据本身外，还需要 1 或 2 个字节来存储数据的字节数。注意: VARBINARY 类型必须指定(M)，否则报错 ，类似于字符串类型中的 VARCHAR 类型。

---

创建表，包含一个 BINARY 类型，以及一个 VARBINARY 类型

注意：定义 VARBINARY 类型时没有指定范围，会报错

CREATE TABLE test_binary1(
f1 BINARY,
f2 BINARY(5),
f3 VARBINARY,   -- varbinary 为指明范围,创建失败,并报错
f4 VARBINARY(5)
);
1
2
3
4
5
6

---

正确创建数据表

CREATE TABLE test_binary1 (
f1 BINARY,
f2 BINARY(5),
f3 VARBINARY(10)
);

DESC test_binary1;  -- 查看表结构
1
2
3
4
5
6
7

---

插入数据

INSERT INTO test_binary1 (f1,f2,f3)
VALUES ('a','ab','abc');
1
2

---

注意：BINARY 没有指明范围默认是 BINARY(1) 个字节数。不可超过存储范围，不然报错

INSERT INTO test_binary1 (f1)
VALUES ('ab');   -- f1 binary 没有指明范围默认是一个binary(1),这里是两个字符报错
1
2

---

使用函数 LENGTH()查看字符所占的大小

从结果上看，可以发现，BINARY 没有自动收缩长度，而是使用固定长度，而 VARBINARY 收缩了长度

SELECT LENGTH(f1), LENGTH(f2),LENGTH(f3)
FROM test_binary1;
1
2

---

11.2 BLOB二进制大对象

BLOB 是一个二进制大对象，可以容纳可变数量的数据。

MySQL中的BLOB 类型包括 TINYBLOB，BLOB，MEDIUMBLOB 以及 LONGBLOB 4 中类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如 图片，音频以及视频等等。

需要注意的是，在实际工作中，往往不会在MySQL数据库中使用 BLOB 类型存储大对象数据，通常会将图片，音频和视频文件存储到 服务器的磁盘上 ，并将图片，音频和视频的访问路径存储到MySQL中

---

创建数据表，包含 BLOB 类型

CREATE TABLE test_blob1(
id INT,
img BLOB
);

DESC test_blob1;  -- 查看表结构
1
2
3
4
5
6

---

插入数据，BLOB 范围存储比较大，不容易，超过存储范围

INSERT INTO test_blob1 (id,img)
VALUES (1,'1001');

SELECT *
FROM test_blob1;
1
2
3
4
5

---

11.3 TEXT和BLOB的使用注意事项：

在使用 text 和 blob 字段类型时要注意以下几点，以便更好的发挥数据库的性能

1. BLOB 和 TEXT 值也会引起自己的一些问题，特别是执行大量的删除或更新操作的时候。删除这种值 会在数据表中留下很大的："空洞" ，以后填入这些 "空洞"的记录可能长度不同。为了提高性能，建议定期，使用 OPTIMIZE TABLE 功能对这类表进行碎片整理。
2. 如果需要对大文本字段进行模糊查询，MySQL 提供了前缀索引。但是仍然要在不必要的时候避免检索大型的 BLOB 或 TEXT 值。例如：SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的，WHERE 子句只会找到所需要的数据行，否则，你可能毫无目的地在网络上传输大量的值。
3. 把 BLOB 或TEXT 列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就有意义了。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候，不会通过网络传输大量的 BLOB 或 TEXT 的值。

---

12. JSON类型

JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式。

简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 javaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

在 mysql 5,7 中，就已经支持 JSON 数据类型。

在 MySQL 8.x 版本中共，JSON 类型提供了可以进行自动验证的 JSON 文档和优化的存储结构，使得在 MySQL中存储和读取 JSON 类型的数据更加方便和高效。

---

创建数据表，表中包含一个 JSON 类型的字段 js

CREATE TABLE test_json (
js json
);
 
DESC test_json;  -- 查看表结构

1
2
3
4
5
6

---

插入数据，注意其中的格式：

INSERT INTO test_json(js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", 
"city":"beijing"}}');
1
2
3

---

当需要检索 JSON 类型的字段中数据的某个具体值时，可以使用 -> 和 ->> 符号

通过 -> 和 ->> 符号，从 JSON 字段中正确查询出了指定的 JSON 数据的值

SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city
FROM test_json;
1
2

---

13. 空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东 西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如 一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry： 指一个点或点的 集合，代表世界上任何具有位置的事物。

MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型： GEOMETRY、POINT、LINESTRING、POLYGON

以及集合类型： MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION 。

• Geometry是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是Geometry的 子类。
  • Point，顾名思义就是点，有一个坐标值。例如POINT(121.213342 31.234532)，POINT(30 10)， 坐标值支持DECIMAL类型，经度（longitude）在前，维度（latitude）在后，用空格分隔。
  • LineString，线，由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的 （simple）；如果起点和终点重叠，那就是封闭的（closed）。例如LINESTRING(30 10,10 30,40 40)，点与点之间用逗号分隔，一个点中的经纬度用空格分隔，与POINT格式一致。

---

下面展示几种常见的几何图形元素：

• MultiPoint、MultiLineString、MultiPolygon、GeometryCollection 这4种类型都是集合类，是多个

  Point、LineString 或 Polygon 组合而成。

下面展示的是多个同类或异类几何图形元素的组合：

---

14. 扩展: 数据库，数据表，数据列的字符集

创建数据库时，指明其字符集：使用 CHARACTER SET

 CREATE DATABASE IF NOT EXISTS dbtest5 CHARACTER SET 'utf8';
 -- if not exists 判断该数据库是否已经存在,不存在,创建,以存在,不创建,不报错,但给予警告
 -- character set 设定字符集;
1
2
3

SHOW CREATE DATABASE dbtest5;  -- 查看创建的数据库的字符集属性
1

---

同样可以在，创建表的时候,指明其字符集 ： 使用 CHARACTER SET

CREATE TABLE IF NOT EXISTS temp(
id INT 
) CHARACTER SET 'utf8';
-- if not exists 判断该表时候已经存在了，不存在,创建，以存在,不创建,不报错,但给予警告
-- charactear set 创建表时，指定字符集
1
2
3
4
5

SHOW CREATE TABLE temp;  -- 查看创建表的字符集属性;
1

---

也可以在：创建字段时，以同样的方式指明创建字段的字符集

CREATE TABLE temp1 (
id INT,
`name` VARCHAR(5) CHARACTER SET 'gbk'  -- 设定字段的字符集
);
-- character set 创建字段指明字符集
1
2
3
4
5

SHOW CREATE TABLE temp1;  -- 查看创建表的字符集以及其中字段的字符集
1

---

注意:

如果创建字段时没有指明字符集属性 ——> 默认和创建表时的字符集一样

如果创建表时没有指明字符集属性 ——> 默认和 创建的数据库的字符集一样

如果创建数据库时没有指明字符集属性 ——> 默认和 安装 MySQL时的字符集一样

---

15. Mysql 数据类型总结

阿里巴巴《Java开发手册》之MySQL数据库：

• 任何字段如果为非负数，必须是 UNSIGNED (无符号类型)
• 小数类型为 DECIMAL(定点类型)，禁止使用 FLOAT 和 DOUBLE (浮点数类型)

说明: 在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候,得到不正确的结果

如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数，并分开存储

• 如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型
• 如果VARCHAR 是可变长字符串类型，不预先分配存储空间，长度不要超过 5000，如果存储长度大于 5000

该定义为字段类型为 TEXT 独立出来一张表，用主键来对应，避免影响其他字段索引效率。

---

16. 最后:

限于自身水平，其中存在的错误希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见 !!!

---