1
1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
2
select * from information_schema.SCHEMATA where schema_name > 'm';
3
3
CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH
4
NULL mysql latin1 latin1_swedish_ci NULL
5
NULL test latin1 latin1_swedish_ci NULL
4
NULL mysql utf8 utf8_general_ci NULL
5
NULL test utf8 utf8_general_ci NULL
6
6
select schema_name from information_schema.schemata;
40
40
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
41
t2 InnoDB 10 Compact 0 0 16384 # 0 0 NULL # # NULL latin1_swedish_ci NULL
42
t3 InnoDB 10 Compact 0 0 16384 # 16384 0 NULL # # NULL latin1_swedish_ci NULL
43
t5 InnoDB 10 Compact 1 16384 16384 # 0 0 11 # # NULL latin1_swedish_ci NULL
41
t2 InnoDB 10 Compact 0 0 16384 # 0 0 NULL # # NULL utf8_general_ci NULL
42
t3 InnoDB 10 Compact 0 0 16384 # 16384 0 NULL # # NULL utf8_general_ci NULL
43
t5 InnoDB 10 Compact 1 16384 16384 # 0 0 11 # # NULL utf8_general_ci NULL
44
44
show full columns from t3 like "a%";
45
45
Field Type Collation Null Key Default Extra Privileges Comment
46
46
a int NULL YES MUL NULL
62
62
select * from information_schema.CHARACTER_SETS
63
63
where CHARACTER_SET_NAME like 'latin1%';
64
64
CHARACTER_SET_NAME DEFAULT_COLLATE_NAME DESCRIPTION MAXLEN
65
latin1 latin1_swedish_ci cp1252 West European 1
66
65
SHOW CHARACTER SET LIKE 'latin1%';
67
66
Charset Description Default collation Maxlen
68
latin1 cp1252 West European latin1_swedish_ci 1
69
67
select * from information_schema.COLLATIONS
70
68
where COLLATION_NAME like 'latin1%';
71
69
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
72
latin1_german1_ci latin1 5 # 1
73
latin1_swedish_ci latin1 8 Yes # 1
74
latin1_danish_ci latin1 15 # 1
75
latin1_german2_ci latin1 31 # 2
76
latin1_bin latin1 47 # 1
77
latin1_general_ci latin1 48 # 1
78
latin1_general_cs latin1 49 # 1
79
latin1_spanish_ci latin1 94 # 1
80
70
SHOW COLLATION LIKE 'latin1%';
81
71
Collation Charset Id Default Compiled Sortlen
82
latin1_german1_ci latin1 5 # 1
83
latin1_swedish_ci latin1 8 Yes # 1
84
latin1_danish_ci latin1 15 # 1
85
latin1_german2_ci latin1 31 # 2
86
latin1_bin latin1 47 # 1
87
latin1_general_ci latin1 48 # 1
88
latin1_general_cs latin1 49 # 1
89
latin1_spanish_ci latin1 94 # 1
90
72
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
91
73
where COLLATION_NAME like 'latin1%';
92
74
COLLATION_NAME CHARACTER_SET_NAME
93
latin1_german1_ci latin1
94
latin1_swedish_ci latin1
95
latin1_danish_ci latin1
96
latin1_german2_ci latin1
98
latin1_general_ci latin1
99
latin1_general_cs latin1
100
latin1_spanish_ci latin1
101
75
select * from information_schema.table_names;
102
76
ERROR 42S02: Unknown table 'table_names' in information_schema
103
77
select column_type from information_schema.columns
252
226
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
253
227
information_schema.columns where table_schema='test' and table_name = 't1';
254
228
CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH
259
233
create table t1(f1 char(5));
added
removed
tests/r/information_schema.result
