很重要！MySQL的复杂数据类型，学会使用再也不怕复杂场景了、枚举(ENUM)、集合(SET)、JSON、空间数据类型

原创

长风清留杨 2024-11-26 23:14:55 博主文章分类：MySQL入门到入魔 ©著作权

文章标签 mysql json android 数据库 sql 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者长风清留杨的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL数据库作为关系型数据库管理系统（RDBMS）中的佼佼者，提供了多种数据类型以满足不同应用场景的需求。本文将深入探讨MySQL中的枚举类型（ENUM）、集合类型（SET）、JSON类型以及空间数据类型

枚举类型（ENUM）

枚举类型是一种用于存储固定值列表的数据类型。它允许将列限制为预定义的一组值之一，只能选择这些值中的一个。枚举类型不仅是字符型数据类型，还具有数据安全性、存储优化和数据可读性的优点。

数据安全性：枚举类型可以限制列的取值范围，确保只有预定义的值被插入。
数据存储优化：枚举类型只存储整数值而不是字符串，从而节省存储空间。
数据可读性：枚举类型将固定值列表以易于理解的方式表示，提高了数据的可读性。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    status ENUM('active', 'inactive', 'suspended') NOT NULL
);

在此示例中，status字段使用了ENUM类型，允许的值为active、inactive和suspended。

ENUM类型的创建和使用

创建包含ENUM类型的表

在创建表时，可以将列定义为ENUM类型，并列出所有可能的值。

CREATE TABLE test (
    name VARCHAR(40),
    sex ENUM('male', 'female')  -- 定义sex列为ENUM类型，只能存储'male'或'female'
);

插入ENUM类型的数据

向包含ENUM字段的表插入数据时，只能插入预定义的值之一。

INSERT INTO test (name, sex) VALUES ('a', 'male'), ('b', 'female'), ('c', 'male');

如果尝试插入一个不在ENUM列表中的值，MySQL将抛出错误。

INSERT INTO test (name, sex) VALUES ('d', 'other');  -- 会报错，因为'other'不在ENUM列表中

查询包含ENUM类型的表

查询ENUM列的数据与查询普通字符串数据类似。

SELECT * FROM test WHERE sex = 'male';  -- 查询sex为'male'的记录

更新ENUM类型的数据

更新ENUM列的数据也与更新普通字符串数据类似。

UPDATE test SET sex = 'female' WHERE name = 'a';  -- 将name为'a'的记录的sex更新为'female'

ENUM类型的特性和注意事项

数据存储方式

ENUM类型的值以整数形式存储，而不是直接存储为字符串。每个枚举值在存储时被映射为一个从1开始的整数。例如，在上面的test表中，'male’对应1，'female’对应2。这种存储方式使得ENUM类型在空间占用上比较紧凑。

读写时不要使用数字

由于ENUM类型使用索引值存储，因此不要用枚举类型来存储数字格式的列，否则会引起很大的混淆。

CREATE TABLE test2 (
    numbers ENUM('0', '1', '2')
);

INSERT INTO test2 (numbers) VALUES (2), ('2'), ('3');  -- '3'不是合法值，会尝试使用索引值，结果可能不是预期的

NULL和空字符串的处理

ENUM类型列可以接受NULL值和空字符串（‘’）作为合法值。空字符串在存储时的索引值为0，而NULL值的索引是NULL。

CREATE TABLE test3 (
    status ENUM('active', 'inactive', 'banned') NULL  -- 允许NULL值
);

INSERT INTO test3 (status) VALUES (NULL), (''), ('active');  -- 插入NULL、空字符串和合法枚举值

枚举值的排序

ENUM类型列在排序时，默认按照索引值进行排序，而不是按照字符串的字母顺序。如果需要按照字母顺序排序，可以使用ORDER BY CAST(col AS CHAR)或ORDER BY CONCAT(col)。

SELECT * FROM test ORDER BY CAST(sex AS CHAR);  -- 按照sex列的字母顺序排序

枚举值的修改

如果需要为现有的ENUM列添加或删除值，可以使用ALTER TABLE语句。但是，新的值必须包含现有的所有值，否则现有的数据可能无法匹配新的枚举列表，导致数据不一致。

ALTER TABLE test MODIFY COLUMN sex ENUM('male', 'female', 'other');  -- 向sex列添加新值'other'

ENUM类型的优点和缺点

优点：

数据完整性：ENUM类型强制数据只能在预定义的列表中选择，避免了存入非法值的情况。
存储效率：ENUM值在MySQL中以整数形式存储，空间占用非常少，尤其当枚举值数量较少时，存储效率高。
代码可读性：使用ENUM可以使代码和数据结构更加清晰，程序员和维护人员可以从数据库结构中很容易地看到一个字段允许的值范围。

