← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to mysql-test/r/information_schema.result

Renamed more stuff to drizzle.

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/r/information_schema.result
 
1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
 
2
show variables where variable_name like "skip_show_database";
 
3
Variable_name   Value
 
4
skip_show_database      OFF
 
5
select * from information_schema.SCHEMATA where schema_name > 'm';
 
6
CATALOG_NAME    SCHEMA_NAME     DEFAULT_CHARACTER_SET_NAME      DEFAULT_COLLATION_NAME  SQL_PATH
 
7
NULL    mysql   latin1  latin1_swedish_ci       NULL
 
8
NULL    test    latin1  latin1_swedish_ci       NULL
 
9
select schema_name from information_schema.schemata;
 
10
schema_name
 
11
information_schema
 
12
mysql
 
13
test
 
14
show databases like 't%';
 
15
Database (t%)
 
16
test
 
17
show databases;
 
18
Database
 
19
information_schema
 
20
mysql
 
21
test
 
22
show databases where `database` = 't%';
 
23
Database
 
24
create database mysqltest;
 
25
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
 
26
create table test.t2(a int);
 
27
create table t3(a int, KEY a_data (a));
 
28
create table mysqltest.t4(a int);
 
29
create table t5 (id int auto_increment primary key);
 
30
insert into t5 values (10);
 
31
select table_name from information_schema.TABLES
 
32
where table_schema = "mysqltest" and table_name like "t%";
 
33
table_name
 
34
t1
 
35
t4
 
36
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
 
37
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
 
38
NULL    mysqltest       t1      1       mysqltest       string_data     1       b       A       NULL    NULL    NULL    YES     BTREE           
 
39
show keys from t3 where Key_name = "a_data";
 
40
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_Comment
 
41
t3      1       a_data  1       a       A       NULL    NULL    NULL    YES     BTREE           
 
42
show tables like 't%';
 
43
Tables_in_test (t%)
 
44
t2
 
45
t3
 
46
t5
 
47
show table status;
 
48
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
 
49
t2      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
50
t3      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
51
t5      MyISAM  10      Fixed   1       7       7       #       2048    0       11      #       #       NULL    latin1_swedish_ci       NULL            
 
52
show full columns from t3 like "a%";
 
53
Field   Type    Collation       Null    Key     Default Extra   Privileges      Comment
 
54
a       int(11) NULL    YES     MUL     NULL                    
 
55
select * from information_schema.COLUMNS where table_name="t1"
 
56
and column_name= "a";
 
57
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
 
58
NULL    mysqltest       t1      a       1       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    int(11)                                 Default Default
 
59
show columns from mysqltest.t1 where field like "%a%";
 
60
Field   Type    Null    Key     Default Extra
 
61
a       int(11) YES             NULL    
 
62
select table_name, column_name, privileges from information_schema.columns 
 
63
where table_schema = 'mysqltest' and table_name = 't1';
 
64
table_name      column_name     privileges
 
65
t1      a       
 
66
t1      b       
 
67
show columns from mysqltest.t1;
 
68
Field   Type    Null    Key     Default Extra
 
69
a       int(11) YES             NULL    
 
70
b       varchar(30)     YES     MUL     NULL    
 
71
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
 
72
drop database mysqltest;
 
73
select * from information_schema.CHARACTER_SETS
 
74
where CHARACTER_SET_NAME like 'latin1%';
 
75
CHARACTER_SET_NAME      DEFAULT_COLLATE_NAME    DESCRIPTION     MAXLEN
 
76
latin1  latin1_swedish_ci       cp1252 West European    1
 
77
SHOW CHARACTER SET LIKE 'latin1%';
 
78
Charset Description     Default collation       Maxlen
 
79
latin1  cp1252 West European    latin1_swedish_ci       1
 
80
SHOW CHARACTER SET WHERE charset like 'latin1%';
 
81
Charset Description     Default collation       Maxlen
 
