详细分析Mysql中的STR_TO_DATE基本知识（全）

前言

对于该知识点，主要因为数据库类型为String（类似2024-03-26），放置于后端操作后，需要自定义比较，而且不是在sql内存做处理，后续特别麻烦

1. 基本知识

STR_TO_DATE是MySQL中的一个日期时间处理函数，用于将字符串转换为日期时间类型。

它的基本用法是将一个包含日期时间信息的字符串转换为对应的日期时间类型

基本的语法如下：

STR_TO_DATE(str, format)

• str：要转换为日期时间的字符串
• format：指定了输入字符串的日期时间格式

返回一个日期时间类型的值，或者在无法解析输入字符串时返回NUL

对应的日期格式如下：

format参数定义了输入字符串的日期时间格式，可以包含各种日期时间格式化符号，如%Y、%m、%d等，用来表示年、月、日等不同部分

具体的格式化符号及其含义如下：（注意是两位数字。这里埋一个伏笔，如果遇到不是两位的也可做处理）

• %Y：四位年份
• %y：两位年份
• %m：两位月份
• %c：月份（0-12）
• %d：两位日期
• %H：小时（00-23）
• %h：小时（01-12）
• %i：两位分钟
• %s：两位秒数
• %p：AM或PM

简单的示例如下：

• 将字符串’2024-03-28’转换为日期类型：SELECT STR_TO_DATE('2024-03-28', '%Y-%m-%d');，返回值为2024-03-28
• 将字符串’Mar 28, 2024 10:30:00 PM’转换为日期时间类型：SELECT STR_TO_DATE('Mar 28, 2024 10:30:00 PM', '%b %d, %Y %h:%i:%s %p');，返回值为2024-03-28 22:30:00

2. Demo

employee的表，其中有一个字段hire_date存储了员工的入职日期，类型为字符串

现在想将这个字段转换为日期类型，并进行一些基本的查询操作

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    hire_date VARCHAR(20)
);

INSERT INTO employee (id, name, hire_date) VALUES
(1, 'John Doe', '2020-01-15'),
(2, 'Jane Smith', '2019-09-10'),
(3, 'Alice Johnson', '2021-03-25');

查询入职日期在2020年之后的员工：

SELECT * FROM employee WHERE STR_TO_DATE(hire_date, '%Y-%m-%d') > '2020-01-01';

查询入职日期在2019年的员工：

SELECT * FROM employee WHERE STR_TO_DATE(hire_date, '%Y-%m-%d') BETWEEN '2019-01-01' AND '2019-12-31';

3. 实战Demo

类似如下Demo

SELECT * FROM equipment_tyre_repare_order_detail where status = 1  ORDER BY STR_TO_DATE(repare_time, '%Y-%m-%d')

最终截图如下：

如果遇到无法解析的情况可以更换为如下：
（日期字段repare_time有一位月份或日期的情况，可以使用DATE_FORMAT函数来处理，DATE_FORMAT函数允许您指定日期的格式，以便正确解析日期字符串）

SELECT * FROM equipment_tyre_repare_order_detail 
WHERE status = 1 
AND STR_TO_DATE(DATE_FORMAT(repare_time, '%Y-%m-%d'), '%Y-%m-%d');

如果还是不行，可以再次升级：
使用如下代码：（LPAD函数来确保日期和月份是两位数，使用STR_TO_DATE函数将其转换为日期对象，并对其进行排序）

SELECT * FROM equipment_tyre_repare_order_detail 
WHERE status = 1 
ORDER BY STR_TO_DATE(
    CONCAT(
        SUBSTRING_INDEX(repare_time, '-', 1), '-', 
        LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(repare_time, '-', -2), '-', 1), 2, '0'), '-', 
        LPAD(SUBSTRING_INDEX(repare_time, '-', -1), 2, '0')
    ),
    '%Y-%m-%d'
);

4. Sql彩蛋

上述Sql中提及LPAD以及SUBSTRING_INDEX函数

知识点补充如下：

4.1 LPAD函数

LPAD是MySQL中的一个字符串函数，用于向一个字符串的左侧添加指定的字符，直到达到指定的长度

它的基本语法如下：LPAD(str, len, padstr)

• str：要进行填充的字符串
• len：填充后字符串的长度
• padstr：要填充的字符或子字符串

Demo如下：

假有一个字符串’123’，现在希望将其填充到长度为5，用字符’0’进行填充，即在字符串的左侧填充两个’0’，使其长度达到5

SELECT LPAD('123', 5, '0');

最终得到结果00123

4.2 SUBSTRING_INDEX函数

SUBSTRING_INDEX 是 MySQL 中的一个字符串函数，用于从一个字符串中获取子字符串，基于指定的分隔符和索引位置