缺点：

扩展性差：一旦定义了ENUM类型，如果需要添加新的枚举值，必须使用ALTER TABLE修改表结构。对于大表来说，修改表结构可能会影响性能。
限制性：ENUM类型只适用于值范围固定且相对较小的字段。如果有更复杂的需求（例如支持多选），ENUM不适合使用。

详细示例

以下是一个包含ENUM类型字段的表的完整示例，展示了从创建表到插入、查询和更新数据的完整过程。

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    status ENUM('active', 'inactive', 'banned') NOT NULL DEFAULT 'active'  -- 定义status列为ENUM类型，默认值为'active'
);

-- 插入数据
INSERT INTO users (username, status) VALUES ('alice', 'active'), ('bob', 'inactive'), ('charlie', 'banned');

-- 查询数据
SELECT * FROM users WHERE status = 'active';  -- 查询status为'active'的用户

-- 更新数据
UPDATE users SET status = 'inactive' WHERE username = 'alice';  -- 将username为'alice'的用户的status更新为'inactive'

-- 添加新枚举值（注意：在实际应用中，应谨慎操作，避免数据不一致）
ALTER TABLE users MODIFY COLUMN status ENUM('active', 'inactive', 'banned', 'pending');  -- 向status列添加新值'pending'

集合类型（SET）

集合类型也是一种字符串对象，但它允许将多个值组合在一起。与ENUM不同，SET可以包含零个或多个选定的值。

灵活性：SET类型允许用户选择多个值，并且这些值之间没有互斥关系。
应用场景：SET类型常用于表示具有多个属性的字段，如用户角色、产品标签等。

集合类型的定义

集合类型在定义表结构时使用 SET 关键字，并指定集合中允许的值。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    hobbies SET('reading', 'swimming', 'traveling', 'cooking')
);

在这个例子中，hobbies 列是一个集合类型，可以存储 ‘reading’, ‘swimming’, ‘traveling’, 和 ‘cooking’ 中的零个或多个值。

插入数据

向集合类型列插入数据时，可以插入一个或多个值，值之间用逗号分隔。

-- 插入单个值
INSERT INTO users (name, hobbies) VALUES ('Alice', 'reading');

-- 插入多个值
INSERT INTO users (name, hobbies) VALUES ('Bob', 'swimming,traveling');

-- 插入空集合（即不指定任何值）
INSERT INTO users (name, hobbies) VALUES ('Charlie', '');

查询数据

查询集合类型列时，返回的结果也是逗号分隔的字符串。

SELECT * FROM users;

返回结果如下：

id	name	hobbies
1	Alice	reading
2	Bob	swimming,traveling
3	Charlie

更新数据

更新集合类型列时，可以使用 SET 语句来添加、删除或替换集合中的值。

-- 添加新的值（如果值不存在则添加，存在则忽略）
UPDATE users SET hobbies = CONCAT(hobbies, ',cooking') WHERE name = 'Alice';

-- 删除特定的值（如果值存在则删除，不存在则忽略）
UPDATE users SET hobbies = REPLACE(hobbies, 'swimming,', '') WHERE name = 'Bob';
UPDATE users SET hobbies = TRIM(BOTH ',' FROM REPLACE(hobbies, ',swimming', '')) WHERE name = 'Bob' AND hobbies LIKE '%,swimming,%' OR hobbies = 'swimming';

-- 替换为新的值集合（完全替换原有的值）
UPDATE users SET hobbies = 'reading,cooking' WHERE name = 'Charlie';

注意：在删除集合中的值时，需要小心处理字符串的边界情况，例如开头和结尾的逗号。上面的删除语句中，第二个 UPDATE 语句用于处理集合中只有一个值且该值需要被删除的情况。

查找包含特定值的记录

可以使用 FIND_IN_SET 函数来查找集合中包含特定值的记录。

-- 查找喜欢阅读的用户
SELECT * FROM users WHERE FIND_IN_SET('reading', hobbies) > 0;

-- 查找喜欢游泳或旅行的用户（注意：FIND_IN_SET 不支持 OR 逻辑，需要分别查询然后合并结果或使用 LIKE）
SELECT * FROM users WHERE FIND_IN_SET('swimming', hobbies) > 0 OR FIND_IN_SET('traveling', hobbies) > 0;
-- 或者使用 LIKE（但这种方法效率较低，且不如 FIND_IN_SET 准确）
SELECT * FROM users WHERE hobbies LIKE '%swimming%' OR hobbies LIKE '%traveling%';

