information_schema.result 22.7 KB
Newer Older
1 2
grant all privileges on test.* to mysqltest_1@localhost;
select * from information_schema.SCHEMATA where schema_name > 'm';
3 4 5
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	SQL_PATH
NULL	mysql	latin1	NULL
NULL	test	latin1	NULL
6 7
select schema_name from information_schema.schemata;
schema_name
8
information_schema
9 10 11
mysql
test
show databases *;
12
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	SQL_PATH
13
NULL	information_schema	utf8	NULL
14 15
NULL	mysql	latin1	NULL
NULL	test	latin1	NULL
16 17 18 19 20
show databases like 't%';
Database (t%)
test
show databases;
Database
21
information_schema
22 23 24
mysql
test
show databases * where schema_name like 't%';
25 26
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	SQL_PATH
NULL	test	latin1	NULL
27
show databases * where schema_name = 't%';
28
CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	SQL_PATH
29 30 31 32 33 34 35 36
create database testtets;
create table testtets.t1(a int, b VARCHAR(30), KEY string_data (b));
create table test.t2(a int);
create table t3(a int, KEY a_data (a));
create table testtets.t4(a int);
create view v1 (c) as select table_name from information_schema.TABLES;
select * from v1;
c
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
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
TABLE_NAMES
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
columns_priv
db
func
help_category
help_keyword
help_relation
help_topic
host
proc
tables_priv
time_zone
time_zone_leap_second
time_zone_name
time_zone_transition
time_zone_transition_type
user
t2
t3
v1
t1
t4
select c,table_name from v1 
left join information_schema.TABLES v2 on (v1.c=v2.table_name)
where v1.c like "t%";
c	table_name
78 79 80 81
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_NAMES	TABLE_NAMES
82 83 84 85 86 87 88 89 90 91 92 93 94 95
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
t2	t2
t3	t3
t1	t1
t4	t4
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
96 97 98 99
TABLES	TABLES
TABLE_PRIVILEGES	TABLE_PRIVILEGES
TABLE_CONSTRAINTS	TABLE_CONSTRAINTS
TABLE_NAMES	TABLE_NAMES
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117
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
t2	t2
t3	t3
t1	t1
t4	t4
select table_name from information_schema.TABLES
where table_schema = "testtets" and table_name like "t%";
table_name
t1
t4
select * from information_schema.STATISTICS where TABLE_SCHEMA = "testtets";
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
NULL	testtets	t1	1	testtets	string_data	1	b	A	NULL	NULL	NULL	YES	BTREE	
118
show keys * from t3 where TABLE_SCHEMA Like "test%";
119 120
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT
NULL	test	t3	1	test	a_data	1	a	A	NULL	NULL	NULL	YES	BTREE	
121
show keys from t3 where INDEX_NAME = "a_data";
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141
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 tables * from test where table_name like 't%';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
NULL	test	t2	
NULL	test	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
t2	MyISAM	9	Fixed	0	0	0	21474836479	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
t3	MyISAM	9	Fixed	0	0	0	21474836479	1024	0	NULL	#	#	NULL	latin1_swedish_ci	NULL		
v1	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#	#	NULL	NULL	NULL	NULL	view
show full columns from t3 like "a%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
a	int(11)	NULL	YES	MUL	NULL		select,insert,update,references	
show full columns from mysql.db like "Insert%";
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
142
Insert_priv	enum('N','Y')	utf8_bin	NO		N		select,insert,update,references	
143 144
show full columns from v1;
Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
145
c	varchar(64)	utf8_general_ci	NO				select,insert,update,references	
146 147
select * from information_schema.COLUMNS where table_name="t1"
and column_name= "a";
148 149
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT
NULL	testtets	t1	a	1	NULL	YES	int	11	11	11	0	NULL	NULL	int(11)			select,insert,update,references	
150
show columns * from testtets.t1 where table_name = "t1";
151 152 153
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT
NULL	testtets	t1	a	1	NULL	YES	int	11	11	11	0	NULL	NULL	int(11)			select,insert,update,references	
NULL	testtets	t1	b	2	NULL	YES	varchar	30	30	NULL	NULL	latin1	latin1_swedish_ci	varchar(30)	MUL		select,insert,update,references	
154 155 156 157 158
drop view v1;
drop tables testtets.t4, testtets.t1, t2, t3;
drop database testtets;
select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like 'latin1%';
159 160
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
161 162 163 164
SHOW CHARACTER SET LIKE 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	ISO 8859-1 West European	latin1_swedish_ci	1
SHOW CHARACTER SET * LIKE 'latin1%';
165 166
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
167 168 169 170 171 172 173
SHOW CHARACTER SET WHERE CHARACTER_SET_NAME like 'latin1%';
Charset	Description	Default collation	Maxlen
latin1	ISO 8859-1 West European	latin1_swedish_ci	1
SHOW CHARACTER SET CHARACTER_SET_NAME WHERE CHARACTER_SET_NAME like 'latin1%';
CHARACTER_SET_NAME
latin1
SHOW CHARACTER SET *  WHERE CHARACTER_SET_NAME like 'latin1%';
174 175
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
176 177
select * from information_schema.COLLATIONS
where COLLATION_NAME like 'latin1%';
178
COLLATION_NAME	CHARSET	ID	DEFAULT	COMPILED	SORTLEN
179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
SHOW COLLATION LIKE 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
SHOW COLLATION * LIKE 'latin1%';
198
COLLATION_NAME	CHARSET	ID	DEFAULT	COMPILED	SORTLEN
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
SHOW COLLATION WHERE COLLATION_NAME like 'latin1%';
Collation	Charset	Id	Default	Compiled	Sortlen
latin1_german1_ci	latin1	5			0
latin1_swedish_ci	latin1	8	Yes	Yes	1
latin1_danish_ci	latin1	15			0
latin1_german2_ci	latin1	31		Yes	2
latin1_bin	latin1	47		Yes	1
latin1_general_ci	latin1	48			0
latin1_general_cs	latin1	49			0
latin1_spanish_ci	latin1	94			0
SHOW COLLATION COLLATION_NAME WHERE COLLATION_NAME like 'latin1%';
COLLATION_NAME
latin1_german1_ci
latin1_swedish_ci
latin1_danish_ci
latin1_german2_ci
latin1_bin
latin1_general_ci
latin1_general_cs
latin1_spanish_ci
SHOW COLLATION *  WHERE COLLATION_NAME like 'latin1%';
228
COLLATION_NAME	CHARSET	ID	DEFAULT	COMPILED	SORTLEN
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
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|
255 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
SQL	CONTAINS SQL	int
260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278
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,
unknown's avatar
unknown committed
279
mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8);
280 281 282 283 284 285
ROUTINE_NAME	name
sub1	sub1
sel2	sel2
select count(*) from information_schema.ROUTINES;
count(*)
2
unknown's avatar
unknown committed
286
create view v0 (c) as select schema_name from information_schema.schemata;
287 288
select * from v0;
c
289
information_schema
290 291 292 293 294
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	
unknown's avatar
unknown committed
295
create view v1 (c) as select table_name from information_schema.tables
296 297 298 299
where table_name="v1";
select * from v1;
c
v1
unknown's avatar
unknown committed
300
create view v2 (c) as select column_name from information_schema.columns
301 302 303 304
where table_name="v2";
select * from v2;
c
c
unknown's avatar
unknown committed
305
create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
306 307 308 309
where CHARACTER_SET_NAME like "latin1%";
select * from v3;
c
latin1
unknown's avatar
unknown committed
310
create view v4 (c) as select COLLATION_NAME from information_schema.collations
311 312 313 314 315 316 317 318 319 320 321 322 323
where COLLATION_NAME like "latin1%";
select * from v4;
c
latin1_german1_ci
latin1_swedish_ci
latin1_danish_ci
latin1_german2_ci
latin1_bin
latin1_general_ci
latin1_general_cs
latin1_spanish_ci
show keys from v4;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
unknown's avatar
unknown committed
324
select * from information_schema.views where TABLE_NAME like "v%";
325
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION	CHECK_OPTION	IS_UPDATABLE
unknown's avatar
unknown committed
326 327 328 329 330
NULL	test	v0	select `schemata`.`SCHEMA_NAME` AS `c` from `information_schema`.`schemata`	NONE	NO
NULL	test	v1	select `tables`.`TABLE_NAME` AS `c` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` = _utf8'v1')	NONE	NO
NULL	test	v2	select `columns`.`COLUMN_NAME` AS `c` from `information_schema`.`columns` where (`columns`.`TABLE_NAME` = _utf8'v2')	NONE	NO
NULL	test	v3	select `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 `collations`.`COLLATION_NAME` AS `c` from `information_schema`.`collations` where (`collations`.`COLLATION_NAME` like _utf8'latin1%')	NONE	NO
331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385
drop view v0, v1, v2, v3, v4;
create table t1 (a int);
grant select,update,insert on t1 to mysqltest_1@localhost;
grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
grant all on test.* to mysqltest_1@localhost with grant option;
select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	USAGE	NO
select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	test	SELECT	YES
'mysqltest_1'@'localhost'	NULL	test	INSERT	YES
'mysqltest_1'@'localhost'	NULL	test	UPDATE	YES
'mysqltest_1'@'localhost'	NULL	test	DELETE	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE	YES
'mysqltest_1'@'localhost'	NULL	test	DROP	YES
'mysqltest_1'@'localhost'	NULL	test	REFERENCES	YES
'mysqltest_1'@'localhost'	NULL	test	INDEX	YES
'mysqltest_1'@'localhost'	NULL	test	ALTER	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE TEMPORARY TABLES	YES
'mysqltest_1'@'localhost'	NULL	test	LOCK TABLES	YES
'mysqltest_1'@'localhost'	NULL	test	CREATE VIEW	YES
'mysqltest_1'@'localhost'	NULL	test	SHOW VIEW	YES
select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	test	t1	SELECT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	INSERT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	UPDATE	NO
select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_1'@'localhost'	NULL	test	t1	a	SELECT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	a	INSERT	NO
'mysqltest_1'@'localhost'	NULL	test	t1	a	UPDATE	NO
'mysqltest_1'@'localhost'	NULL	test	t1	a	REFERENCES	NO
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
delete from mysql.tables_priv where user='mysqltest_1';
delete from mysql.columns_priv where user='mysqltest_1';
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";
386 387 388 389 390
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
391 392
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA= "test";
393 394 395 396 397
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT
NULL	test	PRIMARY	NULL	test	t1	a	1	NULL
NULL	test	constraint_1	NULL	test	t1	a	1	NULL
NULL	test	key_1	NULL	test	t1	a	1	NULL
NULL	test	key_2	NULL	test	t1	a	1	NULL
398 399 400 401 402
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
403
t1	a
404 405 406 407 408 409 410 411
select ROUTINE_NAME from information_schema.ROUTINES;
ROUTINE_NAME
sel2
sub1
delete from mysql.user where user='mysqltest_1';
drop table t1;
drop procedure sel2;
drop function sub1;
unknown's avatar
unknown committed
412 413 414 415 416 417
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
418 419 420
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
unknown's avatar
unknown committed
421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437
grant select (a) on test.t1 to joe@localhost with grant option;
select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'joe'@'localhost'	NULL	test	t1	a	SELECT	YES
select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
drop view v1, v2, v3;
drop table t1;
delete from mysql.user where user='joe';
delete from mysql.db where user='joe';
delete from mysql.tables_priv where user='joe';
delete from mysql.columns_priv where user='joe';
flush privileges;
create procedure px5 ()
begin
declare v int;
declare c cursor for select version from
438
information_schema.tables where table_schema <> 'information_schema';
unknown's avatar
unknown committed
439 440 441 442 443 444 445 446 447 448 449
open c;
fetch c into v;
select v;
close c;
end;//
call px5()//
v
9
call px5()//
v
9
450 451 452
select sql_mode from information_schema.ROUTINES;
sql_mode

