information_schema.result 68.8 KB
Newer Older
unknown's avatar
unknown committed
1 2
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
DROP VIEW IF EXISTS v1;
3 4 5
show variables where variable_name like "skip_show_database";
Variable_name	Value
skip_show_database	OFF
6 7
grant select, update, execute on test.* to mysqltest_2@localhost;
grant select, update on test.* to mysqltest_1@localhost;
8 9
create user mysqltest_3@localhost;
create user mysqltest_3;
10
select * from information_schema.SCHEMATA where schema_name > 'm';
11
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH
unknown's avatar
unknown committed
12
NULL	mtr	latin1	latin1_swedish_ci	NULL
13 14
NULL	mysql	latin1	latin1_swedish_ci	NULL
NULL	test	latin1	latin1_swedish_ci	NULL
15 16
select schema_name from information_schema.schemata;
schema_name
17
information_schema
unknown's avatar
unknown committed
18
mtr
19 20 21 22 23 24 25
mysql
test
show databases like 't%';
Database (t%)
test
show databases;
Database
26
information_schema
unknown's avatar
unknown committed
27
mtr
28 29
mysql
test
30 31
show databases where `database` = 't%';
Database
32 33
create database mysqltest;
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
34 35
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
36
create table mysqltest.t4(a int);
37 38
create table t5 (id int auto_increment primary key);
insert into t5 values (10);
39 40 41 42 43
create view v1 (c) as
SELECT table_name FROM information_schema.TABLES
WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND
table_name<>'ndb_binlog_index' AND
table_name<>'ndb_apply_status';
44 45
select * from v1;
c
46 47 48
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
49 50
COLUMNS
COLUMN_PRIVILEGES
unknown's avatar
unknown committed
51
ENGINES
52
EVENTS
unknown's avatar
unknown committed
53
FILES
54 55
GLOBAL_STATUS
GLOBAL_VARIABLES
56
KEY_COLUMN_USAGE
57
PARTITIONS
unknown's avatar
unknown committed
58
PLUGINS
59
PROCESSLIST
60
PROFILING
61
REFERENTIAL_CONSTRAINTS
62
ROUTINES
63
SCHEMATA
64
SCHEMA_PRIVILEGES
65 66
SESSION_STATUS
SESSION_VARIABLES
67 68
STATISTICS
TABLES
69
TABLE_CONSTRAINTS
70
TABLE_PRIVILEGES
71
TRIGGERS
72
USER_PRIVILEGES
73
VIEWS
74 75
columns_priv
db
76
event
77
func
78
general_log
79 80 81 82 83
help_category
help_keyword
help_relation
help_topic
host
84
plugin
85
proc
86
procs_priv
unknown's avatar
unknown committed
87
servers
88
slow_log
89 90 91 92 93 94 95
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
96 97
t1
t4
98 99
t2
t3
100
t5
101 102
v1
select c,table_name from v1 
103 104 105 106 107
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
108
TABLE_PRIVILEGES	TABLE_PRIVILEGES
109
TRIGGERS	TRIGGERS
110 111 112 113 114 115 116 117 118 119
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
120
t5	t5
121
select c,table_name from v1 
122 123 124
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
125 126
TABLES	TABLES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
127
TABLE_PRIVILEGES	TABLE_PRIVILEGES
128
TRIGGERS	TRIGGERS
129 130 131 132 133 134 135 136
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
137 138
t2	t2
t3	t3
139
t5	t5
140 141 142 143
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
144 145
TABLES	TABLES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
146
TABLE_PRIVILEGES	TABLE_PRIVILEGES
147
TRIGGERS	TRIGGERS
148 149 150 151 152 153 154 155
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
156 157
t2	t2
t3	t3
158
t5	t5
159
select table_name from information_schema.TABLES
160
where table_schema = "mysqltest" and table_name like "t%";
161 162 163
table_name
t1
t4
164
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
165
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
166
NULL	mysqltest	t1	1	mysqltest	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
167
show keys from t3 where Key_name = "a_data";
168 169 170 171 172 173
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
174
t5
175 176
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
177 178
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		
179
t5	MyISAM	10	Fixed	1	7	7	#	2048	0	11	#	#	NULL	latin1_swedish_ci	NULL		
180
v1	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	VIEW
181 182 183 184 185
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
186
Insert_priv	enum('N','Y')	utf8_general_ci	NO		N		select,insert,update,references	
187 188
show full columns from v1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
189
c	varchar(64)	utf8_general_ci	NO				select,insert,update,references	
190 191
select * from information_schema.COLUMNS where table_name="t1"
and column_name= "a";
192
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
unknown's avatar
unknown committed
193
NULL	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	int(11)			select,insert,update,references	
194 195 196
show columns from mysqltest.t1 where field like "%a%";
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
197
create view mysqltest.v1 (c) as select a from mysqltest.t1;
198
grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
199
grant select on mysqltest.v1 to mysqltest_3;
200 201 202 203 204
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;
205 206
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
207 208 209 210
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
211 212
explain select * from v1;
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
213
drop view v1, mysqltest.v1;
214
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
215
drop database mysqltest;
216 217
select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';
218
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
unknown's avatar
unknown committed
219
latin1	latin1_swedish_ci	cp1252 West European	1
220 221
SHOW CHARACTER SET LIKE 'latin1%';
Charset	Description	Default collation	Maxlen
unknown's avatar
unknown committed
222
latin1	cp1252 West European	latin1_swedish_ci	1
223
SHOW CHARACTER SET WHERE charset like 'latin1%';
224
Charset	Description	Default collation	Maxlen
unknown's avatar
unknown committed
225
latin1	cp1252 West European	latin1_swedish_ci	1
226 227
select * from information_schema.COLLATIONS
where COLLATION_NAME like 'latin1%';
228
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN
229 230 231 232 233 234 235 236
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
237 238
SHOW COLLATION LIKE 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
239 240 241 242 243 244 245 246
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
247
SHOW COLLATION WHERE collation like 'latin1%';
248
Collation	Charset	Id	Default	Compiled	Sortlen
249 250 251 252 253 254 255 256
latin1_german1_ci	latin1	5		#	1
latin1_swedish_ci	latin1	8	Yes	#	1
latin1_danish_ci	latin1	15		#	1
latin1_german2_ci	latin1	31		#	2
latin1_bin	latin1	47		#	1
latin1_general_ci	latin1	48		#	1
latin1_general_cs	latin1	49		#	1
latin1_spanish_ci	latin1	94		#	1
257 258 259 260 261 262 263 264 265 266 267
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
unknown's avatar
unknown committed
268 269 270
drop procedure if exists sel2;
drop function if exists sub1;
drop function if exists sub2;
271 272 273 274 275 276 277
create function sub1(i int) returns int
return i+1;
create procedure sel2()
begin
select * from t1;
select * from t2;
end|
278
select parameter_style, sql_data_access, dtd_identifier 
279
from information_schema.routines where routine_schema='test';
280 281
parameter_style	sql_data_access	dtd_identifier
SQL	CONTAINS SQL	NULL
unknown's avatar
unknown committed
282
SQL	CONTAINS SQL	int(11)
283
show procedure status where db='test';
unknown's avatar
unknown committed
284 285
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sel2	PROCEDURE	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
286
show function status where db='test';
unknown's avatar
unknown committed
287 288
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sub1	FUNCTION	root@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
289 290
select a.ROUTINE_NAME from information_schema.ROUTINES a,
information_schema.SCHEMATA b where
291
a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test';
292 293 294 295 296 297 298
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
299 300
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Using join buffer
301
select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
302
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1;
303 304
ROUTINE_NAME	name
sel2	sel2
unknown's avatar
unknown committed
305
sub1	sub1
306
select count(*) from information_schema.ROUTINES where routine_schema='test';
307 308
count(*)
2
309
create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test'
310 311 312 313 314 315
order by routine_schema, routine_name;
select * from v1;
routine_schema	routine_name
test	sel2
test	sub1
drop view v1;
316 317 318 319 320 321
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
322 323
sel2	NULL
sub1	NULL
324 325 326
grant all privileges on test.* to mysqltest_1@localhost;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
327 328
sel2	NULL
sub1	NULL
329 330 331 332
create function sub2(i int) returns int
return i+1;
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
333 334
sel2	NULL
sub1	NULL
335 336
sub2	return i+1
show create procedure sel2;
unknown's avatar
unknown committed
337 338
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
sel2		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
339
show create function sub1;
unknown's avatar
unknown committed
340 341
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
sub1		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
342
show create function sub2;
unknown's avatar
unknown committed
343
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
344
sub2		CREATE DEFINER=`mysqltest_1`@`localhost` FUNCTION `sub2`(i int) RETURNS int(11)
unknown's avatar
unknown committed
345
return i+1	latin1	latin1_swedish_ci	latin1_swedish_ci
346
show function status like "sub2";
unknown's avatar
unknown committed
347 348
Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
test	sub2	FUNCTION	mysqltest_1@localhost	#	#	DEFINER		latin1	latin1_swedish_ci	latin1_swedish_ci
349 350
drop function sub2;
show create procedure sel2;
unknown's avatar
unknown committed
351
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
352
sel2		CREATE DEFINER=`root`@`localhost` PROCEDURE `sel2`()
353 354 355
begin
select * from t1;
select * from t2;
unknown's avatar
unknown committed
356
end	latin1	latin1_swedish_ci	latin1_swedish_ci
unknown's avatar
unknown committed
357
create view v0 (c) as select schema_name from information_schema.schemata;
358 359
select * from v0;
c
360
information_schema
unknown's avatar
unknown committed
361
mtr
362 363 364 365
mysql
test
explain select * from v0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
366
1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	NULL	
unknown's avatar
unknown committed
367
create view v1 (c) as select table_name from information_schema.tables
368 369 370 371
where table_name="v1";
select * from v1;
c
v1
unknown's avatar
unknown committed
372
create view v2 (c) as select column_name from information_schema.columns
373 374 375 376
where table_name="v2";
select * from v2;
c
c
unknown's avatar
unknown committed
377
create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
378 379 380 381
where CHARACTER_SET_NAME like "latin1%";
select * from v3;
c
latin1
unknown's avatar
unknown committed
382
create view v4 (c) as select COLLATION_NAME from information_schema.collations
383 384 385 386 387 388 389 390 391 392 393 394 395
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
unknown's avatar
unknown committed
396
select * from information_schema.views where TABLE_NAME like "v%";
unknown's avatar
unknown committed
397
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION
398 399 400 401 402
NULL	test	v0	select `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata`	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci
NULL	test	v1	select `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = 'v1')	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci
NULL	test	v2	select `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = 'v2')	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci
NULL	test	v3	select `character_sets`.`CHARACTER_SET_NAME` AS `c` from `information_schema`.`character_sets` where (`character_sets`.`CHARACTER_SET_NAME` like 'latin1%')	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci
NULL	test	v4	select `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like 'latin1%')	NONE	NO	root@localhost	DEFINER	latin1	latin1_swedish_ci
403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423
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
424
'mysqltest_1'@'localhost'	NULL	test	EXECUTE	YES
425 426
'mysqltest_1'@'localhost'	NULL	test	CREATE VIEW	YES
'mysqltest_1'@'localhost'	NULL	test	SHOW VIEW	YES
427 428
'mysqltest_1'@'localhost'	NULL	test	CREATE ROUTINE	YES
'mysqltest_1'@'localhost'	NULL	test	ALTER ROUTINE	YES
429
'mysqltest_1'@'localhost'	NULL	test	EVENT	YES
430
'mysqltest_1'@'localhost'	NULL	test	TRIGGER	YES
431 432 433 434 435 436 437 438 439 440 441
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
442 443 444 445
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%';
446 447 448 449 450 451 452 453 454
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` (
455
  `a` int(11) NOT NULL DEFAULT '0',
456
  PRIMARY KEY (`a`),
457 458 459 460 461 462
  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";
463 464 465 466 467
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
468 469
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA= "test";
470 471 472 473 474
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
475 476 477 478 479
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
480
t1	a
481 482 483 484 485 486 487 488
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;
unknown's avatar
unknown committed
489 490 491 492 493
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;
unknown's avatar
unknown committed
494
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION
495 496 497
NULL	test	v1	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci
NULL	test	v2	select `test`.`t1`.`a` AS `c` from `test`.`t1`	LOCAL	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci
NULL	test	v3	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci
unknown's avatar
unknown committed
498 499 500 501 502 503 504 505 506 507 508 509 510
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;
511 512 513 514 515 516
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;
unknown's avatar
unknown committed
517 518 519 520 521 522
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
523
10
unknown's avatar
unknown committed
524
drop table t1;
unknown's avatar
unknown committed
525
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
unknown's avatar
unknown committed
526
Table	Create Table
527
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
528 529 530 531
  `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'
532
) ENGINE=MEMORY DEFAULT CHARSET=utf8
unknown's avatar
unknown committed
533
set names latin2;
unknown's avatar
unknown committed
534
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
unknown's avatar
unknown committed
535
Table	Create Table
536
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
537 538 539 540
  `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'
541
) ENGINE=MEMORY DEFAULT CHARSET=utf8
unknown's avatar
unknown committed
542 543 544 545
set names latin1;
create table t1 select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like "latin1";
select * from t1;
546
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
unknown's avatar
unknown committed
547
latin1	latin1_swedish_ci	cp1252 West European	1
unknown's avatar
unknown committed
548 549 550 551
alter table t1 default character set utf8;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
552 553 554 555
  `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'
unknown's avatar
unknown committed
556 557
) ENGINE=MyISAM DEFAULT CHARSET=utf8
drop table t1;
558 559 560 561 562 563 564 565 566
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
567 568 569 570 571 572
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
unknown's avatar
unknown committed
573
g	int(11)	NULL	NULL	10	0
574 575
h	double(10,3)	NULL	NULL	10	3
i	double	NULL	NULL	22	NULL
576
drop table t1;
unknown's avatar
unknown committed
577 578 579 580
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
581 582
proc	db	char(64)
proc	name	char(64)
unknown's avatar
unknown committed
583
proc	type	enum('FUNCTION','PROCEDURE')
584
proc	specific_name	char(64)
unknown's avatar
unknown committed
585 586 587 588 589
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
unknown's avatar
unknown committed
590
proc	returns	longblob
unknown's avatar
unknown committed
591
proc	body	longblob
592
proc	definer	char(77)
unknown's avatar
unknown committed
593 594
proc	created	timestamp
proc	modified	timestamp
595
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','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
596
proc	comment	char(64)
unknown's avatar
unknown committed
597 598 599 600
proc	character_set_client	char(32)
proc	collation_connection	char(32)
proc	db_collation	char(32)
proc	body_utf8	longblob
unknown's avatar
unknown committed
601 602 603 604 605 606
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;
unknown's avatar
unknown committed
607 608 609 610 611 612 613 614 615 616 617
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";
618
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
unknown's avatar
unknown committed
619 620
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_NAME= "vo";
621
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
unknown's avatar
unknown committed
622
drop view vo;
623 624 625 626
select TABLE_NAME,TABLE_TYPE,ENGINE
from information_schema.tables 
where table_schema='information_schema' limit 2;
TABLE_NAME	TABLE_TYPE	ENGINE
627 628
CHARACTER_SETS	SYSTEM VIEW	MEMORY
COLLATIONS	SYSTEM VIEW	MEMORY
629 630 631 632
show tables from information_schema like "T%";
Tables_in_information_schema (T%)
TABLES
TABLE_CONSTRAINTS
633
TABLE_PRIVILEGES
634
TRIGGERS
635
create database information_schema;
636
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
637 638 639
use information_schema;
show full tables like "T%";
Tables_in_information_schema (T%)	Table_type
640 641
TABLES	SYSTEM VIEW
TABLE_CONSTRAINTS	SYSTEM VIEW
642
TABLE_PRIVILEGES	SYSTEM VIEW
643
TRIGGERS	SYSTEM VIEW
644 645 646 647 648 649 650 651 652 653
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
654
TABLE_PRIVILEGES
655
TRIGGERS
656 657 658 659 660 661 662 663 664 665 666 667 668 669 670
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
671 672
Create_routine_priv	select,insert,update,references
Alter_routine_priv	select,insert,update,references
673 674
max_questions	select,insert,update,references
max_connections	select,insert,update,references
unknown's avatar
unknown committed
675
max_user_connections	select,insert,update,references
676 677 678 679
use test;
create function sub1(i int) returns int
return i+1;
create table t1(f1 int);
unknown's avatar
unknown committed
680 681
create view v2 (c) as select f1 from t1;
create view v3 (c) as select sub1(1);
682 683 684
create table t4(f1 int, KEY f1_key (f1));
drop table t1;
drop function sub1;
685 686 687
select table_name from information_schema.views
where table_schema='test';
table_name
688 689
v2
v3
690 691 692
select table_name from information_schema.views
where table_schema='test';
table_name
693 694
v2
v3
695 696 697 698 699
select column_name from information_schema.columns
where table_schema='test';
column_name
f1
Warnings:
700 701
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
702 703 704 705 706 707
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
708
show create view v2;
unknown's avatar
unknown committed
709 710
View	Create View	character_set_client	collation_connection
v2	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `test`.`t1`.`f1` AS `c` from `t1`	latin1	latin1_swedish_ci
711 712 713
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;
unknown's avatar
unknown committed
714 715
View	Create View	character_set_client	collation_connection
v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `sub1`(1) AS `c`	latin1	latin1_swedish_ci
716 717
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
unknown's avatar
unknown committed
718 719
drop view v2;
drop view v3;
720
drop table t4;
721 722
select * from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
723 724 725 726 727 728
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)
729 730 731 732 733 734 735 736
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
737 738 739
show open tables where `table` like "user";
Database	Table	In_use	Name_locked
mysql	user	0	0
740 741
show status where variable_name like "%database%";
Variable_name	Value
742
Com_show_databases	3
743 744
show variables where variable_name like "skip_show_databas";
Variable_name	Value
745 746
show global status like "Threads_running";
Variable_name	Value
747
Threads_running	#
748 749 750 751 752 753
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;
754
use test;
755 756 757
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;
758
count(*)
759
68
760 761
drop view a2, a1;
drop table t_crashme;
762 763 764 765
select table_schema,table_name, column_name from
information_schema.columns 
where data_type = 'longtext';
table_schema	table_name	column_name
unknown's avatar
unknown committed
766
information_schema	COLUMNS	COLUMN_DEFAULT
767
information_schema	COLUMNS	COLUMN_TYPE
768
information_schema	EVENTS	EVENT_DEFINITION
769 770 771
information_schema	PARTITIONS	PARTITION_EXPRESSION
information_schema	PARTITIONS	SUBPARTITION_EXPRESSION
information_schema	PARTITIONS	PARTITION_DESCRIPTION
unknown's avatar
unknown committed
772
information_schema	PLUGINS	PLUGIN_DESCRIPTION
773
information_schema	PROCESSLIST	INFO
774 775
information_schema	ROUTINES	ROUTINE_DEFINITION
information_schema	ROUTINES	SQL_MODE
776 777
information_schema	TRIGGERS	ACTION_CONDITION
information_schema	TRIGGERS	ACTION_STATEMENT
778
information_schema	VIEWS	VIEW_DEFINITION
779 780 781
select table_name, column_name, data_type from information_schema.columns
where data_type = 'datetime';
table_name	column_name	data_type
782 783 784 785 786 787
EVENTS	EXECUTE_AT	datetime
EVENTS	STARTS	datetime
EVENTS	ENDS	datetime
EVENTS	CREATED	datetime
EVENTS	LAST_ALTERED	datetime
EVENTS	LAST_EXECUTED	datetime
unknown's avatar
unknown committed
788 789 790 791 792 793
FILES	CREATION_TIME	datetime
FILES	LAST_UPDATE_TIME	datetime
FILES	LAST_ACCESS_TIME	datetime
FILES	CREATE_TIME	datetime
FILES	UPDATE_TIME	datetime
FILES	CHECK_TIME	datetime
794 795 796
PARTITIONS	CREATE_TIME	datetime
PARTITIONS	UPDATE_TIME	datetime
PARTITIONS	CHECK_TIME	datetime
797 798
ROUTINES	CREATED	datetime
ROUTINES	LAST_ALTERED	datetime
799 800 801
TABLES	CREATE_TIME	datetime
TABLES	UPDATE_TIME	datetime
TABLES	CHECK_TIME	datetime
802
TRIGGERS	CREATED	datetime
803 804 805 806
event	execute_at	datetime
event	last_executed	datetime
event	starts	datetime
event	ends	datetime
807 808 809 810 811 812 813
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
814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835
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;
836
grant select on test.* to mysqltest_4@localhost;
837 838 839 840 841
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
unknown's avatar
unknown committed
842
FILES	TABLE_NAME	select
843
KEY_COLUMN_USAGE	TABLE_NAME	select
844
PARTITIONS	TABLE_NAME	select
845
REFERENTIAL_CONSTRAINTS	TABLE_NAME	select
846 847 848 849 850
STATISTICS	TABLE_NAME	select
TABLES	TABLE_NAME	select
TABLE_CONSTRAINTS	TABLE_NAME	select
TABLE_PRIVILEGES	TABLE_NAME	select
VIEWS	TABLE_NAME	select
851
delete from mysql.user where user='mysqltest_4';
852
delete from mysql.db where user='mysqltest_4';
853
flush privileges;
854
SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA;
unknown's avatar
unknown committed
855
table_schema	count(*)
856
information_schema	28
unknown's avatar
unknown committed
857
mysql	22
858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877
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;
unknown's avatar
unknown committed
878
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
879
trg1	INSERT	t1	begin
880 881 882
if new.j > 10 then
set new.j := 10;
end if;
unknown's avatar
unknown committed
883
end	BEFORE	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
884
trg2	UPDATE	t1	begin
885 886 887
if old.i % 2 = 0 then
set new.j := -1;
end if;
unknown's avatar
unknown committed
888
end	BEFORE	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
889
trg3	UPDATE	t1	begin
890 891 892
if new.j = -1 then
set @fired:= "Yes";
end if;
unknown's avatar
unknown committed
893
end	AFTER	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
894
select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest');
unknown's avatar
unknown committed
895
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	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
896
NULL	test	trg1	INSERT	NULL	test	t1	0	NULL	begin
897 898 899
if new.j > 10 then
set new.j := 10;
end if;
unknown's avatar
unknown committed
900
end	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
901
NULL	test	trg2	UPDATE	NULL	test	t1	0	NULL	begin
902 903 904
if old.i % 2 = 0 then
set new.j := -1;
end if;
unknown's avatar
unknown committed
905
end	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
906
NULL	test	trg3	UPDATE	NULL	test	t1	0	NULL	begin
907 908 909
if new.j = -1 then
set @fired:= "Yes";
end if;
unknown's avatar
unknown committed
910
end	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
911 912 913 914
drop trigger trg1;
drop trigger trg2;
drop trigger trg3;
drop table t1;
915 916 917 918 919 920 921
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;
922
select * from information_schema.column_privileges order by grantee;
923 924
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	NULL	mysqltest	t1	f1	SELECT	NO
925
select * from information_schema.table_privileges order by grantee;
926
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
927
select * from information_schema.schema_privileges order by grantee;
928
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
929
select * from information_schema.user_privileges order by grantee;
930 931 932 933 934 935
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'
936
select * from information_schema.column_privileges order by grantee;
937
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
938
select * from information_schema.table_privileges order by grantee;
939 940
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	NULL	mysqltest	t2	SELECT	NO
941
select * from information_schema.schema_privileges order by grantee;
942
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
943
select * from information_schema.user_privileges order by grantee;
944 945 946 947 948 949
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'
950
select * from information_schema.column_privileges order by grantee;
951
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
952
select * from information_schema.table_privileges order by grantee;
953
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
954
select * from information_schema.schema_privileges order by grantee;
955 956
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	NULL	mysqltest	SELECT	NO
957
select * from information_schema.user_privileges order by grantee;
958 959 960 961 962 963
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'
964 965
select * from information_schema.column_privileges where grantee like '%user%'
order by grantee;
966 967
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user1'@'localhost'	NULL	mysqltest	t1	f1	SELECT	NO
968 969
select * from information_schema.table_privileges where grantee like '%user%'
order by grantee;
970 971
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'user2'@'localhost'	NULL	mysqltest	t2	SELECT	NO
972 973
select * from information_schema.schema_privileges where grantee like '%user%'
order by grantee;
974 975
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'user3'@'localhost'	NULL	mysqltest	SELECT	NO
976 977
select * from information_schema.user_privileges where grantee like '%user%'
order by grantee;
978 979 980 981 982 983 984 985 986 987 988
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;
unknown's avatar
unknown committed
989 990
drop procedure if exists p1;
drop procedure if exists p2;
991 992 993 994 995 996 997 998 999
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;
1000 1001 1002
show create database information_schema;
Database	Create Database
information_schema	CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017
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
unknown's avatar
unknown committed
1018 1019 1020 1021 1022
f1	3	0
f2	5	0
f3	7	0
f4	10	0
f5	19	0
1023 1024 1025
f6	1	NULL
f7	64	NULL
drop table t1;
1026 1027 1028 1029 1030 1031 1032 1033 1034
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;
unknown's avatar
unknown committed
1035 1036 1037 1038 1039 1040 1041 1042
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;
1043 1044
show columns from t1;
Field	Type	Null	Key	Default	Extra
1045
a	int(11)	NO		NULL	
1046
b	int(11)	YES		NULL	
unknown's avatar
unknown committed
1047
drop table t1;
unknown's avatar
unknown committed
1048 1049 1050 1051 1052 1053 1054 1055
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		
1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066
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;
1067
alter database information_schema;
1068
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081
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 |
1082
ERROR 42000: Unknown database 'information_schema'
1083
select  ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
1084 1085 1086 1087 1088
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'
1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100
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;
1101 1102 1103 1104 1105 1106 1107 1108
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;
1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119
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;
1120 1121 1122 1123 1124 1125 1126
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;
1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142
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;
1143 1144 1145
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
from information_schema.columns where
table_schema='information_schema' and
1146 1147
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
 or column_type = 'varchar(27)')
1148 1149
group by column_type order by num;
column_type	group_concat(table_schema, '.', table_name)	num
1150
varchar(27)	information_schema.COLUMNS	1
1151
varchar(7)	information_schema.ROUTINES,information_schema.VIEWS	2
unknown's avatar
unknown committed
1152
varchar(20)	information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PROFILING	6
1153 1154 1155 1156 1157 1158 1159 1160
create table t1(f1 char(1) not null, f2 char(9) not null)
default character set utf8;
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
information_schema.columns where table_schema='test' and table_name = 't1';
CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH
1	3
9	27
drop table t1;
1161 1162 1163
use mysql;
INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL',
'NO','DEFINER','','','BEGIN\r\n  \r\nEND','root@%','2006-03-02 18:40:03',
unknown's avatar
unknown committed
1164
'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a');
1165
select routine_name from information_schema.routines where ROUTINE_SCHEMA='test';
1166 1167 1168 1169
routine_name

delete from proc where name='';
use test;
1170 1171 1172 1173 1174 1175 1176
grant select on test.* to mysqltest_1@localhost;
create table t1 (id int);
create view v1 as select * from t1;
create definer = mysqltest_1@localhost
sql security definer view v2 as select 1;
select * from information_schema.views
where table_name='v1' or table_name='v2';
unknown's avatar
unknown committed
1177 1178
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION
NULL	test	v1		NONE	YES	root@localhost	DEFINER	latin1	latin1_swedish_ci
1179
NULL	test	v2	select 1 AS `1`	NONE	NO	mysqltest_1@localhost	DEFINER	latin1	latin1_swedish_ci
1180 1181 1182
drop view v1, v2;
drop table t1;
drop user mysqltest_1@localhost;
1183 1184 1185 1186 1187 1188 1189 1190 1191
set @a:= '.';
create table t1(f1 char(5));
create table t2(f1 char(5));
select concat(@a, table_name), @a, table_name
from information_schema.tables where table_schema = 'test';
concat(@a, table_name)	@a	table_name
.t1	.	t1
.t2	.	t2
drop table t1,t2;
1192 1193 1194 1195 1196 1197 1198
DROP PROCEDURE IF EXISTS p1;
DROP FUNCTION IF EXISTS f1;
CREATE PROCEDURE p1() SET @a= 1;
CREATE FUNCTION f1() RETURNS INT RETURN @a + 1;
CREATE USER mysql_bug20230@localhost;
GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;
1199
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
1200 1201 1202 1203
ROUTINE_NAME	ROUTINE_DEFINITION
f1	RETURN @a + 1
p1	SET @a= 1
SHOW CREATE PROCEDURE p1;
unknown's avatar
unknown committed
1204
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
1205
p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
unknown's avatar
unknown committed
1206
SET @a= 1	latin1	latin1_swedish_ci	latin1_swedish_ci
1207
SHOW CREATE FUNCTION f1;
unknown's avatar
unknown committed
1208
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
1209
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
unknown's avatar
unknown committed
1210
RETURN @a + 1	latin1	latin1_swedish_ci	latin1_swedish_ci
1211
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
1212 1213 1214 1215
ROUTINE_NAME	ROUTINE_DEFINITION
f1	NULL
p1	NULL
SHOW CREATE PROCEDURE p1;
unknown's avatar
unknown committed
1216 1217
Procedure	sql_mode	Create Procedure	character_set_client	collation_connection	Database Collation
p1		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
1218
SHOW CREATE FUNCTION f1;
unknown's avatar
unknown committed
1219 1220
Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
f1		NULL	latin1	latin1_swedish_ci	latin1_swedish_ci
1221 1222 1223 1224 1225 1226 1227
CALL p1();
SELECT f1();
f1()
2
DROP FUNCTION f1;
DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;
1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
INNER JOIN
information_schema.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'information_schema' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM information_schema.columns c2
WHERE c2.table_schema = t.table_schema AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
        );
table_name	column_name
CHARACTER_SETS	CHARACTER_SET_NAME
COLLATIONS	COLLATION_NAME
COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
COLUMNS	TABLE_SCHEMA
COLUMN_PRIVILEGES	TABLE_SCHEMA
unknown's avatar
unknown committed
1248 1249 1250
ENGINES	ENGINE
EVENTS	EVENT_SCHEMA
FILES	TABLE_SCHEMA
1251 1252
GLOBAL_STATUS	VARIABLE_NAME
GLOBAL_VARIABLES	VARIABLE_NAME
1253
KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
unknown's avatar
unknown committed
1254 1255 1256
PARTITIONS	TABLE_SCHEMA
PLUGINS	PLUGIN_NAME
PROCESSLIST	ID
1257
PROFILING	QUERY_ID
unknown's avatar
unknown committed
1258
REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA
1259 1260 1261
ROUTINES	ROUTINE_SCHEMA
SCHEMATA	SCHEMA_NAME
SCHEMA_PRIVILEGES	TABLE_SCHEMA
1262 1263
SESSION_STATUS	VARIABLE_NAME
SESSION_VARIABLES	VARIABLE_NAME
1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290
STATISTICS	TABLE_SCHEMA
TABLES	TABLE_SCHEMA
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_PRIVILEGES	TABLE_SCHEMA
TRIGGERS	TRIGGER_SCHEMA
USER_PRIVILEGES	GRANTEE
VIEWS	TABLE_SCHEMA
SELECT t.table_name, c1.column_name
FROM information_schema.tables t
INNER JOIN
information_schema.columns c1
ON t.table_schema = c1.table_schema AND
t.table_name = c1.table_name
WHERE t.table_schema = 'information_schema' AND
c1.ordinal_position =
( SELECT COALESCE(MIN(c2.ordinal_position),1)
FROM information_schema.columns c2
WHERE c2.table_schema = 'information_schema' AND
c2.table_name = t.table_name AND
c2.column_name LIKE '%SCHEMA%'
        );
table_name	column_name
CHARACTER_SETS	CHARACTER_SET_NAME
COLLATIONS	COLLATION_NAME
COLLATION_CHARACTER_SET_APPLICABILITY	COLLATION_NAME
COLUMNS	TABLE_SCHEMA
COLUMN_PRIVILEGES	TABLE_SCHEMA
unknown's avatar
unknown committed
1291 1292 1293
ENGINES	ENGINE
EVENTS	EVENT_SCHEMA
FILES	TABLE_SCHEMA
1294 1295
GLOBAL_STATUS	VARIABLE_NAME
GLOBAL_VARIABLES	VARIABLE_NAME
1296
KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
unknown's avatar
unknown committed
1297 1298 1299
PARTITIONS	TABLE_SCHEMA
PLUGINS	PLUGIN_NAME
PROCESSLIST	ID
1300
PROFILING	QUERY_ID
unknown's avatar
unknown committed
1301
REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA
1302 1303 1304
ROUTINES	ROUTINE_SCHEMA
SCHEMATA	SCHEMA_NAME
SCHEMA_PRIVILEGES	TABLE_SCHEMA
1305 1306
SESSION_STATUS	VARIABLE_NAME
SESSION_VARIABLES	VARIABLE_NAME
1307 1308 1309 1310 1311 1312 1313
STATISTICS	TABLE_SCHEMA
TABLES	TABLE_SCHEMA
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_PRIVILEGES	TABLE_SCHEMA
TRIGGERS	TRIGGER_SCHEMA
USER_PRIVILEGES	GRANTEE
VIEWS	TABLE_SCHEMA
1314
SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test');
1315 1316 1317 1318
MAX(table_name)
VIEWS
SELECT table_name from information_schema.tables
WHERE table_name=(SELECT MAX(table_name)
1319
FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'));
1320 1321
table_name
VIEWS
1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334
DROP TABLE IF EXISTS bug23037;
DROP FUNCTION IF EXISTS get_value;
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)
fld1	7cf7a6782be951a1f2464a350da926a5	65532
SELECT MD5(get_value());
MD5(get_value())
7cf7a6782be951a1f2464a350da926a5
SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
COLUMN_NAME	MD5(COLUMN_DEFAULT)	LENGTH(COLUMN_DEFAULT)	COLUMN_DEFAULT=get_value()
fld1	7cf7a6782be951a1f2464a350da926a5	65532	1
DROP TABLE bug23037;
DROP FUNCTION get_value;
1335 1336 1337 1338 1339 1340 1341 1342
create view v1 as
select table_schema as object_schema,
table_name   as object_name,
table_type   as object_type
from information_schema.tables
order by object_schema;
explain select * from v1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1343
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases; Using filesort
1344 1345 1346
explain select * from (select table_name from information_schema.tables) as a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
1347
2	DERIVED	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
1348
drop view v1;
1349 1350 1351 1352 1353 1354 1355 1356 1357 1358
create table t1 (f1 int(11));
create table t2 (f1 int(11), f2 int(11));
select table_name from information_schema.tables
where table_schema = 'test' and table_name not in
(select table_name from information_schema.columns
where table_schema = 'test' and column_name = 'f3');
table_name
t1
t2
drop table t1,t2;
1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384
select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
(select cast(table_name as char)  from information_schema.tables
order by table_name limit 1) limit 1;
f1
1
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
count(*) as num1
from information_schema.tables t
inner join information_schema.columns c1
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
where t.table_schema = 'information_schema' and
c1.ordinal_position =
(select isnull(c2.column_type) -
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
count(*) as num
from information_schema.columns c2 where
c2.table_schema='information_schema' and
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
group by c2.column_type order by num limit 1)
group by t.table_name order by num1, t.table_name;
table_name	group_concat(t.table_schema, '.', t.table_name)	num1
CHARACTER_SETS	information_schema.CHARACTER_SETS	1
COLLATIONS	information_schema.COLLATIONS	1
COLLATION_CHARACTER_SET_APPLICABILITY	information_schema.COLLATION_CHARACTER_SET_APPLICABILITY	1
COLUMNS	information_schema.COLUMNS	1
COLUMN_PRIVILEGES	information_schema.COLUMN_PRIVILEGES	1
unknown's avatar
unknown committed
1385 1386 1387 1388 1389
ENGINES	information_schema.ENGINES	1
EVENTS	information_schema.EVENTS	1
FILES	information_schema.FILES	1
GLOBAL_STATUS	information_schema.GLOBAL_STATUS	1
GLOBAL_VARIABLES	information_schema.GLOBAL_VARIABLES	1
1390
KEY_COLUMN_USAGE	information_schema.KEY_COLUMN_USAGE	1
unknown's avatar
unknown committed
1391 1392 1393
PARTITIONS	information_schema.PARTITIONS	1
PLUGINS	information_schema.PLUGINS	1
PROCESSLIST	information_schema.PROCESSLIST	1
1394
PROFILING	information_schema.PROFILING	1
unknown's avatar
unknown committed
1395
REFERENTIAL_CONSTRAINTS	information_schema.REFERENTIAL_CONSTRAINTS	1
1396 1397 1398
ROUTINES	information_schema.ROUTINES	1
SCHEMATA	information_schema.SCHEMATA	1
SCHEMA_PRIVILEGES	information_schema.SCHEMA_PRIVILEGES	1
unknown's avatar
unknown committed
1399 1400
SESSION_STATUS	information_schema.SESSION_STATUS	1
SESSION_VARIABLES	information_schema.SESSION_VARIABLES	1
1401 1402 1403 1404 1405 1406 1407
STATISTICS	information_schema.STATISTICS	1
TABLES	information_schema.TABLES	1
TABLE_CONSTRAINTS	information_schema.TABLE_CONSTRAINTS	1
TABLE_PRIVILEGES	information_schema.TABLE_PRIVILEGES	1
TRIGGERS	information_schema.TRIGGERS	1
USER_PRIVILEGES	information_schema.USER_PRIVILEGES	1
VIEWS	information_schema.VIEWS	1
1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418
create table t1(f1 int);
create view v1 as select f1+1 as a from t1;
create table t2 (f1 int, f2 int);
create view v2 as select f1+1 as a, f2 as b from t2;
select table_name, is_updatable from information_schema.views;
table_name	is_updatable
v1	NO
v2	YES
delete from v1;
drop view v1,v2;
drop table t1,t2;
1419 1420 1421 1422
alter database;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
alter database test;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
1423 1424 1425 1426 1427 1428 1429 1430 1431 1432
create database mysqltest;
create table mysqltest.t1(a int, b int, c int);
create trigger mysqltest.t1_ai after insert on mysqltest.t1
for each row set @a = new.a + new.b + new.c;
grant select(b) on mysqltest.t1 to mysqltest_1@localhost;
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
t1_ai
show triggers from mysqltest;
1433 1434
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
t1_ai	INSERT	t1	set @a = new.a + new.b + new.c	AFTER	NULL		root@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
1435 1436 1437 1438 1439 1440 1441
show columns from t1;
Field	Type	Null	Key	Default	Extra
b	int(11)	YES		NULL	
select column_name from information_schema.columns where table_name='t1';
column_name
b
show triggers;
1442
Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
1443 1444 1445 1446 1447
select trigger_name from information_schema.triggers
where event_object_table='t1';
trigger_name
drop user mysqltest_1@localhost;
drop database mysqltest;
1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478
create table t1 (
f1 varchar(50),
f2 varchar(50) not null,
f3 varchar(50) default '',
f4 varchar(50) default NULL,
f5 bigint not null,
f6 bigint not null default 10,
f7 datetime not null,
f8 datetime default '2006-01-01'
);
select column_default from information_schema.columns where table_name= 't1';
column_default
NULL
NULL

NULL
NULL
10
NULL
2006-01-01 00:00:00
show columns from t1;
Field	Type	Null	Key	Default	Extra
f1	varchar(50)	YES		NULL	
f2	varchar(50)	NO		NULL	
f3	varchar(50)	YES			
f4	varchar(50)	YES		NULL	
f5	bigint(20)	NO		NULL	
f6	bigint(20)	NO		10	
f7	datetime	NO		NULL	
f8	datetime	YES		2006-01-01 00:00:00	
drop table t1;
unknown's avatar
unknown committed
1479 1480 1481 1482
show fields from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
show keys from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494
USE information_schema;
SET max_heap_table_size = 16384;
CREATE TABLE test.t1( a INT );
SELECT *
FROM tables ta
JOIN collations co ON ( co.collation_name = ta.table_catalog )
JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog );
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT	COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN	CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
DROP TABLE test.t1;
SET max_heap_table_size = DEFAULT;
USE test;
End of 5.0 tests.
unknown's avatar
unknown committed
1495 1496
select * from information_schema.engines WHERE ENGINE="MyISAM";
ENGINE	SUPPORT	COMMENT	TRANSACTIONS	XA	SAVEPOINTS
unknown's avatar
unknown committed
1497
MyISAM	DEFAULT	Default engine as of MySQL 3.23 with great performance	NO	NO	NO
unknown's avatar
unknown committed
1498 1499 1500 1501 1502
grant select on *.* to user3148@localhost;
select user,db from information_schema.processlist;
user	db
user3148	test
drop user user3148@localhost;
1503 1504 1505 1506
DROP TABLE IF EXISTS server_status;
DROP EVENT IF EXISTS event_status;
SET GLOBAL event_scheduler=1;
CREATE EVENT event_status
1507
ON SCHEDULE AT NOW()
1508
ON COMPLETION NOT PRESERVE
1509 1510
DO
BEGIN
1511 1512 1513 1514 1515
CREATE TABLE server_status
SELECT variable_name
FROM information_schema.global_status
WHERE variable_name LIKE 'ABORTED_CONNECTS' OR
variable_name LIKE 'BINLOG_CACHE_DISK_USE';
1516
END$$
1517
SELECT variable_name FROM server_status;
1518 1519 1520 1521 1522
variable_name
ABORTED_CONNECTS
BINLOG_CACHE_DISK_USE
DROP TABLE server_status;
SET GLOBAL event_scheduler=0;
1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546
explain select table_name from information_schema.views where
table_schema='test' and table_name='v1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 0 databases
explain select * from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_full_table; Scanned all databases
explain select * from information_schema.collations;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	collations	ALL	NULL	NULL	NULL	NULL	NULL	
explain select * from information_schema.tables where
table_schema='test' and table_name= 't1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 0 databases
explain select table_name, table_type from information_schema.tables
where table_schema='test';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	TABLE_SCHEMA	NULL	NULL	NULL	Using where; Open_frm_only; Scanned 1 database
explain select b.table_name
from information_schema.tables a, information_schema.columns b
where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Skip_open_table; Scanned 0 databases
1	SIMPLE	b	ALL	NULL	NULL	NULL	NULL	NULL	Using where; Open_frm_only; Scanned all databases; Using join buffer
1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mysqltest';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = '';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'test';
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH
NULL	test	latin1	latin1_swedish_ci	NULL
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='';
count(*)
0
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting';
count(*)
0
1569 1570
CREATE VIEW v1
AS SELECT *
1571
FROM information_schema.tables;
1572 1573
SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1';
VIEW_DEFINITION
1574
select `tables`.`TABLE_CATALOG` AS `TABLE_CATALOG`,`tables`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`tables`.`TABLE_NAME` AS `TABLE_NAME`,`tables`.`TABLE_TYPE` AS `TABLE_TYPE`,`tables`.`ENGINE` AS `ENGINE`,`tables`.`VERSION` AS `VERSION`,`tables`.`ROW_FORMAT` AS `ROW_FORMAT`,`tables`.`TABLE_ROWS` AS `TABLE_ROWS`,`tables`.`AVG_ROW_LENGTH` AS `AVG_ROW_LENGTH`,`tables`.`DATA_LENGTH` AS `DATA_LENGTH`,`tables`.`MAX_DATA_LENGTH` AS `MAX_DATA_LENGTH`,`tables`.`INDEX_LENGTH` AS `INDEX_LENGTH`,`tables`.`DATA_FREE` AS `DATA_FREE`,`tables`.`AUTO_INCREMENT` AS `AUTO_INCREMENT`,`tables`.`CREATE_TIME` AS `CREATE_TIME`,`tables`.`UPDATE_TIME` AS `UPDATE_TIME`,`tables`.`CHECK_TIME` AS `CHECK_TIME`,`tables`.`TABLE_COLLATION` AS `TABLE_COLLATION`,`tables`.`CHECKSUM` AS `CHECKSUM`,`tables`.`CREATE_OPTIONS` AS `CREATE_OPTIONS`,`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT` from `information_schema`.`tables`
1575
DROP VIEW v1;
1576 1577 1578 1579
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME ='information_schema';
SCHEMA_NAME
information_schema
1580 1581 1582 1583
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
TABLE_COLLATION
utf8_bin
1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623
select * from information_schema.columns where table_schema = NULL;
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
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
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
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
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
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
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
select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME	PARTITION_ORDINAL_POSITION	SUBPARTITION_ORDINAL_POSITION	PARTITION_METHOD	SUBPARTITION_METHOD	PARTITION_EXPRESSION	SUBPARTITION_EXPRESSION	PARTITION_DESCRIPTION	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	CREATE_TIME	UPDATE_TIME	CHECK_TIME	CHECKSUM	PARTITION_COMMENT	NODEGROUP	TABLESPACE_NAME
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	UNIQUE_CONSTRAINT_CATALOG	UNIQUE_CONSTRAINT_SCHEMA	UNIQUE_CONSTRAINT_NAME	MATCH_OPTION	UPDATE_RULE	DELETE_RULE	TABLE_NAME	REFERENCED_TABLE_NAME
select * from information_schema.schemata where schema_name = NULL;
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	DEFAULT_COLLATION_NAME	SQL_PATH
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
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
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
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
select * from information_schema.tables where table_schema = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
select * from information_schema.tables where table_catalog = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
select * from information_schema.tables where table_name = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
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	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;
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	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION
select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE	CHARACTER_SET_CLIENT	COLLATION_CONNECTION
1624
explain extended select 1 from information_schema.tables;
1625
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1626
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
1627
Warnings:
1628
Note	1003	select 1 AS `1` from `information_schema`.`tables`
1629 1630 1631 1632 1633 1634 1635
use information_schema;
show events;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
show events from information_schema;
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
show events where Db= 'information_schema';
Db	Name	Definer	Time zone	Type	Execute at	Interval value	Interval field	Starts	Ends	Status	Originator	character_set_client	collation_connection	Database Collation
unknown's avatar
unknown committed
1636
use test;
1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651
#
# Bug#34166: Server crash in SHOW OPEN TABLES and prelocking
#
drop table if exists t1;
drop function if exists f1;
create table t1 (a int);
create function f1() returns int
begin
insert into t1 (a) values (1);
return 0;
end|
show open tables where f1()=0;
show open tables where f1()=0;
drop table t1;
drop function f1;
1652 1653
select * from information_schema.tables where 1=sleep(100000);
select * from information_schema.columns where 1=sleep(100000);
1654 1655 1656 1657 1658 1659 1660 1661 1662
explain select count(*) from information_schema.tables;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
explain select count(*) from information_schema.columns;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	columns	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
explain select count(*) from information_schema.views;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	views	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases
1663
End of 5.1 tests.