mysql函数-连接和递归

------------------------------------------------------------

在 MySQL 中，连接函数主要用于字符串拼接、表连接操作以及处理连接相关的系统函数。下面为你详细介绍这些函数的分类和使用方法：

一、字符串连接函数

1. `CONCAT(str1, str2, ...)`

用于拼接多个字符串。如果参数中有 NULL，则返回 NULL。

示例：

sql
 
SELECT CONCAT('Hello', ' ', 'World'); -- 输出：Hello World
SELECT CONCAT('Name:', NULL, 'Age');   -- 输出：NULL

2. `CONCAT_WS(separator, str1, str2, ...)`

使用指定的分隔符 separator 拼接字符串，忽略 NULL 值。

示例：

sql
 
SELECT CONCAT_WS('-', '2023', '05', '15'); -- 输出：2023-05-15
SELECT CONCAT_WS(',', 'Apple', NULL, 'Banana'); -- 输出：Apple,Banana

二、表连接操作（JOIN）

用于结合两个或多个表中的记录，基于它们之间的关联字段。

1. `INNER JOIN`

返回两个表中匹配的记录。

示例：

sql
 
SELECT *
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

2. `LEFT JOIN` / `LEFT OUTER JOIN`

返回左表的所有记录，以及右表中匹配的记录（若没有匹配则右表字段为 NULL）。

示例：

sql
 
SELECT *
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

3. `RIGHT JOIN` / `RIGHT OUTER JOIN`

与 LEFT JOIN 相反，返回右表的所有记录和左表匹配的记录。

4. `FULL OUTER JOIN`

MySQL 不直接支持 FULL OUTER JOIN，但可以通过 UNION 模拟：

sql
 
SELECT * FROM table1 LEFT JOIN table2 ON ...
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON ...;

5. `CROSS JOIN`

返回两个表的笛卡尔积（所有可能的组合）。

示例：

sql
 
SELECT * FROM table1 CROSS JOIN table2;

三、连接相关的系统函数

1. `CONNECTION_ID()`

返回当前连接的唯一标识符。

示例：

sql
 
SELECT CONNECTION_ID(); -- 输出当前连接的 ID（如：256）

2. `GET_LOCK(name, timeout)`

获取一个命名锁，成功返回 1，失败返回 0，超时返回 NULL。

示例：

sql
 
SELECT GET_LOCK('my_lock', 10); -- 尝试获取锁，超时时间 10 秒

3. `RELEASE_LOCK(name)`

释放指定的命名锁。

示例：

sql
 
SELECT RELEASE_LOCK('my_lock'); -- 释放锁

四、使用场景示例

1. 字符串拼接示例

sql
 
-- 拼接用户姓名和邮箱
SELECT CONCAT_WS(' - ', first_name, last_name, email) AS user_info
FROM users;

2. 表连接示例

sql

-- 查询每个客户的订单数量
SELECT customers.name, COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;

注意事项

字符串连接：
- CONCAT 遇到 NULL 会返回 NULL，而 CONCAT_WS 会忽略 NULL。
- 拼接大量字符串时，性能可能受影响。
表连接：
- 确保关联字段有索引，否则可能导致全表扫描。
- 使用 JOIN 时注意结果集的大小（如 CROSS JOIN 可能产生大量数据）。
锁函数：
- 锁只在当前连接有效，连接关闭时自动释放。
- 避免死锁，确保按相同顺序获取锁。

-----------------------------------------------------------

在 MySQL 中，处理层级数据（如树形结构）通常需要使用递归查询或特定的连接策略。以下是几种常用的层级连接方法：

一、递归 CTE（MySQL 8.0+）

使用 WITH RECURSIVE 关键字实现递归查询，适用于已知父节点找所有子节点或已知子节点找所有父节点。

1. 查询子节点（向下递归）

sql
 
 

WITH RECURSIVE sub_tree AS (
    -- 初始查询：根节点
    SELECT id, parent_id, name, 1 AS level
    FROM your_table
    WHERE id = 1  -- 根节点 ID
    
    UNION ALL
    
    -- 递归查询：子节点
    SELECT t.id, t.parent_id, t.name, st.level + 1
    FROM your_table t
    JOIN sub_tree st ON t.parent_id = st.id
)
SELECT * FROM sub_tree;

 

2. 查询父节点（向上递归）

sql
 
 

WITH RECURSIVE parent_tree AS (
    -- 初始查询：子节点
    SELECT id, parent_id, name, 1 AS level
    FROM your_table
    WHERE id = 10  -- 子节点 ID
    
    UNION ALL
    
    -- 递归查询：父节点
    SELECT t.id, t.parent_id, t.name, pt.level + 1
    FROM your_table t
    JOIN parent_tree pt ON t.id = pt.parent_id
    WHERE t.parent_id IS NOT NULL  -- 终止条件
)
SELECT * FROM parent_tree;

 

