MySQL怎么存数组|极客笔记

MySQL怎么存数组

在实际开发中，我们经常需要将数组这种数据结构存储到数据库中。虽然MySQL并没有直接支持数组数据类型，但是我们可以通过一些方法来实现这个功能。本文将详细介绍如何在MySQL中存储数组。

方法一：使用JSON数据类型

MySQL从5.7.8版本开始支持JSON数据类型，可以存储JSON格式的数据。我们可以将数组转换成JSON字符串，然后存储到JSON类型的字段中。下面是一个示例：

CREATE TABLE `test_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test_json` (`data`) VALUES
  ('["apple", "banana", "orange"]'),
  ('["red", "green", "blue"]');

查询结果如下：

SELECT * FROM test_json;

+----+-----------------------------+
| id | data                        |
+----+-----------------------------+
|  1 | ["apple", "banana", "orange"]   |
|  2 | ["red", "green", "blue"]        |
+----+-----------------------------+

可以看到，我们成功将数组存储到了JSON类型的字段中。

方法二：使用多对多关联表

另一种常见的方法是使用多对多关联表。假设我们有一个用户表和一个兴趣爱好表，用户可以有多个兴趣爱好，一个兴趣爱好也可以被多个用户拥有。我们可以创建一个中间表来存储用户和兴趣爱好的关系。下面是一个示例：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `interests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_interests` (
  `user_id` int(11) NOT NULL,
  `interest_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`, `interest_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`interest_id`) REFERENCES `interests`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`name`) VALUES
  ('Alice'),
  ('Bob');

INSERT INTO `interests` (`name`) VALUES
  ('Music'),
  ('Reading');

INSERT INTO `user_interests` (`user_id`, `interest_id`) VALUES
  (1, 1),
  (1, 2),
  (2, 2);

查询结果如下：

SELECT u.name, GROUP_CONCAT(i.name) as interests
FROM users u
JOIN user_interests ui ON u.id = ui.user_id
JOIN interests i ON ui.interest_id = i.id
GROUP BY u.id;

+-------+------------------+
| name  | interests        |
+-------+------------------+
| Alice | Music,Reading    |
| Bob   | Reading          |
+-------+------------------+

通过中间表的方式，我们成功地实现了将数组数据存储到数据库中的功能。

方法三：使用字符串拼接

如果数组元素较少且顺序不重要，我们也可以将数组元素用逗号或其他分隔符拼接成一个字符串，然后存储到数据库中。在读取数据时再根据分隔符将字符串拆分成数组。这种方式虽然简单但是不够灵活，不适合处理复杂的数组数据。下面是一个示例：

CREATE TABLE `test_string` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test_string` (`data`) VALUES
  ('apple,banana,orange'),
  ('red,green,blue');

SELECT * FROM test_string;

+----+------------------+
| id | data             |
+----+------------------+
|  1 | apple,banana,orange |
|  2 | red,green,blue       |
+----+------------------+