1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
DROP SCHEMA IF EXISTS data_dictionary;
3
select count(*) from data_dictionary.SCHEMAS where schema_name > 'm';
6
select count(*) from data_dictionary.schemas;
9
show databases like 't%';
18
create database mysqltest;
19
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
20
create table test.t2(a int);
21
create table t3(a int, KEY a_data (a));
22
create table mysqltest.t4(a int);
23
create table t5 (id int auto_increment primary key);
24
insert into t5 values (10);
25
select table_name from data_dictionary.TABLES
26
where table_schema = "mysqltest" and table_name like "t%";
30
show tables like 't%';
36
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
37
# test t5 STANDARD InnoDB # # # # #
38
show columns from t3 like "a%";
39
Field Type Null Default Default_is_NULL On_Update
41
select * from data_dictionary.COLUMNS where table_name="t1"
43
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
44
mysqltest t1 a INTEGER 0 TRUE TRUE FALSE FALSE FALSE FALSE FALSE 0 INTEGER 0 0 0 0
45
select table_name, column_name from data_dictionary.columns
46
where table_schema = 'mysqltest' and table_name = 't1';
47
table_name column_name
50
show columns from mysqltest.t1;
51
Field Type Null Default Default_is_NULL On_Update
54
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
55
drop database mysqltest;
56
select * from data_dictionary.CHARACTER_SETS
57
where CHARACTER_SET_NAME like 'latin1%';
58
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
59
select * from data_dictionary.COLLATIONS
60
where COLLATION_NAME like 'latin1%';
61
CHARACTER_SET_NAME COLLATION_NAME DESCRIPTION ID IS_DEFAULT IS_COMPILED SORTLEN
62
select DATA_TYPE from data_dictionary.columns
63
where table_schema="data_dictionary" and table_name="COLUMNS" and
64
(column_name="character_set_name" or column_name="collation_name");
67
select TABLE_TYPE from data_dictionary.tables where
68
table_schema="data_dictionary" and table_name="COLUMNS";
71
select table_type from data_dictionary.tables
72
where table_schema="mysql" and table_name="user";
74
select table_schema,table_name, column_name from
75
data_dictionary.columns
76
where DATA_TYPE = 'longtext';
77
table_schema table_name column_name
78
select table_name, column_name, DATA_TYPE from data_dictionary.columns
79
where DATA_TYPE = 'datetime';
80
table_name column_name DATA_TYPE
81
SELECT COUNT(*) FROM DATA_DICTIONARY.TABLES A
83
(SELECT * FROM DATA_DICTIONARY.COLUMNS B
84
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
85
AND A.TABLE_NAME = B.TABLE_NAME);
92
x_decimal DECIMAL(5,3),
93
x_numeric NUMERIC(5,3),
96
x_double_precision DOUBLE PRECISION );
97
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
98
FROM DATA_DICTIONARY.COLUMNS
99
WHERE TABLE_NAME= 't1';
100
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
108
x_double_precision 0 0
110
SELECT table_schema, count(*) FROM data_dictionary.TABLES
111
WHERE table_name NOT LIKE 'ndb_%' AND
112
table_name NOT LIKE 'falcon%' AND
114
GROUP BY TABLE_SCHEMA;
115
table_schema count(*)
116
create table t1(f1 LONGBLOB, f2 LONGTEXT);
117
select column_name, DATA_TYPE, CHARACTER_OCTET_LENGTH,
118
CHARACTER_MAXIMUM_LENGTH
119
from data_dictionary.columns
120
where table_name='t1';
121
column_name DATA_TYPE CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
125
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
126
f5 BIGINT, f6 int, f7 int);
127
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
128
from data_dictionary.columns
129
where table_name='t1';
130
column_name NUMERIC_PRECISION NUMERIC_SCALE
139
create table t1 (a int not null, b int);
141
select column_name, column_default from columns
142
where table_schema='test' and table_name='t1';
143
column_name column_default
147
show columns from t1;
148
Field Type Null Default Default_is_NULL On_Update
149
a INTEGER FALSE FALSE
152
alter database data_dictionary;
153
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
156
create temporary table schemas(f1 char(10));
157
ERROR 42000: Access denied for user ''@'' to database 'data_dictionary'
159
create table t1(id int);
160
insert into t1(id) values (1);
161
select 1 from (select 1 from test.t1) a;
165
select 1 from (select 1 from test.t1) a;
170
create temporary table t1(f1 int, index(f1));
171
show columns from t1;
172
Field Type Null Default Default_is_NULL On_Update
175
Field Type Null Default Default_is_NULL On_Update
177
show indexes from t1;
178
Table Unique Key_name Seq_in_index Column_name
181
create table t1(f1 varbinary(32), f2 varbinary(64));
182
select character_maximum_length, character_octet_length
183
from data_dictionary.columns where table_name='t1';
184
character_maximum_length character_octet_length
188
select DATA_TYPE, group_concat(table_schema, '.', table_name), count(*) as num
189
from data_dictionary.columns where
190
table_schema='data_dictionary' and
191
(DATA_TYPE = 'varchar' or DATA_TYPE = 'varchar'
192
or DATA_TYPE = 'varchar')
193
group by DATA_TYPE order by DATA_TYPE, num;
194
DATA_TYPE group_concat(table_schema, '.', table_name) num
195
VARCHAR data_dictionary.CHARACTER_SETS,data_dictionary.CHARACTER_SETS,data_dictionary.CHARACTER_SETS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.GLOBAL_STATEMENTS,data_dictionary.GLOBAL_STATEMENTS,data_dictionary.GLOBAL_STATUS,data_dictionary.GLOBAL_STATUS,data_dictionary.GLOBAL_VARIABLES,data_dictionary.GLOBAL_VARIABLES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEX_PART 139
197
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
198
create table t1(f1 char(1) not null, f2 char(9) not null);
199
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
200
data_dictionary.columns where table_schema='test' and table_name = 't1';
201
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
206
create table t1(f1 char(5));
207
create table t2(f1 char(5));
208
select concat(@a, table_name), @a, table_name
209
from data_dictionary.tables where table_schema = 'test';
210
concat(@a, table_name) @a table_name
214
SELECT t.table_name, c1.column_name
215
FROM data_dictionary.tables t
217
data_dictionary.columns c1
218
ON t.table_schema = c1.table_schema AND
219
t.table_name = c1.table_name
220
WHERE t.table_schema = 'data_dictionary' AND
221
c1.ordinal_position =
222
( SELECT COALESCE(MIN(c2.ordinal_position),1)
223
FROM data_dictionary.columns c2
224
WHERE c2.table_schema = t.table_schema AND
225
c2.table_name = t.table_name AND
226
c2.column_name LIKE '%SCHEMA%'
228
AND t.table_name NOT LIKE 'falcon%'
229
AND t.ENGINE IS NULL;
230
table_name column_name
231
SELECT t.table_name, c1.column_name
232
FROM data_dictionary.tables t
234
data_dictionary.columns c1
235
ON t.table_schema = c1.table_schema AND
236
t.table_name = c1.table_name
237
WHERE t.table_schema = 'data_dictionary' AND
238
c1.ordinal_position =
239
( SELECT COALESCE(MIN(c2.ordinal_position),1)
240
FROM data_dictionary.columns c2
241
WHERE c2.table_schema = 'data_dictionary' AND
242
c2.table_name = t.table_name AND
243
c2.column_name LIKE '%SCHEMA%'
245
AND t.table_name NOT LIKE 'falcon%'
246
AND t.ENGINE IS NULL;
247
table_name column_name
248
SELECT MAX(table_name) FROM data_dictionary.tables;
250
TABLE_DEFINITION_CACHE
251
SELECT table_name from data_dictionary.tables
252
WHERE table_name=(SELECT MAX(table_name)
253
FROM data_dictionary.tables);
255
TABLE_DEFINITION_CACHE
256
create table t1 (f1 int);
257
create table t2 (f1 int, f2 int);
258
select table_name from data_dictionary.tables
259
where table_schema = 'test' and table_name not in
260
(select table_name from data_dictionary.columns
261
where table_schema = 'test' and column_name = 'f3');
266
select 1 as f1 from data_dictionary.tables where "CHARACTER_SETS"=
267
(select cast(table_name as char) from data_dictionary.tables
268
order by table_name limit 1) limit 1;
271
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
273
from data_dictionary.tables t
274
inner join data_dictionary.columns c1
275
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
276
where t.table_schema = 'data_dictionary' AND
277
t.table_name not like 'falcon%' AND
279
c1.ordinal_position =
280
(select isnull(c2.DATA_TYPE) -
281
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
283
from data_dictionary.columns c2 where
284
c2.table_schema='data_dictionary' and
285
(c2.DATA_TYPE = 'varchar' or c2.DATA_TYPE = 'varchar')
286
group by c2.DATA_TYPE order by num limit 1)
287
group by t.table_name order by num1, t.table_name;
288
table_name group_concat(t.table_schema, '.', t.table_name) num1
290
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
292
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
295
f2 varchar(50) not null,
296
f3 varchar(50) default '',
297
f4 varchar(50) default NULL,
299
f6 bigint not null default 10,
300
f7 datetime not null,
301
f8 datetime default '2006-01-01'
303
select column_default from data_dictionary.columns where table_name= 't1';
313
show columns from t1;
314
Field Type Null Default Default_is_NULL On_Update
316
f2 VARCHAR FALSE FALSE
317
f3 VARCHAR TRUE FALSE
319
f5 BIGINT FALSE FALSE
320
f6 BIGINT FALSE 10 FALSE
321
f7 DATETIME FALSE 10 FALSE
322
f8 DATETIME TRUE 2006-01-01 FALSE
324
SET max_heap_table_size = DEFAULT;
327
SELECT SCHEMA_NAME FROM DATA_DICTIONARY.SCHEMAS
328
WHERE SCHEMA_NAME ='data_dictionary';
331
SELECT TABLE_COLLATION FROM DATA_DICTIONARY.TABLES
332
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
334
select * from data_dictionary.columns where table_schema = NULL;
335
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
336
select * from `data_dictionary`.`COLUMNS` where `TABLE_NAME` = NULL;
337
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE IS_INDEXED IS_USED_IN_PRIMARY IS_UNIQUE IS_MULTI IS_FIRST_IN_MULTI INDEXES_FOUND_IN DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
338
select * from `data_dictionary`.`INDEXES` where `TABLE_SCHEMA` = NULL;
339
TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_USED_IN_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
340
select * from `data_dictionary`.`INDEXES` where `TABLE_NAME` = NULL;
341
TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_USED_IN_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
342
select * from data_dictionary.schemas where schema_name = NULL;
343
SCHEMA_NAME DEFAULT_COLLATION_NAME
344
select * from data_dictionary.tables where table_schema = NULL;
345
TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION TABLE_COMMENT
346
select * from data_dictionary.tables where table_name = NULL;
347
TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE ROW_FORMAT TABLE_COLLATION TABLE_COMMENT
349
# Test that the query is visible to self and others.
351
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
353
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID()
354
SELECT * FROM data_dictionary.character_sets ORDER BY character_set_name;
355
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
358
SELECT * FROM data_dictionary.collations ORDER BY collation_name;
359
CHARACTER_SET_NAME COLLATION_NAME DESCRIPTION ID IS_DEFAULT IS_COMPILED SORTLEN
360
binary binary binary 63 TRUE TRUE 1
361
utf8_general_ci utf8_bin utf8 46 FALSE TRUE 1
362
utf8_general_ci utf8_czech_ci utf8 234 FALSE TRUE 8
363
utf8_general_ci utf8_danish_ci utf8 235 FALSE TRUE 8
364
utf8_general_ci utf8_esperanto_ci utf8 241 FALSE TRUE 8
365
utf8_general_ci utf8_estonian_ci utf8 230 FALSE TRUE 8
366
utf8_general_ci utf8_general_ci utf8 45 TRUE TRUE 1
367
utf8_general_ci utf8_hungarian_ci utf8 242 FALSE TRUE 8
368
utf8_general_ci utf8_icelandic_ci utf8 225 FALSE TRUE 8
369
utf8_general_ci utf8_latvian_ci utf8 226 FALSE TRUE 8
370
utf8_general_ci utf8_lithuanian_ci utf8 236 FALSE TRUE 8
371
utf8_general_ci utf8_persian_ci utf8 240 FALSE TRUE 8
372
utf8_general_ci utf8_polish_ci utf8 229 FALSE TRUE 8
373
utf8_general_ci utf8_romanian_ci utf8 227 FALSE TRUE 8
374
utf8_general_ci utf8_roman_ci utf8 239 FALSE TRUE 8
375
utf8_general_ci utf8_sinhala_ci utf8 243 FALSE TRUE 8
376
utf8_general_ci utf8_slovak_ci utf8 237 FALSE TRUE 8
377
utf8_general_ci utf8_slovenian_ci utf8 228 FALSE TRUE 8
378
utf8_general_ci utf8_spanish2_ci utf8 238 FALSE TRUE 8
379
utf8_general_ci utf8_spanish_ci utf8 231 FALSE TRUE 8
380
utf8_general_ci utf8_swedish_ci utf8 232 FALSE TRUE 8
381
utf8_general_ci utf8_turkish_ci utf8 233 FALSE TRUE 8
382
utf8_general_ci utf8_unicode_ci utf8 224 FALSE TRUE 8
383
SELECT table_name, column_name
384
FROM data_dictionary.columns
386
(SELECT table_name FROM data_dictionary.tables
387
WHERE ENGINE IS NULL)
389
table_name column_name
390
SELECT count(schema_name) FROM data_dictionary.schemas ORDER BY schema_name;
393
SELECT count(*) FROM data_dictionary.session_status ORDER BY variable_name;
396
SELECT count(*) FROM data_dictionary.session_variables ORDER BY variable_name;
399
SELECT count(*) FROM data_dictionary.global_status ORDER BY variable_name;
402
SELECT count(*) FROM data_dictionary.global_variables ORDER BY variable_name;
405
SELECT table_schema, table_name FROM data_dictionary.tables WHERE ENGINE IS NULL ORDER BY table_name;
406
table_schema table_name
407
SELECT count(*) FROM data_dictionary.plugins;