MySQL 字符串连接：CONCAT 函数用法与技巧 – wiki基地

---

MySQL 字符串连接：CONCAT 函数用法与技巧深度解析

在数据库应用开发中，字符串的处理是一项基础且频繁的操作。无论是拼接用户姓名、组合地址信息，还是构建动态查询语句，字符串连接（String Concatenation）都扮演着至关重要的角色。MySQL 提供了多种方式来实现字符串连接，其中 CONCAT() 函数是最常用、最标准的方法之一。

本文将深入探讨 MySQL 中 CONCAT() 函数的用法、特性，以及相关的 CONCAT_WS() 函数，并通过丰富的示例和技巧，帮助您熟练掌握字符串连接在实际开发中的应用。

1. 理解字符串连接的需求与意义

在关系型数据库中，数据通常被分散存储在不同的列中，以遵循规范化原则。然而，在展示数据或进行某些特定的数据处理时，我们往往需要将来自不同列的字符串内容组合起来。例如：

• 将 first_name 和 last_name 连接起来形成完整的姓名。
• 将 street_address, city, state, zip_code 连接起来形成完整的地址。
• 构建一个包含特定信息的日志或描述字符串。
• 动态生成文件路径或 URL。
• 在 WHERE 子句中组合条件进行模糊匹配（尽管性能需注意）。

字符串连接函数就是为了满足这些需求而设计的。

2. CONCAT() 函数的基础用法

CONCAT() 是 MySQL 中用于连接两个或多个字符串的标准函数。

基本语法：

sql
CONCAT(string1, string2, string3, ...)

CONCAT() 函数接受一个或多个字符串参数，并将它们按照参数出现的顺序连接成一个单一的字符串。参数可以是列名、字符串字面量、数字或任何可以被隐式转换为字符串的表达式。

示例 1：连接两个字符串字面量

sql
SELECT CONCAT('Hello', ' World');
-- 结果: 'Hello World'

这个例子非常简单，直接将两个字符串字面量连接在一起。

示例 2：连接多个字符串字面量

sql
SELECT CONCAT('MySQL', ' is', ' powerful', '!');
-- 结果: 'MySQL is powerful!'

可以看出，CONCAT() 函数可以方便地连接任意数量的字符串。

示例 3：连接列数据

假设我们有一个 users 表，包含 first_name 和 last_name 列。

“`sql
— 假设 users 表结构
CREATE TABLE users (
user_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);

— 插入一些示例数据
INSERT INTO users (user_id, first_name, last_name) VALUES
(1, ‘John’, ‘Doe’),
(2, ‘Jane’, ‘Smith’),
(3, ‘Peter’, ‘Jones’);
“`

使用 CONCAT() 连接 first_name 和 last_name 以获取全名：

sql
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

结果可能如下：

+-------------+
| full_name |
+-------------+
| John Doe |
| Jane Smith |
| Peter Jones |
+-------------+

我们在 first_name 和 last_name 之间加入了一个空格字符串 ' ' 作为分隔符，使得全名更具可读性。这是 CONCAT() 的常见用法之一：通过在需要连接的字符串之间插入分隔符来实现格式化输出。

3. CONCAT() 函数对 NULL 值的处理

理解 CONCAT() 函数如何处理 NULL 值至关重要，因为这直接影响到查询结果的准确性。

CONCAT() 函数的 NULL 值处理规则：

如果 CONCAT() 函数的任何一个参数为 NULL，则整个函数的返回结果也为 NULL。

示例 4：参数中包含 NULL 值

sql
SELECT CONCAT('Prefix', NULL, 'Suffix');
-- 结果: NULL

sql
SELECT CONCAT('Value: ', NULL);
-- 结果: NULL

sql
SELECT CONCAT(NULL, 'Anything');
-- 结果: NULL

示例 5：表中列数据包含 NULL

假设我们在 users 表中新增一个 middle_name 列，并且某些用户的该列数据为 NULL。

“`sql
ALTER TABLE users ADD COLUMN middle_name VARCHAR(50);

UPDATE users SET middle_name = ‘A.’ WHERE user_id = 1; — John A. Doe
UPDATE users SET middle_name = NULL WHERE user_id = 2; — Jane Smith
UPDATE users SET middle_name = ‘B.’ WHERE user_id = 3; — Peter B. Jones
“`

