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
106
107
108
109
110
| --------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `test`
--------------
--------------
create table `test`
( `id` integer unsigned not null auto_increment primary key,
`x` integer unsigned not null,
`y` integer unsigned not null,
`lib` varchar(255) not null,
index `idx` (`x`,`y`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `test` (`x`,`y`,`lib`) values
(1,14,'un'),(7,8,'deux'),(0,5,'trois'),(4,9,'quatre')
--------------
--------------
select * from `test`
--------------
+----+---+----+--------+
| id | x | y | lib |
+----+---+----+--------+
| 1 | 1 | 14 | un |
| 2 | 7 | 8 | deux |
| 3 | 0 | 5 | trois |
| 4 | 4 | 9 | quatre |
+----+---+----+--------+
--------------
commit
--------------
--------------
DROP PROCEDURE IF EXISTS param
--------------
--------------
CREATE PROCEDURE param (IN _Texte varchar(255))
DETERMINISTIC
NO SQL
BEGIN
DECLARE _v varchar(255) DEFAULT '';
DECLARE _i integer DEFAULT NULL;
SET _i = 1;
SET @z = 'SELECT * FROM `test` WHERE ';
WHILE _i < 4
DO
SET _v = substring_index(substring_index(_Texte, '}', _i), '{', -1);
SET @z = concat(@z,'(', substring_index(substring_index(_v, ',', 1),':', 1),' = ',
substring_index(substring_index(_v, ',', 1),':',-1),' AND ',
substring_index(substring_index(_v, ',', -1),':', 1),' = ',
substring_index(substring_index(_v, ',', -1),':',-1),')');
SET _i = _i + 1;
IF _i < 4 THEN
SET @z = concat(@z,' OR ');
END IF;
END WHILE;
select @z as '';
PREPARE stmt FROM @z;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
--------------
--------------
call `param`('[{x:1,y:14},{x:7,y:8},{x:0,y:5}]')
--------------
+-----------------------------------------------------------------------------------------+
| |
+-----------------------------------------------------------------------------------------+
| SELECT * FROM `test` WHERE (x = 1 AND y = 14) OR (x = 7 AND y = 8) OR (x = 0 AND y = 5) |
+-----------------------------------------------------------------------------------------+
+----+---+----+-------+
| id | x | y | lib |
+----+---+----+-------+
| 3 | 0 | 5 | trois |
| 1 | 1 | 14 | un |
| 2 | 7 | 8 | deux |
+----+---+----+-------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
