select getSfz(A.身份证号码),B.姓名 from A
left join B ON A.身份证号码=B.身份证号码;
自定义函数放在select中极快,约十几秒完成
select A.身份证号码, B.姓名 from A
left join B ON getSfz(A.身份证号码) = getSfz(B.身份证号码)
自定义函数放在where或ON条件中,这种方式查询就变得极慢,约15分钟才完成,但在MYSQL5.5.28环境下约几十秒完成
------------------------------------------------------------------------------
测试中的真实数据:
1-将18位的身份证号码转换为15位的号码
DELIMITER $
DROP FUNCTION IF EXISTS get_sfz$
CREATE DEFINER = CURRENT_USER FUNCTION get_sfz(
in_SFZ CHAR(32))
RETURNS CHAR(32)
NO SQL
BEGIN
DECLARE c_str CHAR(32) default '';
SET c_str = in_SFZ;
IF LENGTH(in_SFZ) = 18 THEN
SET c_str = concat( left(in_SFZ,6), substring(in_SFZ,9,9) );
END IF;
RETURN c_str;
END;$
DELIMITER ;
2-查询语句:
select a.KHJLFLNM, a.GSWDBRNO, get_sfz(a.ZJHMCFNO) as sfz1, b.CIP_CSTNO as khh1
from GLFMKHJL as a
left join PB_CSTINF_PRO as b ON get_sfz( b.CIP_CTFNO ) = get_sfz(a.ZJHMCFNO)
where length(a.KHJLSTAF)=7 and a.XTDLBOOL<>'9';
1-将18位的身份证号码转换为15位的号码
DELIMITER $
DROP FUNCTION IF EXISTS get_sfz$
CREATE DEFINER = CURRENT_USER FUNCTION get_sfz(
in_SFZ CHAR(32))
RETURNS CHAR(32)
NO SQL
BEGIN
DECLARE c_str CHAR(32) default '';
SET c_str = in_SFZ;
IF LENGTH(in_SFZ) = 18 THEN
SET c_str = concat( left(in_SFZ,6), substring(in_SFZ,9,9) );
END IF;
RETURN c_str;
END;$
DELIMITER ;
2-查询SQL
select a.* from (
select KHJLFLNM as xm, GSWDBRNO as jg, get_sfz( ZJHMCFNO ) as sfz1
from GLFMKHJL where length(KHJLSTAF)=7 and XTDLBOOL<>'9'
) as a
left join (
select CIP_CSTNO as khh1, get_sfz( CIP_CTFNO ) as sfz2
from IBS_PB_CSTINF_PRO
) as b on a.sfz1=b.sfz2;
---------------------------------------------------------------------------------------------
对SQL进行计划查看:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: GLFMKHJL
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 900
filtered: 90.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: IBS_PB_CSTINF_PRO
partitions: NULL
type: index
possible_keys: NULL
key: sfz_idx
key_len: 63
ref: NULL
rows: 39411
filtered: 100.00
Extra: Using where; Using index; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.01 sec)
------------------------------------------------------------------------------------------------------------------
show warnings;
| Note | 1003 | /* select#1 */ select `xxgl2`.`GLFMKHJL`.`KHJLFLNM` AS `xm`,`xxgl2`.`GLFMKHJL`.`GSWDBRNO` AS `jg`,`get_sfz15to18`(`xxgl2`.`GLFMKHJL`.`ZJHMCFNO`) AS `sfz1` from `xxgl2`.`GLFMKHJL` left join (`xxgl2`.`IBS_PB_CSTINF_PRO`) on((`get_sfz15to18`(`xxgl2`.`GLFMKHJL`.`ZJHMCFNO`) = `get_sfz15to18`(`xxgl2`.`IBS_PB_CSTINF_PRO`.`CIP_CTFNO`))) where ((length(`xxgl2`.`GLFMKHJL`.`KHJLSTAF`) = 7) and (`xxgl2`.`GLFMKHJL`.`XTDLBOOL` <> '9')
从警告信息看,这条SQL被自动转换为:
select KHJLFLNM AS xm,GSWDBRNO AS jg, get_sfz15to18(ZJHMCFNO) AS sfz1
from GLFMKHJL
left join IBS_PB_CSTINF_PRO on get_sfz15to18(ZJHMCFNO) = get_sfz15to18(CIP_CTFNO)
where length(KHJLSTAF) = 7 and XTDLBOOL<>'9'