user_var.result 6 KB
Newer Older
1
drop table if exists t1,t2;
2
set @a := foo;
3
ERROR 42S22: Unknown column 'foo' in 'field list'
4 5
set @a := connection_id() + 3;
select @a - connection_id();
6 7
@a - connection_id()
3
8 9 10 11
set @b := 1;
select @b;
@b
1
12 13 14 15 16
CREATE TABLE t1 ( i int not null, v int not null,index (i));
insert into t1 values (1,1),(1,3),(2,1);
create table t2 (i int not null, unique (i));
insert into t2 select distinct i from t1;
select * from t2;
17 18 19
i
1
2
20
select distinct t2.i,@vv1:=if(sv1.i,1,0),@vv2:=if(sv2.i,1,0),@vv3:=if(sv3.i,1,0), @vv1+@vv2+@vv3 from t2 left join t1 as sv1 on sv1.i=t2.i and sv1.v=1 left join t1 as sv2 on sv2.i=t2.i and sv2.v=2 left join t1 as sv3 on sv3.i=t2.i and sv3.v=3;
21 22 23
i	@vv1:=if(sv1.i,1,0)	@vv2:=if(sv2.i,1,0)	@vv3:=if(sv3.i,1,0)	@vv1+@vv2+@vv3
1	1	0	1	2
2	1	0	0	1
24
explain select * from t1 where i=@vv1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
25
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
26 27 28 29 30
1	SIMPLE	t1	ref	i	i	4	const	1	
select @vv1,i,v from t1 where i=@vv1;
@vv1	i	v
1	1	1
1	1	3
31
explain select * from t1 where @vv1:=@vv1+1 and i=@vv1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
32
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
33
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
34
explain select @vv1:=i from t1 where i=@vv1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
35
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
36
1	SIMPLE	t1	index	NULL	i	4	NULL	3	Using where; Using index
37
explain select * from t1 where i=@vv1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
38
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
39
1	SIMPLE	t1	ref	i	i	4	const	1	
40
drop table t1,t2;
41
set @a=0,@b=0;
42
select @a:=10,   @b:=1,   @a > @b, @a < @b;
43 44
@a:=10	@b:=1	@a > @b	@a < @b
10	1	1	0
45
select @a:="10", @b:="1", @a > @b, @a < @b;
46 47
@a:="10"	@b:="1"	@a > @b	@a < @b
10	1	1	0
48
select @a:=10,   @b:=2,   @a > @b, @a < @b;
49
@a:=10	@b:=2	@a > @b	@a < @b
50
10	2	0	1
51
select @a:="10", @b:="2", @a > @b, @a < @b;
52
@a:="10"	@b:="2"	@a > @b	@a < @b
53
10	2	1	0
vva@eagle.mysql.r18.ru's avatar
vva@eagle.mysql.r18.ru committed
54 55 56 57 58 59
select @a:=1;
@a:=1
1
select @a, @a:=1;
@a	@a:=1
1	1
60 61
create table t1 (id int, d double, c char(10));
insert into t1 values (1,2.0, "test");
vva@eagle.mysql.r18.ru's avatar
vva@eagle.mysql.r18.ru committed
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
select @c:=0;
@c:=0
0
update t1 SET id=(@c:=@c+1);
select @c;
@c
1
select @c:=0;
@c:=0
0
update t1 set id=(@c:=@c+1);
select @c;
@c
1
select @c:=0;
@c:=0
0
select @c:=@c+1;
@c:=@c+1
1
82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103
select @d,(@d:=id),@d from t1;
@d	(@d:=id)	@d
NULL	1	1
select @e,(@e:=d),@e from t1;
@e	(@e:=d)	@e
NULL	2	2
select @f,(@f:=c),@f from t1;
@f	(@f:=c)	@f
NULL	test	test
set @g=1;
select @g,(@g:=c),@g from t1;
@g	(@g:=c)	@g
1	test	test
select @c, @d, @e, @f;
@c	@d	@e	@f
1	1	2	test
select @d:=id, @e:=id, @f:=id, @g:=@id from t1;
@d:=id	@e:=id	@f:=id	@g:=@id
1	1	1	NULL
select @c, @d, @e, @f, @g;
@c	@d	@e	@f	@g
1	1	1	1	NULL
vva@eagle.mysql.r18.ru's avatar
vva@eagle.mysql.r18.ru committed
104 105 106
drop table t1;
select @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b, @a:=10, @b:=2, @a>@b, @a:="10", @b:="2", @a>@b;
@a:=10	@b:=2	@a>@b	@a:="10"	@b:="2"	@a>@b	@a:=10	@b:=2	@a>@b	@a:="10"	@b:="2"	@a>@b
107
10	2	1	10	2	1	10	2	1	10	2	1
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
create table t1 (i int not null);
insert t1 values (1),(2),(2),(3),(3),(3);
select @a:=0;
@a:=0
0
select @a, @a:=@a+count(*), count(*), @a from t1 group by i;
@a	@a:=@a+count(*)	count(*)	@a
0	1	1	0
0	2	2	0
0	3	3	0
select @a:=0;
@a:=0
0
select @a+0, @a:=@a+0+count(*), count(*), @a+0 from t1 group by i;
@a+0	@a:=@a+0+count(*)	count(*)	@a+0
0	1	1	0
1	3	2	0
3	6	3	0
drop table t1;
127 128 129
set @a=_latin2'test';
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
130
latin2	latin2_general_ci	2
131 132 133 134 135 136 137 138 139
select @a=_latin2'TEST';
@a=_latin2'TEST'
1
select @a=_latin2'TEST' collate latin2_bin;
@a=_latin2'TEST' collate latin2_bin
0
set @a=_latin2'test' collate latin2_general_ci;
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
140
latin2	latin2_general_ci	2
141 142 143 144
select @a=_latin2'TEST';
@a=_latin2'TEST'
1
select @a=_latin2'TEST' collate latin2_bin;
145 146
@a=_latin2'TEST' collate latin2_bin
0
147 148 149 150 151 152 153 154
select charset(@a:=_latin2'test');
charset(@a:=_latin2'test')
latin2
select collation(@a:=_latin2'test');
collation(@a:=_latin2'test')
latin2_general_ci
select coercibility(@a:=_latin2'test');
coercibility(@a:=_latin2'test')
155
2
156 157 158 159 160
select collation(@a:=_latin2'test' collate latin2_bin);
collation(@a:=_latin2'test' collate latin2_bin)
latin2_bin
select coercibility(@a:=_latin2'test' collate latin2_bin);
coercibility(@a:=_latin2'test' collate latin2_bin)
161
2
162 163 164 165 166
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST';
(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST'
0
select charset(@a),collation(@a),coercibility(@a);
charset(@a)	collation(@a)	coercibility(@a)
167
latin2	latin2_bin	2
168
select (@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci;
169 170
(@a:=_latin2'test' collate latin2_bin) = _latin2'TEST' collate latin2_general_ci
1
171 172 173 174
set @var= NULL ;
select FIELD( @var,'1it','Hit') as my_column;
my_column
0
175 176 177 178 179 180 181
select @v, coercibility(@v);
@v	coercibility(@v)
NULL	2
set @v1=null, @v2=1, @v3=1.1, @v4=now();
select coercibility(@v1),coercibility(@v2),coercibility(@v3),coercibility(@v4);
coercibility(@v1)	coercibility(@v2)	coercibility(@v3)	coercibility(@v4)
2	2	2	2
182 183
set session @honk=99;
set one_shot @honk=99;
serg@serg.mylan's avatar
serg@serg.mylan committed
184
ERROR HY000: The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server
185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225
set @first_var= NULL;
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` longblob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var= cast(NULL as signed integer);
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var= NULL;
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var= concat(NULL);
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` longblob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
set @first_var=1;
set @first_var= cast(NULL as CHAR);
create table t1 select @first_var;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `@first_var` longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;