菜鸟开发之MySQL常见字段值处理

仰天大笑出门去，我辈更要谱天曲。

SQL一日同风起，基础不夯注定倾。

众里寻它千百度，蓦然回首，那个错误还在吐。

Java开发该过程中性能问题是存在的，而数据处理则是占据大头，平时开发一定要注意代码质量和需求实现的性能问题。如果需要聚合、过滤大量数据，或者实物一致性要求高时，需要优先SQL(统计报表)；如果业务复杂、动态规则，或者高度使用中间结果，则在Java代码中处理，减少SQL变更，减少数据库交互。

一、数据拼接

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。

2、进阶处理

1）COALESCE()、IFNULL

SELECT TRIM(COALESCE(address, '')) AS clean_address FROM orders;

2）条件处理

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(json_doc, path[, path]...)：通过 JSON 路径表达式（JSON Path）直接查询嵌套的 JSON 字段，适用于处理存储在 MySQL 中的半结构化数据。

json_doc：JSON 格式的文档（字段或字符串）。

path：JSON 路径表达式，指定要提取的数据位置。支持多个路径参数。

路径表达式

对象属性：使用点 . 访问，例如 $.name；

数组元素：使用方括号 [] 访问。例如 $[0]；

嵌套结构：组合使用点和方括号，例如 $.address.street 或 $.items[0].name。

1）简单字段获取

{
        "name": "Alice",
        "age": 30,

        "is_active": true
}

SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name') AS name,JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.age') AS age;-- 输出：
-- name: "Alice"（JSON 字符串）
-- age: 30（JSON 数值）

 2）嵌套字段获取

{
  "person": {
        "name": "Bob",
                "address": {
                "city": "Beijing",
                "postcode": "100000"
             }
   }
}

SELECT JSON_EXTRACT(json_column, '$.person.address.city') AS city
FROM users;-- 输出："Beijing"

 3）数组元素获取

{
          "tags": ["MySQL", "Database", "JSON"]
}

SELECT JSON_EXTRACT(json_column, '$.tags[0]') AS first_tag,JSON_EXTRACT(json_column, '$.tags[2]') AS third_tag
FROM posts;-- 输出：
-- first_tag: "MySQL"
-- third_tag: "JSON"

 4）技巧使用

-- JSON 字符串返回值默认带引号，可使用 JSON_UNQUOTE() 转换为普通字符串：
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "Alice"}', '$.name')); 
-- 输出: Alice-- 简化
-- ->：等价于 JSON_EXTRACT()
-- ->>：等价于 JSON_UNQUOTE(JSON_EXTRACT())
SELECT profile->'$.address.city' AS city_json,profile->>'$.address.city' AS city_text
FROM users;-- 当路径不存在时返回默认值
SELECT COALESCE(JSON_EXTRACT(profile, '$.nickname'), '无名') AS nickname
FROM users;

5、正则表达式

REGEXP_SUBSTR

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

六、数据拆分

1、按分隔符拆分

SUBSTRING_INDEX(str, delimiter, count)：分隔符截取

LEFT()、RIGHT()：截取左右部分

UPDATE users
SETlast_name = SUBSTRING_INDEX(full_name, ',', 1),first_name = SUBSTRING_INDEX(full_name, ',', -1);

2、按位置拆分

-- 更新 order_prefix 列
UPDATE orders
SET order_prefix = LEFT(order_number, 3);-- 更新 order_suffix 列
UPDATE orders
SET order_suffix = SUBSTRING(order_number, 4);

七、数据求值 

1、基本运算

加减乘除：+  -  *  /

2、聚合函数

-- COUNT()：统计行数；    SUM()：计算总和；    AVG()：计算平均值
-- MIN()、MAX()：最小最大值SELECT COUNT(*) AS total_users,AVG(age) AS avg_age,MAX(salary) AS max_salary
FROM employees;

 3、数学函数

-- ROUND()：四舍五入；   CEIL()、FLOOP()：向上向下取整
-- ABS()：绝对值；       POW()、SQRT()：冥运算和平方根SELECT ROUND(123.4567, 2);  -- 输出: 123.46
SELECT CEIL(123.45), FLOOR(123.45);  -- 输出: 124, 123
SELECT ABS(-10);  -- 输出: 10
SELECT POW(2, 3), SQRT(16);  -- 输出: 8, 4

4、常见处理

1）日期计算

DATEDIFF()：计算日期差

-- 输出: 2023-10-12 12:34:56
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);  

 DATE_ADD()、DATE_SUB()：日期加减

-- 输出: 2023-10-12 12:34:56
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);

2）条件处理

CASE WHEN：多条件判断

SELECT CASE WHEN score >= 90 THEN 'A'WHEN score >= 80 THEN 'B'ELSE 'C'END AS grade
FROM exams;

ROW_NUMBER()：每行分配唯一序号

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,name, salary
FROM employees;

RANK()、DENSE_RANK()：计算排名

SELECT RANK() OVER (ORDER BY score DESC) AS rank,name, score
FROM students;

SUM()、OVER()：计算累计和

SELECT month, revenue,SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM sales;

变量计算累计和

SET @total := 0;
SELECT order_id, amount,(@total := @total + amount) AS running_total
FROM orders;

稍纵即逝谁为先，撸起袖子加油干。