82
latin1  cp1252 West European    latin1_swedish_ci       1
 
83
select * from information_schema.COLLATIONS
 
84
where COLLATION_NAME like 'latin1%';
 
85
COLLATION_NAME  CHARACTER_SET_NAME      ID      IS_DEFAULT      IS_COMPILED     SORTLEN
 
86
latin1_german1_ci       latin1  5               #       1
 
87
latin1_swedish_ci       latin1  8       Yes     #       1
 
88
latin1_danish_ci        latin1  15              #       1
 
89
latin1_german2_ci       latin1  31              #       2
 
90
latin1_bin      latin1  47              #       1
 
91
latin1_general_ci       latin1  48              #       1
 
92
latin1_general_cs       latin1  49              #       1
 
93
latin1_spanish_ci       latin1  94              #       1
 
94
SHOW COLLATION LIKE 'latin1%';
 
95
Collation       Charset Id      Default Compiled        Sortlen
 
96
latin1_german1_ci       latin1  5               #       1
 
97
latin1_swedish_ci       latin1  8       Yes     #       1
 
98
latin1_danish_ci        latin1  15              #       1
 
99
latin1_german2_ci       latin1  31              #       2
 
100
latin1_bin      latin1  47              #       1
 
101
latin1_general_ci       latin1  48              #       1
 
102
latin1_general_cs       latin1  49              #       1
 
103
latin1_spanish_ci       latin1  94              #       1
 
104
SHOW COLLATION WHERE collation like 'latin1%';
 
105
Collation       Charset Id      Default Compiled        Sortlen
 
106
latin1_german1_ci       latin1  5               #       1
 
107
latin1_swedish_ci       latin1  8       Yes     #       1
 
108
latin1_danish_ci        latin1  15              #       1
 
109
latin1_german2_ci       latin1  31              #       2
 
110
latin1_bin      latin1  47              #       1
 
111
latin1_general_ci       latin1  48              #       1
 
112
latin1_general_cs       latin1  49              #       1
 
113
latin1_spanish_ci       latin1  94              #       1
 
114
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
 
115
where COLLATION_NAME like 'latin1%';
 
116
COLLATION_NAME  CHARACTER_SET_NAME
 
117
latin1_german1_ci       latin1
 
118
latin1_swedish_ci       latin1
 
119
latin1_danish_ci        latin1
 
120
latin1_german2_ci       latin1
 
121
latin1_bin      latin1
 
122
latin1_general_ci       latin1
 
123
latin1_general_cs       latin1
 
124
latin1_spanish_ci       latin1
 
125
select * from information_schema.table_names;
 
126
ERROR 42S02: Unknown table 'table_names' in information_schema
 
127
select column_type from information_schema.columns
 
128
where table_schema="information_schema" and table_name="COLUMNS" and
 
129
(column_name="character_set_name" or column_name="collation_name");
 
130
column_type
 
131
varchar(64)
 
132
varchar(64)
 
133
select TABLE_ROWS from information_schema.tables where 
 
134
table_schema="information_schema" and table_name="COLUMNS";
 
135
TABLE_ROWS
 
136
NULL
 
137
select table_type from information_schema.tables
 
138
where table_schema="mysql" and table_name="user";
 
139
table_type
 
140
show open tables where `table` like "user";
 
141
Database        Table   In_use  Name_locked
 
142
show status where variable_name like "%database%";
 
143
Variable_name   Value
 
144
Com_show_databases      3
 
145
show variables where variable_name like "skip_show_databas";
 
146
Variable_name   Value
 
147
show global status like "Threads_running";
 
148
Variable_name   Value
 
149
Threads_running #
 
150
select table_schema,table_name, column_name from
 
151
information_schema.columns 
 
152
where data_type = 'longtext';
 
153
table_schema    table_name      column_name
 
154
information_schema      COLUMNS COLUMN_DEFAULT
 
