drop table if exists t1,t2; drop database if exists mysqltest; 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 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 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 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 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 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 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, `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, `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, 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, `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, `type_time` time NOT NULL, `type_datetime` datetime NOT NULL, `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 @old_sql_mode= @@sql_mode, sql_mode= ''; SET @old_sql_quote_show_create= @@sql_quote_show_create, sql_quote_show_create= OFF; CREATE TABLE `a/b` (i INT); ERROR 42000: Incorrect table name 'a/b' 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 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 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 HEAP 9 Fixed 2 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 2 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 2 9 # # # 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 HEAP 9 Fixed 4 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 4 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 4 9 # # # 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 HEAP 9 Fixed 5 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 5 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 5 9 # # # 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 HEAP 9 Fixed 4 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 4 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 4 9 # # # 9 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 HEAP 9 Fixed 0 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 0 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 0 9 # # # 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 HEAP 9 Fixed 1 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 1 5 # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 1 9 # # # 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 HEAP 9 Fixed 0 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t2 HEAP 9 Fixed 0 5 # # # 5 NULL NULL NULL NULL latin1_swedish_ci NULL t3 HEAP 9 Fixed 0 9 # # # 9 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: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' drop database mysqltest; ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest' select * from mysqltest.t1; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' show create database mysqltest; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' drop table mysqltest.t1; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' drop database mysqltest; ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest' select * from mysqltest.t1; ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysqltest' show create database mysqltest; ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysqltest' 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;