1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
$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'
]
];
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;
}
}
}
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;
}
}
}
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;
}
}
}
}
}
}
|