155
information_schema      COLUMNS COLUMN_TYPE
 
156
information_schema      PLUGINS PLUGIN_DESCRIPTION
 
157
information_schema      PROCESSLIST     INFO
 
158
select table_name, column_name, data_type from information_schema.columns
 
159
where data_type = 'datetime';
 
160
table_name      column_name     data_type
 
161
TABLES  CREATE_TIME     datetime
 
162
TABLES  UPDATE_TIME     datetime
 
163
TABLES  CHECK_TIME      datetime
 
164
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
 
165
WHERE NOT EXISTS 
 
166
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
 
167
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
 
168
AND A.TABLE_NAME = B.TABLE_NAME);
 
169
COUNT(*)
 
170
0
 
171
create table t1
 
172
( x_bigint BIGINT,
 
173
x_integer INTEGER,
 
174
x_smallint SMALLINT,
 
175
x_decimal DECIMAL(5,3),
 
176
x_numeric NUMERIC(5,3),
 
177
x_real REAL,
 
178
x_float FLOAT,
 
179
x_double_precision DOUBLE PRECISION );
 
180
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
 
181
FROM INFORMATION_SCHEMA.COLUMNS
 
182
WHERE TABLE_NAME= 't1';
 
183
COLUMN_NAME     CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH
 
184
x_bigint        NULL    NULL
 
185
x_integer       NULL    NULL
 
186
x_smallint      NULL    NULL
 
187
x_decimal       NULL    NULL
 
188
x_numeric       NULL    NULL
 
189
x_real  NULL    NULL
 
190
x_float NULL    NULL
 
191
x_double_precision      NULL    NULL
 
192
drop table t1;
 
193
SELECT table_schema, count(*) FROM information_schema.TABLES
 
194
WHERE table_name NOT LIKE 'ndb_%' AND table_name NOT LIKE 'falcon%' GROUP BY TABLE_SCHEMA;
 
195
table_schema    count(*)
 
196
information_schema      16
 
197
show create database information_schema;
 
198
Database        Create Database
 
199
information_schema      CREATE DATABASE "information_schema" /*!40100 DEFAULT CHARACTER SET utf8 */
 
200
create table t1(f1 LONGBLOB, f2 LONGTEXT);
 
201
select column_name,data_type,CHARACTER_OCTET_LENGTH,
 
202
CHARACTER_MAXIMUM_LENGTH
 
203
from information_schema.columns
 
204
where table_name='t1';
 
205
column_name     data_type       CHARACTER_OCTET_LENGTH  CHARACTER_MAXIMUM_LENGTH
 
206
f1      longblob        4294967295      4294967295
 
207
f2      longtext        4294967295      4294967295
 
208
drop table t1;
 
209
create table t1(f1 tinyint, f2 SMALLINT, f3 BIGINT, f4 int,
 
210
f5 BIGINT, f6 TINYINT, f7 SMALLINT);
 
211
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
 
212
from information_schema.columns
 
213
where table_name='t1';
 
214
column_name     NUMERIC_PRECISION       NUMERIC_SCALE
 
215
f1      3       0
 
216
f2      5       0
 
217
f3      19      0
 
218
f4      10      0
 
219
f5      19      0
 
220
f6      3       0
 
221
f7      5       0
 
222
drop table t1;
 
223
create table t1 (a int not null, b int);
 
224
use information_schema;
 
225
select column_name, column_default from columns
 
226
where table_schema='test' and table_name='t1';
 
227
column_name     column_default
 
228
a       NULL
 
229
b       NULL
 
230
use test;
 
231
show columns from t1;
 
232
Field   Type    Null    Key     Default Extra
 
233
a       int(11) NO              NULL    
 
234
b       int(11) YES             NULL    
 
235
drop table t1;
 
236
CREATE TABLE t1 (a int);
 
237
CREATE TABLE t2 (b int);
 
