1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
|
drop table if exists t1,t2,t3;
SET SQL_WARNINGS=1;
CREATE TABLE t1 (
auto int NOT NULL auto_increment,
string char(10) default "hello",
tiny int DEFAULT '0' NOT NULL ,
short int DEFAULT '1' NOT NULL ,
medium int DEFAULT '0' NOT NULL,
long_int int DEFAULT '0' NOT NULL,
longlong bigint DEFAULT '0' NOT NULL,
real_float float DEFAULT 0.0 NOT NULL,
real_double double,
utiny int DEFAULT '0' NOT NULL,
ushort int DEFAULT '00000' NOT NULL,
umedium int DEFAULT '0' NOT NULL,
ulong int DEFAULT '0' NOT NULL,
ulonglong bigint DEFAULT '0' NOT NULL,
time_stamp timestamp,
date_field date,
date_time datetime,
blob_col blob,
tinyblob_col tinyblob,
mediumblob_col mediumblob not null default '',
longblob_col longblob not null default '',
options enum('one','two','tree') not null ,
flags enum('one','two','tree') not null default 'one',
PRIMARY KEY (auto),
KEY (utiny),
KEY (tiny),
KEY (short),
KEY any_name (medium),
KEY (longlong),
KEY (real_float),
KEY (ushort),
KEY (umedium),
KEY (ulong),
KEY (ulonglong,ulong),
KEY (options,flags)
);
show fields from t1;
Field Type Null Default Default_is_NULL On_Update
auto INTEGER NO NO
string VARCHAR YES hello NO
tiny INTEGER NO 0 NO
short INTEGER NO 1 NO
medium INTEGER NO 0 NO
long_int INTEGER NO 0 NO
longlong BIGINT NO 0 NO
real_float DOUBLE NO 0.0 NO
real_double DOUBLE YES YES
utiny INTEGER NO 0 NO
ushort INTEGER NO 00000 NO
umedium INTEGER NO 0 NO
ulong INTEGER NO 0 NO
ulonglong BIGINT NO 0 NO
time_stamp TIMESTAMP YES YES
date_field DATE YES YES
date_time DATETIME YES YES
blob_col BLOB YES YES
tinyblob_col BLOB YES YES
mediumblob_col BLOB NO NO
longblob_col BLOB NO NO
options ENUM NO NO
flags ENUM NO one NO
show keys from t1;
Table Unique Key_name Seq_in_index Column_name
t1 YES PRIMARY 1 auto
t1 NO utiny 1 utiny
t1 NO tiny 1 tiny
t1 NO short 1 short
t1 NO any_name 1 medium
t1 NO longlong 1 longlong
t1 NO real_float 1 real_float
t1 NO ushort 1 ushort
t1 NO umedium 1 umedium
t1 NO ulong 1 ulong
t1 NO ulonglong 1 ulonglong
t1 NO ulonglong 2 ulong
t1 NO options 1 options
t1 NO options 2 flags
CREATE UNIQUE INDEX test on t1 ( auto ) ;
CREATE INDEX test2 on t1 ( ulonglong,ulong) ;
CREATE INDEX test3 on t1 ( medium ) ;
DROP INDEX test ON t1;
insert into t1 values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,NULL,NULL,1,1,1,1,'one','one');
insert into t1 values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,NULL,NULL,2,2,'two','one');
insert into t1 values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,NULL,'19970303','19970303101010','','','','3',3,3);
ERROR 22001: Data too long for column 'string' at row 1
insert into t1 values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,NULL,19970807,19970403090807,-1,-1,-1,'-1',-1,-1);
ERROR HY000: Received an invalid enum value '-1'.
insert into t1 values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,NULL,0,0,-4294967295,-4294967295,-4294967295,'-4294967295',0,"tree");
ERROR 22001: Data too long for column 'string' at row 1
insert into t1 values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,NULL,0,0,4294967295,4294967295,4294967295,'4294967295',0,0);
ERROR 22003: Out of range value for column 'tiny' at row 1
insert into t1 (tiny) values (1);
select auto,string,tiny,short,medium,long_int,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000),date_field,date_time,blob_col,tinyblob_col,mediumblob_col,longblob_col from t1;
auto string tiny short medium long_int longlong real_float real_double utiny ushort umedium ulong ulonglong mod(floor(time_stamp/1000000),1000000)-mod(curdate(),1000000) date_field date_time blob_col tinyblob_col mediumblob_col longblob_col
10 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL NULL 1 1 1 1
11 2 2 2 2 2 2 2 2 2 2 2 2 2 NULL NULL NULL NULL NULL 2 2
12 hello 1 1 0 0 0 0 NULL 0 0 0 0 0 NULL NULL NULL NULL NULL
CREATE TABLE t2 (
auto int NOT NULL auto_increment,
string char(20),
mediumblob_col mediumblob not null,
new_field char(2),
PRIMARY KEY (auto)
);
INSERT INTO t2 (string,mediumblob_col) SELECT string,mediumblob_col from t1 where auto > 10;
select * from t2;
auto string mediumblob_col new_field
1 2 2 NULL
2 hello NULL
select distinct flags from t1;
flags
one
select flags from t1 where find_in_set("two",flags)>0;
flags
select flags from t1 where find_in_set("unknown",flags)>0;
flags
select options,flags from t1 where options="ONE" and flags="ONE";
options flags
one one
one one
select options,flags from t1 where options="one" and flags="one";
options flags
one one
one one
drop table t2;
create table t2 select * from t1;
update t2 set string="changed" where auto=16;
show columns from t1;
Field Type Null Default Default_is_NULL On_Update
auto INTEGER NO NO
string VARCHAR YES hello NO
tiny INTEGER NO 0 NO
short INTEGER NO 1 NO
medium INTEGER NO 0 NO
long_int INTEGER NO 0 NO
longlong BIGINT NO 0 NO
real_float DOUBLE NO 0 NO
real_double DOUBLE YES YES
utiny INTEGER NO 0 NO
ushort INTEGER NO 0 NO
umedium INTEGER NO 0 NO
ulong INTEGER NO 0 NO
ulonglong BIGINT NO 0 NO
time_stamp TIMESTAMP YES YES
date_field DATE YES YES
date_time DATETIME YES YES
blob_col BLOB YES YES
tinyblob_col BLOB YES YES
mediumblob_col BLOB NO NO
longblob_col BLOB NO NO
options ENUM NO NO
flags ENUM NO one NO
show columns from t2;
Field Type Null Default Default_is_NULL On_Update
auto INTEGER NO 0 NO
string VARCHAR YES hello NO
tiny INTEGER NO 0 NO
short INTEGER NO 1 NO
medium INTEGER NO 0 NO
long_int INTEGER NO 0 NO
longlong BIGINT NO 0 NO
real_float DOUBLE NO 0 NO
real_double DOUBLE YES YES
utiny INTEGER NO 0 NO
ushort INTEGER NO 0 NO
umedium INTEGER NO 0 NO
ulong INTEGER NO 0 NO
ulonglong BIGINT NO 0 NO
time_stamp TIMESTAMP YES YES
date_field DATE YES YES
date_time DATETIME YES YES
blob_col BLOB YES YES
tinyblob_col BLOB YES YES
mediumblob_col BLOB NO NO
longblob_col BLOB NO NO
options ENUM NO NO
flags ENUM NO one NO
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)));
auto auto
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);
auto auto
drop table t2;
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;
show columns from t2;
Field Type Null Default Default_is_NULL On_Update
auto BIGINT NO NO
t1 INTEGER NO NO
t2 VARCHAR NO NO
t3 VARCHAR YES YES
t4 VARBINARY YES YES
t5 TEXT YES YES
t6 BLOB YES YES
t7 VARCHAR NO NO
t8 VARBINARY YES YES
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8
1 a 256 256 4096 4096
1 a 256 256 4096 4096
1 a 256 256 4096 4096
drop table t1,t2;
create table t1 (c int);
insert into t1 values(1),(2);
create table t2 select * from t1;
create table t3 select * from t1, t2;
ERROR 42S21: Duplicate column name 'c'
create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1 CROSS JOIN t2;
show columns from t3;
Field Type Null Default Default_is_NULL On_Update
c1 INTEGER YES YES
c2 INTEGER YES YES
const INTEGER NO NO
drop table t1,t2,t3;
create table t1 ( myfield INT NOT NULL, UNIQUE INDEX (myfield), unique (myfield), index(myfield));
drop table t1;
create table t1 ( id integer not null primary key );
create table t2 ( id integer not null primary key );
insert into t1 values (1), (2);
insert into t2 values (1);
select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
id_A id_B
1 1
2 NULL
select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
id_A id_B
1 1
2 NULL
create table t3 (id_A integer not null, id_B integer null );
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
select * from t3;
id_A id_B
1 1
2 NULL
truncate table t3;
insert into t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
select * from t3;
id_A id_B
1 1
2 NULL
drop table t3;
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 using ( id );
select * from t3;
id_A id_B
1 1
2 NULL
drop table t3;
create table t3 select t1.id as id_A, t2.id as id_B from t1 left join t2 on (t1.id = t2.id);
select * from t3;
id_A id_B
1 1
2 NULL
drop table t1,t2,t3;
|