DBA | MySQL 数据库五种数据类型介绍实践指南

大家好，我是 WeiyiGeek，一名深耕安全运维开发（SecOpsDev）领域的技术从业者，致力于探索DevOps与安全的融合（DevSecOps），自动化运维工具开发与实践，企业网络安全防护，欢迎各位道友一起学习交流、一起进步 ，若此文对你有帮助，一定记得倒点个关注⭐与小红星❤️，收藏学习不迷路  。

MySQL 数据类型

描述：在前面章节中，我们实践了如何创建一个表，并简单定义表中的字段名称，以及简单的类型。此小节将深入讲解 MySQL 8.x 支持的数据类型。

原文链接：https://articles.zsxq.com/id_wj25n3h3ejx7.html

什么是数据类型?为什么要出现数据类型?

定义数据类型的本质是在定义列，因为不同的数据类型可以存储并且高效处理各类数据，并且进行数据处理的方式算法也是有所不同的，这也是关系型数据库的一个特点。

数据分类的意义是什么?

1.分配合适的存储空间，减少磁盘占用，不同的数据类型其存储空间是不一致的。 2.提高查询效率，不同的数据类型其处理方式也是有所差异的。 3.提高数据的安全性，例如：不允许用户输入负数，可以使用无符号类型。 4.提高数据的一致性，例如：不允许用户输入非数字类型的数据。

MySQL 支持的数据类型大致可以分为以下几类：数字类型、字符串 （字符和字节）类型、日期和时间类型、空间类型和 JSON 数据类型。

参考来源: https://dev.mysql.com/doc/refman/8.4/en/data-types.html

1.数字类型

描述：MySQL 支持多种数字类型，每种类型的存储空间和取值范围不同, 其取值范围越大需要的空间也就越多, 此外，特别注意有无符号（UNSIGNED）数据取值范围。

数字类型分类：

位值类型 - BIT : M 表示每个值的位数，据指定的位数(n)来分配存储空间，其取值范围为1 ~ 64，以二进制形式存储，占用空间极小（如BIT(8)仅需1字节），缺省为 1 表示 0 与 1，无符号类型。布尔类型 - BOOL, BOOLEAN : 是 TINYINT(1) 的同义词占用1字节，使用二进制形式存储，0 表示 FALSE，非零值表示 true：整数类型（精确值） - INTEGER、INT、SMALLINT、TINYINT、 MEDIUMINT，BIGINT浮点类型（近似值） - FLOAT、DOUBLE定点类型（精确值） - DECIMAL、NUMERIC

由上表可知，每种类型的取值范围是不同的，开发或者DBA通常需要根据业务需求进行选择字段对应类型。

BIT(M) : 适合于用户性别、状态标记、用户权限、配置参数选项字段，例如 BIT(1) 可用 0 表示关闭、1表示启用。
BOOL, BOOLEAN : 适合于用户状态，例如 false 表示关闭、 true 表示启用。
TINYINT : 适合于存储较小的整数值，例如用户年龄、用户等级等。
SMALLINT : 适合于存储较小的整数值，例如等级、评分等。
MEDIUMINT : 适合于存储中等大小的整数值，例如用户ID、订单号等。
INT, INTEGER : 适合于存储较大的整数值，例如用户ID、订单号等。
BIGINT : 适合于存储非常大的整数值，例如人口信息、订单号、物流号等。
FLOAT : 适合于存储单精度浮点数，例如价格、评分等。
DOUBLE(M,D) : 适合于存储双精度浮点数，例如地理坐标、科学实验数据等，例如 DOUBLE(5,2) 表示最多存储 5 位数字，其中小数点后保留两位。
DECIMAL(M,D) : 适合于存储精确的小数，例如金钱、财务等，例如 DECIMAL(5,2) 表示最多存储 5 位数字，其中小数点后保留两位，取值范围 -999.99 到 999.99。

知识扩展：DOUBLE(M,D) 和 DECIMAL(M,D) 的主要差异体现在存储方式、精度控制和适用场景三个方面。

DOUBLE‌：基于 IEEE 754 标准的双精度浮点数，使用 8 字节存储，以二进制近似存储数值，可能存在舍入误差；其 (M,D) 参数仅影响显示宽度，不限制实际存储范围；计算速度更快，适合科学计算等对性能敏感，但对精度要求不高的场景。

DECIMAL：定点数类型，以字符串形式精确存储数值，每 4 字节存储 9 位数字（如 DECIMAL(18,2) 占 9 字节），完全避免浮点误差；(M,D) 严格限制存储范围（M 为总位数，D 为小数位，且 D ≤ M）；计算开销较大，存储空间随精度增长（如 DECIMAL(65,30) 需要更多字节），但保证精确性。

在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

温馨提示：若为字段配置了自增长属性，则该字段必须是整数无符号(UNSIGNED)类型，即不能为负数。

