| 1 |
set @v1a="Hello"; |
| 2 |
set @v2a="World"; |
| 3 |
set @v3a=65; |
| 4 |
set @v4a=NULL; |
| 5 |
select @v1a, @v2a, @v3a, @v4a, @v_unknown; |
| 6 |
@v1a @v2a @v3a @v4a @v_unknown |
| 7 |
Hello World 65 NULL NULL |
| 8 |
select VARIABLE_NAME, VARIABLE_VALUE |
| 9 |
from performance_schema.user_variables_by_thread |
| 10 |
order by VARIABLE_NAME; |
| 11 |
VARIABLE_NAME VARIABLE_VALUE |
| 12 |
v1a Hello |
| 13 |
v2a World |
| 14 |
v3a 65 |
| 15 |
v4a NULL |
| 16 |
set @v1b="Salut"; |
| 17 |
set @v2b="La compagnie"; |
| 18 |
set @v3b=12; |
| 19 |
set @v4b=NULL; |
| 20 |
select @v1b, @v2b, @v3b, @v4b, @v_unknown; |
| 21 |
@v1b @v2b @v3b @v4b @v_unknown |
| 22 |
Salut La compagnie 12 NULL NULL |
| 23 |
select VARIABLE_NAME, VARIABLE_VALUE |
| 24 |
from performance_schema.user_variables_by_thread |
| 25 |
order by VARIABLE_NAME; |
| 26 |
VARIABLE_NAME VARIABLE_VALUE |
| 27 |
v1a Hello |
| 28 |
v1b Salut |
| 29 |
v2a World |
| 30 |
v2b La compagnie |
| 31 |
v3a 65 |
| 32 |
v3b 12 |
| 33 |
v4a NULL |
| 34 |
v4b NULL |
| 35 |
select VARIABLE_NAME, VARIABLE_VALUE |
| 36 |
from performance_schema.user_variables_by_thread |
| 37 |
order by VARIABLE_NAME; |
| 38 |
VARIABLE_NAME VARIABLE_VALUE |
| 39 |
v1a Hello |
| 40 |
v2a World |
| 41 |
v3a 65 |
| 42 |
v4a NULL |
| 43 |
set @blob_under_16_bits=repeat("a", 65000); |
| 44 |
set @blob_under_24_bits=repeat("a", 1000000); |
| 45 |
select length(@blob_under_16_bits); |
| 46 |
length(@blob_under_16_bits) |
| 47 |
65000 |
| 48 |
select length(@blob_under_24_bits); |
| 49 |
length(@blob_under_24_bits) |
| 50 |
1000000 |
| 51 |
select variable_name, length(variable_value) |
| 52 |
from performance_schema.user_variables_by_thread |
| 53 |
where VARIABLE_NAME like "blob%" |
| 54 |
order by VARIABLE_NAME; |
| 55 |
variable_name length(variable_value) |
| 56 |
blob_under_16_bits 65000 |
| 57 |
blob_under_24_bits 1000000 |
| 58 |
set @a=10; |
| 59 |
CREATE procedure test_user_val |
| 60 |
(IN input INT,OUT output INT) |
| 61 |
BEGIN |
| 62 |
SELECT input + input |
| 63 |
INTO output; |
| 64 |
END| |
| 65 |
CALL test_user_val(@a , @total); |
| 66 |
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread |
| 67 |
where variable_name in ('a','total') order by VARIABLE_NAME; |
| 68 |
VARIABLE_NAME VARIABLE_VALUE |
| 69 |
a 10 |
| 70 |
total 20 |
| 71 |
create table t1 (c1 int); |
| 72 |
CREATE TRIGGER test_uvar_trigger |
| 73 |
AFTER INSERT ON t1 |
| 74 |
FOR EACH ROW BEGIN |
| 75 |
set @a=@a+@a; |
| 76 |
END| |
| 77 |
insert into t1 values(1); |
| 78 |
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread |
| 79 |
where variable_name ='a' order by VARIABLE_NAME; |
| 80 |
VARIABLE_NAME VARIABLE_VALUE |
| 81 |
a 20 |
| 82 |
insert into t1 values(1); |
| 83 |
select VARIABLE_NAME,VARIABLE_VALUE from performance_schema.user_variables_by_thread |
| 84 |
where variable_name ='a' order by VARIABLE_NAME; |
| 85 |
VARIABLE_NAME VARIABLE_VALUE |
| 86 |
a 40 |
| 87 |
drop table t1; |
| 88 |
drop procedure test_user_val; |
| 89 |
# |
| 90 |
# Bug#35781732 5.7 vs 8.0 user variables differences output in performance_schema.user_variables_by_thread |
| 91 |
# |
| 92 |
SET @bc=123.656; |
| 93 |
SET @cde='123.656'; |
| 94 |
SELECT @bc,@cde,variable_name,variable_value,CAST(variable_value AS CHAR) val_char,CAST(variable_value AS DEC(9,4)) val_num |
| 95 |
FROM performance_schema.user_variables_by_thread |
| 96 |
WHERE (variable_name='bc' OR variable_name='cde') |
| 97 |
ORDER BY variable_name; |
| 98 |
@bc @cde variable_name variable_value val_char val_num |
| 99 |
123.656 123.656 bc 123.656 123.656 123.6560 |
| 100 |
123.656 123.656 cde 123.656 123.656 123.6560 |