← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

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

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

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