注意事项

集合中的值在定义时是大小写敏感的，但在查询时通常不区分大小写（这取决于字符集和排序规则）。
集合中的值不能包含逗号，因为逗号用作分隔符。
集合中的值最多可以有64个，这是MySQL的限制。
空字符串（‘’）被视为一个有效的集合值，表示没有选择任何选项。

完整示例

下面是一个完整的示例，包括创建表、插入数据、更新数据和查询数据：

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    hobbies SET('reading', 'swimming', 'traveling', 'cooking')
);

-- 插入数据
INSERT INTO users (name, hobbies) VALUES ('Alice', 'reading');
INSERT INTO users (name, hobbies) VALUES ('Bob', 'swimming,traveling');
INSERT INTO users (name, hobbies) VALUES ('Charlie', '');

-- 查询数据
SELECT * FROM users;

-- 更新数据
UPDATE users SET hobbies = CONCAT(hobbies, ',cooking') WHERE name = 'Alice';
UPDATE users SET hobbies = REPLACE(hobbies, 'swimming,', '') WHERE name = 'Bob';
UPDATE users SET hobbies = TRIM(BOTH ',' FROM REPLACE(hobbies, ',swimming', '')) WHERE name = 'Bob' AND hobbies LIKE '%,swimming,%' OR hobbies = 'swimming';
UPDATE users SET hobbies = 'reading,cooking' WHERE name = 'Charlie';

-- 再次查询数据
SELECT * FROM users;

-- 查找喜欢阅读的用户
SELECT * FROM users WHERE FIND_IN_SET('reading', hobbies) > 0;

JSON类型

JSON（JavaScript Object Notation）类型是MySQL 5.7.8版本开始支持的一种数据类型。它优化了存储格式，可以快速访问某个元素的值，并且节省网络带宽。

插入操作：
直接插入JSON格式的字符串。
使用函数如JSON_ARRAY()和JSON_OBJECT()。
查询操作：使用JSON_EXTRACT()函数从JSON文档中提取指定路径的元素。

CREATE TABLE mytest (
    id BIGINT,
    other JSON
);

INSERT INTO mytest VALUES (1, '[1, "abc", null, true, "00:00:00.000000"]');

SELECT JSON_EXTRACT(other, '$[0]') AS val FROM mytest;

other字段为JSON类型，通过JSON_EXTRACT()函数可以提取JSON数组中的第一个元素。

JSON类型的优点和缺点

优点：

灵活性：JSON类型允许存储半结构化数据，适用于需要存储复杂数据结构的应用。
易于使用：JSON格式易于人阅读和编写，也易于机器解析和生成。
兼容性：JSON是一种广泛使用的数据交换格式，与许多编程语言和框架兼容。

缺点：

性能：对于大型JSON文档和复杂的查询，性能可能不如传统关系型数据。
索引限制：虽然可以对JSON列进行索引，但索引的使用受限于JSON路径表达式。
数据验证：虽然MySQL会自动验证JSON数据的有效性，但错误的数据插入仍然可能导致应用错误。

JSON类型的创建和使用

创建包含JSON类型的表

在创建表时，可以将列定义为JSON类型。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    details JSON  -- 定义details列为JSON类型
);

插入JSON类型的数据

向包含JSON字段的表插入数据时，可以插入有效的JSON字符串。

INSERT INTO employees (name, details) VALUES 
('Alice', '{"age": 30, "department": "HR", "skills": ["communication", "organization"]}'),
('Bob', '{"age": 25, "department": "Engineering", "skills": ["coding", "debugging", "designing"]}');

查询JSON类型的数据

查询JSON列的数据时，可以使用MySQL提供的JSON函数和操作符。

-- 查询所有员工
SELECT * FROM employees;

-- 查询年龄大于25岁的员工
SELECT * FROM employees WHERE JSON_EXTRACT(details, '$.age') > 25;

-- 查询属于HR部门的员工
SELECT * FROM employees WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$.department')) = 'HR';

注意：JSON_EXTRACT函数用于从JSON文档中提取数据， $很重要！MySQL的复杂数据类型，学会使用再也不怕复杂场景了、枚举(ENUM)、集合(SET)、JSON、空间数据类型_mysql$ .department是JSON路径表达式，分别表示提取age和department字段的值。JSON_UNQUOTE函数用于去除JSON值的引号。

更新JSON类型的数据

更新JSON列的数据时，可以使用JSON_SET、JSON_REPLACE、JSON_REMOVE等函数。