238
SHOW TABLE STATUS FROM test
 
239
WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 
240
WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');
 
241
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
 
242
t1      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
243
t2      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
244
DROP TABLE t1,t2;
 
245
alter database information_schema;
 
246
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
 
247
drop database information_schema;
 
248
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
 
249
drop table information_schema.tables;
 
250
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
 
251
alter table information_schema.tables;
 
252
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
 
253
use information_schema;
 
254
create temporary table schemata(f1 char(10));
 
255
ERROR 42000: Access denied for user ''@'' to database 'information_schema'
 
256
use test;
 
257
create table t1(id int);
 
258
insert into t1(id) values (1);
 
259
select 1 from (select 1 from test.t1) a;
 
260
1
 
261
1
 
262
use information_schema;
 
263
select 1 from (select 1 from test.t1) a;
 
264
1
 
265
1
 
266
use test;
 
267
drop table t1;
 
268
create temporary table t1(f1 int, index(f1));
 
269
show columns from t1;
 
270
Field   Type    Null    Key     Default Extra
 
271
f1      int(11) YES     MUL     NULL    
 
272
describe t1;
 
273
Field   Type    Null    Key     Default Extra
 
274
f1      int(11) YES     MUL     NULL    
 
275
show indexes from t1;
 
276
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_Comment
 
277
t1      1       f1      1       f1      A       NULL    NULL    NULL    YES     BTREE           
 
278
drop table t1;
 
279
create table t1(f1 binary(32), f2 varbinary(64));
 
280
select character_maximum_length, character_octet_length
 
281
from information_schema.columns where table_name='t1';
 
282
character_maximum_length        character_octet_length
 
283
32      32
 
284
64      64
 
285
drop table t1;
 
286
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
 
287
from information_schema.columns where
 
288
table_schema='information_schema' and
 
289
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
 
290
 or column_type = 'varchar(27)')
 
291
group by column_type order by num;
 
292
column_type     group_concat(table_schema, '.', table_name)     num
 
293
varchar(27)     information_schema.COLUMNS      1
 
294
varchar(20)     information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS        3
 
295
create table t1(f1 char(1) not null, f2 char(9) not null)
 
296
default character set utf8;
 
297
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
 
298
information_schema.columns where table_schema='test' and table_name = 't1';
 
299
CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH
 
300
1       4
 
301
9       36
 
302
drop table t1;
 
303
set @a:= '.';
 
304
create table t1(f1 char(5));
 
305
create table t2(f1 char(5));
 
306
select concat(@a, table_name), @a, table_name
 
307
from information_schema.tables where table_schema = 'test';
 
308
concat(@a, table_name)  @a      table_name
 
309
.t1     .       t1
 
310
.t2     .       t2
 
311
drop table t1,t2;
 
312
SELECT t.table_name, c1.column_name
 
313
FROM information_schema.tables t
 
314
INNER JOIN
 
315
information_schema.columns c1
 
316
ON t.table_schema = c1.table_schema AND
 
317
t.table_name = c1.table_name
 
318
WHERE t.table_schema = 'information_schema' AND
 
319
c1.ordinal_position =
 
320
( SELECT COALESCE(MIN(c2.ordinal_position),1)
 
321
FROM information_schema.columns c2
 
322
WHERE c2.table_schema = t.table_schema AND
 
323
c2.table_name = t.table_name AND
 
324
c2.column_name LIKE '%SCHEMA%'
 
325
        )
 
326
AND t.table_name NOT LIKE 'falcon%';
 
327
table_name      column_name
 
328
CHARACTER_SETS  CHARACTER_SET_NAME
 
329
COLLATIONS      COLLATION_NAME
 
330
COLLATION_CHARACTER_SET_APPLICABILITY   COLLATION_NAME
 
331
COLUMNS TABLE_SCHEMA
 
332
GLOBAL_STATUS   VARIABLE_NAME
 
