MySQL 数组处理：如何从MySQL查询中构建数组的方法与步骤

🎉 MySQL数组构建全攻略：从理论到实战，轻松玩转数组操作！ 🎉

📚 方法一：JSON大法好（MySQL 5.7+专属）

步骤拆解：

1. 建表🏗️

   CREATE TABLE users (
     id INT PRIMARY KEY,
     hobbies JSON  -- 🚀 JSON类型登场！
   );

2. 插数据💾

   INSERT INTO users (id, hobbies) 
   VALUES (1, '["reading", "swimming", "coding"]');

3. 查数据🔍

   -- 取第一个爱好
   SELECT hobbies->"$[0]" AS first_hobby FROM users WHERE id = 1;
   -- 💡 进阶：遍历所有元素
   SELECT JSON_EXTRACT(hobbies, CONCAT('$[', numbers.n, ']')) AS hobby
   FROM users
   CROSS JOIN (生成数字序列的子查询) numbers  -- 需配合数字辅助表
   WHERE id = 1;

4. 改数据✏️

   -- 末尾追加元素
   UPDATE users 
   SET hobbies = JSON_ARRAY_APPEND(hobbies, '$', 'gaming') 
   WHERE id = 1;

优缺点：
✅ 灵活度高，支持嵌套结构
❌ 大数据量时性能可能打折（需加索引优化）

📜 方法二：字符串模拟大法

步骤拆解：

1. 建表🏗️

   CREATE TABLE users (
     id INT PRIMARY KEY,
     hobbies TEXT  -- 🧵 用TEXT存字符串数组
   );

2. 插数据💾

   INSERT INTO users (id, hobbies) 
   VALUES (1, 'reading,swimming,coding');

3. 查数据🔍

   -- 取第一个爱好（需防越界）
   SELECT 
     SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', 1), ',', -1) 
     AS first_hobby 
   FROM users 
   WHERE id = 1;
   -- 💡 进阶：动态分割（需数字辅助表）
   -- 类似JSON方法的CROSS JOIN逻辑

4. 改数据✏️

   -- 追加元素（需拼接字符串）
   UPDATE users 
   SET hobbies = CONCAT(hobbies, ',gaming') 
   WHERE id = 1;

优缺点：
✅ 兼容所有版本，实现简单
❌ 数据一致性差，查询复杂度高

🗃️ 方法三：关联表正规军

步骤拆解：

1. 建表🏗️

   -- 主表
   CREATE TABLE users (id INT PRIMARY KEY);
   -- 关联表（一对多）
   CREATE TABLE user_hobbies (
     user_id INT,
     hobby VARCHAR(255),
     PRIMARY KEY (user_id, hobby),
     FOREIGN KEY (user_id) REFERENCES users(id)
   );

2. 插数据💾

   -- 主表插入
   INSERT INTO users (id) VALUES (1);
   -- 关联表插入多个爱好
   INSERT INTO user_hobbies (user_id, hobby) 
   VALUES (1, 'reading'), (1, 'swimming'), (1, 'coding');

3. 查数据🔍

   -- 查询用户所有爱好
   SELECT u.id, uh.hobby 
   FROM users u
   JOIN user_hobbies uh ON u.id = uh.user_id
   WHERE u.id = 1;

4. 改数据✏️

   -- 删除某个爱好
   DELETE FROM user_hobbies 
   WHERE user_id = 1 AND hobby = 'coding';

优缺点：
✅ 数据结构清晰，查询效率高
❌ 需维护额外表，写操作稍复杂

🎯 如何选择？

• 选JSON：需要灵活性和复杂查询（如嵌套结构）
• 选关联表：追求数据一致性和高频查询场景
• 选字符串：兼容性要求高或数据量极小

💡 终极建议：
优先用JSON（现代项目标配），高频查询场景加虚拟列索引；传统系统升级可先用字符串过渡，逐步迁移到JSON。

🚨 避坑指南：

• JSON字段避免超大数组（影响性能）
• 字符串分割法慎用动态SQL（防注入风险）
• 关联表记得给(user_id, hobby)加复合索引！

快去试试这些方法，让你的MySQL数组操作6到飞起吧！🚀