详细分析Mysql中的STR

2024-05-14 1161阅读

温馨提示：这篇文章已超过384天没有更新，请注意相关的内容是否还可用！

目录

  • 前言
  • 1. 基本知识
  • 2. Demo
  • 3. 实战Demo
  • 4. Sql彩蛋
    • 4.1 LPAD函数
    • 4.2 SUBSTRING_INDEX函数
    • 5. Java彩蛋

      前言

      对于该知识点，主要因为数据库类型为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')

            最终截图如下：

            详细分析Mysql中的STR

            如果遇到无法解析的情况可以更换为如下：

            （日期字段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

                  1. 从字符串的末尾开始计数，可以使用负数作为 count 参数，例如获取倒数第二个逗号后的子字符串
                  SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', -2);

                  返回 cherry,grape

                  5. Java彩蛋

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

                  详细分析Mysql中的STR

                  代码如下：

                  /**
 * 轮胎更换记录的条件查询
 * @param tyreRepareOrderDetailVo
 * @param query
 * @return
 */
@Override
public IPage inquire(TyreRepareOrderDetailVO tyreRepareOrderDetailVo, Query query){
	QueryWrapper wrapper = new QueryWrapper()
		.eq("status", 1)
		.orderByAsc("equipment_no") // 按照车号升序排列
		.orderByDesc("replace_location");
	
	IPage pages = super.page(Condition.getPage(query), wrapper);
	// 对更换日期进行排序
	List records = pages.getRecords();
	Collections.sort(records, new Comparator() {
		@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 {
    @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);
    }
}
VPS购买请点击我

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!

相关阅读

目录[+]