mysql求varchar類型的最大值(或按照varchar類型排序)

工作中為了友善後續擴充，建表時将數字類型定義為varchar類型進行存儲，但是在排序時會亂序，這裡是因為mysql預設order by 隻對數字與日期類型可以排序

示範：

一、資料初始化

CREATE TABLE `testvarcharmax` (
  `age` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

insert into testvarcharmax VALUES ('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9'),('10'),('11')；
           

二、錯誤結果

1.求age的最大值，結果預期為11，但是sql結果顯示9

mysql> select MAX(age) from testvarcharmax;
+----------+
| MAX(age) |
+----------+
| 9        |
+----------+
1 row in set (0.00 sec)
           

2.按age倒叙排列，結果預期第一行為11，但結果顯示9

mysql> select age from testvarcharmax order by age desc;
+------+
| age  |
+------+
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 11   |
| 10   |
| 1    |
+------+
11 rows in set (0.00 sec)
           

三、修改sql語句

方式一：對age執行加法運算，sql語句改為：

select MAX(age+0) from testvarcharmax;

select age from testvarcharmax order by age+0 desc;

mysql> select MAX(age+0) from testvarcharmax;
+------------+
| MAX(age+0) |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec)

mysql> select age from testvarcharmax order by age+0 desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)
           

方式二：使用CAST函數字将age轉換為int後求最大值或排序、sql語句修改為：

select MAX(CAST(age as signed int)) as maxAge from testvarcharmax;（或select MAX(CAST(age as signed integer)) as maxAge from testvarcharmax; select MAX(CAST(age as signed)) as maxAge from testvarcharmax;）

select age from testvarcharmax order by CAST(age as SIGNED) desc; （或select age from testvarcharmax order by CAST(age as SIGNED int) desc; select age from testvarcharmax order by CAST(age as SIGNED integer) desc;）

mysql> select MAX(CAST(age as signed integer)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CAST(age as signed int)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CAST(age as signed)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select age from testvarcharmax order by CAST(age as SIGNED) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CAST(age as SIGNED int) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CAST(age as SIGNED integer) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)
           

方式三、使用CONVERT函數将age轉換為int後求最大值或排序、sql語句修改為：

select MAX(CONVERT(age, signed)) as maxAge from testvarcharmax;（或select MAX(CONVERT(age, signed int)) as maxAge from testvarcharmax; select MAX(CONVERT(age, signed integer)) as maxAge from testvarcharmax;）

select age from testvarcharmax order by CONVERT(age, SIGNED) desc; （或select age from testvarcharmax order by CONVERT(age, SIGNED int) desc; select age from testvarcharmax order by CONVERT(age, SIGNED integer) desc;）

mysql> select MAX(CONVERT(age, signed)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CONVERT(age, signed int)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select MAX(CONVERT(age, signed integer)) as maxAge from testvarcharmax;
+--------+
| maxAge |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select age from testvarcharmax order by CONVERT(age, SIGNED) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CONVERT(age, SIGNED int) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)

mysql> select age from testvarcharmax order by CONVERT(age, SIGNED integer) desc;
+------+
| age  |
+------+
| 11   |
| 10   |
| 9    |
| 8    |
| 7    |
| 6    |
| 5    |
| 4    |
| 3    |
| 2    |
| 1    |
+------+
11 rows in set (0.00 sec)