variables.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

drop table if exists t1,t2;
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
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;
@test_int	@test_double	@test_string	@test_string2	@select
10	1e-10	abcdeghi	abcdefghij	NULL
set @test_int="hello",@test_double="hello",@test_string="hello",@test_string2="hello";
select @test_int,@test_double,@test_string,@test_string2;
@test_int	@test_double	@test_string	@test_string2
hello	hello	hello	hello
set @test_int="hellohello",@test_double="hellohello",@test_string="hellohello",@test_string2="hellohello";
select @test_int,@test_double,@test_string,@test_string2;
@test_int	@test_double	@test_string	@test_string2
hellohello	hellohello	hellohello	hellohello
set @test_int=null,@test_double=null,@test_string=null,@test_string2=null;
select @test_int,@test_double,@test_string,@test_string2;
@test_int	@test_double	@test_string	@test_string2
NULL	NULL	NULL	NULL
select @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
@t1:=(@t2:=1)+@t3:=4	@t1	@t2	@t3
5	5	1	4
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 sql_no_cache (@t1:=((@t2:=1) + (@t3:=4))) AS `@t1:=(@t2:=1)+@t3:=4`,(@t1) AS `@t1`,(@t2) AS `@t2`,(@t3) AS `@t3`
select @t5;
@t5
1.23456
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;
@min_cid:=min(c_id)	@max_cid:=max(c_id)
1	4
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid;
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
SELECT * FROM t1 WHERE c_id=@min_cid OR c_id=@max_cid OR c_id=666;
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
ALTER TABLE t1 DROP PRIMARY KEY;
select * from t1 where c_id=@min_cid OR c_id=@max_cid;
c_id	c_name	c_country
1	Bozo	USA
4	Mr. Floppy	GB
drop table t1;
set GLOBAL max_join_size=10;
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
max_join_size	10
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
max_join_size	HA_POS_ERROR
set @@max_join_size=1000, @@global.max_join_size=2000;
select @@local.max_join_size, @@global.max_join_size;
@@local.max_join_size	@@global.max_join_size
1000	2000
select @@identity,  length(@@version)>0;
@@identity	length(@@version)>0
0	1
select @@VERSION=version();
@@VERSION=version()
1
select last_insert_id(345);
last_insert_id(345)
345
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 sql_no_cache last_insert_id(345) AS `last_insert_id(345)`
select @@IDENTITY,last_insert_id(), @@identity;
@@IDENTITY	last_insert_id()	@@identity
345	345	345
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 sql_no_cache 345 AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,345 AS `@@identity`
set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON";
set global concurrent_insert=2;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	2
set global concurrent_insert=1;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	1
set global concurrent_insert=0;
show variables like 'concurrent_insert';
Variable_name	Value
concurrent_insert	0
set global concurrent_insert=DEFAULT;
select @@concurrent_insert;
@@concurrent_insert
1
set global timed_mutexes=ON;
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	ON
set global timed_mutexes=0;
show variables like 'timed_mutexes';
Variable_name	Value
timed_mutexes	OFF
set storage_engine=MYISAM, storage_engine="HEAP", global storage_engine="MERGE";
show local variables like 'storage_engine';
Variable_name	Value
storage_engine	MEMORY
show global variables like 'storage_engine';
Variable_name	Value
storage_engine	MRG_MYISAM
set GLOBAL query_cache_size=100000;
set GLOBAL myisam_max_sort_file_size=2000000;
show global variables like 'myisam_max_sort_file_size';
Variable_name	Value
myisam_max_sort_file_size	1048576
set GLOBAL myisam_max_sort_file_size=default;
show variables like 'myisam_max_sort_file_size';
Variable_name	Value
myisam_max_sort_file_size	FILE_SIZE
set global net_retry_count=10, session net_retry_count=10;
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;
show variables like "character_set_client";
Variable_name	Value
character_set_client	cp1251
select @@timestamp>0;
@@timestamp>0
1
set @@rand_seed1=10000000,@@rand_seed2=1000000;
select ROUND(RAND(),5);
ROUND(RAND(),5)
0.02887
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
SELECT @@version LIKE 'non-existent';
@@version LIKE 'non-existent'
0
SELECT @@version_compile_os LIKE 'non-existent';
@@version_compile_os LIKE 'non-existent'
0
set big_tables=OFFF;
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
set big_tables="OFFF";
ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF'
set unknown_variable=1;
ERROR HY000: Unknown system variable 'unknown_variable'
set max_join_size="hello";
ERROR 42000: Incorrect argument type to variable 'max_join_size'
set storage_engine=UNKNOWN_TABLE_TYPE;
ERROR 42000: Unknown table engine 'UNKNOWN_TABLE_TYPE'
set storage_engine=MERGE, big_tables=2;
ERROR 42000: Variable 'big_tables' can't be set to the value of '2'
show local variables like 'storage_engine';
Variable_name	Value
storage_engine	MEMORY
set SESSION query_cache_size=10000;
ERROR HY000: Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
set GLOBAL storage_engine=DEFAULT;
ERROR 42000: Variable 'storage_engine' doesn't have a default value
set character_set_client=UNKNOWN_CHARACTER_SET;
ERROR 42000: Unknown character set: 'UNKNOWN_CHARACTER_SET'
set collation_connection=UNKNOWN_COLLATION;
ERROR HY000: Unknown collation: 'UNKNOWN_COLLATION'
set character_set_client=NULL;
ERROR 42000: Variable 'character_set_client' can't be set to the value of 'NULL'
set collation_connection=NULL;
ERROR 42000: Variable 'collation_connection' can't be set to the value of 'NULL'
set global autocommit=1;
ERROR HY000: Variable 'autocommit' is a SESSION variable and can't be used with SET GLOBAL
select @@global.timestamp;
ERROR HY000: Variable 'timestamp' is a SESSION variable
set @@version='';
ERROR HY000: Unknown system variable 'version'
set @@concurrent_insert=1;
ERROR HY000: Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL
set @@global.sql_auto_is_null=1;
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable and can't be used with SET GLOBAL
select @@global.sql_auto_is_null;
ERROR HY000: Variable 'sql_auto_is_null' is a SESSION variable
set myisam_max_sort_file_size=100;
ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and should be set with SET GLOBAL
set @@SQL_WARNINGS=NULL;
ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL'
set autocommit=1;
set big_tables=1;
select @@autocommit, @@big_tables;
@@autocommit	@@big_tables
1	1
set global binlog_cache_size=100;