C#和MySQL技巧分享：日期的模糊查询

文章目录

• 前言+ 一、EF Core 模糊查询+ 二、MySql 日期模糊查询分析和优化+

• 2.1 测试环境准备+

• 2.1.1 创建数据库+ 2.1.2 查看测试数据

• 2.2 查询日期的运行效率对比+ 2.3 运行效率优化

• 三、EF Core 模糊查询优化+

• 3.1 字符串转日期+ 3.2 使用日期格式查询

• 四、优化建议+ 总结

前言

在处理数据库查询时，特别是在涉及到模糊查询和日期字段时，我们常常面临一个挑战：如何在确保查询效率的同时，实现精确和灵活的数据检索？众所周知，直接转换数据库字段类型进行匹配往往会导致查询效率下降，甚至引发全表搜索的问题，这在处理大量数据时尤为明显。因此，找到一种既能保持数据库性能又能满足查询需求的方法显得尤为重要。

一、EF Core 模糊查询

在使用Entity Framework Core（EF Core）进行数据库操作时，模糊查询是一个常用而又复杂的功能。在EF Core中，进行模糊查询通常涉及到Contains方法的使用。然而，如果直接对非字符串字段应用ToString()然后进行比较，EF Core会将其解析为字符串匹配。这种做法虽然在某些场景下有效，但可能导致效率问题。以下是一个典型的例子：

expression = expression.And(p => 
	// ... 其他条件
    p.CreateTime.ToString().Contains(strlike)) ||
    // ... 其他条件
    p.PayStatus.Contains(strlike));

这段代码试图在多个字段上应用模糊匹配，包括将日期时间转换为字符串进行匹配。这种操作会在SQL级别产生如下查询：

WHERE (`e`.`id` = `e7`.`sale_order_id`) AND ((@__strLike_1 LIKE '') OR (LOCATE(@__strLike_1, CAST(`e8`.`create_time` AS char) COLLATE utf8mb4_bin) > 0)))

这样做是非常不可取的做法，需要接受严厉的批评！

二、MySql 日期模糊查询分析和优化

2.1 测试环境准备

我用到的是MySql数据库，Mysql Workbench可视化桌面软件。

2.1.1 创建数据库

-- 检查并删除已存在的数据库
DROP DATABASE IF EXISTS `date_time_test`;

-- 创建数据库
CREATE DATABASE `date_time_test`;

-- 使用新创建的数据库
USE `date_time_test`;

-- 检查并删除已存在的表
DROP TABLE IF EXISTS sales_orders;

-- 创建表
CREATE TABLE sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    create_time DATETIME,
    order_no VARCHAR(50)
);

-- 定义分隔符
DELIMITER $$

-- 创建插入数据的存储过程
CREATE PROCEDURE InsertTestData()
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION; -- 开始事务
    WHILE i <= 1000000 DO
        INSERT INTO sales_orders (create_time, order_no) 
        VALUES (NOW() - INTERVAL FLOOR(RAND() * 365) DAY, CONCAT('Order_', LPAD(i, 8, '0')));
        IF i % 10000 = 0 THEN -- 每10000条数据提交一次事务
            COMMIT;
            START TRANSACTION;
        END IF;
        SET i = i + 1;
    END WHILE;
    COMMIT; -- 最后提交剩余的事务
END$$

-- 恢复默认分隔符
DELIMITER ;

-- 调用存储过程以生成数据
CALL InsertTestData();

2.1.2 查看测试数据

我这里运行了两次存储过程，因此生成了2百万的随机数据。

select count(1) from sales_orders

2.2 查询日期的运行效率对比

我们对比一下我能想到的几个写法：

SELECT count(1) FROM sales_orders
WHERE DATE_FORMAT(sales_orders.create_time, '%Y-%m-%d') = '2023-12-01';

SELECT count(1) FROM sales_orders
WHERE LOCATE('2023-12-01', CAST(sales_orders.create_time AS char) COLLATE utf8mb4_bin) > 0;

select count(1) from sales_orders
WHERE sales_orders.create_time >= '2023-12-01 00:00:00' and sales_orders.create_time < '2023-12-02 00:00:00';

-- 错误写法 这个只能查询到'2023-12-01 00:00:00'这个时间点的结果
select count(1) from sales_orders
WHERE sales