-- 为Alice添加一个新的技能
UPDATE employees SET details = JSON_SET(details, '$.skills', JSON_ARRAY_APPEND(JSON_EXTRACT(details, '$.skills'), '$', 'teaching')) WHERE name = 'Alice';

-- 将Bob的部门更改为Sales
UPDATE employees SET details = JSON_REPLACE(details, '$.department', 'Sales') WHERE name = 'Bob';

-- 删除Alice的年龄信息
UPDATE employees SET details = JSON_REMOVE(details, '$.age') WHERE name = 'Alice';

详细示例

以下是一个包含JSON类型字段的表的完整示例，展示了从创建表到插入、查询和更新数据的完整过程。

-- 创建表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_details JSON  -- 定义order_details列为JSON类型
);

-- 插入数据
INSERT INTO orders (customer_name, order_details) VALUES 
('John Doe', '{"products": [{"product_id": 1, "quantity": 2, "price": 100}, {"product_id": 2, "quantity": 1, "price": 200}], "total_amount": 400}'),
('Jane Smith', '{"products": [{"product_id": 3, "quantity": 3, "price": 50}], "total_amount": 150}');

-- 查询所有订单
SELECT * FROM orders;

-- 查询总金额大于200的订单
SELECT * FROM orders WHERE JSON_EXTRACT(order_details, '$.total_amount') > 200;

-- 查询包含特定产品的订单
SELECT * FROM orders WHERE JSON_CONTAINS(order_details->'$.products', '{"product_id": 2}', '$');

-- 更新订单详情（为John Doe的订单添加一个新产品）
UPDATE orders SET order_details = JSON_SET(order_details, '$.products', JSON_ARRAY_APPEND(JSON_EXTRACT(order_details, '$.products'), '$', '{"product_id": 4, "quantity": 1, "price": 150}')) WHERE customer_name = 'John Doe';

-- 删除订单中的某个产品（删除Jane Smith订单中的产品ID为3的产品）
SET @json_path = '$.products[* ? (@.product_id == 3)]';  -- JSON路径表达式，匹配product_id为3的产品
UPDATE orders SET order_details = JSON_REMOVE(order_details, @json_path) WHERE customer_name = 'Jane Smith';

注意：在上面的示例中，使用了JSON路径表达式和MySQL的JSON函数来操作JSON数据。JSON_ARRAY_APPEND函数用于向JSON数组中添加元素，JSON_CONTAINS函数用于检查JSON文档中是否包含特定的值或对象，JSON_REMOVE函数用于从JSON文档中删除指定的值或对象。

JSON类型的特性和注意事项

数据验证

MySQL会自动验证插入到JSON列的数据是否为有效的JSON格式。如果数据不是有效的JSON，MySQL将拒绝插入并返回错误。

索引和查询性能

虽然MySQL允许对JSON列进行索引，但索引的使用受限于JSON路径表达式。对于复杂的JSON查询，性能可能不如传统关系型数据。

JSON函数和操作符

MySQL提供了一系列JSON函数和操作符来操作JSON数据，包括JSON_EXTRACT、JSON_UNQUOTE、JSON_SET、JSON_REPLACE、JSON_REMOVE、JSON_CONTAINS、JSON_ARRAY、JSON_OBJECT等。

存储和性能

JSON类型的数据以文本形式存储，对于大型JSON文档，存储和检索可能会比存储结构化数据更慢。因此，在设计数据库时，应根据具体需求选择合适的数据类型。

空间数据类型

空间数据类型用于存储和处理地理空间数据，如地理位置、几何形状和地理空间关系等。MySQL中的空间数据类型主要包括GEOMETRY、POINT、LINESTRING、POLYGON等。

空间数据类型	描述
GEOMETRY	抽象数据类型，表示任何几何形状
POINT	表示二维或三维空间中的点
LINESTRING	表示二维或三维空间中的线
POLYGON	表示二维或三维空间中的多边形
MULTIPOINT	表示多个点的集合
MULTILINESTRING	表示多个线的集合
MULTIPOLYGON	表示多个多边形的集合
GEOMETRYCOLLECTION	表示几何形状的集合，可包含不同类型的形状

应用场景：

地图绘制：存储地图上的点、线、多边形等几何形状。
空间查询：查找距离某个点一定范围内的其他点，或查找与某个多边形相交的其他多边形。
空间分析：计算两个多边形的面积，或计算两个点之间的距离。

CREATE TABLE spatial_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    geometry_column GEOMETRY
);

INSERT INTO spatial_data (geometry_column) VALUES (ST_GeomFromText('POINT(1 1)'));

