如何在MySQL中查询格式化数据及格式不规范的数据？

1. 如何在MySQL中查询格式化数据？ #

MySQL提供了大量内置函数来处理不同类型数据的格式化需求，常见场景如下：

• 日期/时间格式化：用DATE_FORMAT()自定义日期时间的显示样式。比如把created_at字段转成YYYY-MM-DD HH:MM:SS格式：

  SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS formatted_time
  FROM your_table;
  

  常用格式符包括%Y（4位年份）、%d（两位日期）、%H（24小时制小时数）等，可按需组合使用。

• 字符串格式化：

  • 统一大小写：用UPPER()转全大写，LOWER()转全小写：

    SELECT UPPER(username) AS upper_username, LOWER(full_name) AS lower_name
    FROM users;
    

  • 拼接内容：用CONCAT()把多个字段或固定字符串合并成一个内容：

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

• 数字格式化：用FORMAT()给数字添加千分位分隔符，并指定小数位数，比如把价格格式化为带两位小数的标准形式：

  SELECT FORMAT(price, 2) AS formatted_price
  FROM products;
  

2. 如何在MySQL中查询格式不规范的数据？ #

针对你提到的姓名大小写混乱的场景（比如John Doe、john doe、JOHN DOE、joHn DoE），要筛选出除标准格式外的不规范数据，可以通过统一转换大小写后对比的方式实现。

假设你的表名为user_profiles，存储姓名的字段是full_name，查询语句如下：

SELECT *
FROM user_profiles
WHERE LOWER(full_name) != LOWER('John Doe');

也可以用大写转换的方式，效果完全一致：

SELECT *
FROM user_profiles
WHERE UPPER(full_name) != UPPER('John Doe');

原理说明 #

MySQL默认的字符排序规则（如utf8mb4_general_ci）是不区分大小写的，直接用full_name != 'John Doe'无法匹配到那些大小写不同的行。而通过LOWER()或UPPER()把字段值和目标字符串都转成统一大小写后，就能精准筛选出大小写格式不规范的记录。

如果你的需求是更严格的格式校验（比如要求每个单词首字母大写、其余小写），MySQL 8.0.23及以上版本支持INITCAP()函数，可以直接生成标准格式后对比：

SELECT *
FROM user_profiles
WHERE full_name != INITCAP(full_name);

这条语句会直接找出所有不符合“首字母大写、其余小写”格式的记录，无需指定固定的标准值。

内容的提问来源于stack exchange，提问作者user3346575