菜鸟开发之MySQL常见字段值处理-EW帮帮网

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

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, ' ', '');

注意事项

大表更新时，分批处理（如 LIMIT 1000）。
为筛选字段添加索引（如 WHERE 条件字段）。
TRIM('') 返回空字符串，TRIM(NULL) 返回 NULL。
使用 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,   -- 输出: 2023
  MONTH('2023-08-20') AS month, -- 输出: 8
  DAY('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: alice@example.com', '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}');
-- 输出: 'alice@example.com'

六、数据拆分

1、按分隔符拆分

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

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

UPDATE users
SET
  last_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;

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

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

一、数据拼接

1、CONCAT

2、CONCAT_WS

二、空值判断

1、是否为NULL

2、是否空字符串

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

4、处理NULL和空字符串

三、数据替换

1、REPLACE函数

2、UPDATE语句

3、UPDATE结合CASE

4、COALESCE和IFNULL

5、正则表达式

6、INSERT

四、空格处理

1、去除空格的核心函数

1）首尾TRIM

2）左LRTM右RTRIM

3）所有空格REPLACE

2、进阶处理

1）COALESCE()、IFNULL

2）条件处理

五、数据截取

1、字符串

1）SUBSTR、SUBSTRING

2）LEFT、RIGHT

3）MID

2、数值

1）TRUNCATE

2）ROUND

3、日期时间

1）DATE_FORMAT

2）专用日期函数

4、JSON值获取

1）简单字段获取

2）嵌套字段获取

3）数组元素获取

4）技巧使用

5、正则表达式

六、数据拆分

1、按分隔符拆分

2、按位置拆分

七、数据求值

1、基本运算

2、聚合函数

3、数学函数

4、常见处理

1）日期计算

2）条件处理

网站公告

今日签到

热门文章

最新发布

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