333
GLOBAL_VARIABLES        VARIABLE_NAME
 
334
KEY_COLUMN_USAGE        CONSTRAINT_SCHEMA
 
335
PLUGINS PLUGIN_NAME
 
336
PROCESSLIST     ID
 
337
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
 
338
SCHEMATA        SCHEMA_NAME
 
339
SESSION_STATUS  VARIABLE_NAME
 
340
SESSION_VARIABLES       VARIABLE_NAME
 
341
STATISTICS      TABLE_SCHEMA
 
342
TABLES  TABLE_SCHEMA
 
343
TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA
 
344
SELECT t.table_name, c1.column_name
 
345
FROM information_schema.tables t
 
346
INNER JOIN
 
347
information_schema.columns c1
 
348
ON t.table_schema = c1.table_schema AND
 
349
t.table_name = c1.table_name
 
350
WHERE t.table_schema = 'information_schema' AND
 
351
c1.ordinal_position =
 
352
( SELECT COALESCE(MIN(c2.ordinal_position),1)
 
353
FROM information_schema.columns c2
 
354
WHERE c2.table_schema = 'information_schema' AND
 
355
c2.table_name = t.table_name AND
 
356
c2.column_name LIKE '%SCHEMA%'
 
357
        )
 
358
AND t.table_name NOT LIKE 'falcon%';
 
359
table_name      column_name
 
360
CHARACTER_SETS  CHARACTER_SET_NAME
 
361
COLLATIONS      COLLATION_NAME
 
362
COLLATION_CHARACTER_SET_APPLICABILITY   COLLATION_NAME
 
363
COLUMNS TABLE_SCHEMA
 
364
GLOBAL_STATUS   VARIABLE_NAME
 
365
GLOBAL_VARIABLES        VARIABLE_NAME
 
366
KEY_COLUMN_USAGE        CONSTRAINT_SCHEMA
 
367
PLUGINS PLUGIN_NAME
 
368
PROCESSLIST     ID
 
369
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
 
370
SCHEMATA        SCHEMA_NAME
 
371
SESSION_STATUS  VARIABLE_NAME
 
372
SESSION_VARIABLES       VARIABLE_NAME
 
373
STATISTICS      TABLE_SCHEMA
 
374
TABLES  TABLE_SCHEMA
 
375
TABLE_CONSTRAINTS       CONSTRAINT_SCHEMA
 
376
SELECT MAX(table_name) FROM information_schema.tables;
 
377
MAX(table_name)
 
378
TABLE_CONSTRAINTS
 
379
SELECT table_name from information_schema.tables
 
380
WHERE table_name=(SELECT MAX(table_name)
 
381
FROM information_schema.tables);
 
382
table_name
 
383
TABLE_CONSTRAINTS
 
384
create table t1 (f1 int(11));
 
385
create table t2 (f1 int(11), f2 int(11));
 
386
select table_name from information_schema.tables
 
387
where table_schema = 'test' and table_name not in
 
388
(select table_name from information_schema.columns
 
389
where table_schema = 'test' and column_name = 'f3');
 
390
table_name
 
391
t1
 
392
t2
 
393
drop table t1,t2;
 
394
select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
 
395
(select cast(table_name as char)  from information_schema.tables
 
396
order by table_name limit 1) limit 1;
 
397
f1
 
398
1
 
399
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
 
400
count(*) as num1
 
401
from information_schema.tables t
 
402
inner join information_schema.columns c1
 
403
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
 
404
where t.table_schema = 'information_schema' AND
 
405
t.table_name not like 'falcon%' AND
 
406
c1.ordinal_position =
 
407
(select isnull(c2.column_type) -
 
408
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
 
409
count(*) as num
 
410
from information_schema.columns c2 where
 
411
c2.table_schema='information_schema' and
 
412
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
 
413
group by c2.column_type order by num limit 1)
 
414
group by t.table_name order by num1, t.table_name;
 
