mysql函数类型转换错误

Question

i have function return employee number integer

CREATE  FUNCTION `getUser`(s CHAR(20)) RETURNS char(50) CHARSET latin1
RETURN  @user;

when i execute its return with (0.00 sec)

but its execute this

select e.emp_number from hs_hr_employee e where 

hie_code_1 in ( select hie_code_1 from hs_hr_emp_level L,hs_hr_users U   where L.emp_number=U.emp_number and L.emp_number=getUser2() and  ( U.def_level=1 or U.def_level=4 )   )
or
 hie_code_3 in ( select hie_code_3 from hs_hr_emp_level L,hs_hr_users U   where L.emp_number=U.emp_number and L.emp_number=getUser2()  and  U.def_level=2   )
or
  hie_code_4 in ( select hie_code_4 from hs_hr_emp_level L,hs_hr_users U   where L.emp_number=U.emp_number and L.emp_number=getUser2()   and  U.def_level=3 )

get more slower;

but when i hard coded to

select e.emp_number from hs_hr_employee e where 

hie_code_1 in ( select hie_code_1 from hs_hr_emp_level L,hs_hr_users U   where L.emp_number=U.emp_number and L.emp_number=5 and  ( U.def_level=1 or U.def_level=4 )   )
or
 hie_code_3 in ( select hie_code_3 from hs_hr_emp_level L,hs_hr_users U   where L.emp_number=U.emp_number and L.emp_number=5  and  U.def_level=2   )
or
  hie_code_4 in ( select hie_code_4 from hs_hr_emp_level L,hs_hr_users U   where L.emp_number=U.emp_number and L.emp_number=5   and  U.def_level=3 )

this will return with with (0.00 sec) time. please give solution for optimiste function ;

Answer 1

Your samples make my eyes hurt, but here's an attempt at a possible alternative

SELECT DISTINCT e.emp_number 

FROM hs_hr_employee e 

INNER JOIN hs_hr_emp_level L
ON L.emp_number = e.emp_number
AND (
    e.hie_code_1 = U.hie_code_1
    OR e.hie_code_3 = U.hie_code_3
    OR e.hie_code_4 = U.hie_code_4
)

INNER JOIN hs_hr_users U 
ON U.emp_number = e.emp_number
AND U.def_level IN (1,2,3,4)

-- You stated getUser2() returns paramter @user, so just accessing directly
WHERE e.emp_number = @user

Assumptions as follows

1. hs_hr_employee.emp_number == hs_hr_emp_level.emp_number == hs_hr_emp_level.emp_number
2. hs_hr_employee.hie_code_*n* ==hs_hr_users.hie_code_n
3. getUser2() returns @user, which is an employee number

Syntatically the above does what your complex query is doing. It's hard to advise as the above feels incorrect - if you won't supply a full table structure / suitable details though then I can't help further.