现在，我们尝试连接 first_name, middle_name, 和 last_name 来构建一个包含中间名的全名：

sql
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name_with_middle
FROM users;

结果可能如下：

+-----------------------+
| full_name_with_middle |
+-----------------------+
| John A. Doe |
| NULL |
| Peter B. Jones |
+-----------------------+

可以看到，对于 Jane Smith 这条记录，由于其 middle_name 为 NULL，导致整个 CONCAT() 函数的结果变成了 NULL，这可能不是我们期望的行为。我们通常希望在这种情况下只连接非 NULL 的部分。

处理 NULL 值的策略：

为了避免 CONCAT() 函数因 NULL 参数而返回 NULL，我们需要在将参数传递给 CONCAT() 之前处理潜在的 NULL 值。常用的方法是使用 IFNULL() 或 COALESCE() 函数。

• IFNULL(expr1, expr2): 如果 expr1 不是 NULL，返回 expr1，否则返回 expr2。
• COALESCE(expr1, expr2, ..., expr_n): 返回参数列表中的第一个非 NULL 值。

示例 6：使用 IFNULL() 处理 NULL

我们可以使用 IFNULL() 将 NULL 的 middle_name 替换为空字符串 ''：

sql
SELECT CONCAT(first_name, ' ', IFNULL(middle_name, ''), ' ', last_name) AS full_name_handled_null
FROM users;

结果可能如下：

+------------------------+
| full_name_handled_null |
+------------------------+
| John A. Doe |
| Jane Smith |
| Peter B. Jones |
+------------------------+

现在，当 middle_name 是 NULL 时，它被替换成了空字符串，CONCAT() 函数就能正常工作，并连接 first_name、两个空格和 last_name。注意 Jane Smith 记录中 Jane 和 Smith 之间仍然有两个空格，这是因为我们在 first_name 和 IFNULL(middle_name, '') 之间放了一个空格，又在 IFNULL(middle_name, '') 和 last_name 之间放了一个空格。当 middle_name 是空字符串时，这两个空格就相邻了。这引出了使用分隔符连接多个可能为 NULL 的字段时，CONCAT_WS() 函数的优势。

4. CONCAT_WS() 函数：带分隔符的连接

为了更方便地使用分隔符连接字符串，并且智能地处理 NULL 值，MySQL 提供了 CONCAT_WS() 函数。

CONCAT_WS() 的语法：

sql
CONCAT_WS(separator, string1, string2, string3, ...)

CONCAT_WS() 函数的第一个参数是分隔符（separator），它可以是任意字符串。后面的参数是要连接的字符串。

CONCAT_WS() 的特性：

1. 使用指定的分隔符： 除了第一个参数外，所有后续参数都会被使用第一个参数指定的分隔符连接起来。
2. 智能处理 NULL 值： CONCAT_WS() 会跳过任何后续参数中的 NULL 值，而不会像 CONCAT() 那样整个结果变为 NULL。
3. 分隔符的处理：
   • 如果分隔符（第一个参数）为 NULL，则整个函数的返回结果为 NULL。
   • 如果所有后续参数都是 NULL 或空字符串，且分隔符不为 NULL，则结果为空字符串 ''。

示例 7：使用 CONCAT_WS() 连接姓名

使用 CONCAT_WS() 连接 first_name, middle_name, last_name，并以空格 ' ' 作为分隔符：

sql
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name_ws
FROM users;

结果可能如下：

+------------------+
| full_name_ws |
+------------------+
| John A. Doe |
| Jane Smith |
| Peter B. Jones |
+------------------+

对比 CONCAT() 处理 NULL 的示例，CONCAT_WS() 在 middle_name 为 NULL 的情况下，直接跳过了这个参数，并正确地连接了 first_name 和 last_name，中间只有一个空格分隔符。这正是我们通常希望在构建姓名、地址等字符串时得到的结果。

示例 8：使用 CONCAT_WS() 连接地址

假设有一个 addresses 表：

“`sql
CREATE TABLE addresses (
addr_id INT PRIMARY KEY,
street VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(10)
);

INSERT INTO addresses (addr_id, street, city, state, zip_code) VALUES
(101, ‘123 Main St’, ‘Anytown’, ‘CA’, ‘91234’),
(102, ‘456 Oak Ave’, ‘Somecity’, ‘NY’, ‘10001’),
(103, ‘789 Pine Ln’, ‘Otherville’, NULL, ‘60001’); — State is NULL
“`

