information_schema.result 31.2 KB
Newer Older
1
DROP TABLE IF EXISTS t0,t1,t2;
2 3 4
show variables where variable_name like "skip_show_database";
Variable_name	Value
skip_show_database	OFF
5 6
grant select, update, execute on test.* to mysqltest_2@localhost;
grant select, update on test.* to mysqltest_1@localhost;
7 8
create user mysqltest_3@localhost;
create user mysqltest_3;
9
select * from information_schema.SCHEMATA where schema_name > 'm';
10 11 12
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
13 14
select schema_name from information_schema.schemata;
schema_name
15
information_schema
16 17 18 19 20 21 22
mysql
test
show databases like 't%';
Database (t%)
test
show databases;
Database
23
information_schema
24 25
mysql
test
26 27
show databases where `database` = 't%';
Database
28 29
create database mysqltest;
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
30 31
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
32
create table mysqltest.t4(a int);
33 34 35
create view v1 (c) as select table_name from information_schema.TABLES;
select * from v1;
c
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
SCHEMATA
TABLES
COLUMNS
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
ROUTINES
STATISTICS
VIEWS
USER_PRIVILEGES
SCHEMA_PRIVILEGES
TABLE_PRIVILEGES
COLUMN_PRIVILEGES
TABLE_CONSTRAINTS
KEY_COLUMN_USAGE
51 52 53 54 55 56 57 58 59
columns_priv
db
func
help_category
help_keyword
help_relation
help_topic
host
proc
60
procs_priv
61 62 63 64 65 66 67
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
68 69
t1
t4
70 71 72 73
t2
t3
v1
select c,table_name from v1 
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
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
select c,table_name from v1 
91 92 93
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
94 95 96
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
97 98 99 100 101 102 103 104
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
105 106
t2	t2
t3	t3
107 108 109 110
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
111 112 113
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
114 115 116 117 118 119 120 121
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
122 123
t2	t2
t3	t3
124
select table_name from information_schema.TABLES
125
where table_schema = "mysqltest" and table_name like "t%";
126 127 128
table_name
t1
t4
129
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
130
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
131
NULL	mysqltest	t1	1	mysqltest	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
132
show keys from t3 where Key_name = "a_data";
133 134 135 136 137 138 139 140
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
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
141 142
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		
143
v1	NULL	NULL	NULL	NULL	NULL	NULL	#	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	view
144 145 146 147 148
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
149
Insert_priv	enum('N','Y')	utf8_general_ci	NO		N		select,insert,update,references	
150 151
show full columns from v1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
152
c	varchar(64)	utf8_general_ci	NO				select,insert,update,references	
153 154
select * from information_schema.COLUMNS where table_name="t1"
and column_name= "a";
155
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
156 157 158 159
NULL	mysqltest	t1	a	1	NULL	YES	int	NULL	NULL	11	0	NULL	NULL	int(11)			select,insert,update,references	
show columns from mysqltest.t1 where field like "%a%";
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
160
create view mysqltest.v1 (c) as select a from mysqltest.t1;
161
grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
162
grant select on mysqltest.v1 to mysqltest_3;
163 164 165 166 167
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;
168 169
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
170
b	varchar(30)	YES	MUL	NULL	
171 172 173 174 175
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;
176 177
drop tables mysqltest.t4, mysqltest.t1, t2, t3;
drop database mysqltest;
178 179
select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';
180 181
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
182 183 184
SHOW CHARACTER SET LIKE 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	ISO 8859-1 West European	latin1_swedish_ci	1
185
SHOW CHARACTER SET WHERE charset like 'latin1%';
186 187 188 189
Charset	Description	Default collation	Maxlen
latin1	ISO 8859-1 West European	latin1_swedish_ci	1
select * from information_schema.COLLATIONS
where COLLATION_NAME like 'latin1%';
190
COLLATION_NAME	CHARACTER_SET_NAME	ID	IS_DEFAULT	IS_COMPILED	SORTLEN
191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
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
209
SHOW COLLATION WHERE collation like 'latin1%';
210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236
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
create function sub1(i int) returns int
return i+1;
create procedure sel2()
begin
select * from t1;
select * from t2;
end|
237 238 239 240
select parameter_style, sql_data_access, dtd_identifier 
from information_schema.routines;
parameter_style	sql_data_access	dtd_identifier
SQL	CONTAINS SQL	NULL
241
SQL	CONTAINS SQL	int(11)
242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260
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,
261
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8);
262 263 264 265 266 267
ROUTINE_NAME	name
sub1	sub1
sel2	sel2
select count(*) from information_schema.ROUTINES;
count(*)
2
268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295
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
296
sub2		CREATE FUNCTION `test`.`sub2`(i int) RETURNS int(11)
297 298 299 300 301 302 303 304 305
return i+1
drop function sub2;
show create procedure sel2;
Procedure	sql_mode	Create Procedure
sel2		CREATE PROCEDURE `test`.`sel2`()
begin
select * from t1;
select * from t2;
end
306
create view v0 (c) as select schema_name from information_schema.schemata;
307 308
select * from v0;
c
309
information_schema
310 311 312 313 314
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	
315
create view v1 (c) as select table_name from information_schema.tables
316 317 318 319
where table_name="v1";
select * from v1;
c
v1
320
create view v2 (c) as select column_name from information_schema.columns
321 322 323 324
where table_name="v2";
select * from v2;
c
c
325
create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
326 327 328 329
where CHARACTER_SET_NAME like "latin1%";
select * from v3;
c
latin1
330
create view v4 (c) as select COLLATION_NAME from information_schema.collations
331 332 333 334 335 336 337 338 339 340 341 342 343
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
344
select * from information_schema.views where TABLE_NAME like "v%";
345
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE
346 347 348 349 350
NULL	test	v0	select sql_no_cache `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata`	NONE	NO
NULL	test	v1	select sql_no_cache `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1')	NONE	NO
NULL	test	v2	select sql_no_cache `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2')	NONE	NO
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
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
351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371
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
372
'mysqltest_1'@'localhost'	NULL	test	EXECUTE	YES
373 374
'mysqltest_1'@'localhost'	NULL	test	CREATE VIEW	YES
'mysqltest_1'@'localhost'	NULL	test	SHOW VIEW	YES
375 376
'mysqltest_1'@'localhost'	NULL	test	CREATE ROUTINE	YES
'mysqltest_1'@'localhost'	NULL	test	ALTER ROUTINE	YES
377 378 379 380 381 382 383 384 385 386 387
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
388 389 390 391
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%';
392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408
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";
409 410 411 412 413
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
414 415
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA= "test";
416 417 418 419 420
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
421 422 423 424 425
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
426
t1	a
427 428 429 430 431 432 433 434
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;
435 436 437 438 439 440
create table t1(a int);
create view v1 (c) as select a from t1 with check option;
create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION;
create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION;
select * from information_schema.views;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE
441 442 443
NULL	test	v1	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES
NULL	test	v2	select `test`.`t1`.`a` AS `c` from `test`.`t1`	LOCAL	YES
NULL	test	v3	select `test`.`t1`.`a` AS `c` from `test`.`t1`	CASCADED	YES
444 445 446 447 448 449 450 451 452 453 454 455 456
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;
457 458 459 460 461 462
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;
463 464 465 466 467 468
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
469
0
470
10
471
drop table t1;
472
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
473
Table	Create Table
474
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
475 476
  `CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '',
477
  `DESCRIPTION` varchar(60) NOT NULL default '',
478
  `MAXLEN` bigint(3) NOT NULL default '0'
479
) ENGINE=MEMORY DEFAULT CHARSET=utf8
480
set names latin2;
481
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
482
Table	Create Table
483
CHARACTER_SETS	CREATE TEMPORARY TABLE `CHARACTER_SETS` (
484 485
  `CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '',
