1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
create database mysqltest;
3
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
4
create table test.t2(a int);
5
create table t3(a int, KEY a_data (a));
6
create table mysqltest.t4(a int);
7
create table t5 (id int auto_increment primary key);
8
insert into t5 values (10);
9
select table_name from data_dictionary.tables
10
where table_schema = "mysqltest" and table_name like "t%";
14
select * from data_dictionary.indexes where TABLE_SCHEMA = "mysqltest";
15
TABLE_SCHEMA TABLE_NAME INDEX_NAME IS_USED_IN_PRIMARY IS_UNIQUE IS_NULLABLE KEY_LENGTH INDEX_TYPE INDEX_COMMENT
16
mysqltest t1 string_data NO NO YES 120 UNKNOWN NULL
17
show tables like 't%';
23
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
24
# test t5 STANDARD PBXT # # # # #
25
show columns from t3 like "a%";
26
Field Type Null Default Default_is_NULL On_Update
28
select * from data_dictionary.columns where table_name="t1"
30
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
31
mysqltest t1 a INTEGER 0 NULL YES YES NO NO NO NO NO 0 INTEGER 0 0 0 0 NULL
32
select table_name, column_name from data_dictionary.columns
33
where table_schema = 'mysqltest' and table_name = 't1';
34
table_name column_name
37
show columns from mysqltest.t1;
38
Field Type Null Default Default_is_NULL On_Update
41
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
42
drop database mysqltest;
43
select column_type from data_dictionary.columns
44
where table_schema="data_dictionary" and table_name="COLUMNS" and
45
(column_name="character_set_name" or column_name="collation_name");
48
select count(*) from data_dictionary.tables where
49
table_schema="data_dictionary" and table_name="COLUMNS";
52
select count(*) from data_dictionary.tables
53
where table_schema="mysql" and table_name="user";
56
select table_schema, table_name, column_name from data_dictionary.columns where data_type = 'longtext';
57
table_schema table_name column_name
58
select table_name, column_name, data_type from data_dictionary.columns where data_type = 'datetime';
59
table_name column_name data_type
60
SELECT COUNT(*) FROM data_dictionary.tables A
62
(SELECT * FROM data_dictionary.columns B
63
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
64
AND A.TABLE_NAME = B.TABLE_NAME);
71
x_decimal DECIMAL(5,3),
72
x_numeric NUMERIC(5,3),
75
x_double_precision DOUBLE PRECISION );
76
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
77
FROM data_dictionary.columns
78
WHERE TABLE_NAME= 't1';
79
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
82
x_double_precision 0 0
89
SELECT table_schema, count(*) FROM data_dictionary.tables
90
WHERE table_name NOT LIKE 'ndb_%' AND
91
table_name NOT LIKE 'falcon%'
92
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
96
create table t1(f1 LONGBLOB, f2 LONGTEXT);
97
select column_name,data_type,CHARACTER_OCTET_LENGTH,
98
CHARACTER_MAXIMUM_LENGTH
99
from data_dictionary.columns
100
where table_name='t1';
101
column_name data_type CHARACTER_OCTET_LENGTH CHARACTER_MAXIMUM_LENGTH
105
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
106
f5 BIGINT, f6 int, f7 int);
107
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
108
from data_dictionary.columns
109
where table_name='t1';
110
column_name NUMERIC_PRECISION NUMERIC_SCALE
119
create table t1 (a int not null, b int);
120
select column_name, column_default from data_dictionary.columns
121
where table_schema='test' and table_name='t1';
122
column_name column_default
126
show columns from t1;
127
Field Type Null Default Default_is_NULL On_Update
132
create table t1(id int);
133
insert into t1(id) values (1);
134
select 1 from (select 1 from test.t1) a;
138
select 1 from (select 1 from test.t1) a;
143
create table t1(f1 varbinary(32), f2 varbinary(64));
144
select character_maximum_length, character_octet_length
145
from data_dictionary.columns where table_name='t1';
146
character_maximum_length character_octet_length
150
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
151
from data_dictionary.columns where
152
table_schema='data_dictionary' and
153
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
154
or column_type = 'varchar(27)')
155
group by column_type order by column_type, num;
156
column_type group_concat(table_schema, '.', table_name) num
157
create table t1(f1 char(1) not null, f2 char(9) not null);
158
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
159
data_dictionary.columns where table_schema='test' and table_name = 't1';
160
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
165
create table t1(f1 char(5));
166
create table t2(f1 char(5));
167
select concat(@a, table_name), @a, table_name
168
from data_dictionary.tables where table_schema = 'test';
169
concat(@a, table_name) @a table_name
173
SELECT t.table_name, c1.column_name
174
FROM data_dictionary.tables t
176
data_dictionary.columns c1
177
ON t.table_schema = c1.table_schema AND
178
t.table_name = c1.table_name
179
WHERE t.table_schema = 'data_dictionary' AND
180
c1.ordinal_position =
181
( SELECT COALESCE(MIN(c2.ordinal_position),1)
182
FROM data_dictionary.columns c2
183
WHERE c2.table_schema = t.table_schema AND
184
c2.table_name = t.table_name AND
185
c2.column_name LIKE '%SCHEMA%'
187
AND t.table_name NOT LIKE 'falcon%'
188
ORDER BY t.table_name, c1.column_name;
189
table_name column_name
190
CHARACTER_SETS DEFAULT_COLLATE_NAME
191
COLLATIONS COLLATION_NAME
193
CUMULATIVE_SQL_COMMANDS COUNT_SELECT
194
CUMULATIVE_USER_STATS BYTES_RECEIVED
195
CURRENT_SQL_COMMANDS IP
196
DRIZZLE_PROTOCOL_STATUS VARIABLE_VALUE
197
GLOBAL_STATEMENTS VARIABLE_VALUE
198
GLOBAL_STATUS VARIABLE_VALUE
199
GLOBAL_VARIABLES VARIABLE_VALUE
201
INDEX_PARTS TABLE_SCHEMA
202
INNODB_CMP COMPRESS_OPS
203
INNODB_CMPMEM PAGES_USED
204
INNODB_CMPMEM_RESET PAGES_USED
205
INNODB_CMP_RESET COMPRESS_OPS
206
INNODB_LOCKS LOCK_TRX_ID
207
INNODB_LOCK_WAITS REQUESTED_LOCK_ID
208
INNODB_STATUS VARIABLE_VALUE
210
MODULES MODULE_VERSION
211
MYSQL_PROTOCOL_STATUS VARIABLE_VALUE
214
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
215
REPLICATION_STREAMS APPLIER
217
SCOREBOARD_STATISTICS NUMBER_OF_RANGE_LOCKS
218
SESSION_STATEMENTS VARIABLE_VALUE
219
SESSION_STATUS VARIABLE_VALUE
220
SESSION_VARIABLES VARIABLE_VALUE
223
SHOW_SCHEMAS SCHEMA_NAME
224
SHOW_TABLE_STATUS Schema
225
SHOW_TEMPORARY_TABLES TABLE_SCHEMA
227
TABLE_CACHE TABLE_SCHEMA
228
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
229
TABLE_DEFINITION_CACHE TABLE_SCHEMA
230
SELECT t.table_name, c1.column_name
231
FROM data_dictionary.tables t
233
data_dictionary.columns c1
234
ON t.table_schema = c1.table_schema AND
235
t.table_name = c1.table_name
236
WHERE t.table_schema = 'data_dictionary' AND
237
c1.ordinal_position =
238
( SELECT COALESCE(MIN(c2.ordinal_position),1)
239
FROM data_dictionary.columns c2
240
WHERE c2.table_schema = 'data_dictionary' AND
241
c2.table_name = t.table_name AND
242
c2.column_name LIKE '%SCHEMA%'
244
AND t.table_name NOT LIKE 'falcon%'
245
ORDER BY t.table_name, c1.column_name;
246
table_name column_name
247
CHARACTER_SETS DEFAULT_COLLATE_NAME
248
COLLATIONS COLLATION_NAME
250
CUMULATIVE_SQL_COMMANDS COUNT_SELECT
251
CUMULATIVE_USER_STATS BYTES_RECEIVED
252
CURRENT_SQL_COMMANDS IP
253
DRIZZLE_PROTOCOL_STATUS VARIABLE_VALUE
254
GLOBAL_STATEMENTS VARIABLE_VALUE
255
GLOBAL_STATUS VARIABLE_VALUE
256
GLOBAL_VARIABLES VARIABLE_VALUE
258
INDEX_PARTS TABLE_SCHEMA
259
INNODB_CMP COMPRESS_OPS
260
INNODB_CMPMEM PAGES_USED
261
INNODB_CMPMEM_RESET PAGES_USED
262
INNODB_CMP_RESET COMPRESS_OPS
263
INNODB_LOCKS LOCK_TRX_ID
264
INNODB_LOCK_WAITS REQUESTED_LOCK_ID
265
INNODB_STATUS VARIABLE_VALUE
267
MODULES MODULE_VERSION
268
MYSQL_PROTOCOL_STATUS VARIABLE_VALUE
271
REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
272
REPLICATION_STREAMS APPLIER
274
SCOREBOARD_STATISTICS NUMBER_OF_RANGE_LOCKS
275
SESSION_STATEMENTS VARIABLE_VALUE
276
SESSION_STATUS VARIABLE_VALUE
277
SESSION_VARIABLES VARIABLE_VALUE
280
SHOW_SCHEMAS SCHEMA_NAME
281
SHOW_TABLE_STATUS Schema
282
SHOW_TEMPORARY_TABLES TABLE_SCHEMA
284
TABLE_CACHE TABLE_SCHEMA
285
TABLE_CONSTRAINTS CONSTRAINT_SCHEMA
286
TABLE_DEFINITION_CACHE TABLE_SCHEMA
287
SELECT MAX(table_name) FROM data_dictionary.tables;
290
SELECT table_name from data_dictionary.tables
291
WHERE table_name=(SELECT MAX(table_name)
292
FROM data_dictionary.tables)
296
create table t1 (f1 int);
297
create table t2 (f1 int, f2 int);
299
select 1 as f1 from data_dictionary.tables where "CHARACTER_SETS"=
300
(select cast(table_name as char) from data_dictionary.tables
301
order by table_name limit 1) limit 1;
304
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
306
from data_dictionary.tables t
307
inner join data_dictionary.columns c1
308
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
309
where t.table_schema = 'data_dictionary' AND
310
t.table_name not like 'falcon%' AND
311
c1.ordinal_position =
312
(select isnull(c2.column_type) -
313
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
315
from data_dictionary.columns c2 where
316
c2.table_schema='data_dictionary' and
317
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
318
group by c2.column_type order by num limit 1)
319
group by t.table_name order by num1, t.table_name;
320
table_name group_concat(t.table_schema, '.', t.table_name) num1
322
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
324
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
327
f2 varchar(50) not null,
328
f3 varchar(50) default '',
329
f4 varchar(50) default NULL,
331
f6 bigint not null default 10,
332
f7 datetime not null,
333
f8 datetime default '2006-01-01'
335
show columns from t1;
336
Field Type Null Default Default_is_NULL On_Update
344
f8 DATETIME YES 2006-01-01 00:00:00 NO
346
SET max_heap_table_size = DEFAULT;
349
SELECT SCHEMA_NAME FROM data_dictionary.schemas
350
WHERE SCHEMA_NAME ='data_dictionary';
353
SELECT TABLE_COLLATION FROM data_dictionary.tables
354
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
357
# Test that the query is visible to self and others.
359
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
361
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID()
362
SELECT info, command, db
363
FROM data_dictionary.processlist
364
WHERE id = CONNECTION_ID();
366
FROM data_dictionary.processlist
367
SELECT info, command, db
368
WHERE id = CONNECTION_ID() Query test