如何在 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