如何在MySQL中对数字字符串使用比较运算符? [英] How to use comparison operator for numeric string in mysql?
问题描述
我有一个employee表,该表具有类似varchar类型的经验,该字段合并了年份和月份总数,并用破折号(-)分隔,因此我必须按具有3年以上经验的年份筛选经验./p>
我的表结构:
所以现在我必须拥有超过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
对于可读性可能会更好.不会对查询产生任何影响.
I have an employee table which has field like experience with varchar type, this field combines both total year and total month separated by dash(-), so I have to filter experience by year who has more than 3 years experience.
my table structure:
So now I have to get more than 3 years experienced id's. I tried like below,
SELECT * FROM employee WHERE experience LIKE '>=3%';
I know comparison operator will not support for string but I do not have any new solution for that, Is there any solution?
better answer based on converting to number:
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
would probably be better for readability. wouldn't likely have any affect on the query.
这篇关于如何在MySQL中对数字字符串使用比较运算符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!