415
table_name      group_concat(t.table_schema, '.', t.table_name) num1
 
416
CHARACTER_SETS  information_schema.CHARACTER_SETS       1
 
417
COLLATIONS      information_schema.COLLATIONS   1
 
418
COLUMNS information_schema.COLUMNS      1
 
419
KEY_COLUMN_USAGE        information_schema.KEY_COLUMN_USAGE     1
 
420
PLUGINS information_schema.PLUGINS      1
 
421
PROCESSLIST     information_schema.PROCESSLIST  1
 
422
REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS      1
 
423
SCHEMATA        information_schema.SCHEMATA     1
 
424
STATISTICS      information_schema.STATISTICS   1
 
425
TABLES  information_schema.TABLES       1
 
426
TABLE_CONSTRAINTS       information_schema.TABLE_CONSTRAINTS    1
 
427
alter database;
 
428
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
 
429
alter database test;
 
430
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
 
431
create table t1 (
 
432
f1 varchar(50),
 
433
f2 varchar(50) not null,
 
434
f3 varchar(50) default '',
 
435
f4 varchar(50) default NULL,
 
436
f5 bigint not null,
 
437
f6 bigint not null default 10,
 
438
f7 datetime not null,
 
439
f8 datetime default '2006-01-01'
 
440
);
 
441
select column_default from information_schema.columns where table_name= 't1';
 
442
column_default
 
443
NULL
 
444
NULL
 
445
NULL
 
446
NULL
 
447
NULL
 
448
NULL
 
449
NULL
 
450
NULL
 
451
show columns from t1;
 
452
Field   Type    Null    Key     Default Extra
 
453
f1      varchar(50)     YES             NULL    
 
454
f2      varchar(50)     NO              NULL    
 
455
f3      varchar(50)     YES             NULL    
 
456
f4      varchar(50)     YES             NULL    
 
457
f5      bigint(20)      NO              NULL    
 
458
f6      bigint(20)      NO              NULL    
 
459
f7      datetime        NO              NULL    
 
460
f8      datetime        YES             NULL    
 
461
drop table t1;
 
462
show fields from information_schema.table_names;
 
463
ERROR 42S02: Unknown table 'table_names' in information_schema
 
464
show keys from information_schema.table_names;
 
465
ERROR 42S02: Unknown table 'table_names' in information_schema
 
466
SET max_heap_table_size = DEFAULT;
 
467
USE test;
 
468
End of 5.0 tests.
 
469
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
 
470
WHERE SCHEMA_NAME ='information_schema';
 
471
SCHEMA_NAME
 
472
information_schema
 
473
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
 
474
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
 
475
TABLE_COLLATION
 
476
select * from information_schema.columns where table_schema = NULL;
 
477
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
 
478
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
 
479
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
 
480
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
 
481
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
 
482
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
 
483
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
 
484
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
 
485
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
 
486
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
 
487
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
 
488
select * from information_schema.schemata where schema_name = NULL;
 
489
CATALOG_NAME    SCHEMA_NAME     DEFAULT_CHARACTER_SET_NAME      DEFAULT_COLLATION_NAME  SQL_PATH
 
490
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
 
491
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
 
492
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
 
493
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
 
494
select * from information_schema.tables where table_schema = NULL;
 
495
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
 
496
select * from information_schema.tables where table_catalog = NULL;
 
497
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
 
498
select * from information_schema.tables where table_name = NULL;
 
499
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
 
500
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
 
501
CONSTRAINT_CATALOG      CONSTRAINT_SCHEMA       CONSTRAINT_NAME TABLE_SCHEMA    TABLE_NAME      CONSTRAINT_TYPE
 
502
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
 
503
CONSTRAINT_CATALOG      CONSTRAINT_SCHEMA       CONSTRAINT_NAME TABLE_SCHEMA    TABLE_NAME      CONSTRAINT_TYPE