DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5; DROP VIEW IF EXISTS v1; show variables where variable_name like "skip_show_database"; Variable_name Value skip_show_database OFF grant select, update, execute on test.* to mysqltest_2@localhost; grant select, update on test.* to mysqltest_1@localhost; create user mysqltest_3@localhost; create user mysqltest_3; select * from information_schema.SCHEMATA where schema_name > 'm'; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH NULL mysql latin1 latin1_swedish_ci NULL NULL test latin1 latin1_swedish_ci NULL select schema_name from information_schema.schemata; schema_name information_schema cluster_replication mysql test show databases like 't%'; Database (t%) test show databases; Database information_schema cluster_replication mysql test show databases where `database` = 't%'; Database create database mysqltest; create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b)); create table test.t2(a int); create table t3(a int, KEY a_data (a)); create table mysqltest.t4(a int); create table t5 (id int auto_increment primary key); insert into t5 values (10); create view v1 (c) as select table_name from information_schema.TABLES; select * from v1; c CHARACTER_SETS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS COLUMN_PRIVILEGES ENGINES EVENTS KEY_COLUMN_USAGE PARTITIONS PLUGINS ROUTINES SCHEMATA SCHEMA_PRIVILEGES STATISTICS TABLES TABLE_CONSTRAINTS TABLE_PRIVILEGES TRIGGERS VIEWS USER_PRIVILEGES binlog_index columns_priv db event func general_log help_category help_keyword help_relation help_topic host plugin proc procs_priv slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user t1 t4 t2 t3 t5 v1 select c,table_name from v1 inner join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second time_zone_name time_zone_name time_zone_transition time_zone_transition time_zone_transition_type time_zone_transition_type t1 t1 t4 t4 t2 t2 t3 t3 t5 t5 select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second time_zone_name time_zone_name time_zone_transition time_zone_transition time_zone_transition_type time_zone_transition_type t1 t1 t4 t4 t2 t2 t3 t3 t5 t5 select c, v2.table_name from v1 right join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; c table_name TABLES TABLES TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TRIGGERS TRIGGERS tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second time_zone_name time_zone_name time_zone_transition time_zone_transition time_zone_transition_type time_zone_transition_type t1 t1 t4 t4 t2 t2 t3 t3 t5 t5 select table_name from information_schema.TABLES where table_schema = "mysqltest" and table_name like "t%"; table_name t1 t4 select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT NULL mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE show keys from t3 where Key_name = "a_data"; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t3 1 a_data 1 a A NULL NULL NULL YES BTREE show tables like 't%'; Tables_in_test (t%) t2 t3 t5 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 t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL t3 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL t5 MyISAM 10 Fixed 1 7 7 # 2048 0 11 # # NULL latin1_swedish_ci NULL v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW show full columns from t3 like "a%"; Field Type Collation Null Key Default Extra Privileges Comment a int(11) NULL YES MUL NULL select,insert,update,references show full columns from mysql.db like "Insert%"; Field Type Collation Null Key Default Extra Privileges Comment Insert_priv enum('N','Y') utf8_general_ci NO N select,insert,update,references show full columns from v1; Field Type Collation Null Key Default Extra Privileges Comment c varchar(64) utf8_general_ci NO select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references show columns from mysqltest.t1 where field like "%a%"; Field Type Null Key Default Extra a int(11) YES NULL create view mysqltest.v1 (c) as select a from mysqltest.t1; grant select (a) on mysqltest.t1 to mysqltest_2@localhost; grant select on mysqltest.v1 to mysqltest_3; select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 't1'; table_name column_name privileges t1 a select show columns from mysqltest.t1; Field Type Null Key Default Extra a int(11) YES NULL b varchar(30) YES MUL NULL select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 'v1'; table_name column_name privileges v1 c select drop view v1, mysqltest.v1; drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; drop database mysqltest; select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like 'latin1%'; CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN latin1 latin1_swedish_ci cp1252 West European 1 SHOW CHARACTER SET LIKE 'latin1%'; Charset Description Default collation Maxlen latin1 cp1252 West European latin1_swedish_ci 1 SHOW CHARACTER SET WHERE charset like 'latin1%'; Charset Description Default collation Maxlen latin1 cp1252 West European latin1_swedish_ci 1 select * from information_schema.COLLATIONS where COLLATION_NAME like 'latin1%'; COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN latin1_german1_ci latin1 5 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin1_danish_ci latin1 15 0 latin1_german2_ci latin1 31 Yes 2 latin1_bin latin1 47 Yes 1 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_spanish_ci latin1 94 0 SHOW COLLATION LIKE 'latin1%'; Collation Charset Id Default Compiled Sortlen latin1_german1_ci latin1 5 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin1_danish_ci latin1 15 0 latin1_german2_ci latin1 31 Yes 2 latin1_bin latin1 47 Yes 1 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_spanish_ci latin1 94 0 SHOW COLLATION WHERE collation like 'latin1%'; Collation Charset Id Default Compiled Sortlen latin1_german1_ci latin1 5 0 latin1_swedish_ci latin1 8 Yes Yes 1 latin1_danish_ci latin1 15 0 latin1_german2_ci latin1 31 Yes 2 latin1_bin latin1 47 Yes 1 latin1_general_ci latin1 48 0 latin1_general_cs latin1 49 0 latin1_spanish_ci latin1 94 0 select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY where COLLATION_NAME like 'latin1%'; COLLATION_NAME CHARACTER_SET_NAME latin1_german1_ci latin1 latin1_swedish_ci latin1 latin1_danish_ci latin1 latin1_german2_ci latin1 latin1_bin latin1 latin1_general_ci latin1 latin1_general_cs latin1 latin1_spanish_ci latin1 drop procedure if exists sel2; drop function if exists sub1; drop function if exists sub2; create function sub1(i int) returns int return i+1; create procedure sel2() begin select * from t1; select * from t2; end| select parameter_style, sql_data_access, dtd_identifier from information_schema.routines; parameter_style sql_data_access dtd_identifier SQL CONTAINS SQL NULL SQL CONTAINS SQL int(11) show procedure status; Db Name Type Definer Modified Created Security_type Comment test sel2 PROCEDURE root@localhost # # DEFINER show function status; Db Name Type Definer Modified Created Security_type Comment test sub1 FUNCTION root@localhost # # DEFINER select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; ROUTINE_NAME sel2 sub1 explain select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where a.ROUTINE_SCHEMA = b.SCHEMA_NAME; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE # ALL NULL NULL NULL NULL 2 1 SIMPLE # ALL NULL NULL NULL NULL 2 Using where select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1; ROUTINE_NAME name sel2 sel2 sub1 sub1 select count(*) from information_schema.ROUTINES; count(*) 2 select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION show create function sub1; ERROR 42000: FUNCTION sub1 does not exist select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION sel2 sub1 grant all privileges on test.* to mysqltest_1@localhost; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION sel2 sub1 create function sub2(i int) returns int return i+1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ROUTINE_NAME ROUTINE_DEFINITION sel2 sub1 sub2 return i+1 show create procedure sel2; Procedure sql_mode Create Procedure sel2 show create function sub1; Function sql_mode Create Function sub1 show create function sub2; Function sql_mode Create Function sub2 CREATE FUNCTION `sub2`(i int) RETURNS int(11) return i+1 show function status like "sub2"; Db Name Type Definer Modified Created Security_type Comment test sub2 FUNCTION mysqltest_1@localhost # # DEFINER drop function sub2; show create procedure sel2; Procedure sql_mode Create Procedure sel2 CREATE PROCEDURE `sel2`() begin select * from t1; select * from t2; end create view v0 (c) as select schema_name from information_schema.schemata; select * from v0; c information_schema cluster_replication mysql test explain select * from v0; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY # ALL NULL NULL NULL NULL 2 create view v1 (c) as select table_name from information_schema.tables where table_name="v1"; select * from v1; c v1 create view v2 (c) as select column_name from information_schema.columns where table_name="v2"; select * from v2; c c create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets where CHARACTER_SET_NAME like "latin1%"; select * from v3; c latin1 create view v4 (c) as select COLLATION_NAME from information_schema.collations where COLLATION_NAME like "latin1%"; select * from v4; c latin1_german1_ci latin1_swedish_ci latin1_danish_ci latin1_german2_ci latin1_bin latin1_general_ci latin1_general_cs latin1_spanish_ci show keys from v4; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment select * from information_schema.views where TABLE_NAME like "v%"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE NULL test v0 select sql_no_cache `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata` NONE NO root@localhost DEFINER NULL test v1 select sql_no_cache `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1') NONE NO root@localhost DEFINER NULL test v2 select sql_no_cache `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2') NONE NO root@localhost DEFINER NULL test v3 select sql_no_cache `character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`character_sets`.`CHARACTER_SET_NAME` like _utf8'latin1%') NONE NO root@localhost DEFINER NULL test v4 select sql_no_cache `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like _utf8'latin1%') NONE NO root@localhost DEFINER drop view v0, v1, v2, v3, v4; create table t1 (a int); grant select,update,insert on t1 to mysqltest_1@localhost; grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost; grant all on test.* to mysqltest_1@localhost with grant option; select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL USAGE NO select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test SELECT YES 'mysqltest_1'@'localhost' NULL test INSERT YES 'mysqltest_1'@'localhost' NULL test UPDATE YES 'mysqltest_1'@'localhost' NULL test DELETE YES 'mysqltest_1'@'localhost' NULL test CREATE YES 'mysqltest_1'@'localhost' NULL test DROP YES 'mysqltest_1'@'localhost' NULL test REFERENCES YES 'mysqltest_1'@'localhost' NULL test INDEX YES 'mysqltest_1'@'localhost' NULL test ALTER YES 'mysqltest_1'@'localhost' NULL test CREATE TEMPORARY TABLES YES 'mysqltest_1'@'localhost' NULL test LOCK TABLES YES 'mysqltest_1'@'localhost' NULL test EXECUTE YES 'mysqltest_1'@'localhost' NULL test CREATE VIEW YES 'mysqltest_1'@'localhost' NULL test SHOW VIEW YES 'mysqltest_1'@'localhost' NULL test CREATE ROUTINE YES 'mysqltest_1'@'localhost' NULL test ALTER ROUTINE YES 'mysqltest_1'@'localhost' NULL test EVENT YES select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test t1 SELECT NO 'mysqltest_1'@'localhost' NULL test t1 INSERT NO 'mysqltest_1'@'localhost' NULL test t1 UPDATE NO select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'mysqltest_1'@'localhost' NULL test t1 a SELECT NO 'mysqltest_1'@'localhost' NULL test t1 a INSERT NO 'mysqltest_1'@'localhost' NULL test t1 a UPDATE NO 'mysqltest_1'@'localhost' NULL test t1 a REFERENCES NO delete from mysql.user where user like 'mysqltest%'; delete from mysql.db where user like 'mysqltest%'; delete from mysql.tables_priv where user like 'mysqltest%'; delete from mysql.columns_priv where user like 'mysqltest%'; flush privileges; drop table t1; create table t1 (a int null, primary key(a)); alter table t1 add constraint constraint_1 unique (a); alter table t1 add constraint unique key_1(a); alter table t1 add constraint constraint_2 unique key_2(a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', PRIMARY KEY (`a`), UNIQUE KEY `constraint_1` (`a`), UNIQUE KEY `key_1` (`a`), UNIQUE KEY `key_2` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from information_schema.TABLE_CONSTRAINTS where TABLE_SCHEMA= "test"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE NULL test PRIMARY test t1 PRIMARY KEY NULL test constraint_1 test t1 UNIQUE NULL test key_1 test t1 UNIQUE NULL test key_2 test t1 UNIQUE select * from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA= "test"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME NULL test PRIMARY NULL test t1 a 1 NULL NULL NULL NULL NULL test constraint_1 NULL test t1 a 1 NULL NULL NULL NULL NULL test key_1 NULL test t1 a 1 NULL NULL NULL NULL NULL test key_2 NULL test t1 a 1 NULL NULL NULL NULL select table_name from information_schema.TABLES where table_schema like "test%"; table_name t1 select table_name,column_name from information_schema.COLUMNS where table_schema like "test%"; table_name column_name t1 a select ROUTINE_NAME from information_schema.ROUTINES; ROUTINE_NAME sel2 sub1 delete from mysql.user where user='mysqltest_1'; drop table t1; drop procedure sel2; drop function sub1; create table t1(a int); create view v1 (c) as select a from t1 with check option; create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION; create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION; select * from information_schema.views; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE NULL test v1 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER NULL test v2 select `test`.`t1`.`a` AS `c` from `test`.`t1` LOCAL YES root@localhost DEFINER NULL test v3 select `test`.`t1`.`a` AS `c` from `test`.`t1` CASCADED YES root@localhost DEFINER grant select (a) on test.t1 to joe@localhost with grant option; select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'joe'@'localhost' NULL test t1 a SELECT YES select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE drop view v1, v2, v3; drop table t1; delete from mysql.user where user='joe'; delete from mysql.db where user='joe'; delete from mysql.tables_priv where user='joe'; delete from mysql.columns_priv where user='joe'; flush privileges; create table t1 (a int not null auto_increment,b int, primary key (a)); insert into t1 values (1,1),(NULL,3),(NULL,4); select AUTO_INCREMENT from information_schema.tables where table_name = 't1'; AUTO_INCREMENT 4 drop table t1; create table t1 (s1 int); insert into t1 values (0),(9),(0); select s1 from t1 where s1 in (select version from information_schema.tables) union select version from information_schema.tables; s1 10 drop table t1; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 set names latin2; SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets; Table Create Table CHARACTER_SETS CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8 set names latin1; create table t1 select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like "latin1"; select * from t1; CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN latin1 latin1_swedish_ci cp1252 West European 1 alter table t1 default character set utf8; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 drop table t1; create view v1 as select * from information_schema.TABLES; drop view v1; create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2), d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3), i DOUBLE); select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name= 't1'; COLUMN_NAME COLUMN_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE a decimal(5,3) NULL NULL 5 3 b decimal(5,1) NULL NULL 5 1 c float(5,2) NULL NULL 5 2 d decimal(6,4) NULL NULL 6 4 e float NULL NULL 12 NULL f decimal(6,3) NULL NULL 6 3 g int(11) NULL NULL 10 0 h double(10,3) NULL NULL 10 3 i double NULL NULL 22 NULL drop table t1; create table t115 as select table_name, column_name, column_type from information_schema.columns where table_name = 'proc'; select * from t115; table_name column_name column_type proc db char(64) proc name char(64) proc type enum('FUNCTION','PROCEDURE') proc specific_name char(64) proc language enum('SQL') proc sql_data_access enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') proc is_deterministic enum('YES','NO') proc security_type enum('INVOKER','DEFINER') proc param_list blob proc returns char(64) proc body longblob proc definer char(77) proc created timestamp proc modified timestamp proc sql_mode set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE') proc comment char(64) drop table t115; create procedure p108 () begin declare c cursor for select data_type from information_schema.columns; open c; open c; end;// call p108()// ERROR 24000: Cursor is already open drop procedure p108; create view v1 as select A1.table_name from information_schema.TABLES A1 where table_name= "user"; select * from v1; table_name user drop view v1; create view vo as select 'a' union select 'a'; show index from vo; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE select * from information_schema.KEY_COLUMN_USAGE where TABLE_NAME= "vo"; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME drop view vo; select TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema='information_schema' limit 2; TABLE_NAME TABLE_TYPE ENGINE CHARACTER_SETS SYSTEM VIEW MEMORY COLLATIONS SYSTEM VIEW MEMORY show tables from information_schema like "T%"; Tables_in_information_schema (T%) TABLES TABLE_CONSTRAINTS TABLE_PRIVILEGES TRIGGERS create database information_schema; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' use information_schema; show full tables like "T%"; Tables_in_information_schema (T%) Table_type TABLES SYSTEM VIEW TABLE_CONSTRAINTS SYSTEM VIEW TABLE_PRIVILEGES SYSTEM VIEW TRIGGERS SYSTEM VIEW create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema use test; show tables; Tables_in_test use information_schema; show tables like "T%"; Tables_in_information_schema (T%) TABLES TABLE_CONSTRAINTS TABLE_PRIVILEGES TRIGGERS select table_name from tables where table_name='user'; table_name user select column_name, privileges from columns where table_name='user' and column_name like '%o%'; column_name privileges Host select,insert,update,references Password select,insert,update,references Drop_priv select,insert,update,references Reload_priv select,insert,update,references Shutdown_priv select,insert,update,references Process_priv select,insert,update,references Show_db_priv select,insert,update,references Lock_tables_priv select,insert,update,references Show_view_priv select,insert,update,references Create_routine_priv select,insert,update,references Alter_routine_priv select,insert,update,references max_questions select,insert,update,references max_connections select,insert,update,references max_user_connections select,insert,update,references use test; create function sub1(i int) returns int return i+1; create table t1(f1 int); create view v2 (c) as select f1 from t1; create view v3 (c) as select sub1(1); create table t4(f1 int, KEY f1_key (f1)); drop table t1; drop function sub1; select table_name from information_schema.views where table_schema='test'; table_name v2 v3 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select table_name from information_schema.views where table_schema='test'; table_name v2 v3 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select column_name from information_schema.columns where table_schema='test'; column_name f1 Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select index_name from information_schema.statistics where table_schema='test'; index_name f1_key select constraint_name from information_schema.table_constraints where table_schema='test'; constraint_name show create view v2; View Create View v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1` Warnings: Warning 1356 View 'test.v2' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them show create table v3; View Create View v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select sql_no_cache `test`.`sub1`(1) AS `c` Warnings: Warning 1356 View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v2; drop view v3; drop table t4; select * from information_schema.table_names; ERROR 42S02: Unknown table 'table_names' in information_schema select column_type from information_schema.columns where table_schema="information_schema" and table_name="COLUMNS" and (column_name="character_set_name" or column_name="collation_name"); column_type varchar(64) varchar(64) select TABLE_ROWS from information_schema.tables where table_schema="information_schema" and table_name="COLUMNS"; TABLE_ROWS NULL select table_type from information_schema.tables where table_schema="mysql" and table_name="user"; table_type BASE TABLE show open tables where `table` like "user"; Database Table In_use Name_locked mysql user 0 0 show status where variable_name like "%database%"; Variable_name Value Com_show_databases 3 show variables where variable_name like "skip_show_databas"; Variable_name Value show global status like "Threads_running"; Variable_name Value Threads_running # create table t1(f1 int); create table t2(f2 int); create view v1 as select * from t1, t2; set @got_val= (select count(*) from information_schema.columns); drop view v1; drop table t1, t2; CREATE TABLE t_crashme ( f1 BIGINT); CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1; CREATE VIEW a2 AS SELECT t_CRASHME FROM a1; count(*) 110 drop view a2, a1; drop table t_crashme; select table_schema,table_name, column_name from information_schema.columns where data_type = 'longtext'; table_schema table_name column_name information_schema COLUMNS COLUMN_TYPE information_schema EVENTS EVENT_BODY information_schema EVENTS SQL_MODE information_schema PARTITIONS PARTITION_EXPRESSION information_schema PARTITIONS SUBPARTITION_EXPRESSION information_schema PARTITIONS PARTITION_DESCRIPTION information_schema PLUGINS PLUGIN_DESCRIPTION information_schema ROUTINES ROUTINE_DEFINITION information_schema ROUTINES SQL_MODE information_schema TRIGGERS ACTION_CONDITION information_schema TRIGGERS ACTION_STATEMENT information_schema TRIGGERS SQL_MODE information_schema TRIGGERS DEFINER information_schema VIEWS VIEW_DEFINITION select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; table_name column_name data_type EVENTS EXECUTE_AT datetime EVENTS STARTS datetime EVENTS ENDS datetime EVENTS CREATED datetime EVENTS LAST_ALTERED datetime EVENTS LAST_EXECUTED datetime PARTITIONS CREATE_TIME datetime PARTITIONS UPDATE_TIME datetime PARTITIONS CHECK_TIME datetime ROUTINES CREATED datetime ROUTINES LAST_ALTERED datetime TABLES CREATE_TIME datetime TABLES UPDATE_TIME datetime TABLES CHECK_TIME datetime TRIGGERS CREATED datetime event execute_at datetime event last_executed datetime event starts datetime event ends datetime SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS B WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME); COUNT(*) 0 create table t1 ( x_bigint BIGINT, x_integer INTEGER, x_smallint SMALLINT, x_decimal DECIMAL(5,3), x_numeric NUMERIC(5,3), x_real REAL, x_float FLOAT, x_double_precision DOUBLE PRECISION ); SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= 't1'; COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH x_bigint NULL NULL x_integer NULL NULL x_smallint NULL NULL x_decimal NULL NULL x_numeric NULL NULL x_real NULL NULL x_float NULL NULL x_double_precision NULL NULL drop table t1; grant select on test.* to mysqltest_4@localhost; SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; TABLE_NAME COLUMN_NAME PRIVILEGES COLUMNS TABLE_NAME select COLUMN_PRIVILEGES TABLE_NAME select KEY_COLUMN_USAGE TABLE_NAME select PARTITIONS TABLE_NAME select STATISTICS TABLE_NAME select TABLES TABLE_NAME select TABLE_CONSTRAINTS TABLE_NAME select TABLE_PRIVILEGES TABLE_NAME select VIEWS TABLE_NAME select delete from mysql.user where user='mysqltest_4'; delete from mysql.db where user='mysqltest_4'; flush privileges; SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; table_schema count(*) cluster_replication 1 information_schema 20 mysql 21 create table t1 (i int, j int); create trigger trg1 before insert on t1 for each row begin if new.j > 10 then set new.j := 10; end if; end| create trigger trg2 before update on t1 for each row begin if old.i % 2 = 0 then set new.j := -1; end if; end| create trigger trg3 after update on t1 for each row begin if new.j = -1 then set @fired:= "Yes"; end if; end| show triggers; Trigger Event Table Statement Timing Created sql_mode Definer trg1 INSERT t1 begin if new.j > 10 then set new.j := 10; end if; end BEFORE NULL root@localhost trg2 UPDATE t1 begin if old.i % 2 = 0 then set new.j := -1; end if; end BEFORE NULL root@localhost trg3 UPDATE t1 begin if new.j = -1 then set @fired:= "Yes"; end if; end AFTER NULL root@localhost select * from information_schema.triggers; TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then set new.j := 10; end if; end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg2 UPDATE NULL test t1 0 NULL begin if old.i % 2 = 0 then set new.j := -1; end if; end ROW BEFORE NULL NULL OLD NEW NULL root@localhost NULL test trg3 UPDATE NULL test t1 0 NULL begin if new.j = -1 then set @fired:= "Yes"; end if; end ROW AFTER NULL NULL OLD NEW NULL root@localhost drop trigger trg1; drop trigger trg2; drop trigger trg3; drop table t1; create database mysqltest; create table mysqltest.t1 (f1 int, f2 int); create table mysqltest.t2 (f1 int); grant select (f1) on mysqltest.t1 to user1@localhost; grant select on mysqltest.t2 to user2@localhost; grant select on mysqltest.* to user3@localhost; grant select on *.* to user4@localhost; select * from information_schema.column_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL mysqltest t1 f1 SELECT NO select * from information_schema.table_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE select * from information_schema.schema_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE select * from information_schema.user_privileges; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL USAGE NO show grants; Grants for user1@localhost GRANT USAGE ON *.* TO 'user1'@'localhost' GRANT SELECT (f1) ON `mysqltest`.`t1` TO 'user1'@'localhost' select * from information_schema.column_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE select * from information_schema.table_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' NULL mysqltest t2 SELECT NO select * from information_schema.schema_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE select * from information_schema.user_privileges; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' NULL USAGE NO show grants; Grants for user2@localhost GRANT USAGE ON *.* TO 'user2'@'localhost' GRANT SELECT ON `mysqltest`.`t2` TO 'user2'@'localhost' select * from information_schema.column_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE select * from information_schema.table_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE select * from information_schema.schema_privileges; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' NULL mysqltest SELECT NO select * from information_schema.user_privileges; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' NULL USAGE NO show grants; Grants for user3@localhost GRANT USAGE ON *.* TO 'user3'@'localhost' GRANT SELECT ON `mysqltest`.* TO 'user3'@'localhost' select * from information_schema.column_privileges where grantee like '%user%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL mysqltest t1 f1 SELECT NO select * from information_schema.table_privileges where grantee like '%user%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE 'user2'@'localhost' NULL mysqltest t2 SELECT NO select * from information_schema.schema_privileges where grantee like '%user%'; GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE 'user3'@'localhost' NULL mysqltest SELECT NO select * from information_schema.user_privileges where grantee like '%user%'; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE 'user1'@'localhost' NULL USAGE NO 'user2'@'localhost' NULL USAGE NO 'user3'@'localhost' NULL USAGE NO 'user4'@'localhost' NULL SELECT NO show grants; Grants for user4@localhost GRANT SELECT ON *.* TO 'user4'@'localhost' drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost; use test; drop database mysqltest; drop procedure if exists p1; drop procedure if exists p2; create procedure p1 () modifies sql data set @a = 5; create procedure p2 () set @a = 5; select sql_data_access from information_schema.routines where specific_name like 'p%'; sql_data_access MODIFIES SQL DATA CONTAINS SQL drop procedure p1; drop procedure p2; show create database information_schema; Database Create Database information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ create table t1(f1 LONGBLOB, f2 LONGTEXT); select column_name,data_type,CHARACTER_OCTET_LENGTH, CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name='t1'; column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH f1 longblob 4294967295 4294967295 f2 longtext 4294967295 4294967295 drop table t1; create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int, f5 BIGINT, f6 BIT, f7 bit(64)); select column_name, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name='t1'; column_name NUMERIC_PRECISION NUMERIC_SCALE f1 3 0 f2 5 0 f3 7 0 f4 10 0 f5 19 0 f6 1 NULL f7 64 NULL drop table t1; create table t1 (f1 integer); create trigger tr1 after insert on t1 for each row set @test_var=42; use information_schema; select trigger_schema, trigger_name from triggers where trigger_name='tr1'; trigger_schema trigger_name test tr1 use test; drop table t1; create table t1 (a int not null, b int); use information_schema; select column_name, column_default from columns where table_schema='test' and table_name='t1'; column_name column_default a NULL b NULL use test; show columns from t1; Field Type Null Key Default Extra a int(11) NO b int(11) YES NULL drop table t1; CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); SHOW TABLE STATUS FROM test WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); 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 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL DROP TABLE t1,t2; create table t1(f1 int); create view v1 (c) as select f1 from t1; select database(); database() NULL show fields from test.v1; Field Type Null Key Default Extra c int(11) YES NULL drop view v1; drop table t1; alter database information_schema; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' drop database information_schema; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' drop table information_schema.tables; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' alter table information_schema.tables; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' use information_schema; create temporary table schemata(f1 char(10)); ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' CREATE PROCEDURE p1 () BEGIN SELECT 'foo' FROM DUAL; END | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' select ROUTINE_NAME from routines; ROUTINE_NAME grant all on information_schema.* to 'user1'@'localhost'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' grant select on information_schema.* to 'user1'@'localhost'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' use test; create table t1(id int); insert into t1(id) values (1); select 1 from (select 1 from test.t1) a; 1 1 use information_schema; select 1 from (select 1 from test.t1) a; 1 1 use test; drop table t1; create table t1(a blob, b text charset utf8, c text charset ucs2); select data_type, character_octet_length, character_maximum_length from information_schema.columns where table_name='t1'; data_type character_octet_length character_maximum_length blob 65535 65535 text 65535 65535 text 65535 32767 drop table t1; create table t1 (f1 int(11)); create view v1 as select * from t1; drop table t1; select table_type from information_schema.tables where table_name="v1"; table_type VIEW drop view v1; create temporary table t1(f1 int, index(f1)); show columns from t1; Field Type Null Key Default Extra f1 int(11) YES MUL NULL describe t1; Field Type Null Key Default Extra f1 int(11) YES MUL NULL show indexes from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment t1 1 f1 1 f1 A NULL NULL NULL YES BTREE drop table t1; create table t1(f1 binary(32), f2 varbinary(64)); select character_maximum_length, character_octet_length from information_schema.columns where table_name='t1'; character_maximum_length character_octet_length 32 32 64 64 drop table t1; CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT); INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1; CREATE FUNCTION func2() RETURNS BIGINT RETURN 1; CREATE FUNCTION func1() RETURNS BIGINT BEGIN RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS); END// CREATE VIEW v1 AS SELECT 1 FROM t1 WHERE f3 = (SELECT func2 ()); SELECT func1(); func1() 1 DROP TABLE t1; DROP VIEW v1; DROP FUNCTION func1; DROP FUNCTION func2; select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS MyISAM ENABLED Default engine as of MySQL 3.23 with great performance NO NO NO