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