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); check table t1 fast; Table Op Msg_type Msg_text test.t1 check status Table is already up to date check table t1 fast; Table Op Msg_type Msg_text test.t1 check status Table is already up to date check table t1 changed; Table Op Msg_type Msg_text test.t1 check status OK insert into t1 values (5,5,5); check table t1 changed; Table Op Msg_type Msg_text test.t1 check status OK check table t1 medium; Table Op Msg_type Msg_text test.t1 check status OK check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK show index 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 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 insert into t1 values (5,5,5); ERROR 23000: Duplicate entry '5' for key 1 optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status Table is already up to date drop table t1; show variables like "wait_timeout%"; Variable_name Value wait_timeout 28800 show variables like "WAIT_timeout%"; Variable_name Value wait_timeout 28800 show variables like "this_doesn't_exists%"; Variable_name Value show table status from test like "this_doesn't_exists%"; 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 show databases; Database mysql test show databases like "test%"; Database (test%) test 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; Table Op Msg_type Msg_text test.t1 analyze status OK show index 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 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 repair table t1; Table Op Msg_type Msg_text test.t1 repair status OK show index 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 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 drop table t1; create temporary table t1 (a int not null); show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( `a` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 alter table t1 rename t2; show create table t2; Table Create Table t2 CREATE TEMPORARY TABLE `t2` ( `a` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t2; create table t1 ( test_set set( 'val1', 'val2', 'val3' ) not null default '', name char(20) default 'O''Brien' comment 'O''Brien as default', c int not null comment 'int column', `c-b` int comment 'name with a space', `space ` int comment 'name with a space', ) comment = 'it\'s a table' ; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', `name` char(20) default 'O''Brien' COMMENT 'O''Brien as default', `c` int(11) NOT NULL default '0' COMMENT 'int column', `c-b` int(11) default NULL COMMENT 'name with a space', `space ` int(11) default NULL COMMENT 'name with a space' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' set sql_quote_show_create=0; show create table t1; Table Create Table t1 CREATE TABLE t1 ( test_set set('val1','val2','val3') NOT NULL default '', name char(20) default 'O''Brien' COMMENT 'O''Brien as default', c int(11) NOT NULL default '0' COMMENT 'int column', `c-b` int(11) default NULL COMMENT 'name with a space', `space ` int(11) default NULL COMMENT 'name with a space' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='it''s a table' set sql_quote_show_create=1; show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment 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 c int(11) NULL 0 select,insert,update,references int column c-b int(11) NULL YES NULL select,insert,update,references name with a space space int(11) NULL YES NULL select,insert,update,references name with a space drop table t1; create table t1 (a int not null, unique aa (a)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', UNIQUE KEY `aa` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int not null, primary key (a)); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; flush tables; show open tables; Database Table In_use Name_locked create table t1(n int); insert into t1 values (1); show open tables; Database Table In_use Name_locked test t1 0 0 drop table t1; create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=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', `b` char(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT 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' 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', `b` varchar(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT 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' 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', `b` varchar(10) default NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; 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; 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 show full columns from t1; Field Type Collation Null Key Default Extra Privileges Comment 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 drop table t1; 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" ENGINE=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, `type_timestamp` timestamp NOT NULL, `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`) ) ENGINE=MyISAM DEFAULT 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' 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 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 drop table t1; 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; create table t1 (c decimal, d double, f float, r real); show columns from t1; Field Type Null Key Default Extra c decimal(10,0) YES NULL d double YES NULL f float YES NULL r double YES NULL drop table t1; create table t1 (c decimal(3,3), d double(3,3), f float(3,3)); show columns from t1; Field Type Null Key Default Extra c decimal(4,3) YES NULL d double(4,3) YES NULL f float(4,3) YES NULL drop table t1; SET sql_mode=''; SET sql_quote_show_create=OFF; CREATE TABLE ```ab``cd``` (i INT); SHOW CREATE TABLE ```ab``cd```; Table Create Table `ab`cd` CREATE TABLE ```ab``cd``` ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE ```ab``cd```; CREATE TABLE ```ab````cd``` (i INT); SHOW CREATE TABLE ```ab````cd```; Table Create Table `ab``cd` CREATE TABLE ```ab````cd``` ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE ```ab````cd```; CREATE TABLE ```a` (i INT); SHOW CREATE TABLE ```a`; Table Create Table `a CREATE TABLE ```a` ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE ```a`; SET sql_mode='ANSI_QUOTES'; CREATE TABLE """a" (i INT); SHOW CREATE TABLE """a"; Table Create Table "a CREATE TABLE """a" ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE """a"; SET sql_mode=''; SET sql_quote_show_create=OFF; CREATE TABLE t1 (i INT); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE t1 ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE `table` (i INT); SHOW CREATE TABLE `table`; Table Create Table table CREATE TABLE `table` ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE `table`; SET sql_quote_show_create=ON; select @@max_heap_table_size; @@max_heap_table_size 1047552 CREATE TABLE t1 ( a int(11) default NULL, KEY a TYPE BTREE (a) ) ENGINE=HEAP; CREATE TABLE t2 ( b int(11) default NULL, index(b) ) ENGINE=HEAP; CREATE TABLE t3 ( a int(11) default NULL, b int(11) default NULL, KEY a TYPE BTREE (a), index(b) ) ENGINE=HEAP; insert into t1 values (1),(2); insert into t2 values (1),(2); insert into t3 values (1,1),(2,2); show table status; 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 t1 HEAP Fixed 2 5 39904 249415 42 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 2 5 39904 249415 39904 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 2 9 33072 248103 22090 0 NULL NULL NULL NULL latin1_swedish_ci NULL insert into t1 values (3),(4); insert into t2 values (3),(4); insert into t3 values (3,3),(4,4); show table status; 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 t1 HEAP Fixed 4 5 39904 249415 84 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 4 5 39904 249415 39904 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 4 9 33072 248103 22132 0 NULL NULL NULL NULL latin1_swedish_ci NULL insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); show table status; 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 t1 HEAP Fixed 5 5 39904 249415 105 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 5 5 39904 249415 39904 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 5 9 33072 248103 22153 0 NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1 where a=3; delete from t2 where b=3; delete from t3 where a=3; show table status; 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 t1 HEAP Fixed 4 5 39904 249415 89 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 4 5 39904 249415 39904 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 4 9 33072 248103 22137 9 NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1; delete from t2; delete from t3; show table status; 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 t1 HEAP Fixed 0 5 0 249415 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 0 5 0 249415 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 0 9 0 248103 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL insert into t1 values (5); insert into t2 values (5); insert into t3 values (5,5); show table status; 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 t1 HEAP Fixed 1 5 39904 249415 21 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 1 5 39904 249415 39904 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 1 9 33072 248103 22069 0 NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1 where a=5; delete from t2 where b=5; delete from t3 where a=5; show table status; 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 t1 HEAP Fixed 0 5 39904 249415 5 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP Fixed 0 5 39904 249415 39904 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP Fixed 0 9 33072 248103 22053 9 NULL NULL NULL NULL latin1_swedish_ci NULL drop table t1, t2, t3;