show_check.result 9.39 KB
Newer Older
unknown's avatar
unknown committed
1 2 3
drop table if exists t1,t2;
create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);
unknown's avatar
unknown committed
4
check table t1 fast;
5 6
Table	Op	Msg_type	Msg_text
test.t1	check	status	Table is already up to date
unknown's avatar
unknown committed
7
check table t1 fast;
8 9
Table	Op	Msg_type	Msg_text
test.t1	check	status	Table is already up to date
unknown's avatar
unknown committed
10
check table t1 changed;
11 12
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
13
insert into t1 values (5,5,5);
unknown's avatar
unknown committed
14
check table t1 changed;
15 16
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
17
check table t1 medium;
18 19
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
20
check table t1 extended;
21 22
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
23
show index from t1;
24 25 26 27
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	a	A	5	NULL	NULL		BTREE	
t1	1	b	1	b	A	1	NULL	NULL		BTREE	
t1	1	b	2	c	A	5	NULL	NULL		BTREE	
unknown's avatar
unknown committed
28
insert into t1 values (5,5,5);
29
ERROR 23000: Duplicate entry '5' for key 1
unknown's avatar
unknown committed
30
optimize table t1;
31 32
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
unknown's avatar
unknown committed
33
optimize table t1;
34 35
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	Table is already up to date
unknown's avatar
unknown committed
36 37
drop table t1;
show variables like "wait_timeout%";
38 39
Variable_name	Value
wait_timeout	28800
unknown's avatar
unknown committed
40 41 42
show variables like "WAIT_timeout%";
Variable_name	Value
wait_timeout	28800
unknown's avatar
unknown committed
43
show variables like "this_doesn't_exists%";
44
Variable_name	Value
unknown's avatar
unknown committed
45
show table status from test like "this_doesn't_exists%";
unknown's avatar
unknown committed
46
Name	Type	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
unknown's avatar
unknown committed
47
show databases;
48 49 50
Database
mysql
test
unknown's avatar
unknown committed
51
show databases like "test%";
52 53
Database (test%)
test
unknown's avatar
unknown committed
54 55 56
create table t1 (f1 int not null, f2 int not null, f3 int not null, f4 int not null, primary key(f1,f2,f3,f4));
insert into t1 values (1,1,1,0),(1,1,2,0),(1,1,3,0),(1,2,1,0),(1,2,2,0),(1,2,3,0),(1,3,1,0),(1,3,2,0),(1,3,3,0),(1,1,1,1),(1,1,2,1),(1,1,3,1),(1,2,1,1),(1,2,2,1),(1,2,3,1),(1,3,1,1),(1,3,2,1),(1,3,3,1);
analyze table t1;
57 58
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
unknown's avatar
unknown committed
59
show index from t1;
60 61 62 63 64
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	f1	A	1	NULL	NULL		BTREE	
t1	0	PRIMARY	2	f2	A	3	NULL	NULL		BTREE	
t1	0	PRIMARY	3	f3	A	9	NULL	NULL		BTREE	
t1	0	PRIMARY	4	f4	A	18	NULL	NULL		BTREE	
unknown's avatar
unknown committed
65
repair table t1;
66 67
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
unknown's avatar
unknown committed
68
show index from t1;
69 70 71 72 73
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	f1	A	1	NULL	NULL		BTREE	
t1	0	PRIMARY	2	f2	A	3	NULL	NULL		BTREE	
t1	0	PRIMARY	3	f3	A	9	NULL	NULL		BTREE	
t1	0	PRIMARY	4	f4	A	18	NULL	NULL		BTREE	
unknown's avatar
unknown committed
74 75 76
drop table t1;
create temporary table t1 (a int not null);
show create table t1;
77 78 79
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `a` int(11) NOT NULL default '0'
80
) TYPE=MyISAM CHARSET=latin1
unknown's avatar
unknown committed
81 82
alter table t1 rename t2;
show create table t2;
83 84 85
Table	Create Table
t2	CREATE TEMPORARY TABLE `t2` (
  `a` int(11) NOT NULL default '0'
86
) TYPE=MyISAM CHARSET=latin1
unknown's avatar
unknown committed
87 88 89
drop table t2;
create table t1 (
test_set set( 'val1', 'val2', 'val3' ) not null default '',
unknown's avatar
unknown committed
90 91
name char(20) default 'O''Brien' comment 'O''Brien as default',
c int not null comment 'int column'
unknown's avatar
unknown committed
92 93
  ) comment = 'it\'s a table' ;
