如何在MySQL中使用数字字符串的比较运算符？

我有一个员工表，有类似领域的经验VARCHAR类型，此字段结合了用短划线 (-) 分隔的总年份和总月份，因此我必须按年份过滤具有 3 年以上经验的经验。

我的表结构：

所以现在我必须获得3年以上经验的id。 我尝试如下，

SELECT * FROM employee WHERE experience LIKE '>=3%';

我知道比较运算符不支持字符串，但我没有任何新的解决方案。有什么解决办法吗？

基于转换为数字的更好答案：

select * from employee where cast(substring(experience, 1, instr(experience, '-')-1) as signed) >= 3;

mysql> select cast(substring('11-3', 1, instr('11-3', '-')-1) as signed);
+------------------------------------------------------------+
| cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) |
+------------------------------------------------------------+
|                                                         11 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) > 3;
+----------------------------------------------------------------+
| cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) > 3 |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) >= 3;
+-----------------------------------------------------------------+
| cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) >= 3 |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('3-0', 1, instr('3-0', '-')-1) as signed) >= 3;
+---------------------------------------------------------------+
| cast(substring('3-0', 1, instr('3-0', '-')-1) as signed) >= 3 |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('2-11', 1, instr('2-11', '-')-1) as signed) >= 3;
+-----------------------------------------------------------------+
| cast(substring('2-11', 1, instr('2-11', '-')-1) as signed) >= 3 |
+-----------------------------------------------------------------+
|                                                               0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('22-11', 1, instr('22-11', '-')-1) as signed) >= 3; 
+-------------------------------------------------------------------+
| cast(substring('22-11', 1, instr('22-11', '-')-1) as signed) >= 3 |
+-------------------------------------------------------------------+
|                                                                 1 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('11-0', 1, instr('11-0', '-')-1) as signed) >= 3; 
+-----------------------------------------------------------------+
| cast(substring('11-0', 1, instr('11-0', '-')-1) as signed) >= 3 |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

unsigned可能会更好的可读性。不太可能对查询产生任何影响。