unknown's avatar
unknown committed
453
drop procedure px5;
454 455 456 457 458 459
create table t1 (a int not null auto_increment,b int, primary key (a));
insert into t1 values (1,1),(NULL,3),(NULL,4);
select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
AUTO_INCREMENT
4
drop table t1;
unknown's avatar
unknown committed
460 461 462 463 464 465
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
466
0
unknown's avatar
unknown committed
467
9
468
10
unknown's avatar
unknown committed
469
drop table t1;
unknown's avatar
unknown committed
470
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
unknown's avatar
unknown committed
471
Table	Create Table
unknown's avatar
unknown committed
472
character_sets	CREATE TEMPORARY TABLE `character_sets` (
473 474
  `CHARACTER_SET_NAME` varchar(30) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(60) NOT NULL default '',
475
  `DESCRIPTION` varchar(60) NOT NULL default '',
476
  `MAXLEN` bigint(3) NOT NULL default '0'
477
) ENGINE=HEAP DEFAULT CHARSET=utf8 MAX_ROWS=2252
unknown's avatar
unknown committed
478
set names latin2;
unknown's avatar
unknown committed
479
SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
unknown's avatar
unknown committed
480
Table	Create Table
unknown's avatar
unknown committed
481
character_sets	CREATE TEMPORARY TABLE `character_sets` (
482 483
  `CHARACTER_SET_NAME` varchar(30) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(60) NOT NULL default '',
484
  `DESCRIPTION` varchar(60) NOT NULL default '',
485
  `MAXLEN` bigint(3) NOT NULL default '0'
486
) ENGINE=HEAP DEFAULT CHARSET=utf8 MAX_ROWS=2252
unknown's avatar
unknown committed
487 488 489 490
set names latin1;
create table t1 select * from information_schema.CHARACTER_SETS
where CHARACTER_SET_NAME like "latin1";
select * from t1;
491 492
CHARACTER_SET_NAME	DEFAULT_COLLATE_NAME	DESCRIPTION	MAXLEN
latin1	latin1_swedish_ci	ISO 8859-1 West European	1
unknown's avatar
unknown committed
493 494 495 496
alter table t1 default character set utf8;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
497 498
  `CHARACTER_SET_NAME` varchar(30) NOT NULL default '',
  `DEFAULT_COLLATE_NAME` varchar(60) NOT NULL default '',
