PHP 模拟mysql 执行过程中IndexKey提取过程

• 用php实现只是为了方便自己更容易理解提取过程

// php模拟IndexKey提取过程
$table = <<<EOF
create table tbl_test (a int primary key, b int, c int, d int, e varchar(50));
create index idx_bcd on tbl_test(b, c, d);
insert into tbl_test values (4,3,1,1,'a');
insert into tbl_test values (1,1,1,2,'d');
insert into tbl_test values (8,8,7,8,'h');
insert into tbl_test values (2,2,1,2,'g');
insert into tbl_test values (5,2,2,5,'e');
insert into tbl_test values (3,3,2,1,'c');
insert into tbl_test values (7,4,0,5,'b');
insert into tbl_test values (6,5,2,4,'f');
EOF;

$sql = 'select * from tbl_test where b >= 2 and b < 7 and c > 0 and d != 2 and e != "a"';

$indexFilter = [
	'Index_First_Key' => [],
	'Index_Last_Key' => [],
	'Index_Filter' => []
];
$indexs = ['b', 'c', 'd'];
$wheres = [
	[
		'key' => 'b',
		'type' => '>=',
		'val' => '2'
	],
	[
		'key' => 'b',
		'type' => '<',
		'val' => '7'
	],
	[
		'key' => 'c',
		'type' => '>',
		'val' => '0'
	],
	[
		'key' => 'd',
		'type' => '!=',
		'val' => '2'
	],
	[
		'key' => 'e',
		'type' => '!=',
		'val' => 'a'
	]
];
// Index_First_Key 提取过程
foreach ($indexs as $index) {
	foreach ($wheres as $where) {
		if ($where['key'] !== $index) {
			continue;
		}
		if ($where['key'] === $index && in_array($where['type'], ['=', '>='], true)) {
			$indexFilter['Index_First_Key'][$index] = implode(' ', $where);
			break;
		}
		if ($where['key'] === $index && $where['type'] === '>') {
			$indexFilter['Index_First_Key'][$index] = implode(' ', $where);
			break 2;
		}
	}
}
// Index_Last_Key 提取过程
foreach ($indexs as $index) {
	foreach ($wheres as $where) {
		if ($where['key'] !== $index) {
			continue;
		}
		if ($where['key'] === $index && in_array($where['type'], ['=', '<='], true)) {
			$indexFilter['Index_Last_Key'][$index] = implode(' ', $where);
			break;
		}
		if ($where['key'] === $index && $where['type'] === '<') {
			$indexFilter['Index_Last_Key'][$index] = implode(' ', $where);
			break 2;
		}
	}
}
// Index_Filter 提取过程
foreach ($indexs as $key => $index) {
	if ($key === 0 ) {
		foreach ($wheres as $where) {
			if ($where['key'] !== $index) {
				continue;
			}
			if ($where['key'] === $index && !in_array($where['type'], ['=', '>=','>','<','<='], true)) {
				foreach ($wheres as $where1) {
					if ($where1['key'] !== $index) {
						continue;
					}
					if ($where1['key'] === $index) {
						$indexFilter['Index_Filter'][$index] = implode(' ', $where1);
						break 2;
					}
				}

			}
		}
	}
}
// IndexFilter提取过程略微繁琐，感觉不优雅，没有实现，可以自己理解
``

参考文章

https://www.cnblogs.com/mengxinJ/p/14045520.html#!comments