基于多条件批量更新MySQL多行数据问题求助

问题出在VARCHAR类型的日期字符串比较逻辑上：当直接用字符串做BETWEEN比较时，数据库会按字符的ASCII顺序逐位对比。'12-04-2024'的第一个字符是'1'，小于'20-03-2024'的第一个字符'2'，所以你的条件BETWEEN '20-03-2024' AND '12-04-2024'实际上是一个无效范围（左边界大于右边界），只会匹配比'12-04-2024'大且比'20-03-2024'小的字符串，自然第三行的'12-04-2024'不会被选中。

修正方案：将字符串日期转换为日期类型再比较 #

需要用数据库对应的日期转换函数，把VARCHAR格式的dd-mm-yyyy字符串转为日期类型，再进行范围判断。以下是主流数据库的实现：

1. MySQL/MariaDB

使用STR_TO_DATE函数解析字符串为日期：

UPDATE mydb_stock.sql_product_sales 
SET sales_price = 7.5
WHERE STR_TO_DATE(invoice_date, '%d-%m-%Y') BETWEEN '2024-03-20' AND '2024-04-12' 
  AND product_name = 'Ball';

• %d-%m-%Y对应你的日期格式日-月-年
• 右侧的日期用标准YYYY-MM-DD格式，避免字符串比较问题

2. SQL Server

使用CONVERT函数，格式代码105对应dd-mm-yyyy：

UPDATE mydb_stock.sql_product_sales 
SET sales_price = 7.5
WHERE CONVERT(DATE, invoice_date, 105) BETWEEN '2024-03-20' AND '2024-04-12' 
  AND product_name = 'Ball';

3. Oracle

使用TO_DATE函数指定格式：

UPDATE mydb_stock.sql_product_sales 
SET sales_price = 7.5
WHERE TO_DATE(invoice_date, 'DD-MM-YYYY') BETWEEN TO_DATE('20-03-2024', 'DD-MM-YYYY') AND TO_DATE('12-04-2024', 'DD-MM-YYYY')
  AND product_name = 'Ball';

执行修正后的SQL，就能同时更新第1行和第3行的Ball记录。

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