SUBSTRING_INDEX(str, delim, count)

• str：要处理的字符串
• delim：分隔符，用于标识子字符串的边界
• count：要返回的子字符串的数量

从字符串 str 的开头或结尾开始，根据 delim 分隔符将其分割为多个子字符串，并返回其中的第 count 个子字符串

• 如果 count 为正数，则从字符串开头开始计数
• 如果 count 为负数，则从字符串结尾开始计数
• 如果 count 为0，则返回整个字符串

Demo：

1. 假设有一个字符串 ‘apple,banana,cherry,grape’，现在希望从该字符串中获取第二个逗号分隔的子字符串，即 ‘banana’

SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', 2);

返回apple,banana

2. 从字符串的末尾开始计数，可以使用负数作为 count 参数，例如获取倒数第二个逗号后的子字符串

SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', -2);

返回 cherry,grape

5. Java彩蛋

置于为何要分析这个函数，源头在于一开始我用的Java代码处理，但是一直无法生效排序，后续才使用Sql内存来处理，总体而言推荐使用Sql来处理，以下代码为题外话

代码如下：

/**
 * 轮胎更换记录的条件查询
 * @param tyreRepareOrderDetailVo
 * @param query
 * @return
 */
@Override
public IPage<TyreRepareOrderDetail> inquire(TyreRepareOrderDetailVO tyreRepareOrderDetailVo, Query query){
	QueryWrapper<TyreRepareOrderDetail> wrapper = new QueryWrapper<TyreRepareOrderDetail>()
		.eq("status", 1)
		.orderByAsc("equipment_no") // 按照车号升序排列
		.orderByDesc("replace_location");
	
	IPage<TyreRepareOrderDetail> pages = super.page(Condition.getPage(query), wrapper);

	// 对更换日期进行排序
	List<TyreRepareOrderDetail> records = pages.getRecords();
	Collections.sort(records, new Comparator<TyreRepareOrderDetail>() {
		@Override
		public int compare(TyreRepareOrderDetail detail1, TyreRepareOrderDetail detail2) {
			// 比较更换日期
			int compareResult = compareDateStrings(detail1.getRepareTime(), detail2.getRepareTime());
			if (compareResult != 0) {
				return compareResult;
			}
			// 如果更换日期相同，则比较车号
			compareResult = detail1.getEquipmentNo().compareTo(detail2.getEquipmentNo());
			if (compareResult != 0) {
				return compareResult;
			}
			// 如果车号相同，则比较更换位置
			return detail1.getReplaceLocation().compareTo(detail2.getReplaceLocation());
		}

		// 比较日期字符串
		private int compareDateStrings(String dateString1, String dateString2) {
			LocalDate date1 = parseDateString(dateString1);
			LocalDate date2 = parseDateString(dateString2);
			return date2.compareTo(date1); // 降序排序
		}

		private LocalDate parseDateString(String dateString) {
			// 使用 "-" 进行分割
			String[] parts = dateString.split("-");

			// 根据日期字符串中各部分的值判断是否需要补零
			String year = parts[0];
			String month = parts[1].length() == 1 ? "0" + parts[1] : parts[1];
			String day = parts[2].length() == 1 ? "0" + parts[2] : parts[2];

			// 拼接成完整的日期字符串
			String formattedDate = String.format("%s-%s-%s", year, month, day);

			// 使用 DateTimeFormatter 解析日期字符串
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
			return LocalDate.parse(formattedDate, formatter);
		}
	});
	// 更新结果集，不设置这个一直都是单个页面的排序
	pages.setRecords(records);
	return pages;
}

后续将其整理成自定义的排序模块：（类似模版使用，专门处理数据库为String，但是后端排序需要使用日期格式）
总体而言还是推荐使用Sql内存排序，毕竟取出在Java代码排序也费事费时

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Comparator;

public class DateComparator implements Comparator<String> {
    @Override
    public int compare(String dateString1, String dateString2) {
        LocalDate date1 = parseDateString(dateString1);
        LocalDate date2 = parseDateString(dateString2);
        return date2.compareTo(date1); // 降序排序
    }

    private LocalDate parseDateString(String dateString) {
        // 使用 "-" 进行分割
        String[] parts = dateString.split("-");
        
        // 根据日期字符串中各部分的值判断是否需要补零
        String year = parts[0];
        String month = parts[1].length() == 1 ? "0" + parts[1] : parts[1];
        String day = parts[2].length() == 1 ? "0" + parts[2] : parts[2];
        
        // 拼接成完整的日期字符串
        String formattedDate = String.format("%s-%s-%s", year, month, day);
        
        // 使用 DateTimeFormatter 解析日期字符串
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        return LocalDate.parse(formattedDate, formatter);
    }
}