How to compare a user set variable in MySQL?

Yes, a derived table will do. The inner select block below is a derived table. And every derived table needs a name. In my case, xDerived.

The strategy is to let the derived table cleanse the use of the column name. Coming out of the derived chunk is a clean column named num which the outer select is free to use.

Schema

create table employee
(   id int auto_increment primary key,
    experience varchar(20) not null
);

-- truncate table employee;
insert employee(experience) values 
('4-5'),('7-1'),('4-1'),('6-5'),('8-6'),('5-9'),('10-4');

Query

select id,experience,num 
from 
(   SELECT id,experience, 
    CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) AS num  
    FROM employee  
) xDerived 
where num>=7;

Results

+----+------------+------+
| id | experience | num  |
+----+------------+------+
|  2 | 7-1        |    7 |
|  5 | 8-6        |    8 |
|  7 | 10-4       |   10 |
+----+------------+------+

Note, your @num concept was faulty but hopefully I interpreted what you meant to do above.

Also, I went with 7 not 3 because all your sample data would have returned, and I wanted to show you it would work.


The AS num instruction names the result of convert as num, not a variable named @num.

You could repeat the convert

SELECT experience,CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER)
FROM employee
WHERE CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) >= 3;

Or use a partial (derived) table (only one convert)

SELECT experience,num 
FROM (select experience,
      CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) as num
      FROM employee) as partialtable WHERE num>=3;