| 1 |
SET @@cte_max_recursion_depth := 10000; |
| 2 |
DROP TABLE IF EXISTS test; |
| 3 |
Warnings: |
| 4 |
Note 1051 Unknown table 'test.test' |
| 5 |
CREATE TABLE test (id INT, json_value JSON); |
| 6 |
INSERT INTO test (id, json_value) |
| 7 |
WITH RECURSIVE cte AS ( |
| 8 |
SELECT 1 id |
| 9 |
UNION ALL |
| 10 |
SELECT 1 + id FROM cte WHERE id < @@cte_max_recursion_depth |
| 11 |
) |
| 12 |
SELECT id, |
| 13 |
JSON_ARRAY(CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 14 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 15 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 16 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 17 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 18 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 19 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 20 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 21 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED), |
| 22 |
CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED)) |
| 23 |
FROM cte |
| 24 |
WHERE (@seed := 0) = 0 |
| 25 |
ORDER BY id; |
| 26 |
Warnings: |
| 27 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 28 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 29 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 30 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 31 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 32 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 33 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 34 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 35 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 36 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 37 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 38 |
SELECT id, CAST(json_value AS CHAR) json_value FROM test ORDER BY id LIMIT 20; |
| 39 |
id json_value |
| 40 |
1 [36671, 15019, 31420, 53623, 15628, 90966, 5032, 29840, 8761, 27514] |
| 41 |
2 [88413, 26706, 37385, 26766, 66673, 46376, 96862, 83168, 8807, 23554] |
| 42 |
3 [37412, 20135, 25971, 74819, 34176, 18780, 90939, 11663, 70542, 79371] |
| 43 |
4 [90200, 53748, 43627, 49276, 92689, 7437, 81200, 18880, 73717, 77364] |
| 44 |
5 [21804, 41638, 9477, 86462, 91302, 10560, 40603, 98577, 94875, 90570] |
| 45 |
6 [69269, 98520, 86862, 22414, 75258, 43757, 46698, 69142, 23320, 61863] |
| 46 |
7 [42709, 76624, 89815, 30905, 33358, 28906, 54713, 85446, 84020, 8674] |
| 47 |
8 [4857, 10452, 67127, 63279, 80156, 44155, 29662, 67151, 35373, 88000] |
| 48 |
9 [36420, 84000, 31929, 31338, 389, 54863, 59613, 33992, 34620, 96837] |
| 49 |
10 [37947, 17145, 85265, 22082, 65079, 67319, 38137, 43855, 81328, 68154] |
| 50 |
11 [82534, 3093, 80483, 84911, 87010, 48433, 57594, 78602, 18588, 17028] |
| 51 |
12 [47964, 33369, 40879, 74817, 86028, 51144, 785, 89679, 89986, 54944] |
| 52 |
13 [39720, 10939, 20896, 7832, 42889, 65435, 49592, 22419, 44681, 61900] |
| 53 |
14 [73977, 72208, 73763, 73404, 69856, 35067, 97116, 35462, 58805, 82017] |
| 54 |
15 [34680, 27072, 57557, 47334, 23004, 33080, 5465, 95924, 34718, 32414] |
| 55 |
16 [36959, 76359, 26495, 59856, 73366, 64514, 55665, 68091, 31971, 32029] |
| 56 |
17 [14116, 66090, 6125, 21880, 52322, 67334, 45459, 98284, 64112, 77352] |
| 57 |
18 [35757, 37716, 62871, 59293, 9860, 62104, 34882, 39829, 57542, 40012] |
| 58 |
19 [67628, 50296, 95320, 43648, 95, 50026, 62577, 54456, 12824, 82568] |
| 59 |
20 [13086, 25560, 32681, 48427, 15044, 76641, 45285, 52970, 7243, 59141] |
| 60 |
SET @random_value := 37947; |
| 61 |
SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$'); |
| 62 |
id |
| 63 |
10 |
| 64 |
2486 |
| 65 |
4962 |
| 66 |
7438 |
| 67 |
9915 |
| 68 |
ALTER TABLE test ADD INDEX mvi ((CAST(json_value->'$' AS UNSIGNED ARRAY))); |
| 69 |
EXPLAIN SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$'); |
| 70 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 71 |
1 SIMPLE test NULL ref mvi mvi 9 const 5 100.00 Using where |
| 72 |
Warnings: |
| 73 |
Note 1003 /* select#1 */ select `test`.`test`.`id` AS `id` from `test`.`test` where json'37947' member of (cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array)) |
| 74 |
SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$'); |
| 75 |
id |
| 76 |
10 |
| 77 |
2486 |
| 78 |
4962 |
| 79 |
7438 |
| 80 |
9915 |
| 81 |
SELECT id, CAST(json_value AS CHAR) json_value |
| 82 |
FROM test |
| 83 |
WHERE id IN (1311, 1312); |
| 84 |
id json_value |
| 85 |
1311 [48176, 84025, 77150, 23000, 37731, 70193, 14669, 91697, 71302, 87158] |
| 86 |
1312 [74452, 39930, 14394, 89531, 63967, 72608, 4875, 39049, 73010, 82241] |
| 87 |
SELECT id FROM test WHERE 48176 MEMBER OF (json_value->'$'); |
| 88 |
id |
| 89 |
1311 |
| 90 |
3787 |
| 91 |
6263 |
| 92 |
8739 |
| 93 |
SELECT id FROM test WHERE 74452 MEMBER OF (json_value->'$'); |
| 94 |
id |
| 95 |
1312 |
| 96 |
3788 |
| 97 |
6264 |
| 98 |
8740 |
| 99 |
DROP TABLE test; |