drop table if exists t1,t2; drop table if exists t1aa,t2aa; drop database if exists mysqltest; drop database if exists mysqltest1; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; 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 'PRIMARY' 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 Engine Version 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 information_schema cluster_replication 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 ) 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 ) 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 minus', `space 2` 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 COMMENT 'int column', `c-b` int(11) default NULL COMMENT 'name with a minus', `space 2` 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 COMMENT 'int column', `c-b` int(11) default NULL COMMENT 'name with a minus', `space 2` 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 NO 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 NO select,insert,update,references int column c-b int(11) NULL YES NULL select,insert,update,references name with a minus space 2 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, 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, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; flush tables; show open tables; Database Table In_use Name_locked mysql general_log 1 0 mysql slow_log 1 0 create table t1(n int); insert into t1 values (1); show open tables; Database Table In_use Name_locked mysql general_log 1 0 mysql slow_log 1 0 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, `b` varchar(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, `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, `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 default 0, 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 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, 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 default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `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) int(11) 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(3,3) YES NULL d double(3,3) YES NULL f float(3,3) YES NULL drop table t1; SET @old_sql_mode= @@sql_mode, sql_mode= ''; SET @old_sql_quote_show_create= @@sql_quote_show_create, 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`; CREATE TABLE `a.1` (i INT); SHOW CREATE TABLE `a.1`; Table Create Table a.1 CREATE TABLE `a.1` ( i int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE `a.1`; 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= @old_sql_quote_show_create; SET sql_mode= @old_sql_mode; select @@max_heap_table_size; @@max_heap_table_size 1047552 CREATE TABLE t1 ( a int(11) default NULL, KEY a USING 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 USING 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 Engine Version 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 MEMORY 10 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 2 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 2 # # # # 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 Engine Version 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 MEMORY 10 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 4 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 4 # # # # 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 Engine Version 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 MEMORY 10 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 5 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 5 # # # # 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 Engine Version 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 MEMORY 10 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 4 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL delete from t1; delete from t2; delete from t3; show table status; Name Engine Version 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 MEMORY 10 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 0 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 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 Engine Version 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 MEMORY 10 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 1 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 1 # # # # 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 Engine Version 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 MEMORY 10 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL t2 MEMORY 10 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL t3 MEMORY 10 Fixed 0 # # # # # NULL NULL NULL NULL latin1_swedish_ci NULL drop table t1, t2, t3; create database mysqltest; show create database mysqltest; Database Create Database mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ create table mysqltest.t1(a int); insert into mysqltest.t1 values(1); grant select on `mysqltest`.* to mysqltest_1@localhost; grant usage on `mysqltest`.* to mysqltest_2@localhost; grant drop on `mysqltest`.* to mysqltest_3@localhost; select * from t1; a 1 show create database mysqltest; Database Create Database mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ drop table t1; ERROR 42000: DROP command denied to user 'mysqltest_1'@'localhost' for table 't1' drop database mysqltest; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' select * from mysqltest.t1; ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't1' show create database mysqltest; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' drop table mysqltest.t1; ERROR 42000: DROP command denied to user 'mysqltest_2'@'localhost' for table 't1' drop database mysqltest; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' select * from mysqltest.t1; ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1' show create database mysqltest; Database Create Database mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ drop table mysqltest.t1; drop database mysqltest; set names binary; delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` (`i`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING HASH (`i`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING BTREE (`i`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING BTREE (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ALTER TABLE t1 ENGINE=MEMORY; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` (`i`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING BTREE (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ALTER TABLE t1 ENGINE=MEMORY; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) default NULL, KEY `i` USING BTREE (`i`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1( field1 text NOT NULL, PRIMARY KEY(field1(1000)) ); show index from t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def STATISTICS TABLE_NAME Table 253 64 2 N 1 0 63 def STATISTICS NON_UNIQUE Non_unique 8 1 1 N 32769 0 63 def STATISTICS INDEX_NAME Key_name 253 64 7 N 1 0 63 def STATISTICS SEQ_IN_INDEX Seq_in_index 8 2 1 N 32769 0 63 def STATISTICS COLUMN_NAME Column_name 253 64 6 N 1 0 63 def STATISTICS COLLATION Collation 253 1 1 Y 0 0 63 def STATISTICS CARDINALITY Cardinality 8 21 1 Y 32768 0 63 def STATISTICS SUB_PART Sub_part 8 3 4 Y 32768 0 63 def STATISTICS PACKED Packed 253 10 0 Y 0 0 63 def STATISTICS NULLABLE Null 253 3 0 N 1 0 63 def STATISTICS INDEX_TYPE Index_type 253 16 5 N 1 0 63 def STATISTICS COMMENT Comment 253 16 0 Y 0 0 63 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 0 PRIMARY 1 field1 A 0 1000 NULL BTREE drop table t1; create table t1 ( c1 int NOT NULL, c2 int NOT NULL, PRIMARY KEY USING HASH (c1), INDEX USING BTREE(c2) ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, PRIMARY KEY USING HASH (`c1`), KEY `c2` USING BTREE (`c2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; flush tables; SHOW TABLE STATUS like 't1'; Name Engine Version 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 NULL NULL NULL NULL # # # # NULL NULL NULL NULL NULL NULL NULL NULL Incorrect information in file: './test/t1.frm' show create table t1; ERROR HY000: Incorrect information in file: './test/t1.frm' drop table if exists t1; CREATE TABLE txt1(a int); CREATE TABLE tyt2(a int); CREATE TABLE urkunde(a int); FLUSH TABLES; SELECT 1 FROM mysql.db, mysql.proc, mysql.user, mysql.time_zone, mysql.time_zone_name, txt1, tyt2, urkunde LIMIT 0; 1 SHOW OPEN TABLES; Database Table In_use Name_locked mysql proc 0 0 test urkunde 0 0 mysql time_zone 0 0 mysql db 0 0 test txt1 0 0 mysql slow_log 1 0 test tyt2 0 0 mysql general_log 1 0 mysql user 0 0 mysql time_zone_name 0 0 SHOW OPEN TABLES FROM mysql; Database Table In_use Name_locked mysql proc 0 0 mysql time_zone 0 0 mysql db 0 0 mysql slow_log 1 0 mysql general_log 1 0 mysql user 0 0 mysql time_zone_name 0 0 SHOW OPEN TABLES FROM mysql LIKE 'u%'; Database Table In_use Name_locked mysql user 0 0 SHOW OPEN TABLES LIKE 't%'; Database Table In_use Name_locked mysql time_zone 0 0 test txt1 0 0 test tyt2 0 0 mysql time_zone_name 0 0 SHOW OPEN TABLES LIKE '%o%'; Database Table In_use Name_locked mysql proc 0 0 mysql time_zone 0 0 mysql slow_log 1 0 mysql general_log 1 0 mysql time_zone_name 0 0 FLUSH TABLES; SHOW OPEN TABLES; Database Table In_use Name_locked mysql general_log 1 0 mysql slow_log 1 0 DROP TABLE txt1; DROP TABLE tyt2; DROP TABLE urkunde; SHOW TABLES FROM non_existing_database; ERROR 42000: Unknown database 'non_existing_database' SHOW AUTHORS; create database mysqltest; show create database mysqltest; Database Create Database mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ create table mysqltest.t1(a int); insert into mysqltest.t1 values(1); grant select on `mysqltest`.`t1` to mysqltest_4@localhost; show create database mysqltest; Database Create Database mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ delete from mysql.user where user='mysqltest_4'; delete from mysql.db where user='mysqltest_4'; delete from mysql.tables_priv where user='mysqltest_4'; flush privileges; drop database mysqltest; End of 5.1 tests