1
1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
2
DROP SCHEMA IF EXISTS data_dictionary;
3
CREATE SCHEMA data_dictionary;
4
3
select count(*) from data_dictionary.SCHEMAS where schema_name > 'm';
7
6
select count(*) from data_dictionary.schemas;
9
show databases like 't%';
10
18
create database mysqltest;
11
19
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
12
20
create table test.t2(a int);
30
show tables like 't%';
36
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
37
t2 InnoDB 0 DEFAULT 0 0 0 # 0 0 0 # # 0 utf8_general_ci 0
38
t3 InnoDB 0 DEFAULT 0 0 0 # 0 0 0 # # 0 utf8_general_ci 0
39
t5 InnoDB 0 DEFAULT 0 0 0 # 0 0 0 # # 0 utf8_general_ci 0
40
show columns from t3 like "a%";
41
Field Type Null Default Default is NULL On Update
22
42
select * from data_dictionary.COLUMNS where table_name="t1"
23
43
and column_name= "a";
24
TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_KEY EXTRA COLUMN_COMMENT
25
mysqltest t1 a 0 0 TRUE INTEGER # 0 0 0 # #
44
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
45
mysqltest t1 a INTEGER 0 TRUE TRUE INTEGER 0 0 0 0
26
46
select table_name, column_name from data_dictionary.columns
27
47
where table_schema = 'mysqltest' and table_name = 't1';
28
48
table_name column_name
51
show columns from mysqltest.t1;
52
Field Type Null Default Default is NULL On Update
31
55
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
32
56
drop database mysqltest;
33
57
select * from data_dictionary.CHARACTER_SETS
55
79
select table_name, column_name, DATA_TYPE from data_dictionary.columns
56
80
where DATA_TYPE = 'datetime';
57
81
table_name column_name DATA_TYPE
58
INNODB_TRX trx_started DATETIME
59
INNODB_TRX trx_wait_started DATETIME
60
82
SELECT COUNT(*) FROM DATA_DICTIONARY.TABLES A
62
84
(SELECT * FROM DATA_DICTIONARY.COLUMNS B
120
142
select column_name, column_default from columns
121
143
where table_schema='test' and table_name='t1';
122
144
column_name column_default
148
show columns from t1;
149
Field Type Null Default Default is NULL On Update
150
a INTEGER FALSE FALSE
127
153
alter database data_dictionary;
128
154
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
145
171
create temporary table t1(f1 int, index(f1));
172
show columns from t1;
173
Field Type Null Default Default is NULL On Update
176
Field Type Null Default Default is NULL On Update
178
show indexes from t1;
179
Table Key_name Seq_in_index Column_name
147
182
create table t1(f1 varbinary(32), f2 varbinary(64));
148
183
select character_maximum_length, character_octet_length
158
193
or DATA_TYPE = 'varchar')
159
194
group by DATA_TYPE order by DATA_TYPE, num;
160
195
DATA_TYPE group_concat(table_schema, '.', table_name) num
161
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.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_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INNODB_STATUS, 105
196
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.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_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INDEX_PARTS,data_dictionary.INDEX_PARTS,data_d 106
163
198
Warning 1260 1 line(s) were cut by GROUP_CONCAT()
164
199
create table t1(f1 char(1) not null, f2 char(9) not null);
288
333
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
290
335
select * from data_dictionary.columns where table_schema = NULL;
291
TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_KEY EXTRA COLUMN_COMMENT
336
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
292
337
select * from `data_dictionary`.`COLUMNS` where `TABLE_NAME` = NULL;
293
TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_KEY EXTRA COLUMN_COMMENT
338
TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_TYPE ORDINAL_POSITION COLUMN_DEFAULT COLUMN_DEFAULT_IS_NULL COLUMN_DEFAULT_UPDATE IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE COLLATION_NAME COLUMN_COMMENT
294
339
select * from `data_dictionary`.`INDEXES` where `TABLE_SCHEMA` = NULL;
295
340
TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
296
341
select * from `data_dictionary`.`INDEXES` where `TABLE_NAME` = NULL;