一、建立测试环境：MySQL5.7.11
mysql> create table a (
a1 char(18) not null default '',
a2 char(20),
primary key(a1)
);
mysql> create table b (
b1 char(18) not null default '',
b2 char(20),
primary key(b1)
);
mysql> DELIMITER $$
DROP PROCEDURE IF EXISTS proc_auto_insertdata$$
CREATE PROCEDURE proc_auto_insertdata()
BEGIN
DECLARE init_data INTEGER DEFAULT 1;
DECLARE str char(6);
delete from a;
delete from b;
WHILE init_data <= 2000 DO
IF init_data<10 THEN SET str = concat('000', init_data);
ELSEIF init_data<100 THEN SET str = concat('00', init_data);
ELSEIF init_data<1000 THEN SET str = concat('0', init_data);
ELSE SET str = init_data;
END IF;
INSERT INTO a VALUES(str, CONCAT('用户A-',init_data));
INSERT INTO b VALUES(init_data, CONCAT('用户B-',init_data));
SET init_data = init_data + 1;
END WHILE;
END$$
DELIMITER ;
mysql> CALL proc_auto_insertdata();
mysql> DELIMITER $$
DROP FUNCTION IF EXISTS get_code$$
CREATE DEFINER = CURRENT_USER FUNCTION get_code(in_code CHAR(4))
RETURNS CHAR(4) NO SQL
BEGIN
DECLARE str CHAR(4) default '';
DECLARE init_data INTEGER DEFAULT 1;
SET init_data = in_code;
IF init_data<10 THEN SET str = concat('000', init_data);
ELSEIF init_data<100 THEN SET str = concat('00', init_data);
ELSEIF init_data<1000 THEN SET str = concat('0', init_data);
ELSE SET str = init_data;
END IF;
RETURN str;
END;$$
DELIMITER ;
===========================================================================
二、测试join连接一：
mysql> explain select a1, a2, b2 from a
join b ON a1=get_code(b1);
MySQL 5.7.11 (1 min 25.67 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`a`.`a1` AS `a1`,
`test`.`a`.`a2` AS `a2`,`test`.`b`.`b2` AS `b2`
from `test`.`a` join `test`.`b`
where (`test`.`a`.`a1` = `get_code`(`test`.`b`.`b1`)) |
+-------+------+-----------------------------------------------------------+
MySQL 5.5.28 (47.88 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000
Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
===========================================================================
三、测试join连接二：
mysql> explain select AA.code, AA.a2, BB.b2 from (
select a1 as code, a2 from a
) as AA
join (
select get_code(b1) as code, b2 from b
) as BB ON AA.code = BB.code;
MySQL 5.7.11 (1 min 25.18 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 se
mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`a`.`a1` AS `code`,`test`.`a`.`a2` AS `a2`,`test`.`b`.`b2` AS `b2` from `test`.`a` join `test`.`b` where (`test`.`a`.`a1` = `get_code`(`test`.`b`.`b1`)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+