如何在 MySQL 中存储数组

Rayven Esplanada 2023年1月30日 2020年12月31日
  1. 在 MySQL 中模拟数组作为关系存储数组的方法
  2. 在不同的 SQL 数据类型中存储数组
如何在 MySQL 中存储数组

本教程向你展示如何在 MySQL 中存储或模拟数组作为一个字段。

SQL 在它自己的语言中并没有明确地支持数组作为一种数据类型,但由于它是一个关系型数据库,所以有很多变通的方法来实现它。

像 SQL 这样的关系型数据库是利用关系和键来工作的。大多数时候,我们利用这些关系将多个值连接到它相关的单一行。

关系是数组的去耦版本,它很好地配合了 SQL 的性质和设计(参见规范化)。这就是为什么在 SQL 中没有数组数据类型的原因,因为更多的时候,不需要数组,因为关系的存在。

如果使用或操作不当,数据库中的数组会很危险。你可以在没有它们的情况下实现你的数据库,并且拥有一个非常优化的数据库。

如果你真的想或需要存储数组,这里有一些方法可以做到。

在 MySQL 中模拟数组作为关系存储数组的方法

如果我们要遵循 SQL 的性质和惯例,那么应该把数组当作关系来处理。

比如说我们有一个客户,他在一家餐馆有多个订单。

首先我们建立 customerorder 表。

CREATE TABLE customer (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);

CREATE TABLE order (
`order_id` INT NOT NULL PRIMARY KEY,
`order` VARCHAR(40),
`price` DOUBLE
);

客户和他们的订单表现出一对多的关系,要实现这一点,我们需要一个关联实体来连接这两个表,使它们相互关联。

CREATE TABLE customer_order (
`customer_id` INT NOT NULL,
`order_id` INT NOT NULL,
PRIMARY KEY(`customer_id`, `order_id`)
);

假设表是这样填充的:

  • customer
id name
1 John Doe
2 Jane Doe
  • order
order_id order price
1 Scallops 35.00
2 Lobster 120.00
3 Steak 80.00
4 Cheesecake 25.00
  • customer_order
customer_id order_id
1 1
1 2
1 4
2 3
2 4

从这些表中,我们可以关联 John Doe 订购扇贝、龙虾和芝士蛋糕。同时,Jane Doe 点了牛排和芝士蛋糕。

如果我们想查询 John Doe 点过的所有东西,我们只需使用 SELECT JOIN 查询即可。

SELECT c.name, o.order
FROM customer c
INNER JOIN customer_order co
ON co.customer_id = c.customer_id
INNER JOIN order o
ON o.order_id = co.order_id
WHERE name = 'John Doe'

查询将生成以下结果:

name order
John Doe Scallops
John Doe Lobster
John Doe Cheesecake

本质上,关系是 SQL 版本的数组。所以,如果你曾经想保持良好的设计,请理解关系的概念,并尽可能地使用它们。

在不同的 SQL 数据类型中存储数组

在 MySQL 5.7 更新期间及以后,该语言现在支持 JSON 作为一种数据类型。JSON 为 SQL 提供了一种方便的方法来存储复杂的数据类型,如数组、集合、Map、字典等。

在 SQL 中模拟数组的最快方法是将其存储为字符串。

创建 customerorder 表。

CREATE TABLE customer (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50),
`order` VARCHAR(999)
);

CREATE TABLE order (
`id` INT NOT NULL PRIMARY KEY,
`order` VARCHAR(50),
`price` DOUBLE
);

每次客户下单时,使用 GROUP_CONCAT() 在客户的 order 字段中存储一个新的条目。

UPDATE customer
SET order = CONCAT(order, ', Cheesecake');

UPDATE customer
SET order = CONCAT(order, ', Ravioli');

如果你用订单查询客户,结果将显示:

name order
John Doe ,Cheesecake, Ravioli

这个方法本质上是一个数组类型被存储在一个列中。然而,这将意味着你没有遵循关系型数据库的惯例,而 SQL 是这样的。

这是一种在 SQL 中存储数组的有效方法。不过,当你可以轻松地将数组存储在一个新的表中并配置关系和约束时,强行将数组存储在关系数据库中还是被认为是不好的设计。

Rayven Esplanada avatar Rayven Esplanada avatar

Skilled in Python, Java, Spring Boot, AngularJS, and Agile Methodologies. Strong engineering professional with a passion for development and always seeking opportunities for personal and career growth. A Technical Writer writing about comprehensive how-to articles, environment set-ups, and technical walkthroughs. Specializes in writing Python, Java, Spring, and SQL articles.

LinkedIn