499
  `DESCRIPTION` varchar(60) NOT NULL default '',
500
  `MAXLEN` bigint(3) NOT NULL default '0'
unknown's avatar
unknown committed
501 502
) ENGINE=MyISAM DEFAULT CHARSET=utf8
drop table t1;
503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521
create view v1 as select * from information_schema.TABLES;
drop view v1;
create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
i DOUBLE);
select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, 
CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
from information_schema.columns where table_name= 't1';
COLUMN_NAME	COLUMN_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE
a	decimal(5,3)	7	7	5	3
b	decimal(5,1)	7	7	5	1
c	float(5,2)	5	5	5	2
d	decimal(6,4)	8	8	6	4
e	float	12	12	12	NULL
f	decimal(6,3)	8	8	6	3
g	int(11)	11	11	11	0
h	double(10,3)	10	10	10	3
i	double	22	22	22	NULL
drop table t1;
unknown's avatar
unknown committed
522 523 524 525
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
526 527
proc	db	char(64)
proc	name	char(64)
unknown's avatar
unknown committed
528
proc	type	enum('FUNCTION','PROCEDURE')
529
proc	specific_name	char(64)
unknown's avatar
unknown committed
530 531 532 533 534
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
535
proc	returns	char(64)
unknown's avatar
unknown committed
536
proc	body	blob
537
proc	definer	char(77)
unknown's avatar
unknown committed
538 539 540
proc	created	timestamp
proc	modified	timestamp
proc	sql_mode	set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO')
541
proc	comment	char(64)
unknown's avatar
unknown committed
542 543 544 545 546 547
drop table t115;
create procedure p108 () begin declare c cursor for select data_type
from information_schema.columns;  open c; open c; end;//
call p108()//
ERROR 24000: Cursor is already open
drop procedure p108;
unknown's avatar
unknown committed
548 549 550 551 552 553 554 555 556 557 558
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";
559
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_SCHEMA	TABLE_NAME	CONSTRAINT_TYPE
unknown's avatar
unknown committed
560 561
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_NAME= "vo";
562
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT
unknown's avatar
unknown committed
563
drop view vo;
564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592
select TABLE_NAME,TABLE_TYPE,ENGINE
from information_schema.tables 
where table_schema='information_schema' limit 2;
TABLE_NAME	TABLE_TYPE	ENGINE
SCHEMATA	TEMPORARY	MyISAM
TABLES	TEMPORARY	MyISAM
show tables from information_schema like "t%";
Tables_in_information_schema (t%)
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
TABLE_NAMES	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
TABLE_NAMES
593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617
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;
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;