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

delete from proc where name='';
use test;
1169 1170 1171 1172 1173 1174 1175 1176 1177
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';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE	DEFINER	SECURITY_TYPE
NULL	test	v1		NONE	YES	root@localhost	DEFINER
1178
NULL	test	v2	/* ALGORITHM=UNDEFINED */ select 1 AS `1`	NONE	NO	mysqltest_1@localhost	DEFINER
1179 1180 1181
drop view v1, v2;
drop table t1;
drop user mysqltest_1@localhost;
1182 1183 1184 1185 1186 1187 1188 1189 1190
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;
1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226
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;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
f1	RETURN @a + 1
p1	SET @a= 1
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure
p1		CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a= 1
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11)
RETURN @a + 1
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
ROUTINE_NAME	ROUTINE_DEFINITION
f1	NULL
p1	NULL
SHOW CREATE PROCEDURE p1;
Procedure	sql_mode	Create Procedure
p1		NULL
SHOW CREATE FUNCTION f1;
Function	sql_mode	Create Function
f1		NULL
CALL p1();
SELECT f1();
f1()
2
DROP FUNCTION f1;
DROP PROCEDURE p1;
DROP USER mysql_bug20230@localhost;
1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246
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
evgen@sunlight.local's avatar
evgen@sunlight.local committed
1247 1248 1249
ENGINES	ENGINE
EVENTS	EVENT_SCHEMA
FILES	TABLE_SCHEMA
1250 1251
GLOBAL_STATUS	VARIABLE_NAME
GLOBAL_VARIABLES	VARIABLE_NAME
1252
KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
evgen@sunlight.local's avatar
evgen@sunlight.local committed
1253 1254 1255 1256
PARTITIONS	TABLE_SCHEMA
PLUGINS	PLUGIN_NAME
PROCESSLIST	ID
REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA
1257 1258 1259
ROUTINES	ROUTINE_SCHEMA
SCHEMATA	SCHEMA_NAME
SCHEMA_PRIVILEGES	TABLE_SCHEMA
1260 1261
SESSION_STATUS	VARIABLE_NAME
SESSION_VARIABLES	VARIABLE_NAME
1262 1263 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
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
evgen@sunlight.local's avatar
evgen@sunlight.local committed
1289 1290 1291
ENGINES	ENGINE
EVENTS	EVENT_SCHEMA
FILES	TABLE_SCHEMA
1292 1293
GLOBAL_STATUS	VARIABLE_NAME
GLOBAL_VARIABLES	VARIABLE_NAME
1294
KEY_COLUMN_USAGE	CONSTRAINT_SCHEMA
evgen@sunlight.local's avatar
evgen@sunlight.local committed
1295 1296 1297 1298
PARTITIONS	TABLE_SCHEMA
PLUGINS	PLUGIN_NAME
PROCESSLIST	ID
REFERENTIAL_CONSTRAINTS	CONSTRAINT_SCHEMA
1299 1300 1301
ROUTINES	ROUTINE_SCHEMA
SCHEMATA	SCHEMA_NAME
SCHEMA_PRIVILEGES	TABLE_SCHEMA
1302 1303
SESSION_STATUS	VARIABLE_NAME
SESSION_VARIABLES	VARIABLE_NAME
1304 1305 1306 1307 1308 1309 1310
STATISTICS	TABLE_SCHEMA
TABLES	TABLE_SCHEMA
TABLE_CONSTRAINTS	CONSTRAINT_SCHEMA
TABLE_PRIVILEGES	TABLE_SCHEMA
TRIGGERS	TRIGGER_SCHEMA
USER_PRIVILEGES	GRANTEE
VIEWS	TABLE_SCHEMA
1311 1312 1313 1314 1315 1316 1317 1318
SELECT MAX(table_name) FROM information_schema.tables;
MAX(table_name)
VIEWS
SELECT table_name from information_schema.tables
WHERE table_name=(SELECT MAX(table_name)
FROM information_schema.tables);
table_name
VIEWS
1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331
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;
1332
End of 5.0 tests.
1333 1334 1335 1336 1337 1338 1339 1340
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
grant select on *.* to user3148@localhost;
select user,db from information_schema.processlist;
user	db
user3148	test
drop user user3148@localhost;
1341
End of 5.1 tests.