二、邻接表模型（Adjacency List）

最常见的层级存储方式，通过 parent_id 字段关联父节点。

示例表结构：

sql
 
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

查询直接子节点：

sql
 
SELECT * FROM categories WHERE parent_id = 1;

查询所有层级（需多次连接）：

sql
 
 

SELECT 
    c1.name AS level1,
    c2.name AS level2,
    c3.name AS level3
FROM categories c1
LEFT JOIN categories c2 ON c2.parent_id = c1.id
LEFT JOIN categories c3 ON c3.parent_id = c2.id
WHERE c1.parent_id IS NULL;  -- 从根节点开始

 

三、路径枚举模型（Path Enumeration）

通过存储完整路径（如 1/2/3）快速查询所有层级。

示例表结构：

sql
 
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    path VARCHAR(255)  -- 存储路径，如 "1/2/3"
);

查询子节点：

sql
 
SELECT * FROM employees WHERE path LIKE '1/%';  -- 所有以 "1/" 开头的节点

查询层级深度：

sql
 
SELECT id, name, LENGTH(path) - LENGTH(REPLACE(path, '/', '')) AS depth
FROM employees;

四、嵌套集模型（Nested Sets）

通过左右值（lft 和 rgt）快速查询子树，但插入 / 更新成本高。

示例表结构：

sql
 
CREATE TABLE nested_categories (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    lft INT,
    rgt INT
);

查询子树：

sql
 
SELECT *
FROM nested_categories
WHERE lft BETWEEN 2 AND 9  -- 假设根节点的 lft=2, rgt=9
ORDER BY lft;

五、JSON 存储（MySQL 5.7+）

将层级结构存储为 JSON，适合灵活的树形数据。

示例表结构：

sql
 
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    structure JSON
);

示例数据：

sql
 
INSERT INTO departments (id, name, structure)
VALUES (1, 'IT', '{"name":"IT","children":[{"name":"Dev"},{"name":"Ops"}]}');

查询 JSON 层级：

sql
 
SELECT 
    id,
    JSON_EXTRACT(structure, '$.name') AS dept_name,
    JSON_EXTRACT(structure, '$.children[0].name') AS first_child
FROM departments;

六、性能对比

方法	查询子树	查询父节点	插入 / 更新	适用场景
递归 CTE	快	快	中等	动态层级，MySQL 8.0+
邻接表	慢	快	快	简单层级，少量层级查询
路径枚举	快	中等	中等	频繁查询子树
嵌套集	极快	慢	慢	只读或极少更新的树
JSON 存储	灵活	依赖索引	灵活	结构多变的树形数据

七、选择建议

推荐方案：优先使用递归 CTE + 邻接表（MySQL 8.0+），简单且高效。
复杂查询：若需频繁跨层级查询，考虑路径枚举或嵌套集。
灵活结构：使用 JSON 存储，但需熟悉 JSON 函数。

------------------------------------------------------------

GROUP_CONCAT 是 MySQL 中用于将分组后的多行数据合并为单个字符串的聚合函数。它常用于需要将关联表中的数据合并为一个字段展示的场景。

基本语法

sql
 
GROUP_CONCAT(
    [DISTINCT] expr [, expr ...]
    [ORDER BY expr [ASC | DESC] [, expr ...]]
    [SEPARATOR str_val]
)

DISTINCT：可选，去重。
ORDER BY：可选，指定合并时的排序规则。
SEPARATOR：可选，指定分隔符（默认是逗号 ,）。

示例数据

假设有以下两张表：

sql
 
 

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    student_id INT,
    course_name VARCHAR(50),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO courses VALUES (1, 'Math'), (1, 'Physics'), (2, 'English');

 

常见用法

1. 合并分组后的字符串

sql
 
SELECT 
    s.name,
    GROUP_CONCAT(c.course_name) AS courses
FROM students s
LEFT JOIN courses c ON s.id = c.student_id
GROUP BY s.id;

结果：

plaintext
 
+-------+------------------+
| name  | courses          |
+-------+------------------+
| Alice | Math,Physics     |
| Bob   | English          |
+-------+------------------+

2. 指定分隔符

sql
 
SELECT 
    s.name,
    GROUP_CONCAT(c.course_name SEPARATOR '; ') AS courses
FROM students s
LEFT JOIN courses c ON s.id = c.student_id
GROUP BY s.id;

结果：

plaintext
 
+-------+------------------+
| name  | courses          |
+-------+------------------+
| Alice | Math; Physics    |
| Bob   | English          |
+-------+------------------+

3. 排序后合并

sql
 
SELECT 
    s.name,
    GROUP_CONCAT(c.course_name ORDER BY c.course_name DESC) AS courses