温馨提示：如果为数字列指定 ZEROFILL（MySQL 8.X 之后将 deprecated ）表示长度不够将自动填充 0，并且 MySQL 会自动将 UNSIGNED 属性添加到该列中。

温馨提示：MySQL 8.0 后已弃用 FLOAT/DOUBLE(M,D) 非标准扩展语法，其 (M,D) 参数仅作兼容保留，实际存储仍按浮点数规则处理，并且FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

示例演示

例1.创建一个 tinyint_test 指定无符号与有符号的字段, 并插入几条数据查看效果。

CREATE TABLE tinyint_test (
  col1 TINYINT,          -- 默认是有符号
  col2 TINYINTUNSIGNED-- 定义无符号
);

-- ERROR 1264 (22003): Out of range value for column 'col1' at row 1
INSERTINTO tinyint_test VALUES (-129,128); 
-- ERROR 1264 (22003): Out of range value for column 'col2' at row 1
INSERTINTO tinyint_test VALUES (-128,256); 
-- 正确插入
INSERTINTO tinyint_test VALUES (-128,255); 

例2.创建一个 zerofill_test 表，当前缀不够时自动填充0，并插入几条数据查看效果。

CREATE TABLE zerofill_test (
  col1 INT(4) ZEROFILL -- 长度不够4时则填充0
);

INSERTINTO zerofill_test VALUES (1234),(123),(12);
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM zerofill_test;
+------+
| col1 |
+------+
| 1234 |
| 0123 |
| 0012 |
+------+

例3.创建一个 float_test 表，并插入几条数据验证浮点数查看效果。

CREATE TABLE float_test (
  col1 FLOAT(3,2),
  col2 DOUBLE(3,2)
); 

