如何按自定义mm-yy格式日期对MySQL数据进行升降序查询？

Hey Afzal, totally get where you're coming from—storing dates as custom strings can throw off sorting since databases treat them as text instead of chronological values. Let's walk through how to fix this for the most common databases:

MySQL/MariaDB #

Use the STR_TO_DATE() function to convert your string into a proper date type, then sort on that converted value:

升序查询 #

SELECT * FROM your_table
ORDER BY STR_TO_DATE(your_date_column, '%m-%Y') ASC;

降序查询 #

SELECT * FROM your_table
ORDER BY STR_TO_DATE(your_date_column, '%m-%Y') DESC;

The %m matches the two-digit month, and %Y matches the four-digit year—this tells MySQL exactly how to parse your custom string into a date it can sort correctly.

PostgreSQL #

PostgreSQL uses TO_DATE() for this conversion. The syntax is similar:

升序查询 #

SELECT * FROM your_table
ORDER BY TO_DATE(your_date_column, 'MM-YYYY') ASC;

降序查询 #

SELECT * FROM your_table
ORDER BY TO_DATE(your_date_column, 'MM-YYYY') DESC;

Here, MM represents the two-digit month, and YYYY is the four-digit year.

SQL Server #

SQL Server has a couple of options. One reliable method is to split the string into month and year, then build a date using DATEFROMPARTS():

升序查询 #

SELECT * FROM your_table
ORDER BY DATEFROMPARTS(RIGHT(your_date_column, 4), LEFT(your_date_column, 2), 1) ASC;

降序查询 #

SELECT * FROM your_table
ORDER BY DATEFROMPARTS(RIGHT(your_date_column, 4), LEFT(your_date_column, 2), 1) DESC;

RIGHT(your_date_column,4) grabs the four-digit year, LEFT(your_date_column,2) gets the two-digit month, and we use 1 as the day (since your format doesn't include it—any valid day works here).

性能优化建议 #

If you're sorting this column frequently, consider adding a computed/stored column that holds the converted date permanently. For example, in MySQL:

ALTER TABLE your_table
ADD COLUMN parsed_date DATE AS (STR_TO_DATE(your_date_column, '%m-%Y')) STORED;

Then you can sort directly on parsed_date without converting the string every time—this will be much faster for large datasets.

Hope this gets you sorted (pun totally intended!) with your date queries. If you're working with a less common database, just let me know and I can tweak the solution for you.

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