type_ranges.result

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

drop table if exists t1,t2,t3;
SET SQL_WARNINGS=1;
CREATE TABLE t1 (
auto int(5) unsigned NOT NULL auto_increment,
string char(10) default "hello",
tiny tinyint(4) DEFAULT '0' NOT NULL ,
short smallint(6) DEFAULT '1' NOT NULL ,
medium mediumint(8) DEFAULT '0' NOT NULL,
long_int int(11) DEFAULT '0' NOT NULL,
longlong bigint(13) DEFAULT '0' NOT NULL,
real_float float(13,1) DEFAULT 0.0 NOT NULL,
real_double double(16,4),
utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
ulong int(11) unsigned DEFAULT '0' NOT NULL,
ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
time_stamp timestamp,
date_field date,	
time_field time,	
date_time datetime,
blob_col blob,
tinyblob_col tinyblob,
mediumblob_col mediumblob  not null default '',
longblob_col longblob  not null default '',
options enum('one','two','tree') not null ,
flags set('one','two','tree') not null default '',
PRIMARY KEY (auto),
KEY (utiny),
KEY (tiny),
KEY (short),
KEY any_name (medium),
KEY (longlong),
KEY (real_float),
KEY (ushort),
KEY (umedium),
KEY (ulong),
KEY (ulonglong,ulong),
KEY (options,flags)
);
show full fields from t1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
auto	int(5) unsigned	NULL	NO	PRI	NULL	auto_increment	#	
string	char(10)	latin1_swedish_ci	YES		hello		#	
tiny	tinyint(4)	NULL	NO	MUL	0		#	
short	smallint(6)	NULL	NO	MUL	1		#	
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	MUL	0		#	
ushort	smallint(5) unsigned zerofill	NULL	NO	MUL	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	YES		CURRENT_TIMESTAMP		#	
date_field	date	NULL	YES		NULL		#	
time_field	time	NULL	YES		NULL		#	
date_time	datetime	NULL	YES		NULL		#	
blob_col	blob	NULL	YES		NULL		#	
tinyblob_col	tinyblob	NULL	YES		NULL		#	
mediumblob_col	mediumblob	NULL	NO				#	
longblob_col	longblob	NULL	NO				#	
options	enum('one','two','tree')	latin1_swedish_ci	NO	MUL			#	
flags	set('one','two','tree')	latin1_swedish_ci	NO				#	
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	auto	A	0	NULL	NULL		BTREE	
t1	1	utiny	1	utiny	A	NULL	NULL	NULL		BTREE	
t1	1	tiny	1	tiny	A	NULL	NULL	NULL		BTREE	
t1	1	short	1	short	A	NULL	NULL	NULL		BTREE	
t1	1	any_name	1	medium	A	NULL	NULL	NULL		BTREE	
t1	1	longlong	1	longlong	A	NULL	NULL	NULL		BTREE	
t1	1	real_float	1	real_float	A	NULL	NULL	NULL		BTREE	
t1	1	ushort	1	ushort	A	NULL	NULL	NULL		BTREE	
t1	1	umedium	1	umedium	A	NULL	NULL	NULL		BTREE	
t1	1	ulong	1	ulong	A	NULL	NULL	NULL		BTREE	
t1	1	ulonglong	1	ulonglong	A	NULL	NULL	NULL		BTREE	
t1	1	ulonglong	2	ulong	A	NULL	NULL	NULL		BTREE	
t1	1	options	1	options	A	NULL	NULL	NULL		BTREE	
t1	1	options	2	flags	A	NULL	NULL	NULL		BTREE	
CREATE UNIQUE INDEX test on t1 ( auto ) ;
CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
CREATE INDEX test3 on t1 ( medium ) ;
DROP INDEX test ON t1;
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,NULL,2,2,'two','two,one');
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','10:10:10','19970303101010','','','','3',3,3);
Warnings:
Warning	1265	Data truncated for column 'string' at row 1
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,080706,19970403090807,-1,-1,-1,'-1',-1,-1);
Warnings:
Warning	1264	Out of range value adjusted for column 'utiny' at row 1
Warning	1264	Out of range value adjusted for column 'ushort' at row 1
Warning	1264	Out of range value adjusted for column 'umedium' at row 1
Warning	1264	Out of range value adjusted for column 'ulong' at row 1
Warning	1264	Out of range value adjusted for column 'ulonglong' at row 1
Warning	1265	Data truncated for column 'options' at row 1
Warning	1265	Data truncated for column 'flags' at row 1
insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"one,two,tree");
Warnings:
Warning	1265	Data truncated for column 'string' at row 1
Warning	1264	Out of range value adjusted for column 'tiny' at row 1
Warning	1264	Out of range value adjusted for column 'short' at row 1
Warning	1264	Out of range value adjusted for column 'medium' at row 1
Warning	1264	Out of range value adjusted for column 'long_int' at row 1
Warning	1264	Out of range value adjusted for column 'utiny' at row 1
Warning	1264	Out of range value adjusted for column 'ushort' at row 1
Warning	1264	Out of range value adjusted for column 'umedium' at row 1
Warning	1264	Out of range value adjusted for column 'ulong' at row 1
Warning	1264	Out of range value adjusted for column 'ulonglong' at row 1
Warning	1265	Data truncated for column 'options' at row 1
insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
Warnings:
Warning	1264	Out of range value adjusted for column 'tiny' at row 1
Warning	1264	Out of range value adjusted for column 'short' at row 1
Warning	1264	Out of range value adjusted for column 'medium' at row 1
Warning	1264	Out of range value adjusted for column 'long_int' at row 1
Warning	1264	Out of range value adjusted for column 'utiny' at row 1
Warning	1264	Out of range value adjusted for column 'ushort' at row 1
Warning	1264	Out of range value adjusted for column 'umedium' at row 1
Warning	1265	Data truncated for column 'options' at row 1
insert into t1 (tiny) values (1);
select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,time_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;
auto	string	tiny	short	medium	long_int	longlong	real_float	real_double	utiny	ushort	umedium	ulong	ulonglong	mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000)	date_field	time_field	date_time	blob_col	tinyblob_col	mediumblob_col	longblob_col
10	1	1	1	1	1	1	1.0	1.0000	1	00001	1	1	1	0	0000-00-00	00:00:00	0000-00-00 00:00:00	1	1	1	1
11	2	2	2	2	2	2	2.0	2.0000	2	00002	2	2	2	0	NULL	NULL	NULL	NULL	NULL	2	2
12	0.33333333	3	3	3	3	3	3.0	3.0000	3	00003	3	3	3	0	1997-03-03	10:10:10	1997-03-03 10:10:10				3
13	-1	-1	-1	-1	-1	-1	-1.0	-1.0000	0	00000	0	0	0	0	1997-08-07	08:07:06	1997-04-03 09:08:07	-1	-1	-1	-1
14	-429496729	-128	-32768	-8388608	-2147483648	-4294967295	-4294967296.0	-4294967295.0000	0	00000	0	0	0	0	0000-00-00	00:00:00	0000-00-00 00:00:00	-4294967295	-4294967295	-4294967295	-4294967295
15	4294967295	127	32767	8388607	2147483647	4294967295	4294967296.0	4294967295.0000	255	65535	16777215	4294967295	4294967295	0	0000-00-00	00:00:00	0000-00-00 00:00:00	4294967295	4294967295	4294967295	4294967295
16	hello	1	1	0	0	0	0.0	NULL	0	00000	0	0	0	0	NULL	NULL	NULL	NULL	NULL		
ALTER TABLE t1
add new_field char(10) default "new" not null,
change blob_col new_blob_col varchar(20),
change date_field date_field char(10),
alter column string set default "newdefault",
alter short drop default,
DROP INDEX utiny,
DROP INDEX ushort,
DROP PRIMARY KEY,
DROP FOREIGN KEY any_name,
ADD INDEX (auto);
LOCK TABLES t1 WRITE;
ALTER TABLE t1 
RENAME as t2,
DROP longblob_col;
UNLOCK TABLES;
ALTER TABLE t2 rename as t3;
LOCK TABLES t3 WRITE ;
ALTER TABLE t3 rename as t1;
UNLOCK TABLES;
select auto,new_field,new_blob_col,date_field from t1 ;
auto	new_field	new_blob_col	date_field
10	new	1	0000-00-00
11	new	NULL	NULL
12	new		1997-03-03
13	new	-1	1997-08-07
14	new	-4294967295	0000-00-00
15	new	4294967295	0000-00-00
16	new	NULL	NULL
CREATE TABLE t2 (
auto int(5) unsigned NOT NULL auto_increment,
string char(20),
mediumblob_col mediumblob not null,
new_field char(2),
PRIMARY KEY (auto)
);
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