使用 CONCAT_WS() 连接地址信息，以逗号和空格 ', ' 作为分隔符：

sql
SELECT CONCAT_WS(', ', street, city, state, zip_code) AS full_address
FROM addresses;

结果可能如下：

+--------------------------------+
| full_address |
+--------------------------------+
| 123 Main St, Anytown, CA, 91234|
| 456 Oak Ave, Somecity, NY, 10001|
| 789 Pine Ln, Otherville, 60001 | -- Skipped NULL state
+--------------------------------+

注意对于 addr_id = 103 的记录，由于 state 是 NULL，CONCAT_WS() 直接跳过了 state 参数，将 city 和 zip_code 用分隔符连接起来，得到了期望的结果。

示例 9：分隔符为 NULL

sql
SELECT CONCAT_WS(NULL, 'a', 'b', 'c');
-- 结果: NULL

如果分隔符为 NULL，则结果就是 NULL，这与 CONCAT() 的行为类似。

示例 10：所有后续参数为 NULL 或空字符串

sql
SELECT CONCAT_WS(', ', NULL, '', NULL, '');
-- 结果: ''

如果所有要连接的参数都是 NULL 或空字符串，CONCAT_WS() 返回空字符串（前提是分隔符不为 NULL）。

5. CONCAT() 与 CONCAT_WS() 的比较与选择

理解 CONCAT() 和 CONCAT_WS() 的区别是正确使用的关键。

特性	CONCAT()	CONCAT_WS(separator, …)
分隔符	没有内置分隔符，需手动在参数间插入	第一个参数指定统一分隔符
NULL 处理	任何参数为 NULL，结果即为 NULL	跳过后续参数中的 NULL 值（分隔符为 NULL 除外）
参数数量	至少一个参数	至少两个参数（一个分隔符，至少一个连接项）
适用场景	简单连接；需要严格的 NULL 传播；连接的字符串不需要统一分隔符或分隔符本身需要复杂逻辑控制	使用统一分隔符连接多个字符串；需要自动处理并跳过 NULL 值

何时选择 CONCAT()：

• 你需要连接的字符串数量固定且较少。
• 你对每个字符串之间的分隔符有完全不同的需求（例如，'Name: ', name, ' Age: ', age）。
• 你明确希望当某个部分的数据缺失（为 NULL）时，整个连接结果也变为 NULL（这种需求较少见，通常需要额外的 NULL 处理）。
• 你只需要连接一个字符串（虽然功能上等同于直接返回该字符串）。

何时选择 CONCAT_WS()：

• 你需要使用统一的分隔符连接多个字符串。
• 要连接的字段中可能包含 NULL 值，并且你希望 CONCAT_WS() 能够自动跳过这些 NULL 值，只连接非 NULL 的部分，同时正确地放置分隔符。这大大简化了处理可能为 NULL 的列的逻辑。

在大多数需要用分隔符连接多个列（如姓名、地址、标签列表等）的场景下，CONCAT_WS() 是更简洁、更方便的选择，尤其是当这些列可能包含 NULL 值时。

6. CONCAT 函数的高级应用与技巧

除了基本的连接功能，CONCAT() 和 CONCAT_WS() 还可以与其他 MySQL 函数结合使用，实现更强大的字符串处理能力。

技巧 1：与其他字符串函数结合使用

可以将 CONCAT() 或 CONCAT_WS() 的参数是其他字符串函数的返回值，或者将 CONCAT() 的结果作为其他函数的参数。

• 格式化输出： 结合 UPPER(), LOWER(), SUBSTRING(), LEFT(), RIGHT(), TRIM() 等函数。

  sql
-- 获取用户姓名的首字母缩写
SELECT CONCAT(UPPER(LEFT(first_name, 1)), '.', UPPER(LEFT(last_name, 1))) AS initials
FROM users
WHERE first_name IS NOT NULL AND last_name IS NOT NULL; -- 避免因 LEFT/UPPER 处理 NULL 导致的 CONCAT 结果为 NULL

  或者，如果希望即使某个名字部分为 NULL 也能生成部分缩写，可以结合 IFNULL 或 CONCAT_WS：

  sql