INSERTINTO float_test VALUES (6.3,6.3),(6.33,6.33),(6.333,6.333),(6.335,6.335);
Query OK, 4 rows affected (0.15 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM float_test;
+------+------+
| col1 | col2 |
+------+------+
| 6.30 | 6.30 | -- 位数不够采用0补齐
| 6.33 | 6.33 | -- 刚好
| 6.33 | 6.33 | -- 超出了小数范围，只留下两位小数部分
| 6.34 | 6.34 | -- 超出了小数范围，当第三位是5时，则进1变为6.34
+------+------+

-- 此外, 若整数部分超出宽度范围，则插入报错，而小数部分插入时无强预设小数位数限制。
-- ERROR 1264 (22003): Out of range value for column 'col1' at row 1 
INSERTINTO float_test VALUES (66.3,66.3); -- 插入失败，因为小数占两位，总宽度三位，整数部分超出范围多了一位
INSERTINTO float_test VALUES (6.33333,6.33333); -- 插入正常，不受小数位数限制

例4.创建一个 decimal_test 表，并插入几条数据验证定点数查看效果。

CREATE TABLE decimal_test (
  col1 DECIMAL(5,3) ZEROFILL -- 表示 2位整数，3位小数，不足的以 0 填充
);

INSERTINTO decimal_test VALUES (66.55),(66.555),(66.5554),(66.5556);
Query OK, 4 rows affected, 1 warning (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM decimal_test;
+--------+
| col1   |
+--------+
| 66.550 |  -- 位数不足，填充0
| 66.555 |
| 66.555 |  -- 位数超过，只截取指定小数位数
| 66.556 |  -- 位数超过，若小数部分是最后一位大于等于5，则进1变为66.556
+--------+

2.字符串 （字符和字节）类型

描述：MySQL 字符串类型是一个统称它可以包含多种不同的数据类型，大致可以分为两大类：文本字符型和二进制字节型，其中字符类型可存储单个字符、多个字符、数字、字母等，字节类型可存储二进制数据，而二进制字节型可存储图片、音频、视频等二进制数据。

字符串类型分类：

文本字符型 - CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT二进制字节型 - BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB枚举类型 - ENUM、SET

知识扩展：CHAR 与 VARCHAR 的区别，主要体现在存储空间、性能和适用场景三个方面。

CHAR 和 VARCHAR 类型都可以存储字符串，但它们在内部处理和性能上有所不同，前者性能优于后者。 CHAR 和 VARCHAR 类型存储时占用的空间大小不同，前者固定长度，后者可变长度，所以前置适合存储固定长度的字段场景, 如性别，而后者则适合存储长度可变的字段，如用户名。

例如，一个 CHAR(10) 列能保存一个长度为 10 个字符/字节的字符串，无论实际存储少于 10 个字符/字节，都会占用固定的 10 个字节的空间。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符/字节的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度，对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

示例演示

例1.创建一个 char_test 表，并插入几条数据探究CAHR 与 VARCHAR 类型异同。

CREATE TABLEIFNOTEXISTS char_varchar_test (
  col1 CHAR(4),     -- 固定长度4个字符，无论实际存储少于4个字符，都会占用固定的 4 个字节的空间。
  col2 VARCHAR(4)   -- 可变长度4个字符，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。
);

INSERTINTO char_varchar_test VALUES ('ab  ','ab  '),('a b  ','a b  '),('abc','abc'),('abcd','abcd'),('全栈','全栈'),('全栈技术','全栈技术');
Query OK, 6 rows affected, 1 warning (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 1

-- 使用 concat 函数拼接字符串，可以发现 CHAR 和 VARCHAR 在拼接时差异。
mysql> SELECTconcat(col1,'@'),concat(col2,'@') FROM char_varchar_test;
+------------------+------------------+
| concat(col1,'@') | concat(col2,'@') |
+------------------+------------------+
| ab@              | ab  @            |  -- CHAR 自动去除尾部空格，而 VARCHAR 会保留尾部空格(但不超过设置的字符长度)。
| a b@             | a b @            |
| abc@             | abc@             |
| abcd@            | abcd@            |
| 全栈@            | 全栈@            |
| 全栈技术@        | 全栈技术@        |
+------------------+------------------+

SELECT col1,LENGTH(col1) AS'col1 长度',col2,LENGTH(col2) AS'col2 长度'FROM char_varchar_test;
+--------------+-------------+--------------+-------------+
| col1         | col1 长度   | col2         | col1 长度   |
+--------------+-------------+--------------+-------------+
| ab           |           2 | ab           |           4 | -- 同样可以发现，CHAR 自动去除尾部空格，而 VARCHAR 会保留尾部空格(但不超过设置的字符长度)。
| a b          |           3 | a b          |           4 |
| abc          |           3 | abc          |           3 |
| abcd         |           4 | abcd         |           4 |
| 全栈         |           6 | 全栈         |           6 | -- 中文一个字占用长度为3。
| 全栈技术     |          12 | 全栈技术     |          12 | 
+--------------+-------------+--------------+-------------+

温馨提示：当插入的数据超过定义的长度时，MySQL 会自动截断数据，但是需要你在执行插入语句前执行SET sql_mode=''指令，将 sql_mode 变量参数置位空。例如，对于一个 VARCHAR(10) 类型的列，如果尝试插入一个长度为 15 个字符的字符串，那么只有前 10 个字符会被存储。

SHOW variables LIKE 'sql_mode';
-- 修改 sql_mode 后即使插入的长度超过定义的长度，也不会报错，只会截断。
SET sql_mode='';
INSERT INTO chardemo VALUES ('ABCDEFG'，'ABCDEFG'); 

例2.创建 text_test 表，并插入几条数据验证文本类型的差异。

CREATE TABLEIFNOTEXISTS text_test (
  col1 TEXT
);

INSERTINTO text_test VALUES ('weiyigeek'),('WeiyiGeek'),('公众号:全栈工程师修炼指南'),('博客:blog.weiyigeek.top');
Query OK, 4 rows affected (0.18 sec)
Records: 4  Duplicates: 0  Warnings: 0

-- 使用模糊查询的方式，可发现TEXT类型在模糊查询时不区分大小写。
mysql> SELECT * FROM text_test WHERE col1 LIKE'wei%';
+-----------+
| col1      |
+-----------+
| weiyigeek |
| WeiyiGeek |

-- 使用 BINARY 关键字，可区分大小写。
mysql> SELECT * FROM text_test WHERE col1 LIKEBINARY'wei%';
+-----------+
| col1      |
+-----------+
| weiyigeek |

枚举类型

枚举类型，其其本质是文本字符串类型的一种延伸类型，所以我将其放入到字符串类型中进行讲解。

ENUM

 是一个字符串对象，值为表创建时列规定中枚举的一列值。其值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

# 语法格式
<字段名> ENUM( '值1', '值1', …, '值n' )

SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。 与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

# 语法格式
SET( '值1', '值2', …, '值n' )

示例演示，例如，定义 ENUM 类型的列性别，以及 SET 类型的列专业。

CREATE TABLE special_test (
  sex ENUM('F','M','UN'),  -- 插入的值只能是其中之一，分别表示 Female 女、男Male 和未知
  major SET('Java','Python','C++','Go')  -- 插入的值可以是其中一个，也可以是多个值的联合
);

INSERTINTO special_test VALUES ('F','Java,Python'),('M','C++'),('UN',NULL);
mysql> SELECT * FROM special_test;
+------+-------------+
| sex  | major       |
+------+-------------+
| F    | Java,Python |
| M    | C++         |
| UN   | NULL        |
+------+-------------+
3 rows in set (0.00 sec)


-- 若向 ENUM 类型字段插入不合法的值，则会报错 ERROR 1265 (01000): Data truncated for column 'sex' at row 1
INSERTINTO special_test VALUES ('F,M','Java,Python');

综上所述，SET 类型与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合，简单来说，ENUM 类型是单选，而 SET 类型是多选。

温馨提示：ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

# ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。 
值  索引
NULL  NULL
''  0
F  1
M  2
UN  3

温馨提示：如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

3.日期和时间类型

描述：MySQL 支持多种日期和时间类型，包括 YEAR、DATE、TIME、DATETIME 和 TIMESTAMP 等，每种类型都有其特定的用途和存储格式，这也是在企业开发中需要考虑的一个重要方面。

知识扩展: 协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间，英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

知识扩展：TIMESTAMP 与 DATETIME 日期类型区别。

首先，是存储字节和支持的范围不同，TIMESTAMP 占用4个字节，而 DATETIME 占用8个字节。 其次，DATETIME 按实际输入的格式存储 ，而 TIMESTAMP 是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区，即查询时，根据当前时区的不同，显示的时间值是不同的。

温馨提示：若为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分将被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

示例演示

-- 例1.获取当前数据库日期、时间、时间戳函数。

SELECT CURRENT_DATE(), CURRENT_TIME(),CURRENT_TIMESTAMP(); 
+----------------+----------------+---------------------+
| CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
+----------------+----------------+---------------------+
| 2025-09-23     | 09:39:14       | 2025-09-23 09:39:14 |
+----------------+----------------+---------------------+

例2.创建 year_test 表，并插入四位、二位年份(MySQL 8.x以下)查看其差异。

CREATE TABLE year_test(
  col1 YEAR(4),
  col2 YEAR   -- MySQL 8.0 没有 YEAR(2) 默认同上
);
INSERTINTO year_test VALUE (2020,20),(1979,79),(2099,99),(0,0),('0','0'); 
mysql> SELECT * FROM year_test;
+------+------+
| col1 | col2 |
+------+------+
| 2020 | 2020 |
| 1979 | 1979 |
| 2099 | 1999 |  -- 区别点
| 0000 | 0000 |
| 2000 | 2000 |  -- 区别点
+------+------+

例3.创建一个 date_test 表，并插入几条数据验证日期值。

CREATE TABLE date_test(
  col1 DATE
);
-- 注意日期需以引号包含，插入的几种格式都是表示一个时间。
INSERT INTO date_test VALUES ('2020-2-2'),('20-02-02'),('20-2-2'); 
mysql> SELECT * FROM date_test;
+------------+
| col1       |
+------------+
| 2020-02-02 |
| 2020-02-02 |
| 2020-02-02 |
+------------+

例4.创建一个 time_test 表，并插入几条数据验证时间值或持续时间。

CREATE TABLE time_test(
  col1 TIME
);

-- 注意: 单引号中包含空格的区别
INSERTINTO time_test VALUES ('10:59:59'),(1222),('1222'),('2 10:10'),('2 10'),('10'),(10); 
mysql> SELECT * FROM time_test;
+----------+
| col1     |
+----------+
| 10:59:59 |
| 00:12:22 |
| 00:12:22 |
| 58:10:00 |  -- 表示：2 天10小时10分钟
| 58:00:00 |  -- 表示：2 天10小小时
| 00:00:10 |  -- 表示：10秒
| 00:00:10 |  -- 表示：10秒
+----------+

特别注意：若插入的值不能把转换为时间格式，则会报错，例如 INSERT INTO time_test VALUES (6565); 报错信息为：(1292): Incorrect time value: '6565' for column 'col1' at row 4 。

例5.创建一个 timestamp_test 表，并插入几条数据验证时间戳值。

CREATE TABLEIFNOTEXISTS timestamp_test(
  col1 TIMESTAMP
);
INSERTINTO timestamp_test VALUES (NOW()); -- 插入当前系统时间戳值。
SELECT * FROM timestamp_test;
mysql> SELECT * FROM timestamp_test;
+---------------------+
| col1                |
+---------------------+
| 2025-09-24 00:27:36 |
+---------------------+

-- 设置时区为东八区：
SETtime_zone='+8:00'
-- 查看当前系统时区信息：
SHOWvariablesLIKE'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +08:00 |
+---------------+--------+
mysql> SELECT * FROM timestamp_test;
+---------------------+
| col1                |
+---------------------+
| 2025-09-24 08:27:36 |  -- 根据时区进行转换。
+---------------------+

特别注意：此类型会根据当前的系统时间自动转换为UTC格式的时间戳值。

例6.创建一个 datetime_test 表，并插入几条数据验证混合日期和时间值。

CREATE TABLE datetime_test(
  col1 DATETIME
);
-- 由于MySQL支持不严格的语句匹配的特性所以输入日期类型值是非常灵活的;
INSERTINTO datetime_test VALUES ('2020-02-2'),('20-2-2 10:00:59'),('10$10$10 23:59:59'); 
Query OK, 3 rows affected, 1 warning (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM datetime_test;
+---------------------+
| col1                |
+---------------------+
| 2020-02-02 00:00:00 |
| 2020-02-02 10:00:59 |
| 2010-10-10 23:59:59 |
+---------------------+

温馨提示：在实践开发中，尽量避免使用不严格的语句匹配的特性，例如在插入数据时，尽量使用严格的日期格式。其次，空间占用上，DATETIME 类型比 DATE 和 TIME 类型组合多占2字节，所以开发中建议使用DATE+TIME或者TIMESTAMP代替它。

4.空间数据类型

描述：MySQL 支持空间数据类型，用于存储和操作地理空间数据，这些数据类型遵循 Open Geospatial Consortium (OGC) 标准，允许存储点、线、多边形等几何对象。

功能场景

基于其强大的地理空间数据处理能力，支持标准的几何对象类型，提供丰富的空间函数和操作，支持空间索引优化查询性能，遵循 OGC 标准，与其他 GIS 系统兼容，适用于位置服务、地理信息系统、物流规划等多种应用场景。

主要空间数据类型

温馨提示：上表中的存储大小说明，固定大小类型 POINT 固定为 25 字节，可变大小类型是根据坐标点数量和复杂度而变化,其计算公式为：4 + (8 × 坐标点数) + 其他开销字节。

知识扩展：SRID (空间参考系统标识符)，用于指定坐标系，例如 SRID 0：未知坐标系（默认），SRID 4326：WGS84 经纬度坐标系（常用，前者为纬度，后则为经度），SRID 3857：Web Mercator 投影坐标系。

知识扩展：在插入空间类型数据时，需要确保开启了空间支持。例如：SET have_geometry='YES'。 若未开启，则在创建包含空间类型字段的表时会报错。

-- # 查看是否支持空间数据
mysql> SHOW VARIABLES LIKE 'have_geometry';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_geometry | YES   |
+---------------+-------+

-- # 若未启用，则需启用空间数据支持
SET have_geometry='YES'

示例演示

例1.创建一个包含空间数据类型的表，并插入几条数据。

-- 创建包含空间数据的表
CREATETABLE spatial_examples (
idINT AUTO_INCREMENT PRIMARY KEY, -- 主键自增ID
nameVARCHAR(100),
  location POINT, 
  area POLYGON,   
  route LINESTRING,
  created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP
);

-- 1.插入点数据
-- 使用 WKT (Well-Known Text) 格式
INSERTINTO spatial_examples (name, location) 
VALUES ('公司总部', ST_GeomFromText('POINT(89.3974 39.9093)', 4326));
-- 纬度 Latitude [-90.000000, 90.000000]
-- 经度 Longitude (-180.000000, 180.000000]

-- 使用 WKB (Well-Known Binary) 格式
-- 使用 ST_GeomFromWKB 函数
INSERTINTO spatial_examples (name, location) 
VALUES ('公司总部', ST_GeomFromWKB(X'0101000000000000000000F03F000000000000F03F', 4326));

-- 2.插入多边形数据
INSERTINTO spatial_examples (name, area) 
VALUES ('北京区域', ST_GeomFromText('POLYGON((116.0 39.5, 117.0 39.5, 117.0 40.5, 116.0 40.5, 116.0 39.5))'));
-- 使用 ST_PolygonFromText 函数
INSERTINTO spatial_examples (name, area) 
VALUES ('测试区域', ST_PolygonFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));

-- 3.插入线数据
INSERTINTO spatial_examples (name, route) 
VALUES ('通勤路线', ST_GeomFromText('LINESTRING(116.3 39.9, 116.4 39.8, 116.5 39.7)'));

查询插入的空间数据：

-- 查询所有空间数据
mysql> SELECTid, name, ST_AsText(location) as location_wkt FROM spatial_examples;
+----+--------------+-------------------------+
| id | name         | location_wkt            |
+----+--------------+-------------------------+
|  1 | 公司总部     | POINT(89.3974 39.9093)  |
|  2 | 公司总部     | POINT(89.3974 119.9093) |
|  3 | 公司总部     | POINT(1 1)              |
|  4 | 北京区域     | NULL                    |
|  5 | 测试区域     | NULL                    |
|  6 | 通勤路线     | NULL                    |
+----+--------------+-------------------------+

-- 查询特定类型的几何对象
mysql> SELECTname, ST_GeometryType(area) as geometry_type FROM spatial_examples WHERE area ISNOTNULL;
+--------------+---------------+
| name         | geometry_type |
+--------------+---------------+
| 北京区域     | POLYGON       |
| 测试区域     | POLYGON       |
+--------------+---------------+

-- 查找距离某点10公里范围内的地点
SELECTname, ST_AsText(location)
FROM spatial_examples
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(89.3974 39.9093)',4326)) <= 10000;
+--------------+------------------------+
| name         | ST_AsText(location)    |
+--------------+------------------------+
| 公司总部     | POINT(89.3974 39.9093) |
+--------------+------------------------+
1 row in set (0.00 sec)


-- 计算面积（适用于多边形）
mysql> SELECTname, ST_Area(area) as area_size
    -> FROM spatial_examples
    -> WHERE area ISNOTNULL;
+--------------+-----------+
| name         | area_size |
+--------------+-----------+
| 北京区域     |         1 |
| 测试区域     |       100 |
+--------------+-----------+
2 rows in set (0.00 sec)

-- 计算长度（适用于线）
mysql> SELECTname, ST_Length(route) as route_length FROM spatial_examples WHERE route ISNOTNULL;
+--------------+-------------------+
| name         | route_length      |
+--------------+-------------------+
| 通勤路线     | 0.282842712474618 |
+--------------+-------------------+

-- 计算两点间距离(计算精度纬度比较实用)
SELECT
  ST_Distance(
      Point(116.3974, 39.9093),
      Point(121.4737, 31.2304)
  ) as distanc
+--------------------+
| distanc            |
+--------------------+
| 10.054458060979712 |
+--------------------+

-- 获取几何对象的边界
mysql> SELECTname, ST_AsText(ST_Envelope(area)) as bounding_box FROM spatial_examples WHERE area ISNOTNULL;
+--------------+---------------------------------------------------------+
| name         | bounding_box                                            |
+--------------+---------------------------------------------------------+
| 北京区域     | POLYGON((116 39.5,117 39.5,117 40.5,116 40.5,116 39.5)) |
| 测试区域     | POLYGON((0 0,10 0,10 10,0 10,0 0))                      |
+--------------+---------------------------------------------------------+

-- 获取几何对象的中心点
mysql> SELECTname, ST_AsText(ST_Centroid(area)) as centroid FROM spatial_examples WHERE area ISNOTNULL;
+--------------+-----------------+
| name         | centroid        |
+--------------+-----------------+
| 北京区域     | POINT(116.5 40) |
| 测试区域     | POINT(5 5)      |
+--------------+-----------------+

-- 缓冲区分析
SELECTname, ST_AsText(ST_Buffer(location, 0.1)) as buffer_zone
FROM spatial_examples
WHERE location ISNOTNULL;

例子2.商店位置管理与客户服务范围。

CREATE TABLE stores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT SRID 0,
    service_area POLYGON SRID 0
);

-- 插入示例数据
INSERT INTO stores (name, location, service_area) VALUES
('王府井店', Point(116.4170, 39.9090), 
 ST_GeomFromText('POLYGON((116.4 39.9, 116.45 39.9, 116.45 39.92, 116.4 39.92, 116.4 39.9))')),
('中关村店', Point(116.3160, 39.9830),
 ST_GeomFromText('POLYGON((116.3 39.98, 116.33 39.98, 116.33 39.99, 116.3 39.99, 116.3 39.98))'));

-- 查找距离用户最近的商店
SET @user_location = Point(116.4000, 39.9100);
SELECT name, 
       ST_Distance_Sphere(location, @user_location) as distance_meters
FROM stores
ORDER BY distance_meters ASC
LIMIT 3;
+--------------+--------------------+
| name         | distance_meters    |
+--------------+--------------------+
| 王府井店     | 1454.2359317927449 |
| 中关村店     | 10824.284659737623 |
+--------------+--------------------+

-- 查找服务范围包含用户位置的商店
SET @user_location = Point(116.3,39.99);
SELECT name
FROM stores
WHERE ST_Within(@user_location, service_area) = 1;

温馨提示： SQL 中 create_at 字段可在插入记录时自动记录时间戳，只需要在创建表时将其默认值设置为 CURRENT_TIMESTAMP 即可。

温馨提示：在使用空间数据类型时，为保证性能，有如下几个建议，一是为空间列创建空间索引，二是使用边界框预过滤减少计算量，三是避免在 WHERE 子句中对几何对象进行复杂计算。

-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON spatial_examples(location);
CREATE SPATIAL INDEX idx_area ON spatial_examples(area);
CREATE SPATIAL INDEX idx_route ON spatial_examples(route);

-- 查看索引信息
SHOWINDEXFROM spatial_examples;

-- 空间查询使用索引（自动优化）
EXPLAINSELECTnameFROM spatial_examples 
WHERE ST_Within(location, @beijing_polygon) = 1;

5.JSON 数据类型

描述：MySQL 从 5.7.8 版本开始支持 RFC 8259 定义的原生 JSON 数据类型，可以直接存储和查询 JSON 格式数据，其与字符串列中存储 JSON 格式的字符串相比，其优势如下。

原生存储：JSON 数据以优化的二进制格式存储自动验证：插入时会自动验证 JSON 格式的正确性高效访问：支持快速读取和修改 JSON 文档中的特定部分索引支持：可以在 JSON 列上创建函数索引

示例演示

-- 创建包含 JSON 列的表，例如 爱好
CREATETABLE json_test (
    idINT PRIMARY KEY AUTO_INCREMENT,
    nameVARCHAR(50),
    profile JSON,
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP
);

-- 插入 JSON 对象
INSERTINTO json_test (name, profile) VALUES
('admin', '{"age": 25, "city": "北京", "hobbies": ["读书", "游泳"], "contact": {"email": "admin@weiyigeek.top", "phone": "13800138000"}}'),
('master', '{"age": 30, "city": "上海", "hobbies": ["音乐", "旅行"], "contact": {"email": "master@weiyigeek.top", "phone": "13900139000"}}');

-- 使用 JSON_OBJECT 函数
INSERTINTO json_test (name, profile) VALUES
('唯一极客', JSON_OBJECT(
'age', 28,
'city', '重庆',
'hobbies', JSON_ARRAY('技术', '美食'),
'contact', JSON_OBJECT('email', 'wygk@weiyigeek.top', 'phone', '13600136000'),
'blog', 'https://blog.weiyigeek.top',
'wechat', '全栈工程师修炼指南'
));

查询插入的 JSON 数据

-- 提取 JSON 字段值
mysql> SELECTname, profile->'$.city'as city FROM json_test;
| name         | city     |
+--------------+----------+
| admin        | "北京"   |
| master       | "上海"   |
| 唯一极客     | "重庆"   |

-- 使用 JSON_EXTRACT 函数（等价于 -> 操作符）
SELECTname, JSON_EXTRACT(profile, '$.age') as age FROM json_test;
| name         | age  |
+--------------+------+
| admin        | 25   |
| master       | 30   |
| 唯一极客     | 28   |

-- 提取嵌套对象
mysql> SELECTname, profile->'$.contact.phone'as mobile_phone,profile->'$.contact.email'as email FROM json_test;
| name         | mobile_phone  | email                  |
+--------------+---------------+------------------------+
| admin        | "13800138000" | "admin@weiyigeek.top"  |
| master       | "13900139000" | "master@weiyigeek.top" |
| 唯一极客     | "13600136000" | "wygk@weiyigeek.top"     |

-- 提取数组元素
mysql> SELECTname, profile->'$.hobbies[0]'as first_hobby FROM json_test;
| name         | first_hobby |
+--------------+-------------+
| admin        | "读书"      |
| master       | "音乐"      |
| 唯一极客     | "技术"      |

-- 查询年龄大于 25 的用户
SELECTname, profile->'$.age'as age 
FROM json_test 
WHERE JSON_EXTRACT(profile, '$.age') > 25;

-- 查询在北京的用户
SELECTname, profile->'$.city'as city 
FROM json_test 
WHERE profile->'$.city' = '"北京"';

更新 JSON 数据：

-- 更新整个 JSON 文档
UPDATE json_test SET profile = '{"age": 26, "city": "深圳"}'WHEREid = 1;

-- 使用 JSON_SET 更新特定字段
UPDATE json_test SET profile = JSON_SET(profile, '$.age', 31) WHEREname = 'master';

-- 使用 JSON_REPLACE 替换字段值
UPDATE json_test SET profile = JSON_REPLACE(profile, '$.city', '杭州') WHEREname = 'master';

-- 使用 JSON_INSERT 插入新字段
UPDATE json_test SET profile = JSON_INSERT(profile, '$.company', '重庆极客科技有限责任公司') WHEREid = 1;

-- 使用 JSON_REMOVE 删除字段
UPDATE json_test SET profile = JSON_REMOVE(profile, '$.company') WHEREid = 1;

JSON 函数示例:

-- JSON_LENGTH 获取 JSON 文档长度
mysql> SELECTname, JSON_LENGTH(profile->'$.hobbies') as hobby_count FROM json_test;
| name         | hobby_count |
+--------------+-------------+
| admin        |           2 |
| master       |           2 |
| 唯一极客     |           2 |

-- JSON_KEYS 获取所有键
mysql> SELECTname, JSON_KEYS(profile) as profile_keys FROM json_test;
| name         | profile_keys                                            |
+--------------+---------------------------------------------------------+
| admin        | ["age", "city", "contact", "hobbies"]                   |
| master       | ["age", "city", "contact", "hobbies"]                   |
| 唯一极客     | ["age", "blog", "city", "wechat", "contact", "hobbies"] |


-- 使用 JSON_CONTAINS 查询包含特定爱好的用户
SELECTname, profile->'$.hobbies'as hobbies
FROM json_test 
WHERE JSON_CONTAINS(profile->'$.hobbies', '"游泳"');
| name  | hobbies              |
+-------+----------------------+
| admin | ["读书", "游泳"]     |

-- JSON_OBJECT 创建 JSON 对象
SELECT JSON_OBJECT('name', '测试', 'value', 100);

-- JSON_ARRAY 创建 JSON 数组
SELECT JSON_ARRAY('苹果', '香蕉', '橙子');

-- JSON_MERGE_PATCH 合并 JSON 文档（覆盖式）
SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":3, "c":4}');
-- 结果: {"a": 1, "b": 3, "c": 4}

总结：类型选择与使用

根据不同的使用场景（需求），定义列的数据类型，数据类型决定数据的特性（精度），存储空间与查询效率等多个方面来考虑，另外数据类型在不同的存储引擎（InnoDB、MyISAM）表现与处理方式是不同的。

1.字符串类型的选择

根据不同的场景选择不同的字符串类型，例如：CHAR 类型存储经常变更的短字符串效率高，而 VARCHAR 类型存储不定长的长字符串，虽然根据字符串长度消耗不定的存储空间，但导致磁盘碎片增多磁盘IO效率变低，可使用 optimize table 命令定期在数据库层面的碎片整理;

根据不同的数据引擎选择不同的字符串类型，例如：MYISAM 引擎下读多写少的场景下使用 CHAR 类型比VARCHAR 类型性能更好，而在 InnoDB 中两种性能相差不大但是 VARCHAR 类型更节省空间，在 MEMORY 引擎下由于其存放在内存中查询效率高，为节省空间往往也会选择 VARCHAR 类型 。

根据是否需要存储二进制数据选择不同的字符串类型，例如：BINARY 类型用于存储二进制数据，VARBINARY 类型用于存储不定长的二进制数据，针对与大文本数据选择 TEXT 类型，针对与二进制大对象选择 BLOB 类型（不过最好的方式还是采用静态服务器进行存放二进制文件效率更高）。

2.浮点数类型的选择

根据不同的场景选择不同的浮点数类型，数量少的整数选择 TINYINT、SMALLINT、MEDIUMINT 类型，数量多的选择 INT、BIGINT 类型，并且无符号时选择 UNSIGNED，有精度要求选择 DECIMAL 类型，无精度要求的浮点数选择 FLOAT 或 DOUBLE 类型。 例如：DECIMAL 类型适合存储精确的小数（如金钱、财务数据），FLOAT 和 DOUBLE 类型适合存储非精确的大范围小数（地理位置信息）。

3.日期类型的选择

建议使用 DATA 和 TIME 类型，而不是 DATETIME 类型，因为前两者占用空间更小，并且与时区无关。 建议使用 TIMESTAMP 类型，而不是 DATETIME 类型，因为前者占用空间更小，并且可根据时区进行调整，通常在跨国业务场景中使用。

实践案例

请根据需求创建合适的表结构，例如：设置一张员工信息表：

-- 字段定义：
编号：INT 类型，主键
工号: varchar(10) 类型
姓名：varchar(10) 类型
性别: ENUM('男', '女') 类型
年龄：TINYINT UNSIGNED 类型
手机号：CHAR(11) 类型
技能: JSON 类型
身份证号: CHAR(18) 类型 ，唯一键，索引
入职时间：DATE 类

-- 建表语句示例：
CREATETABLEIFNOTEXISTS employee (
idINT(10) UNSIGNEDNOTNULL AUTO_INCREMENT COMMENT'编号',
  uid VARCHAR(10) COMMENT'工号',
nameVARCHAR(10) NOTNULLCOMMENT'姓名',
  gender ENUM('男', '女', '未知') NOTNULLCOMMENT'性别'DEFAULT'未知',
  age TINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT'年龄'CHECK (age > 0AND age < 120),
  phone_number CHAR(11) NOTNULLCOMMENT'手机号'CHECK (phone_number REGEXP '^1[3-9]d{9}$'),
  skills JSONCOMMENT'技能',
  id_card CHAR(18) NOTNULLUNIQUEKEYCOMMENT'身份证号'CHECK (id_card REGEXP '^d{17}[0-9Xx]$'),
  entry_date DATECOMMENT'入职时间',
  PRIMARY KEY (id),
INDEX  idx_id_card (id_card)
) ENGINE=InnoDB AUTO_INCREMENT=1COMMENT='员工信息表';

-- 插入数据示例：
INSERTINTO employee (uid, name, gender, age, phone_number, skills, id_card, entry_date) VALUES ('001', '张三', '男', 30, '13800138000', '{"编程": "高级", "设计": "中级"}', '500102199001010001', '2020-01-01');

-- 查询数据示例：
mysql> SELECT * FROM employee;
+----+------+--------+--------+-----+--------------+------------------------------------------+--------------------+------------+
| id | uid  | name   | gender | age | phone_number | skills                                   | id_card            | entry_date |
+----+------+--------+--------+-----+--------------+------------------------------------------+--------------------+------------+
|  1 | 001  | 张三   | 男     |  30 | 13800138000  | {"编程": "高级", "设计": "中级"}         | 500102199001010001 | 2020-01-01 |
+----+------+--------+--------+-----+--------------+------------------------------------------+--------------------+------------+

至此，我们已经了解了 MySQL 五种数据类型，以及如何在 MySQL 中选择合适的数据类型，希望这些信息对你有所帮助！

加入：作者【全栈工程师修炼指南】知识星球

『 全栈工程师修炼指南』星球，主要涉及全栈工程师（Full Stack Development）实践文章，包括但不限于企业SecDevOps和网络安全等保合规、安全渗透测试、编程开发、云原生（Cloud Native）、物联网工业控制（IOT）、人工智能Ai，从业书籍笔记，人生职场认识等方面资料或文章。