FROM students s
LEFT JOIN courses c ON s.id = c.student_id
GROUP BY s.id;

结果：

plaintext
 
+-------+------------------+
| name  | courses          |
+-------+------------------+
| Alice | Physics,Math     |
| Bob   | English          |
+-------+------------------+

4. 去重合并

sql
 
SELECT 
    s.name,
    GROUP_CONCAT(DISTINCT c.course_name) AS unique_courses
FROM students s
LEFT JOIN courses c ON s.id = c.student_id
GROUP BY s.id;

注意事项

1. 长度限制

GROUP_CONCAT 默认有最大长度限制（group_concat_max_len，默认 1024 字节）。若数据超长，需临时调整：

sql
 
SET SESSION group_concat_max_len = 1024 * 1024;  -- 设置为 1MB

2. NULL 值处理

GROUP_CONCAT 会忽略 NULL 值。若需包含 NULL，可使用 COALESCE：

sql
 
GROUP_CONCAT(COALESCE(course_name, 'None'))

3. 性能考虑

大量数据合并时可能影响性能，建议配合 ORDER BY 或 DISTINCT 时谨慎使用。

高级用法

1. 合并多列数据

sql
 
SELECT 
    s.name,
    GROUP_CONCAT(CONCAT(c.course_name, ':', c.score) SEPARATOR '; ') AS course_scores
FROM students s
LEFT JOIN course_scores c ON s.id = c.student_id
GROUP BY s.id;

结果：

plaintext
 
+-------+----------------------------+
| name  | course_scores              |
+-------+----------------------------+
| Alice | Math:90; Physics:85        |
| Bob   | English:92                 |
+-------+----------------------------+

2. 与 JSON 结合

sql
 
SELECT 
    s.name,
    CONCAT('[', GROUP_CONCAT(JSON_OBJECT('course', c.course_name, 'score', c.score)), ']') AS courses_json
FROM students s
LEFT JOIN course_scores c ON s.id = c.student_id
GROUP BY s.id;

结果：

plaintext
 
+-------+----------------------------------------------+
| name  | courses_json                                 |
+-------+----------------------------------------------+
| Alice | [{"course":"Math","score":90},{"course":"Physics","score":85}] |
| Bob   | [{"course":"English","score":92}]            |
+-------+----------------------------------------------+

常见错误

错误：结果被截断
→ 增加 group_concat_max_len。
错误：GROUP_CONCAT 不支持分组
→ 确保使用 GROUP BY 子句。

适用场景

将关联表的多行数据合并为单个字段（如用户的所有角色、商品的所有标签）。
生成 CSV 或 JSON 格式的结果集。
简化前端数据处理逻辑。

------------------------------------------------------------

hanease

一、字符串连接函数

1. CONCAT(str1, str2, ...)

2. CONCAT_WS(separator, str1, str2, ...)

二、表连接操作（JOIN）

1. INNER JOIN

2. LEFT JOIN / LEFT OUTER JOIN

3. RIGHT JOIN / RIGHT OUTER JOIN

4. FULL OUTER JOIN

5. CROSS JOIN

三、连接相关的系统函数

1. CONNECTION_ID()

2. GET_LOCK(name, timeout)

3. RELEASE_LOCK(name)

四、使用场景示例

1. 字符串拼接示例

2. 表连接示例

注意事项

一、递归 CTE（MySQL 8.0+）

1. 查询子节点（向下递归）

2. 查询父节点（向上递归）

二、邻接表模型（Adjacency List）

示例表结构：

查询直接子节点：

查询所有层级（需多次连接）：

三、路径枚举模型（Path Enumeration）

示例表结构：

查询子节点：

查询层级深度：

四、嵌套集模型（Nested Sets）

示例表结构：

查询子树：

五、JSON 存储（MySQL 5.7+）

示例表结构：

示例数据：

查询 JSON 层级：

六、性能对比

七、选择建议

基本语法

示例数据

常见用法

1. 合并分组后的字符串

2. 指定分隔符

3. 排序后合并

4. 去重合并

注意事项

1. 长度限制

2. NULL 值处理

3. 性能考虑

高级用法

1. 合并多列数据

2. 与 JSON 结合

常见错误

适用场景

公告

1. `CONCAT(str1, str2, ...)`

2. `CONCAT_WS(separator, str1, str2, ...)`

1. `INNER JOIN`

2. `LEFT JOIN` / `LEFT OUTER JOIN`

3. `RIGHT JOIN` / `RIGHT OUTER JOIN`

4. `FULL OUTER JOIN`

5. `CROSS JOIN`

1. `CONNECTION_ID()`

2. `GET_LOCK(name, timeout)`

3. `RELEASE_LOCK(name)`