-- 更健壮的首字母缩写（使用 CONCAT_WS 自动处理 NULL）
SELECT CONCAT_WS('.', UPPER(LEFT(IFNULL(first_name, ''), 1)), UPPER(LEFT(IFNULL(last_name, ''), 1))) AS initials_ws
FROM users;
-- 注意：如果 first_name 和 last_name 都为空或 NULL，结果可能是 '.', '..', '' 等，具体取决于 CONCAT_WS 的参数处理
-- 例如，如果 first_name='A', last_name=NULL, CONCAT_WS('.', 'A', '') -> 'A.'
-- 如果 first_name=NULL, last_name=NULL, CONCAT_WS('.', '', '') -> ''
-- 这取决于你期望的行为，可能需要更复杂的 IF/CASE 逻辑进行微调。

• 构建动态文本：

  sql
-- 生成用户欢迎消息
SELECT CONCAT('Welcome, ', first_name, '! Your user ID is ', user_id, '.') AS welcome_message
FROM users;
  这里 user_id 是数字，但 CONCAT 会将其隐式转换为字符串。

技巧 2：在 WHERE 子句中使用

虽然不推荐在大型表中使用 CONCAT() 或 CONCAT_WS() 在 WHERE 子句中进行模糊匹配，因为这通常会导致全表扫描，但在某些特定场景或对性能要求不高的查询中是可行的。

sql
-- 查找全名包含 "John Doe" 的用户 (效率较低)
SELECT user_id, first_name, last_name
FROM users
WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';

更常见的用法是在 WHERE 子句中组合其他条件，例如基于连接后的部分结果进行过滤，但同样要注意性能影响。

技巧 3：在 UPDATE 和 INSERT 语句中使用

CONCAT() 和 CONCAT_WS() 经常用于更新或插入基于现有数据组合而成的字符串字段。

“`sql
— 添加一个 full_name 列，并在插入时自动生成
ALTER TABLE users ADD COLUMN full_name VARCHAR(101); — 50+1+50 = 101

— 更新现有用户的 full_name
UPDATE users
SET full_name = CONCAT_WS(‘ ‘, first_name, middle_name, last_name);

— 插入新用户时同时生成 full_name
INSERT INTO users (first_name, middle_name, last_name, full_name)
VALUES (‘Alice’, ‘C.’, ‘Wonderland’, CONCAT_WS(‘ ‘, ‘Alice’, ‘C.’, ‘Wonderland’));
``
或者，更推荐的做法是使用触发器在INSERT或UPDATE时自动维护full_name` 字段，以避免在业务逻辑中重复计算。

技巧 4：处理不同数据类型

CONCAT() 和 CONCAT_WS() 函数会将所有非字符串类型的参数隐式转换为字符串。这意味着你可以直接连接数字、日期、时间等类型的数据。

“`sql
— 连接数字和字符串
SELECT CONCAT(‘The number is: ‘, 12345);
— 结果: ‘The number is: 12345’

— 连接日期和字符串
SELECT CONCAT(‘Today is ‘, CURDATE());
— 结果示例: ‘Today is 2023-10-27’

— 连接日期和时间
SELECT CONCAT(‘Current time: ‘, NOW());
— 结果示例: ‘Current time: 2023-10-27 10:30:00’
“`

MySQL 会根据标准的格式将数字、日期、时间等类型转换为字符串。如果你需要特定的日期/时间格式，应该先使用 DATE_FORMAT() 或 TIME_FORMAT() 函数进行格式化，再进行连接。

sql
-- 使用指定格式连接日期
SELECT CONCAT('Today is ', DATE_FORMAT(CURDATE(), '%Y/%m/%d'));
-- 结果示例: 'Today is 2023/10/27'

技巧 5：构建动态 SQL 片段或 URL

在某些高级应用中，CONCAT 可用于构建 SQL 语句的一部分（注意潜在的 SQL 注入风险）或生成动态 URL。

“`sql
— 构建一个简单的动态 SQL 片段（仅用于理解概念，生产环境需谨慎防注入）
SELECT CONCAT(‘SELECT * FROM users WHERE user_id = ‘, user_id, ‘;’) AS dynamic_sql
FROM users WHERE user_id = 1;
— 结果: ‘SELECT * FROM users WHERE user_id = 1;’

— 构建一个动态 URL
SELECT CONCAT(‘https://example.com/users/’, user_id, ‘/profile’) AS user_profile_url
FROM users WHERE user_id = 1;
— 结果: ‘https://example.com/users/1/profile’
“`
在构建动态 SQL 时，务必采取严格的措施防止 SQL 注入，例如使用参数化查询，而不是简单地拼接用户输入。