486
  `DESCRIPTION` varchar(60) NOT NULL default '',
487
  `MAXLEN` bigint(3) NOT NULL default '0'
488
) ENGINE=MEMORY DEFAULT CHARSET=utf8
489 490 491 492
set names latin1;
create table t1 select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like "latin1";
select * from t1;
493 494
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
495 496 497 498
alter table t1 default character set utf8;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
499 500
  `CHARACTER_SET_NAME` varchar(64) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '',
501
  `DESCRIPTION` varchar(60) NOT NULL default '',
502
  `MAXLEN` bigint(3) NOT NULL default '0'
503 504
) ENGINE=MyISAM DEFAULT CHARSET=utf8
drop table t1;
505 506 507 508 509 510 511 512 513
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
514 515 516 517 518 519 520 521 522
a	decimal(5,3)	NULL	NULL	5	3
b	decimal(5,1)	NULL	NULL	5	1
c	float(5,2)	NULL	NULL	5	2
d	decimal(6,4)	NULL	NULL	6	4
e	float	NULL	NULL	12	NULL
f	decimal(6,3)	NULL	NULL	6	3
g	int(11)	NULL	NULL	11	0
h	double(10,3)	NULL	NULL	10	3
i	double	NULL	NULL	22	NULL
523
drop table t1;
524 525 526 527
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
528 529
proc	db	char(64)
proc	name	char(64)
530
proc	type	enum('FUNCTION','PROCEDURE')
531
proc	specific_name	char(64)
532 533 534 535 536
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
537
proc	returns	char(64)
538
proc	body	blob
539
proc	definer	char(77)
540 541
proc	created	timestamp
proc	modified	timestamp
542
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')
543
proc	comment	char(64)
544 545 546 547 548 549
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;
550 551 552 553 554 555 556 557 558 559 560
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";
561
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
562 563
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_NAME= "vo";
564
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
565
drop view vo;
566 567 568 569
select TABLE_NAME,TABLE_TYPE,ENGINE
from information_schema.tables 
where table_schema='information_schema' limit 2;
TABLE_NAME	TABLE_TYPE	ENGINE
570 571
SCHEMATA	TEMPORARY	MEMORY
TABLES	TEMPORARY	MEMORY
572 573 574 575 576
show tables from information_schema like "T%";
Tables_in_information_schema (T%)
TABLES
TABLE_PRIVILEGES
TABLE_CONSTRAINTS
577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595
create database information_schema;
ERROR HY000: Can't create database 'information_schema'; database exists
use information_schema;
show full tables like "T%";
Tables_in_information_schema (T%)	Table_type
TABLES	TEMPORARY
TABLE_PRIVILEGES	TEMPORARY
TABLE_CONSTRAINTS	TEMPORARY
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_PRIVILEGES
TABLE_CONSTRAINTS
596 597 598 599 600 601 602 603 604 605 606 607 608 609 610
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
611 612
Create_routine_priv	select,insert,update,references
Alter_routine_priv	select,insert,update,references
613 614
max_questions	select,insert,update,references
max_connections	select,insert,update,references
615
max_user_connections	select,insert,update,references
616 617 618 619 620 621 622 623 624
use test;
create function sub1(i int) returns int
return i+1;
create table t1(f1 int);
create view t2 (c) as select f1 from t1;
create view t3 (c) as select sub1(1);
create table t4(f1 int, KEY f1_key (f1));
drop table t1;
drop function sub1;
625 626 627 628 629 630 631 632 633 634 635 636
select table_name from information_schema.views
where table_schema='test';
table_name
Warnings:
Warning	1356	View 'test.t2' references invalid table(s) or column(s) or function(s)
Warning	1356	View 'test.t3' references invalid table(s) or column(s) or function(s)
select table_name from information_schema.views
where table_schema='test';
table_name
Warnings:
Warning	1356	View 'test.t2' references invalid table(s) or column(s) or function(s)
Warning	1356	View 'test.t3' references invalid table(s) or column(s) or function(s)
637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652
select column_name from information_schema.columns
where table_schema='test';
column_name
f1
Warnings:
Warning	1356	View 'test.t2' references invalid table(s) or column(s) or function(s)
Warning	1356	View 'test.t3' references invalid table(s) or column(s) or function(s)
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
drop view t2;
drop view t3;
drop table t4;
653 654
select * from information_schema.table_names;
ERROR 42S02: Unknown table 'table_names' in information_schema
655 656 657 658 659 660
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)
661 662 663 664 665 666 667 668
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
669 670 671
show open tables where `table` like "user";
Database	Table	In_use	Name_locked
mysql	user	0	0
672 673 674 675 676
show status where variable_name like "%database%";
Variable_name	Value
Com_show_databases	0
show variables where variable_name like "skip_show_databas";
Variable_name	Value
677 678 679
show global status like "Threads_running";
Variable_name	Value
Threads_running	1
680 681 682 683 684 685
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;
686 687 688
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;
689 690
count(*)
100
691 692
drop view a2, a1;
drop table t_crashme;
693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708
select table_schema,table_name, column_name from
information_schema.columns 
where data_type = 'longtext';
table_schema	table_name	column_name
information_schema	COLUMNS	COLUMN_TYPE
information_schema	ROUTINES	ROUTINE_DEFINITION
information_schema	ROUTINES	SQL_MODE
information_schema	VIEWS	VIEW_DEFINITION
select table_name, column_name, data_type from information_schema.columns
where data_type = 'datetime';
table_name	column_name	data_type
TABLES	CREATE_TIME	datetime
TABLES	UPDATE_TIME	datetime
TABLES	CHECK_TIME	datetime
ROUTINES	CREATED	datetime
ROUTINES	LAST_ALTERED	datetime
709 710 711 712 713 714 715
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
716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737
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;
738 739 740 741 742 743 744 745 746 747 748 749 750 751
create user mysqltest_4@localhost;
SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME='TABLE_NAME';
TABLE_NAME	COLUMN_NAME	PRIVILEGES
TABLES	TABLE_NAME	select
COLUMNS	TABLE_NAME	select
STATISTICS	TABLE_NAME	select
VIEWS	TABLE_NAME	select
TABLE_PRIVILEGES	TABLE_NAME	select
COLUMN_PRIVILEGES	TABLE_NAME	select
TABLE_CONSTRAINTS	TABLE_NAME	select
KEY_COLUMN_USAGE	TABLE_NAME	select
delete from mysql.user where user='mysqltest_4';
flush privileges;
752
SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;
gluh@mysql.com's avatar
gluh@mysql.com committed
753 754 755
table_schema	count(*)
information_schema	15
mysql	17
756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825
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;
826 827 828 829 830 831 832 833 834
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;