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

前言

对于该知识点，主要由于数据库范例为String（类似2024-03-26），放置于后端操纵后，需要自界说比较，而且不是在sql内存做处理，后续特别麻烦
1. 基本知识

STR_TO_DATE是MySQL中的一个日期时间处理函数，用于将字符串转换为日期时间范例。
它的基本用法是将一个包含日期时间信息的字符串转换为对应的日期时间范例
基本的语法如下：

1. 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存储了员工的入职日期，范例为字符串
现在想将这个字段转换为日期范例，并进行一些基本的查询操纵

1. CREATE TABLE employee (
2.     id INT PRIMARY KEY,
3.     name VARCHAR(50),
4.     hire_date VARCHAR(20)
5. );
7. INSERT INTO employee (id, name, hire_date) VALUES
8. (1, 'John Doe', '2020-01-15'),
9. (2, 'Jane Smith', '2019-09-10'),
10. (3, 'Alice Johnson', '2021-03-25');

复制代码

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

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

复制代码

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

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

复制代码

3. 实战Demo

类似如下Demo

1. 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函数答应您指定日期的格式，以便正确剖析日期字符串）

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

复制代码

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

1. SELECT * FROM equipment_tyre_repare_order_detail
2. WHERE status = 1
3. ORDER BY STR_TO_DATE(
4.     CONCAT(
5.         SUBSTRING_INDEX(repare_time, '-', 1), '-',
6.         LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(repare_time, '-', -2), '-', 1), 2, '0'), '-',
7.         LPAD(SUBSTRING_INDEX(repare_time, '-', -1), 2, '0')
8.     ),
9.     '%Y-%m-%d'
10. );

复制代码

4. Sql彩蛋

上述Sql中提及LPAD以及SUBSTRING_INDEX函数
知识点补充如下：
4.1 LPAD函数

LPAD是MySQL中的一个字符串函数，用于向一个字符串的左侧添加指定的字符，直到达到指定的长度
它的基本语法如下：LPAD(str, len, padstr)

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

Demo如下：
假有一个字符串’123’，现在盼望将其添补到长度为5，用字符’0’进行添补，即在字符串的左侧添补两个’0’，使其长度达到5

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

复制代码

终极得到结果00123
4.2 SUBSTRING_INDEX函数

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

1. SUBSTRING_INDEX(str, delim, count)

复制代码

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

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

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

Demo：

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

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

复制代码

返回apple,banana

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

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

复制代码

返回 cherry,grape
5. Java彩蛋

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

代码如下：

1. /**
2. * 轮胎更换记录的条件查询
3. * @param tyreRepareOrderDetailVo
4. * @param query
5. * @return
6. */
7. @Override
8. public IPage<TyreRepareOrderDetail> inquire(TyreRepareOrderDetailVO tyreRepareOrderDetailVo, Query query){
9.         QueryWrapper<TyreRepareOrderDetail> wrapper = new QueryWrapper<TyreRepareOrderDetail>()
10.                 .eq("status", 1)
11.                 .orderByAsc("equipment_no") // 按照车号升序排列
12.                 .orderByDesc("replace_location");
13.        
14.         IPage<TyreRepareOrderDetail> pages = super.page(Condition.getPage(query), wrapper);
16.         // 对更换日期进行排序
17.         List<TyreRepareOrderDetail> records = pages.getRecords();
18.         Collections.sort(records, new Comparator<TyreRepareOrderDetail>() {
19.                 @Override
20.                 public int compare(TyreRepareOrderDetail detail1, TyreRepareOrderDetail detail2) {
21.                         // 比较更换日期
22.                         int compareResult = compareDateStrings(detail1.getRepareTime(), detail2.getRepareTime());
23.                         if (compareResult != 0) {
24.                                 return compareResult;
25.                         }
26.                         // 如果更换日期相同，则比较车号
27.                         compareResult = detail1.getEquipmentNo().compareTo(detail2.getEquipmentNo());
28.                         if (compareResult != 0) {
29.                                 return compareResult;
30.                         }
31.                         // 如果车号相同，则比较更换位置
32.                         return detail1.getReplaceLocation().compareTo(detail2.getReplaceLocation());
33.                 }
35.                 // 比较日期字符串
36.                 private int compareDateStrings(String dateString1, String dateString2) {
37.                         LocalDate date1 = parseDateString(dateString1);
38.                         LocalDate date2 = parseDateString(dateString2);
39.                         return date2.compareTo(date1); // 降序排序
40.                 }
42.                 private LocalDate parseDateString(String dateString) {
43.                         // 使用 "-" 进行分割
44.                         String[] parts = dateString.split("-");
46.                         // 根据日期字符串中各部分的值判断是否需要补零
47.                         String year = parts[0];
48.                         String month = parts[1].length() == 1 ? "0" + parts[1] : parts[1];
49.                         String day = parts[2].length() == 1 ? "0" + parts[2] : parts[2];
51.                         // 拼接成完整的日期字符串
52.                         String formattedDate = String.format("%s-%s-%s", year, month, day);
54.                         // 使用 DateTimeFormatter 解析日期字符串
55.                         DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
56.                         return LocalDate.parse(formattedDate, formatter);
57.                 }
58.         });
59.         // 更新结果集，不设置这个一直都是单个页面的排序
60.         pages.setRecords(records);
61.         return pages;
62. }

复制代码

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

1. import java.time.LocalDate;
2. import java.time.format.DateTimeFormatter;
3. import java.util.Comparator;
5. public class DateComparator implements Comparator<String> {
6.     @Override
7.     public int compare(String dateString1, String dateString2) {
8.         LocalDate date1 = parseDateString(dateString1);
9.         LocalDate date2 = parseDateString(dateString2);
10.         return date2.compareTo(date1); // 降序排序
11.     }
13.     private LocalDate parseDateString(String dateString) {
14.         // 使用 "-" 进行分割
15.         String[] parts = dateString.split("-");
16.         
17.         // 根据日期字符串中各部分的值判断是否需要补零
18.         String year = parts[0];
19.         String month = parts[1].length() == 1 ? "0" + parts[1] : parts[1];
20.         String day = parts[2].length() == 1 ? "0" + parts[2] : parts[2];
21.         
22.         // 拼接成完整的日期字符串
23.         String formattedDate = String.format("%s-%s-%s", year, month, day);
24.         
25.         // 使用 DateTimeFormatter 解析日期字符串
26.         DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
27.         return LocalDate.parse(formattedDate, formatter);
28.     }
29. }

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。