38
38
KEY (options,flags)
40
40
show fields from t1;
41
Field Type Null Key Default Extra
42
auto int NO PRI NULL auto_increment
43
string varchar(10) YES hello
48
longlong bigint NO MUL 0
49
real_float double NO MUL 0
50
real_double double YES NULL
55
ulonglong bigint NO MUL 0
56
time_stamp timestamp YES NULL
57
date_field date YES NULL
58
date_time datetime YES NULL
59
blob_col blob YES NULL
60
tinyblob_col blob YES NULL
61
mediumblob_col blob NO NULL
62
longblob_col blob NO NULL
63
options enum('one','two','tree') NO MUL NULL
64
flags enum('one','two','tree') NO one
41
Field Type Null Default Default_is_NULL On_Update
42
auto INTEGER FALSE FALSE
43
string VARCHAR TRUE hello FALSE
44
tiny INTEGER FALSE 0 FALSE
45
short INTEGER FALSE 1 FALSE
46
medium INTEGER FALSE 0 FALSE
47
long_int INTEGER FALSE 0 FALSE
48
longlong BIGINT FALSE 0 FALSE
49
real_float DOUBLE FALSE 0.0 FALSE
50
real_double DOUBLE TRUE 0.0 TRUE
51
utiny INTEGER FALSE 0 FALSE
52
ushort INTEGER FALSE 00000 FALSE
53
umedium INTEGER FALSE 0 FALSE
54
ulong INTEGER FALSE 0 FALSE
55
ulonglong BIGINT FALSE 0 FALSE
56
time_stamp TIMESTAMP TRUE 0 TRUE
57
date_field DATE TRUE 0 TRUE
58
date_time DATETIME TRUE 0 TRUE
59
blob_col BLOB TRUE 0 TRUE
60
tinyblob_col BLOB TRUE 0 TRUE
61
mediumblob_col BLOB FALSE 0 FALSE
62
longblob_col BLOB FALSE 0 FALSE
63
options ENUM FALSE 0 FALSE
64
flags ENUM FALSE one FALSE
66
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
67
t1 0 PRIMARY 1 auto A 0 NULL NULL BTREE
68
t1 1 utiny 1 utiny A 0 NULL NULL BTREE
69
t1 1 tiny 1 tiny A 0 NULL NULL BTREE
70
t1 1 short 1 short A 0 NULL NULL BTREE
71
t1 1 any_name 1 medium A 0 NULL NULL BTREE
72
t1 1 longlong 1 longlong A 0 NULL NULL BTREE
73
t1 1 real_float 1 real_float A 0 NULL NULL BTREE
74
t1 1 ushort 1 ushort A 0 NULL NULL BTREE
75
t1 1 umedium 1 umedium A 0 NULL NULL BTREE
76
t1 1 ulong 1 ulong A 0 NULL NULL BTREE
77
t1 1 ulonglong 1 ulonglong A 0 NULL NULL BTREE
78
t1 1 ulonglong 2 ulong A 0 NULL NULL BTREE
79
t1 1 options 1 options A 0 NULL NULL BTREE
80
t1 1 options 2 flags A 0 NULL NULL BTREE
66
Table Unique Key_name Seq_in_index Column_name
67
t1 TRUE PRIMARY 1 auto
68
t1 FALSE utiny 1 utiny
70
t1 FALSE short 1 short
71
t1 FALSE any_name 1 medium
72
t1 FALSE longlong 1 longlong
73
t1 FALSE real_float 1 real_float
74
t1 FALSE ushort 1 ushort
75
t1 FALSE umedium 1 umedium
76
t1 FALSE ulong 1 ulong
77
t1 FALSE ulonglong 1 ulonglong
78
t1 FALSE ulonglong 2 ulong
79
t1 FALSE options 1 options
80
t1 FALSE options 2 flags
81
81
CREATE UNIQUE INDEX test on t1 ( auto ) ;
82
82
CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
83
83
CREATE INDEX test3 on t1 ( medium ) ;
129
129
create table t2 select * from t1;
130
130
update t2 set string="changed" where auto=16;
131
131
show columns from t1;
132
Field Type Null Key Default Extra
133
auto int NO PRI NULL auto_increment
134
string varchar(10) YES hello
139
longlong bigint NO MUL 0
140
real_float double NO MUL 0
141
real_double double YES NULL
146
ulonglong bigint NO MUL 0
147
time_stamp timestamp YES NULL
148
date_field date YES NULL
149
date_time datetime YES NULL
150
blob_col blob YES NULL
151
tinyblob_col blob YES NULL
152
mediumblob_col blob NO NULL
153
longblob_col blob NO NULL
154
options enum('one','two','tree') NO MUL NULL
155
flags enum('one','two','tree') NO one
132
Field Type Null Default Default_is_NULL On_Update
133
auto INTEGER FALSE 0 FALSE
134
string VARCHAR TRUE hello FALSE
135
tiny INTEGER FALSE 0 FALSE
136
short INTEGER FALSE 1 FALSE
137
medium INTEGER FALSE 0 FALSE
138
long_int INTEGER FALSE 0 FALSE
139
longlong BIGINT FALSE 0 FALSE
140
real_float DOUBLE FALSE 0 FALSE
141
real_double DOUBLE TRUE 0 TRUE
142
utiny INTEGER FALSE 0 FALSE
143
ushort INTEGER FALSE 0 FALSE
144
umedium INTEGER FALSE 0 FALSE
145
ulong INTEGER FALSE 0 FALSE
146
ulonglong BIGINT FALSE 0 FALSE
147
time_stamp TIMESTAMP TRUE 0 TRUE
148
date_field DATE TRUE 0 TRUE
149
date_time DATETIME TRUE 0 TRUE
150
blob_col BLOB TRUE 0 TRUE
151
tinyblob_col BLOB TRUE 0 TRUE
152
mediumblob_col BLOB FALSE 0 FALSE
153
longblob_col BLOB FALSE 0 FALSE
154
options ENUM FALSE 0 FALSE
155
flags ENUM FALSE one FALSE
156
156
show columns from t2;
157
Field Type Null Key Default Extra
159
string varchar(10) YES hello
165
real_float double NO 0
166
real_double double YES NULL
171
ulonglong bigint NO 0
172
time_stamp timestamp YES NULL
173
date_field date YES NULL
174
date_time datetime YES NULL
175
blob_col blob YES NULL
176
tinyblob_col blob YES NULL
177
mediumblob_col blob NO NULL
178
longblob_col blob NO NULL
179
options enum('one','two','tree') NO NULL
180
flags enum('one','two','tree') NO one
157
Field Type Null Default Default_is_NULL On_Update
158
auto INTEGER FALSE 0 FALSE
159
string VARCHAR TRUE hello FALSE
160
tiny INTEGER FALSE 0 FALSE
161
short INTEGER FALSE 1 FALSE
162
medium INTEGER FALSE 0 FALSE
163
long_int INTEGER FALSE 0 FALSE
164
longlong BIGINT FALSE 0 FALSE
165
real_float DOUBLE FALSE 0 FALSE
166
real_double DOUBLE TRUE 0 TRUE
167
utiny INTEGER FALSE 0 FALSE
168
ushort INTEGER FALSE 0 FALSE
169
umedium INTEGER FALSE 0 FALSE
170
ulong INTEGER FALSE 0 FALSE
171
ulonglong BIGINT FALSE 0 FALSE
172
time_stamp TIMESTAMP TRUE 0 TRUE
173
date_field DATE TRUE 0 TRUE
174
date_time DATETIME TRUE 0 TRUE
175
blob_col BLOB TRUE 0 TRUE
176
tinyblob_col BLOB TRUE 0 TRUE
177
mediumblob_col BLOB FALSE 0 FALSE
178
longblob_col BLOB FALSE 0 FALSE
179
options ENUM FALSE 0 FALSE
180
flags ENUM FALSE one FALSE
181
181
select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and ((t1.string<>t2.string and (t1.string is not null or t2.string is not null)) or (t1.tiny<>t2.tiny and (t1.tiny is not null or t2.tiny is not null)) or (t1.short<>t2.short and (t1.short is not null or t2.short is not null)) or (t1.medium<>t2.medium and (t1.medium is not null or t2.medium is not null)) or (t1.long_int<>t2.long_int and (t1.long_int is not null or t2.long_int is not null)) or (t1.longlong<>t2.longlong and (t1.longlong is not null or t2.longlong is not null)) or (t1.real_float<>t2.real_float and (t1.real_float is not null or t2.real_float is not null)) or (t1.real_double<>t2.real_double and (t1.real_double is not null or t2.real_double is not null)) or (t1.utiny<>t2.utiny and (t1.utiny is not null or t2.utiny is not null)) or (t1.ushort<>t2.ushort and (t1.ushort is not null or t2.ushort is not null)) or (t1.umedium<>t2.umedium and (t1.umedium is not null or t2.umedium is not null)) or (t1.ulong<>t2.ulong and (t1.ulong is not null or t2.ulong is not null)) or (t1.ulonglong<>t2.ulonglong and (t1.ulonglong is not null or t2.ulonglong is not null)) or (t1.time_stamp<>t2.time_stamp and (t1.time_stamp is not null or t2.time_stamp is not null)) or (t1.date_field<>t2.date_field and (t1.date_field is not null or t2.date_field is not null)) or (t1.date_time<>t2.date_time and (t1.date_time is not null or t2.date_time is not null)) or (t1.tinyblob_col<>t2.tinyblob_col and (t1.tinyblob_col is not null or t2.tinyblob_col is not null)) or (t1.mediumblob_col<>t2.mediumblob_col and (t1.mediumblob_col is not null or t2.mediumblob_col is not null)) or (t1.options<>t2.options and (t1.options is not null or t2.options is not null)) or (t1.flags<>t2.flags and (t1.flags is not null or t2.flags is not null)));
183
183
select t1.auto,t2.auto from t1,t2 where t1.auto=t2.auto and not (t1.string<=>t2.string and t1.tiny<=>t2.tiny and t1.short<=>t2.short and t1.medium<=>t2.medium and t1.long_int<=>t2.long_int and t1.longlong<=>t2.longlong and t1.real_float<=>t2.real_float and t1.real_double<=>t2.real_double and t1.utiny<=>t2.utiny and t1.ushort<=>t2.ushort and t1.umedium<=>t2.umedium and t1.ulong<=>t2.ulong and t1.ulonglong<=>t2.ulonglong and t1.time_stamp<=>t2.time_stamp and t1.date_field<=>t2.date_field and t1.date_time<=>t2.date_time and t1.tinyblob_col<=>t2.tinyblob_col and t1.mediumblob_col<=>t2.mediumblob_col and t1.options<=>t2.options and t1.flags<=>t2.flags);
186
186
create table t2 (primary key (auto)) select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
187
187
show columns from t2;
188
Field Type Null Key Default Extra
189
auto bigint NO PRI NULL
191
t2 varchar(1) NO NULL
192
t3 varchar(256) YES NULL
193
t4 varbinary(256) YES NULL
196
t7 varchar(0) NO NULL
197
t8 varbinary(0) YES NULL
188
Field Type Null Default Default_is_NULL On_Update
189
auto BIGINT FALSE FALSE
190
t1 INTEGER FALSE FALSE
191
t2 VARCHAR FALSE FALSE
196
t7 VARCHAR FALSE FALSE
198
198
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
199
199
t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8
200
200
1 a 256 256 4096 4096