variables.result 14.2 KB
Newer Older
1
drop table if exists t1,t2;
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
set @`test`=1;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
1	1	1	1	1
set @TEST=2;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
2	2	2	2	2
set @"tEST"=3;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
3	3	3	3	3
set @`TeST`=4;
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
4	4	4	4	4
select @`teST`:=5;
@`teST`:=5
5
select @test, @`test`, @TEST, @`TEST`, @"teSt";
@test	@`test`	@TEST	@`TEST`	@"teSt"
5	5	5	5	5
set @select=2,@t5=1.23456;
select @`select`,@not_used;
@`select`	@not_used
2	NULL
28 29
set @test_int=10,@test_double=1e-10,@test_string="abcdeghi",@test_string2="abcdefghij",@select=NULL;
select @test_int,@test_double,@test_string,@test_string2,@select;
30
@test_int	@test_double	@test_string	@test_string2	@select
31
10	1e-10	abcdeghi	abcdefghij	NULL
32 33
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
34 35
@test_int	@test_double	@test_string	@test_string2
hello	hello	hello	hello
36 37
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
38 39
@test_int	@test_double	@test_string	@test_string2
hellohello	hellohello	hellohello	hellohello
40 41
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
42 43
@test_int	@test_double	@test_string	@test_string2
NULL	NULL	NULL	NULL
44
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
45 46
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
47 48 49 50 51
explain extended select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select high_priority no_cache (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
52
select @t5;
53 54
@t5
1.23456
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
55 56 57
CREATE TABLE t1 (c_id INT(4) NOT NULL, c_name CHAR(20), c_country CHAR(3), PRIMARY KEY(c_id));
INSERT INTO t1 VALUES (1,'Bozo','USA'),(2,'Ronald','USA'),(3,'Kinko','IRE'),(4,'Mr. Floppy','GB');
SELECT @min_cid:=min(c_id), @max_cid:=max(c_id) from t1;
58 59
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
60
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
61 62 63
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
64
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
65 66 67
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
68 69
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
70 71 72
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
73
drop table t1;
74 75 76 77 78 79
set max_join_size=100;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	100
show global variables like 'max_join_size';
Variable_name	Value
80
max_join_size	10
81 82 83 84 85 86 87 88 89 90 91
set GLOBAL max_join_size=2000;
show global variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
set max_join_size=DEFAULT;
show variables like 'max_join_size';
Variable_name	Value
max_join_size	2000
set GLOBAL max_join_size=DEFAULT;
show global variables like 'max_join_size';
Variable_name	Value
92
max_join_size	HA_POS_ERROR
93 94
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
95
@@session.max_join_size	@@global.max_join_size
96 97 98 99
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
monty@hundin.mysql.fi's avatar
monty@hundin.mysql.fi committed
100 101 102 103 104 105
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
106 107 108 109 110
explain extended select last_insert_id(345);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select high_priority no_cache last_insert_id(345) AS `last_insert_id(345)`
111
select @@IDENTITY,last_insert_id(), @@identity;
112
@@IDENTITY	last_insert_id()	@@identity
113
345	345	345
114 115 116 117 118
explain extended select @@IDENTITY,last_insert_id(), @@identity;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1003	select high_priority no_cache 345 AS `@@IDENTITY`,345 AS `last_insert_id()`,345 AS `@@identity`
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
set global concurrent_insert=ON;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	ON
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	ON
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	OFF
set global concurrent_insert=OFF;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	OFF
set global concurrent_insert=DEFAULT;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	ON
set table_type=MYISAM, table_type="HEAP", global table_type="INNODB";
show local variables like 'table_type';
Variable_name	Value
table_type	HEAP
show global variables like 'table_type';
Variable_name	Value
table_type	INNODB
set GLOBAL query_cache_size=100000;
148
set GLOBAL myisam_max_sort_file_size=2000000;
149 150
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
151 152
myisam_max_sort_file_size	1048576
set GLOBAL myisam_max_sort_file_size=default;
153 154
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
155
myisam_max_sort_file_size	FILE_SIZE
156
set global net_retry_count=10, session net_retry_count=10;
157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192
set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300;
set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600;
show global variables like 'net_%';
Variable_name	Value
net_buffer_length	1024
net_read_timeout	300
net_retry_count	10
net_write_timeout	200
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	2048
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000;
show global variables like 'net_%';
Variable_name	Value
net_buffer_length	1024
net_read_timeout	900
net_retry_count	10
net_write_timeout	1000
show session variables like 'net_%';
Variable_name	Value
net_buffer_length	7168
net_read_timeout	600
net_retry_count	10
net_write_timeout	500
set net_buffer_length=1;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1024
set net_buffer_length=2000000000;
show variables like 'net_buffer_length';
Variable_name	Value
net_buffer_length	1048576
set character set cp1251_koi8;
193
show variables like "character_set_client";
194
Variable_name	Value
195
character_set_client	cp1251
196 197 198
select @@timestamp>0;
@@timestamp>0
1
199 200 201 202
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234
show variables like '%alloc%';
Variable_name	Value
query_alloc_block_size	8192
query_prealloc_size	8192
range_alloc_block_size	2048
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
set @@range_alloc_block_size=1024*16;
set @@query_alloc_block_size=1024*17+2;
set @@query_prealloc_size=1024*18;
set @@transaction_alloc_block_size=1024*20-1;
set @@transaction_prealloc_size=1024*21-1;
select @@query_alloc_block_size;
@@query_alloc_block_size
17408
show variables like '%alloc%';
Variable_name	Value
query_alloc_block_size	17408
query_prealloc_size	18432
range_alloc_block_size	16384
transaction_alloc_block_size	19456
transaction_prealloc_size	20480
set @@range_alloc_block_size=default;
set @@query_alloc_block_size=default, @@query_prealloc_size=default;
set transaction_alloc_block_size=default, @@transaction_prealloc_size=default;
show variables like '%alloc%';
Variable_name	Value
query_alloc_block_size	8192
query_prealloc_size	8192
range_alloc_block_size	2048
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
235
set big_tables=OFFF;
236
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
237
set big_tables="OFFF";
238
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
239
set unknown_variable=1;
240
ERROR HY000: Unknown system variable 'unknown_variable'
241
set max_join_size="hello";
242
ERROR 42000: Wrong argument type to variable 'max_join_size'
243
set table_type=UNKNOWN_TABLE_TYPE;
244
ERROR 42000: Variable 'table_type' can't be set to the value of 'UNKNOWN_TABLE_TYPE'
245
set table_type=INNODB, big_tables=2;
246
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
247 248 249 250
show local variables like 'table_type';
Variable_name	Value
table_type	HEAP
set SESSION query_cache_size=10000;
251
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
252
set GLOBAL table_type=DEFAULT;
253
ERROR 42000: Variable 'table_type' doesn't have a default value
254
set character_set_client=UNKNOWN_CHARACTER_SET;
255
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
256 257
set collation_connection=UNKNOWN_COLLATION;
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
258
set global autocommit=1;
259
ERROR HY000: Variable 'autocommit' is a LOCAL variable and can't be used with SET GLOBAL
260
select @@global.timestamp;
261
ERROR HY000: Variable 'timestamp' is a LOCAL variable and can't be used with SET GLOBAL
262
set @@version='';
263
ERROR HY000: Unknown system variable 'version'
264
set @@concurrent_insert=1;
265
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
266
set @@global.sql_auto_is_null=1;
267
ERROR HY000: Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL
268
select @@global.sql_auto_is_null;
269
ERROR HY000: Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL
270
set myisam_max_sort_file_size=100;
271
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
272
set myisam_max_extra_sort_file_size=100;
273
ERROR HY000: Variable 'myisam_max_extra_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
274
set @@SQL_WARNINGS=NULL;
275
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
276 277 278 279 280 281 282
set autocommit=1;
set big_tables=1;
select @@autocommit, @@big_tables;
@@autocommit	@@big_tables
1	1
set global binlog_cache_size=100;
set bulk_insert_buffer_size=100;
283 284
set character set cp1251_koi8;
set character set default;
285 286
set @@global.concurrent_insert=1;
set global connect_timeout=100;
287 288 289 290 291 292 293 294 295 296 297
select @@delay_key_write;
@@delay_key_write
ON
set global delay_key_write="OFF";
select @@delay_key_write;
@@delay_key_write
OFF
set global delay_key_write=ALL;
select @@delay_key_write;
@@delay_key_write
ALL
298
set global delay_key_write=1;
299 300 301
select @@delay_key_write;
@@delay_key_write
ON
302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328
set global delayed_insert_limit=100;
set global delayed_insert_timeout=100;
set global delayed_queue_size=100;
set global flush=1;
set global flush_time=100;
set insert_id=1;
set interactive_timeout=100;
set join_buffer_size=100;
set last_insert_id=1;
set global local_infile=1;
set long_query_time=100;
set low_priority_updates=1;
set max_allowed_packet=100;
set global max_binlog_cache_size=100;
set global max_binlog_size=100;
set global max_connect_errors=100;
set global max_connections=100;
set global max_delayed_threads=100;
set max_heap_table_size=100;
set max_join_size=100;
set max_sort_length=100;
set max_tmp_tables=100;
set global max_user_connections=100;
select @@max_user_connections;
@@max_user_connections
100
set global max_write_lock_count=100;
329
set global myisam_max_extra_sort_file_size=100;
330 331 332
select @@myisam_max_extra_sort_file_size;
@@myisam_max_extra_sort_file_size
100
333
set global myisam_max_sort_file_size=100;
334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361
set myisam_sort_buffer_size=100;
set net_buffer_length=100;
set net_read_timeout=100;
set net_write_timeout=100;
set global query_cache_limit=100;
set global query_cache_size=100;
set global query_cache_type=demand;
set read_buffer_size=100;
set read_rnd_buffer_size=100;
set global rpl_recovery_rank=100;
set global server_id=100;
set global slave_net_timeout=100;
set global slow_launch_time=100;
set sort_buffer_size=100;
set sql_auto_is_null=1;
select @@sql_auto_is_null;
@@sql_auto_is_null
1
set @@sql_auto_is_null=0;
select @@sql_auto_is_null;
@@sql_auto_is_null
0
set sql_big_selects=1;
set sql_big_tables=1;
set sql_buffer_result=1;
set sql_log_bin=1;
set sql_log_off=1;
set sql_log_update=1;
pem@mysql.com's avatar
pem@mysql.com committed
362
Warnings:
363
Note	1296	The update log is deprecated and replaced by the binary log. SET SQL_LOG_UPDATE has been ignored.
364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
set sql_low_priority_updates=1;
set sql_max_join_size=200;
select @@sql_max_join_size,@@max_join_size;
@@sql_max_join_size	@@max_join_size
200	200
set sql_quote_show_create=1;
set sql_safe_updates=1;
set sql_select_limit=1;
set global sql_slave_skip_counter=100;
set sql_warnings=1;
set global table_cache=100;
set table_type=myisam;
set global thread_cache_size=100;
set timestamp=1, timestamp=default;
set tmp_table_size=100;
set tx_isolation="READ-COMMITTED";
set wait_timeout=100;
set log_warnings=1;
create table t1 (a int not null auto_increment, primary key(a));
create table t2 (a int not null auto_increment, primary key(a));
insert into t1 values(null),(null),(null);
insert into t2 values(null),(null),(null);
set global key_buffer_size=100000;
select @@key_buffer_size;
@@key_buffer_size
98304
select * from t1 where a=2;
a
2
select * from t2 where a=3;
a
3
check table t1,t2;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
400
select max(a) +1, max(a) +2 into @xx,@yy from t1;
401
drop table t1,t2;
402
select @@xxxxxxxxxx;
403
ERROR HY000: Unknown system variable 'xxxxxxxxxx'
404 405 406
select 1;
1
1
407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
100	200
set @svc=@@global.max_user_connections, @svj=@@local.max_join_size;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
100	200
set @@global.max_user_connections=111,@@local.max_join_size=222;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
111	222
set @@global.max_user_connections=@@local.max_join_size,@@local.max_join_size=@@global.max_user_connections;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
222	111
set @@global.max_user_connections=@svc, @@local.max_join_size=@svj;
select @@global.max_user_connections,@@local.max_join_size;
@@global.max_user_connections	@@session.max_join_size
100	200
set @a=1, @b=2;
set @a=@b, @b=@a;
select @a, @b;
@a	@b
2	1