show create table t1 ;
94 95
Table	Create Table
t1	CREATE TABLE `t1` (
96 97
  `test_set` set('val1','val2','val3') NOT NULL default '',
  `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default',
unknown's avatar
unknown committed
98
  `c` int(11) NOT NULL default '0' COMMENT 'int column'
99
) TYPE=MyISAM CHARSET=latin1 COMMENT='it''s a table'
unknown's avatar
unknown committed
100
show full columns from t1;
101
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
102 103
test_set	set('val1','val2','val3')	latin1_swedish_ci					select,insert,update,references	
name	char(20)	latin1_swedish_ci	YES		O'Brien		select,insert,update,references	O'Brien as default
104
c	int(11)	NULL			0		select,insert,update,references	int column
unknown's avatar
unknown committed
105 106 107
drop table t1;
create table t1 (a int not null, unique aa (a));
show create table t1;
108 109 110 111
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
  UNIQUE KEY `aa` (`a`)
112
) TYPE=MyISAM CHARSET=latin1
unknown's avatar
unknown committed
113 114 115
drop table t1;
create table t1 (a int not null, primary key (a));
show create table t1;
116 117 118 119
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
  PRIMARY KEY  (`a`)
120
) TYPE=MyISAM CHARSET=latin1
unknown's avatar
unknown committed
121 122 123
drop table t1;
flush tables;
show open tables;
124
Database	Table	In_use	Name_locked
unknown's avatar
unknown committed
125 126 127
create table t1(n int);
insert into t1 values (1);
show open tables;
128 129
Database	Table	In_use	Name_locked
test	t1	0	0
unknown's avatar
unknown committed
130
drop table t1;
unknown's avatar
unknown committed
131 132 133 134 135
create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
136
  `b` char(10) default NULL,
unknown's avatar
unknown committed
137
  KEY `b` (`b`)
138
) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
unknown's avatar
unknown committed
139 140 141 142 143
alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
144
  `b` varchar(10) default NULL,
unknown's avatar
unknown committed
145
  KEY `b` (`b`)
146
) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test'
unknown's avatar
unknown committed
147 148 149 150 151
ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
152
  `b` varchar(10) default NULL,
unknown's avatar
unknown committed
153
  KEY `b` (`b`)
154
) TYPE=MyISAM CHARSET=latin1
unknown's avatar
unknown committed
155
drop table t1;
156 157
create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));
show columns from t1;
158 159 160 161 162 163 164
Field	Type	Null	Key	Default	Extra
a	decimal(9,2)	YES		NULL	
b	decimal(9,0)	YES		NULL	
e	double(9,2)	YES		NULL	
f	double(5,0)	YES		NULL	
h	float(3,2)	YES		NULL	
i	float(3,0)	YES		NULL	
unknown's avatar
unknown committed
165
show full columns from t1;
166
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
167 168 169 170 171 172
a	decimal(9,2)	NULL	YES		NULL		select,insert,update,references	
b	decimal(9,0)	NULL	YES		NULL		select,insert,update,references	
e	double(9,2)	NULL	YES		NULL		select,insert,update,references	
f	double(5,0)	NULL	YES		NULL		select,insert,update,references	
h	float(3,2)	NULL	YES		NULL		select,insert,update,references	
i	float(3,0)	NULL	YES		NULL		select,insert,update,references	
173
drop table t1;
unknown's avatar
unknown committed
174 175 176 177 178 179 180 181 182 183 184 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
create table t1 (
type_bool bool not null,
type_tiny tinyint not null auto_increment primary key,
type_short smallint(3),
type_mediumint mediumint,
type_bigint bigint,
type_decimal decimal(5,2),
type_numeric numeric(5,2),
empty_char char(0),
type_char char(2),
type_varchar varchar(10),
type_timestamp timestamp not null,
type_date date not null,
type_time time not null,
type_datetime datetime not null,
type_year year,
type_enum enum ('red', 'green', 'blue'),
type_set enum ('red', 'green', 'blue'),
type_tinyblob tinyblob,
type_blob blob,
type_medium_blob mediumblob,
type_long_blob longblob,
index(type_short)
) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" TYPE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `type_bool` tinyint(1) NOT NULL default '0',
  `type_tiny` tinyint(4) NOT NULL auto_increment,
  `type_short` smallint(3) default NULL,
  `type_mediumint` mediumint(9) default NULL,
  `type_bigint` bigint(20) default NULL,
  `type_decimal` decimal(5,2) default NULL,
  `type_numeric` decimal(5,2) default NULL,
  `empty_char` char(0) default NULL,
  `type_char` char(2) default NULL,
  `type_varchar` varchar(10) default NULL,
unknown's avatar
unknown committed
211
  `type_timestamp` timestamp NOT NULL,
unknown's avatar
unknown committed
212 213 214 215 216 217 218 219 220 221 222 223
  `type_date` date NOT NULL default '0000-00-00',
  `type_time` time NOT NULL default '00:00:00',
  `type_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `type_year` year(4) default NULL,
  `type_enum` enum('red','green','blue') default NULL,
  `type_set` enum('red','green','blue') default NULL,
  `type_tinyblob` tinyblob,
  `type_blob` blob,
  `type_medium_blob` mediumblob,
  `type_long_blob` longblob,
  PRIMARY KEY  (`type_tiny`),
  KEY `type_short` (`type_short`)
unknown's avatar
unknown committed
224
) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test'
unknown's avatar
unknown committed
225 226 227
insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");
select * from t1;
type_bool	type_tiny	type_short	type_mediumint	type_bigint	type_decimal	type_numeric	empty_char	type_char	type_varchar	type_timestamp	type_date	type_time	type_datetime	type_year	type_enum	type_set	type_tinyblob	type_blob	type_medium_blob	type_long_blob
unknown's avatar
unknown committed
228
0	1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2003-02-07 10:00:01	0000-00-00	00:00:00	0000-00-00 00:00:00	NULL	NULL	NULL	NULL	NULL	NULL	NULL
unknown's avatar
unknown committed
229
drop table t1;
230 231 232 233 234 235
create table t1 (a int not null);
create table t2 select max(a) from t1;
show columns from t2;
Field	Type	Null	Key	Default	Extra
max(a)	bigint(20)	YES		NULL	
drop table t1,t2;
236 237
create table t1 (c decimal, d double, f float, r real);
show columns from t1;
238 239 240 241 242
Field	Type	Null	Key	Default	Extra
c	decimal(10,0)	YES		NULL	
d	double	YES		NULL	
f	float	YES		NULL	
r	double	YES		NULL	
243 244 245
drop table t1;
create table t1 (c decimal(3,3), d double(3,3), f float(3,3));
show columns from t1;
246 247 248 249
Field	Type	Null	Key	Default	Extra
c	decimal(4,3)	YES		NULL	
d	double(4,3)	YES		NULL	
f	float(4,3)	YES		NULL	
250
drop table t1;