MySQL常见字段值处理

一、数据拼接

1、CONCAT

CONCAT(string1, string2, ..., stringN)，将两个或多个字符串连接在一起

• 自动忽略 NULL 值参数，仅拼接非 NULL 的字符串。

• 第一个参数必须是分隔符（字符串）。

SELECT CONCAT('Hello', ' ', 'World');  -- 输出: Hello World
SELECT CONCAT('Hello', NULL, 'World'); -- 输出: NULL

2、CONCAT_WS

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

CONCAT_WS() 函数是一个特殊的 CONCAT()，它允许你指定一个分隔符来连接字符串。WS 表示“With Separator”。

• 自动忽略 NULL 值参数，仅拼接非 NULL 的字符串。

• 第一个参数必须是分隔符（字符串）。

SELECT CONCAT_WS('-', '2023', '08', '20');  -- 输出: 2023-08-20
SELECT CONCAT_WS(',', 'Apple', NULL, 'Banana'); -- 输出: Apple,Banana

二、空值判断

1、是否为NULL

1）IS NULL：返回NULL空字符串记录

SELECT * FROM table_name WHERE column_name IS NULL;

 2）IS NOT NULL：返回不为NULL的字符串记录

SELECT * FROM table_name WHERE column_name IS NOT NULL;

2、是否空字符串

1）=''：返回为空字符串

SELECT * FROM table_name WHERE column_name = '';

2）<>''：返回不为空字符串 

SELECT * FROM table_name WHERE column_name <> '';

 3、同时判断NULL和空字符串

1）使用IFNULL或COALESCE

IFNULL()：如果字段为 NULL，则返回指定值。

SELECT IFNULL(column_name, '固定值') FROM table_name;

COALESCE()：返回第一个非 NULL 的值

SELECT COALESCE(column_name, 'Default Value') FROM table_name;

2）NULLIF()：字段等于指定值(如空字符串)，则返回 NULL

SELECT NULLIF(column_name, '') FROM table_name;

4、处理NULL和空字符串

1）NULL和空字符串统一处理

SELECT * FROM table_name WHERE COALESCE(column_name, '') = '';

2）NULL转换为空字符串

SELECT IFNULL(column_name, '') FROM table_name;

3）空字符串转换为 NULL

SELECT NULLIF(column_name, '') FROM table_name;

三、数据替换

1、REPLACE函数

REPLACE(字段名, 旧字符串, 新字符串)：简单替换，主要用于字符串

-- 将 name 字段中的 "旧" 替换为 "新"
SELECT REPLACE(name, '旧', '新') AS new_name FROM products;-- 更新表数据（永久替换）
UPDATE products SET name = REPLACE(name, '旧', '新');

2、UPDATE语句

UPDATE your_table
SET column_name = REPLACE(column_name, 'old_value', 'new_value')
WHERE column_name LIKE '%old_value%';

3、UPDATE结合CASE

CASE WHEN可以处理多种数据类型，主要用于复杂逻辑

语法：

        CASE 
                WHEN 条件1 THEN 替换值1
                WHEN 条件2 THEN 替换值2
                ELSE 默认值
        END

-- 根据 score 的值替换等级
SELECT name,CASE WHEN score >= 90 THEN 'A'WHEN score >= 80 THEN 'B'ELSE 'C'END AS grade
FROM students;-- 更新表数据（将负库存标记为缺货）
UPDATE inventory 
SET stock_status = CASE WHEN stock <= 0 THEN '缺货'ELSE '有货'
END;

4、COALESCE和IFNULL

IFNULL(字段名, 默认值)  -- 若字段为 NULL，返回默认值
COALESCE(字段名, 备用值1, 备用值2)  -- 返回第一个非 NULL 的值

-- 将 NULL 替换为 "未知"
SELECT IFNULL(email, '未知') AS user_email FROM users;-- 优先使用 phone，若为 NULL 则用备用电话
SELECT COALESCE(phone, backup_phone, '无联系方式') AS contact FROM customers;

 5、正则表达式

REGEXP_REPLACE(字段名, 正则表达式, 新字符串)函数，从MySQL 8.0开始，你可以使用正则表达式进行更复杂的替换。

-- 所有数字替换为X
UPDATE your_table
SET column_name = REGEXP_REPLACE(column_name, '[0-9]', 'X');

