1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
select * from information_schema.SCHEMATA where schema_name > 'm';
3
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
4
NULL mysql utf8 utf8_general_ci NULL
5
NULL pbxt utf8 utf8_general_ci NULL
6
NULL test utf8 utf8_general_ci NULL
7
select schema_name from information_schema.schemata;
13
show databases like 't%';
22
create database mysqltest;
23
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
24
create table test.t2(a int);
25
create table t3(a int, KEY a_data (a));
26
create table mysqltest.t4(a int);
27
create table t5 (id int auto_increment primary key);
28
insert into t5 values (10);
29
select table_name from information_schema.TABLES
30
where table_schema = "mysqltest" and table_name like "t%";
34
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
35
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 INDEX_COMMENT
36
NULL mysqltest t1 1 mysqltest string_data 1 b A NULL NULL NULL YES BTREE
37
show tables like 't%';
43
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
44
t2 PBXT 10 Fixed 0 19 1 # 4096 0 NULL # # NULL utf8_general_ci NULL
45
t3 PBXT 10 Fixed 0 19 1 # 4096 0 NULL # # NULL utf8_general_ci NULL
46
t5 PBXT 10 Fixed 1 19 2 # 20480 0 0 # # NULL utf8_general_ci NULL
47
show full columns from t3 like "a%";
48
Field Type Collation Null Key Default Extra Privileges Comment
49
a int NULL YES MUL NULL
50
select * from information_schema.COLUMNS where table_name="t1"
52
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 STORAGE FORMAT
53
NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL int Default Default
54
select table_name, column_name, privileges from information_schema.columns
55
where table_schema = 'mysqltest' and table_name = 't1';
56
table_name column_name privileges
59
show columns from mysqltest.t1;
60
Field Type Null Key Default Extra
62
b varchar(30) YES MUL NULL
63
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
64
drop database mysqltest;
65
select * from information_schema.CHARACTER_SETS
66
where CHARACTER_SET_NAME like 'latin1%';
67
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
68
SHOW CHARACTER SET LIKE 'latin1%';
69
Charset Description Default collation Maxlen
70
select * from information_schema.COLLATIONS
71
where COLLATION_NAME like 'latin1%';
72
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
73
SHOW COLLATION LIKE 'latin1%';
74
Collation Charset Id Default Compiled Sortlen
75
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
76
where COLLATION_NAME like 'latin1%';
77
COLLATION_NAME CHARACTER_SET_NAME
78
select * from information_schema.table_names;
79
ERROR 42S02: Unknown table 'table_names' in information_schema
80
select column_type from information_schema.columns
81
where table_schema="information_schema" and table_name="COLUMNS" and
82
(column_name="character_set_name" or column_name="collation_name");
86
select TABLE_ROWS from information_schema.tables where
87
table_schema="information_schema" and table_name="COLUMNS";
90
select table_type from information_schema.tables
91
where table_schema="mysql" and table_name="user";
93
show global status like "Threads_running";
96
select table_schema,table_name, column_name from
97
information_schema.columns
98
where data_type = 'longtext';
99
table_schema table_name column_name
100
select table_name, column_name, data_type from information_schema.columns
101
where data_type = 'datetime';
102
table_name column_name data_type
103
TABLES CREATE_TIME datetime
104
TABLES UPDATE_TIME datetime
105
TABLES CHECK_TIME datetime
106
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
108
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
109
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
110
AND A.TABLE_NAME = B.TABLE_NAME);
117
x_decimal DECIMAL(5,3),
118
x_numeric NUMERIC(5,3),
121
x_double_precision DOUBLE PRECISION );
122
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
123
FROM INFORMATION_SCHEMA.COLUMNS
124
WHERE TABLE_NAME= 't1';
125
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
133
x_double_precision NULL NULL
135
SELECT table_schema, count(*) FROM information_schema.TABLES
136
WHERE table_name NOT LIKE 'ndb_%' AND table_name NOT LIKE 'falcon%' GROUP BY TABLE_SCHEMA;
137
table_schema count(*)
138
information_schema 17
140
show create database information_schema;
141
Database Create Database
142
information_schema CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
143
create table t1(f1 LONGBLOB, f2 LONGTEXT);
144
select column_name,data_type,CHARACTER_OCTET_LENGTH,
145
CHARACTER_MAXIMUM_LENGTH
146
from information_schema.columns
147
where table_name='t1';
148
column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
149
f1 blob 4294967295 4294967295
150
f2 text 4294967295 4294967295
152
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
153
f5 BIGINT, f6 int, f7 int);
154
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
155
from information_schema.columns
156
where table_name='t1';
157
column_name NUMERIC_PRECISION NUMERIC_SCALE
166
create table t1 (a int not null, b int);
167
use information_schema;
168
select column_name, column_default from columns
169
where table_schema='test' and table_name='t1';
170
column_name column_default
174
show columns from t1;
175
Field Type Null Key Default Extra
179
alter database information_schema;
180
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
181
drop database information_schema;
182
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
183
drop table information_schema.tables;
184
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
185
alter table information_schema.tables;
186
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
187
use information_schema;
188
create temporary table schemata(f1 char(10));
189
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
191
create table t1(id int);
192
insert into t1(id) values (1);
193
select 1 from (select 1 from test.t1) a;
196
use information_schema;
197
select 1 from (select 1 from test.t1) a;
202
create temporary table t1(f1 int, index(f1));
203
show columns from t1;
204
Field Type Null Key Default Extra
207
Field Type Null Key Default Extra
209
show indexes from t1;
210
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
211
t1 1 f1 1 f1 A NULL NULL NULL YES BTREE
213
create table t1(f1 varbinary(32), f2 varbinary(64));
214
select character_maximum_length, character_octet_length
215
from information_schema.columns where table_name='t1';
216
character_maximum_length character_octet_length
220
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
221
from information_schema.columns where
222
table_schema='information_schema' and
223
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
224
or column_type = 'varchar(27)')
225
group by column_type order by num;
226
column_type group_concat(table_schema, '.', table_name) num
227
varchar(20) information_schema.PLUGINS 1
228
varchar(27) information_schema.COLUMNS 1
229
create table t1(f1 char(1) not null, f2 char(9) not null);
230
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
231
information_schema.columns where table_schema='test' and table_name = 't1';
232
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
237
create table t1(f1 char(5));
238
create table t2(f1 char(5));
239
select concat(@a, table_name), @a, table_name
240
from information_schema.tables where table_schema = 'test';
241
concat(@a, table_name) @a table_name
245
SELECT t.table_name, c1.column_name
246
FROM information_schema.tables t
248
information_schema.columns c1
249
ON t.table_schema = c1.table_schema AND
250
t.table_name = c1.table_name
251
WHERE t.table_schema = 'information_schema' AND
252
c1.ordinal_position =
253
( SELECT COALESCE(MIN(c2.ordinal_position),1)
254
FROM information_schema.columns c2
255
WHERE c2.table_schema = t.table_schema AND
256
c2.table_name = t.table_name AND
257
c2.column_name LIKE '%SCHEMA%'
259
AND t.table_name NOT LIKE 'falcon%';
260
table_name column_name
261
CHARACTER_SETS CHARACTER_SET_NAME
262
COLLATIONS COLLATION_NAME
263
COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME
265
GLOBAL_STATUS VARIABLE_NAME
266
GLOBAL_VARIABLES VARIABLE_NAME
267
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
270
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
272
SESSION_STATUS VARIABLE_NAME
273
SESSION_VARIABLES VARIABLE_NAME
274
STATISTICS TABLE_SCHEMA
276
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
277
PBXT_STATISTICS Record_cache_usage
278
SELECT t.table_name, c1.column_name
279
FROM information_schema.tables t
281
information_schema.columns c1
282
ON t.table_schema = c1.table_schema AND
283
t.table_name = c1.table_name
284
WHERE t.table_schema = 'information_schema' AND
285
c1.ordinal_position =
286
( SELECT COALESCE(MIN(c2.ordinal_position),1)
287
FROM information_schema.columns c2
288
WHERE c2.table_schema = 'information_schema' AND
289
c2.table_name = t.table_name AND
290
c2.column_name LIKE '%SCHEMA%'
292
AND t.table_name NOT LIKE 'falcon%';
293
table_name column_name
294
CHARACTER_SETS CHARACTER_SET_NAME
295
COLLATIONS COLLATION_NAME
296
COLLATION_CHARACTER_SET_APPLICABILITY COLLATION_NAME
298
GLOBAL_STATUS VARIABLE_NAME
299
GLOBAL_VARIABLES VARIABLE_NAME
300
KEY_COLUMN_USAGE CONSTRAINT_SCHEMA
303
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
305
SESSION_STATUS VARIABLE_NAME
306
SESSION_VARIABLES VARIABLE_NAME
307
STATISTICS TABLE_SCHEMA
309
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
310
PBXT_STATISTICS Record_cache_usage
311
SELECT MAX(table_name) FROM information_schema.tables;
314
SELECT table_name from information_schema.tables
315
WHERE table_name=(SELECT MAX(table_name)
316
FROM information_schema.tables);
319
create table t1 (f1 int);
320
create table t2 (f1 int, f2 int);
321
select table_name from information_schema.tables
322
where table_schema = 'test' and table_name not in
323
(select table_name from information_schema.columns
324
where table_schema = 'test' and column_name = 'f3');
329
select 1 as f1 from information_schema.tables where "CHARACTER_SETS"=
330
(select cast(table_name as char) from information_schema.tables
331
order by table_name limit 1) limit 1;
334
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
336
from information_schema.tables t
337
inner join information_schema.columns c1
338
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
339
where t.table_schema = 'information_schema' AND
340
t.table_name not like 'falcon%' AND
341
c1.ordinal_position =
342
(select isnull(c2.column_type) -
343
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
345
from information_schema.columns c2 where
346
c2.table_schema='information_schema' and
347
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
348
group by c2.column_type order by num limit 1)
349
group by t.table_name order by num1, t.table_name;
350
table_name group_concat(t.table_schema, '.', t.table_name) num1
351
CHARACTER_SETS information_schema.CHARACTER_SETS 1
352
COLLATIONS information_schema.COLLATIONS 1
353
COLLATION_CHARACTER_SET_APPLICABILITY information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 1
354
COLUMNS information_schema.COLUMNS 1
355
GLOBAL_STATUS information_schema.GLOBAL_STATUS 1
356
GLOBAL_VARIABLES information_schema.GLOBAL_VARIABLES 1
357
KEY_COLUMN_USAGE information_schema.KEY_COLUMN_USAGE 1
358
PBXT_STATISTICS information_schema.PBXT_STATISTICS 1
359
PLUGINS information_schema.PLUGINS 1
360
PROCESSLIST information_schema.PROCESSLIST 1
361
REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1
362
SCHEMATA information_schema.SCHEMATA 1
363
SESSION_STATUS information_schema.SESSION_STATUS 1
364
SESSION_VARIABLES information_schema.SESSION_VARIABLES 1
365
STATISTICS information_schema.STATISTICS 1
366
TABLES information_schema.TABLES 1
367
TABLE_CONSTRAINTS information_schema.TABLE_CONSTRAINTS 1
369
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
371
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
374
f2 varchar(50) not null,
375
f3 varchar(50) default '',
376
f4 varchar(50) default NULL,
378
f6 bigint not null default 10,
379
f7 datetime not null,
380
f8 datetime default '2006-01-01'
382
select column_default from information_schema.columns where table_name= 't1';
392
show columns from t1;
393
Field Type Null Key Default Extra
394
f1 varchar(50) YES NULL
395
f2 varchar(50) NO NULL
396
f3 varchar(50) YES NULL
397
f4 varchar(50) YES NULL
403
show fields from information_schema.table_names;
404
ERROR 42S02: Unknown table 'table_names' in information_schema
405
show keys from information_schema.table_names;
406
ERROR 42S02: Unknown table 'table_names' in information_schema
407
SET max_heap_table_size = DEFAULT;
410
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
411
WHERE SCHEMA_NAME ='information_schema';
414
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
415
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
417
select * from information_schema.columns where table_schema = NULL;
418
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 STORAGE FORMAT
419
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
420
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 STORAGE FORMAT
421
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
422
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
423
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
424
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
425
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
426
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
427
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
428
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
429
select * from information_schema.schemata where schema_name = NULL;
430
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
431
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
432
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 INDEX_COMMENT
433
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
434
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 INDEX_COMMENT
435
select * from information_schema.tables where table_schema = NULL;
436
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
437
select * from information_schema.tables where table_catalog = NULL;
438
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
439
select * from information_schema.tables where table_name = NULL;
440
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
441
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
442
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
443
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
444
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
added
removed
tests/suite/broken/r/pbxt/information_schema.result