SELECT * FROM spatial_data WHERE ST_Contains(geometry_column, ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

geometry_column字段为GEOMETRY类型，用于存储地理空间数据，并通过ST_Contains()函数进行空间查询。

创建表时定义空间数据类型

在创建表时，可以使用这些数据类型来定义列。

CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT,
    path LINESTRING,
    area POLYGON,
    spots MULTIPOINT,
    routes MULTILINESTRING,
    regions MULTIPOLYGON,
    geo_collection GEOMETRYCOLLECTION
);

插入空间数据

插入空间数据时，通常使用 ST_GeomFromText 函数来创建空间对象。这个函数接受一个 Well-Known Text (WKT) 格式的字符串，并返回一个空间对象。

-- 插入一个点
INSERT INTO locations (name, location) VALUES ('Central Park', ST_GeomFromText('POINT(40.7829 -73.9654)'));

-- 插入一条线
INSERT INTO locations (name, path) VALUES ('Fifth Avenue', ST_GeomFromText('LINESTRING(40.7128 -74.0060, 40.7829 -73.9654)'));

-- 插入一个多边形
INSERT INTO locations (name, area) VALUES ('Central Park Polygon', ST_GeomFromText('POLYGON((40.786 -73.965, 40.786 -73.964, 40.785 -73.964, 40.785 -73.965, 40.786 -73.965))'));

注意：WKT 格式使用括号 () 来表示点、线和多边形的坐标，坐标之间用逗号 , 分隔，并且纬度在前，经度在后。对于多边形，外部坐标表示闭合环，内部坐标（如果有的话）表示孔。

查询空间数据

MySQL 提供了一系列函数来查询和操作空间数据。例如，可以使用 ST_Distance 来计算两个点之间的距离，使用 ST_Contains 来检查一个多边形是否包含另一个几何对象，等等。

-- 查询与 Central Park 距离小于 1 公里的点
SELECT name, ST_AsText(location) FROM locations
WHERE ST_Distance(location, ST_GeomFromText('POINT(40.7829 -73.9654)')) < 1000;

-- 查询包含某个点的多边形
SELECT name FROM locations
WHERE ST_Contains(area, ST_GeomFromText('POINT(40.785 -73.965)'));

注意：ST_AsText 函数用于将空间对象转换为 WKT 格式的字符串，以便在查询结果中查看。

更新空间数据

更新空间数据通常涉及使用空间函数来构造新的空间对象，并将其赋值给列。

-- 将 Central Park 的位置更新为一个新的点
UPDATE locations SET location = ST_GeomFromText('POINT(40.7830 -73.9653)') WHERE name = 'Central Park';

删除空间数据

删除空间数据与删除普通数据相同，使用 DELETE 语句即可。

-- 删除名为 Fifth Avenue 的路径
DELETE FROM locations WHERE name = 'Fifth Avenue';

完整示例

下面是一个完整的示例，包括创建表、插入数据、查询数据和更新数据

-- 创建表
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT,
    SPATIAL INDEX(location)  -- 创建空间索引
);

-- 插入数据
INSERT INTO locations (name, location) VALUES ('Central Park', ST_GeomFromText('POINT(40.7829 -73.9654)'));
INSERT INTO locations (name, location) VALUES ('Times Square', ST_GeomFromText('POINT(40.758 -73.9855)'));

-- 查询与 Central Park 距离小于 2 公里的点
SELECT name, ST_AsText(location) FROM locations
WHERE ST_Distance(location, ST_GeomFromText('POINT(40.7829 -73.9654)')) < 2000;

-- 更新 Times Square 的位置
UPDATE locations SET location = ST_GeomFromText('POINT(40.7581 -73.9854)') WHERE name = 'Times Square';

-- 再次查询与 Central Park 距离小于 2 公里的点
SELECT name, ST_AsText(location) FROM locations
WHERE ST_Distance(location, ST_GeomFromText('POINT(40.7829 -73.9654)')) < 2000;

注意事项

索引：为了提高空间查询的性能，可以对空间列创建空间索引。例如，使用 SPATIAL INDEX 关键字。CREATE SPATIAL INDEX idx_location ON locations(location);但是，请注意，并非所有存储引擎都支持空间索引（例如，InnoDB 支持，而 MyISAM 不支持）。
单位：空间函数和操作符的结果通常依赖于所使用的坐标系统（例如，WGS 84 用于 GPS 坐标）。在大多数情况下，距离计算默认使用米作为单位。
空值：空间列可以包含 NULL 值，表示没有存储任何空间数据。
函数和操作符：MySQL 提供了丰富的空间函数和操作符来处理空间数据，包括计算距离、面积、周长、判断空间关系（如相交、包含等）等。