技巧 6：限制连接后的字符串长度

MySQL 中字符串的最大长度受多种因素影响，包括列类型（VARCHAR的最大长度）、max_allowed_packet 配置等。如果 CONCAT 或 CONCAT_WS 的结果超过了某个内部限制或目标列的最大长度，结果可能会被截断或导致错误。通常情况下，对于大多数常见的连接操作，不太会遇到长度限制问题，但如果需要连接非常长的文本字段（如 BLOB/TEXT），应予以注意。

7. 性能考虑

对于 CONCAT() 和 CONCAT_WS() 函数本身而言，它们的执行速度通常非常快，性能开销很小。然而，将它们用于某些特定场景时，可能会间接影响查询性能：

• 在 WHERE 子句中使用连接后的字段进行过滤： 如果你在 WHERE 子句中使用 CONCAT(col1, col2) 进行条件过滤，例如 WHERE CONCAT(first_name, last_name) = '...' 或 WHERE CONCAT(...) LIKE '%...%'，MySQL 数据库通常无法利用在 first_name 或 last_name 单个列上建立的索引。这会导致数据库扫描所有相关的行，计算连接后的字符串，然后再进行比较，从而显著降低查询速度，尤其是在大型表上。在这种情况下，考虑使用多个条件分开过滤（例如 WHERE first_name = '...' AND last_name = '...'），或者考虑在表中存储一个计算好的连接字段并为其建立索引（如前面提到的 full_name 列）。
• 在 ORDER BY 子句中使用： 在 ORDER BY CONCAT(col1, col2) 的情况下，同样无法利用单列索引进行排序优化，可能导致文件排序（filesort），影响性能。

因此，在使用 CONCAT 函数时，虽然函数本身高效，但要注意它在整个查询中的位置和如何影响索引的使用。

8. 其他字符串连接方式（简述）

除了 CONCAT() 和 CONCAT_WS()，MySQL 还支持另一种字符串连接方式，但这取决于 SQL_MODE 设置：

• || 操作符： 在标准的 SQL 语法中，|| 是字符串连接操作符。然而，在 MySQL 中，|| 的默认行为是逻辑 OR 操作符。只有当 SQL_MODE 设置为包含 PIPES_AS_CONCAT 时，|| 才会被视为字符串连接操作符。

  “`sql
  — 如果 SQL_MODE 包含 PIPES_AS_CONCAT
  SELECT ‘Hello’ || ‘ World’;
  — 结果: ‘Hello World’

  — 如果 SQL_MODE 不包含 PIPES_AS_CONCAT (默认行为)
  SELECT ‘Hello’ || ‘ World’;
  — 结果: 0 (逻辑 OR 的结果)
  “`

  由于 || 的行为依赖于 SQL_MODE 设置，为了保证代码的可移植性和避免混淆，强烈推荐在 MySQL 中使用 CONCAT() 或 CONCAT_WS() 函数来进行字符串连接。它们是 MySQL 官方推荐且行为稳定的方法。

9. 总结

CONCAT() 和 CONCAT_WS() 是 MySQL 中用于字符串连接的核心函数。

• CONCAT(str1, str2, ...) 用于简单地将参数连接在一起，但任何 NULL 参数会导致结果为 NULL。
• CONCAT_WS(separator, str1, str2, ...) 使用指定的分隔符连接参数，并且会自动跳过 NULL 值（分隔符本身为 NULL 除外），是处理带有分隔符且可能包含 NULL 的字段连接的首选函数。

熟练掌握这两个函数的用法，理解它们在处理 NULL 值上的区别，并结合其他字符串函数以及在 SELECT, WHERE, UPDATE, INSERT 语句中的应用，能够极大地提升您在 MySQL 中处理字符串数据的能力。在使用时，也要注意其在特定场景（如 WHERE 和 ORDER BY 子句）中可能带来的性能影响。

通过本文的详细介绍和丰富的示例，相信您已经对 MySQL 的字符串连接函数有了全面的认识，并能灵活运用它们解决实际开发中的各种字符串处理问题。