type_ranges.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 344 行 · 第 1/2 页

INSERT INTO t2 (string,mediumblob_col,new_field) SELECT string,mediumblob_col,new_field from t1 where auto > 10;
Warnings:
Warning	1265	Data truncated for column 'new_field' at row 2
Warning	1265	Data truncated for column 'new_field' at row 3
Warning	1265	Data truncated for column 'new_field' at row 4
Warning	1265	Data truncated for column 'new_field' at row 5
Warning	1265	Data truncated for column 'new_field' at row 6
Warning	1265	Data truncated for column 'new_field' at row 7
select * from t2;
auto	string	mediumblob_col	new_field
1	2	2	ne
2	0.33333333		ne
3	-1	-1	ne
4	-429496729	-4294967295	ne
5	4294967295	4294967295	ne
6	hello		ne
select distinct flags from t1;
flags

one,two,tree
one
one,two
select flags from t1 where find_in_set("two",flags)>0;
flags
one,two,tree
one,two,tree
one,two
one,two
select flags from t1 where find_in_set("unknown",flags)>0;
flags
select options,flags from t1 where options="ONE" and flags="ONE";
options	flags
one	one
select options,flags from t1 where options="one" and flags="one";
options	flags
one	one
drop table t2;
create table t2 select * from t1;
update t2 set string="changed" where auto=16;
show full columns from t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
auto	int(5) unsigned	NULL	NO	MUL	NULL	auto_increment	#	
string	char(10)	latin1_swedish_ci	YES		newdefault		#	
tiny	tinyint(4)	NULL	NO	MUL	0		#	
short	smallint(6)	NULL	NO	MUL	NULL		#	
medium	mediumint(8)	NULL	NO	MUL	0		#	
long_int	int(11)	NULL	NO		0		#	
longlong	bigint(13)	NULL	NO	MUL	0		#	
real_float	float(13,1)	NULL	NO	MUL	0.0		#	
real_double	double(16,4)	NULL	YES		NULL		#	
utiny	tinyint(3) unsigned	NULL	NO		0		#	
ushort	smallint(5) unsigned zerofill	NULL	NO		00000		#	
umedium	mediumint(8) unsigned	NULL	NO	MUL	0		#	
ulong	int(11) unsigned	NULL	NO	MUL	0		#	
ulonglong	bigint(13) unsigned	NULL	NO	MUL	0		#	
time_stamp	timestamp	NULL	NO		CURRENT_TIMESTAMP		#	
date_field	char(10)	latin1_swedish_ci	YES		NULL		#	
time_field	time	NULL	YES		NULL		#	
date_time	datetime	NULL	YES		NULL		#	
new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
tinyblob_col	tinyblob	NULL	YES		NULL		#	
mediumblob_col	mediumblob	NULL	NO		NULL		#	
options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL	NULL		#	
flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
new_field	char(10)	latin1_swedish_ci	NO		new		#	
show full columns from t2;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
auto	int(5) unsigned	NULL	NO		0		#	
string	char(10)	latin1_swedish_ci	YES		newdefault		#	
tiny	tinyint(4)	NULL	NO		0		#	
short	smallint(6)	NULL	NO		NULL		#	
medium	mediumint(8)	NULL	NO		0		#	
long_int	int(11)	NULL	NO		0		#	
longlong	bigint(13)	NULL	NO		0		#	
real_float	float(13,1)	NULL	NO		0.0		#	
real_double	double(16,4)	NULL	YES		NULL		#	
utiny	tinyint(3) unsigned	NULL	NO		0		#	
ushort	smallint(5) unsigned zerofill	NULL	NO		00000		#	
umedium	mediumint(8) unsigned	NULL	NO		0		#	
ulong	int(11) unsigned	NULL	NO		0		#	
ulonglong	bigint(13) unsigned	NULL	NO		0		#	
time_stamp	timestamp	NULL	NO		0000-00-00 00:00:00		#	
date_field	char(10)	latin1_swedish_ci	YES		NULL		#	
time_field	time	NULL	YES		NULL		#	
date_time	datetime	NULL	YES		NULL		#	
new_blob_col	varchar(20)	latin1_swedish_ci	YES		NULL		#	
tinyblob_col	tinyblob	NULL	YES		NULL		#	
mediumblob_col	mediumblob	NULL	NO		NULL		#	
options	enum('one','two','tree')	latin1_swedish_ci	NO		NULL		#	
flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
new_field	char(10)	latin1_swedish_ci	NO		new		#	
select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.time_field<>t2.time_field and (t1.time_field is not null or t2.time_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.new_blob_col<>t2.new_blob_col and (t1.new_blob_col is not null or t2.new_blob_col is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)) or (t1.new_field<>t2.new_field and (t1.new_field is not null or t2.new_field is not null)));
auto	auto
16	16
select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.time_field<=>t2.time_field and t1.date_time<=>t2.date_time and t1.new_blob_col<=>t2.new_blob_col and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags and t1.new_field<=>t2.new_field);
auto	auto
16	16
drop table t2;
create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
show full columns from t2;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
auto	bigint(12) unsigned	NULL	NO	PRI	0		#	
t1	int(1)	NULL	NO		0		#	
t2	varchar(1)	latin1_swedish_ci	NO				#	
t3	varchar(256)	latin1_swedish_ci	NO				#	
t4	varbinary(256)	NULL	NO				#	
t5	longtext	latin1_swedish_ci	NO		NULL		#	
t6	longblob	NULL	NO		NULL		#	
t7	char(0)	latin1_swedish_ci	NO				#	
t8	binary(0)	NULL	NO				#	
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
t1	t2	length(t3)	length(t4)	length(t5)	length(t6)	t7	t8
1	a	256	256	4096	4096		
1	a	256	256	4096	4096		
1	a	256	256	4096	4096		
1	a	256	256	4096	4096		
1	a	256	256	4096	4096		
1	a	256	256	4096	4096		
1	a	256	256	4096	4096		
drop table t1,t2;
create table t1 (c int);
insert into t1 values(1),(2);
create table t2 select * from t1;
create table t3 select * from t1, t2;
ERROR 42S21: Duplicate column name 'c'
create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
show full columns from t3;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
c1	int(11)	NULL	YES		NULL		#	
c2	int(11)	NULL	YES		NULL		#	
const	int(1)	NULL	NO		0		#	
drop table t1,t2,t3;
create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
drop table t1;
create table t1 ( id integer unsigned not null primary key );
create table t2 ( id integer unsigned not null primary key );
insert into t1 values (1), (2);
insert into t2 values (1);
select  t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
id_A	id_B
1	1
2	NULL
select  t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
id_A	id_B
1	1
2	NULL
create table t3 (id_A integer unsigned not null, id_B integer unsigned null  );
insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
select * from t3;
id_A	id_B
1	1
2	NULL
delete from t3;
insert into t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
select * from t3;
id_A	id_B
1	1
2	NULL
drop table t3;
create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 using ( id );
select * from t3;
id_A	id_B
1	1
2	NULL
drop table t3;
create table t3 select t1.id as id_A,  t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
select * from t3;
id_A	id_B
1	1
2	NULL
drop table t1,t2,t3;