6、INSERT

INSERT(原字符串, 起始位置, 替换长度, 新字符串)

-- 将手机号中间四位替换为 ****
SELECT INSERT(phone, 4, 4, '****') AS masked_phone FROM users;

四、空格处理

1、去除空格的核心函数

1）首尾TRIM

TRIM()去除首尾空格；SELECT TRIM('  示例文字  ');  -- 输出: '示例文字'

-- 查询时去除首尾空格
SELECT TRIM(username) AS clean_name FROM users;-- 永久更新数据
UPDATE users SET username = TRIM(username);

2）左LRTM右RTRIM

SELECT LTRIM('  左侧空格');  -- 输出: '左侧空格'
SELECT RTRIM('右侧空格  ');  -- 输出: '右侧空格'

3）所有空格REPLACE

SELECT REPLACE('中 间 空 格', ' ', '');  -- 输出: '中间空格'

-- 删除 phone 字段中的所有空格
UPDATE customers SET phone = REPLACE(phone, ' ', '');

注意事项

1. 大表更新时，分批处理（如 LIMIT 1000）。

2. 为筛选字段添加索引（如 WHERE 条件字段）。

3. TRIM('') 返回空字符串，TRIM(NULL) 返回 NULL。

4. 使用 COALESCE(TRIM(field), '') 确保结果非 NULL。

4）条件处理

SELECT CASE WHEN CHAR_LENGTH(TRIM(notes)) = 0 THEN '无备注'ELSE TRIM(notes)END AS formatted_notes
FROM orders;

动态格式化输出 

-- 显示地址信息（首尾无空格，中间连续空格合并为单个）
SELECT id,REGEXP_REPLACE(TRIM(address), ' +', ' ') AS formatted_address
FROM customers;

五、数据截取

1、字符串

1）SUBSTR、SUBSTRING

从指定位置截取字符串的指定长度。

SUBSTRING(str, start_pos, length)；SUBSTR(str, start_pos, length)  -- 与SUBSTRING功能相同

start_pos：起始位置（从1开始计数）。

SELECT SUBSTRING('Hello World', 7, 5);  -- 输出: 'World'
SELECT SUBSTR('2023-08-20', 1, 4);     -- 输出: '2023'（截取年份）

2）LEFT、RIGHT

分别从左侧或右侧截取指定长度的字符串。

LEFT(str, length)   -- 从左截取；RIGHT(str, length)  -- 从右截取

SELECT LEFT('MySQL Tutorial', 5);   -- 输出: 'MySQL'
SELECT RIGHT('123-456-7890', 4);   -- 输出: '7890'（截取手机号后四位）

3）MID

与SUBSTRING()相同，但其语法更直观

SELECT MID('ABCDEFG', 3, 2);  -- 输出: 'CD'

2、数值

1）TRUNCATE

TRUNCATE(number, decimals)截断数值的小数部分，保留指定小数位

SELECT TRUNCATE(123.4567, 2);  -- 输出: 123.45
SELECT TRUNCATE(123.4567, -1); -- 输出: 120（截断整数部分）

2）ROUND

四舍五入到指定小数位（与 TRUNCATE 的区别在于是否四舍五入）

SELECT ROUND(123.4567, 2);  -- 输出: 123.46

3、日期时间

1）DATE_FORMAT

DATE_FORMAT(date, format)：格式化日期时间字段，提取特定部分（如年、月、日）

%Y：四位年份（如2023）
%m：两位月份（01-12）
%d：两位日期（01-31）
%H：小时（00-23）
%i：分钟（00-59）SELECT DATE_FORMAT(NOW(), '%Y-%m');  -- 输出: '2023-08'

2）专用日期函数

提取年/月/日

SELECT YEAR('2023-08-20') AS year,   -- 输出: 2023MONTH('2023-08-20') AS month, -- 输出: 8DAY('2023-08-20') AS day;     -- 输出: 20

4、JSON

JSON_EXTRACT

SELECT JSON_EXTRACT('{"name": "Alice", "age": 25}', '$.name'); -- 输出: "Alice"

5、正则表达式

REGEXP_SUBSTR

SELECT REGEXP_SUBSTR('Email: [e-mail]', '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}');
-- 输出: '[e-mail]'