1
drop table if exists t1,t2;
2
drop database if exists mysqltest;
4
col1 int not null auto_increment primary key,
5
col2 varchar(30) not null,
6
col3 varchar (20) not null,
7
col4 varchar(4) not null,
8
col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
9
col6 int not null, to_be_deleted int);
10
insert into t1 values (2,4,3,5,"PENDING",1,7);
12
add column col4_5 varchar(20) not null after col4,
13
add column col7 varchar(30) not null after col5,
14
add column col8 datetime not null, drop column to_be_deleted,
15
change column col2 fourth varchar(30) not null after col3,
16
modify column col6 int not null first;
18
col6 col1 col3 fourth col4 col4_5 col5 col7 col8
19
1 2 3 4 5 PENDING 0000-00-00 00:00:00
21
create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
22
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
23
alter table t1 add column new_col int, order by payoutid,bandid;
25
bandID payoutID new_col
34
alter table t1 order by bandid,payoutid;
36
bandID payoutID new_col
47
GROUP_ID int(10) unsigned DEFAULT '0' NOT NULL,
48
LANG_ID smallint(5) unsigned DEFAULT '0' NOT NULL,
49
NAME varchar(80) DEFAULT '' NOT NULL,
50
PRIMARY KEY (GROUP_ID,LANG_ID),
52
ALTER TABLE t1 CHANGE NAME NAME CHAR(80) not null;
53
SHOW FULL COLUMNS FROM t1;
54
Field Type Collation Null Key Default Extra Privileges Comment
55
GROUP_ID int(10) unsigned NULL NO PRI 0 #
56
LANG_ID smallint(5) unsigned NULL NO PRI 0 #
57
NAME char(80) latin1_swedish_ci NO MUL #
59
create table t1 (n int);
60
insert into t1 values(9),(3),(12),(10);
61
alter table t1 order by n;
70
id int(11) unsigned NOT NULL default '0',
71
category_id tinyint(4) unsigned NOT NULL default '0',
72
type_id tinyint(4) unsigned NOT NULL default '0',
74
user_id int(11) unsigned NOT NULL default '0',
75
status enum('new','old') NOT NULL default 'new',
78
ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body;
80
CREATE TABLE t1 (AnamneseId int(10) unsigned NOT NULL auto_increment,B BLOB,PRIMARY KEY (AnamneseId)) engine=myisam;
81
insert into t1 values (null,"hello");
83
ALTER TABLE t1 ADD Column new_col int not null;
86
Table Op Msg_type Msg_text
87
test.t1 optimize status OK
89
create table t1 (i int unsigned not null auto_increment primary key);
90
insert into t1 values (null),(null),(null),(null);
91
alter table t1 drop i,add i int unsigned not null auto_increment, drop primary key, add primary key (i);
99
create table t1 (name char(15));
100
insert into t1 (name) values ("current");
101
create database mysqltest;
102
create table mysqltest.t1 (name char(15));
103
insert into mysqltest.t1 (name) values ("mysqltest");
107
select * from mysqltest.t1;
110
alter table t1 rename mysqltest.t1;
111
ERROR 42S01: Table 't1' already exists
115
select * from mysqltest.t1;
119
drop database mysqltest;
120
create table t1 (n1 int not null, n2 int, n3 int, n4 float,
122
key (n1, n2, n3, n4),
123
key (n2, n3, n4, n1),
124
key (n3, n4, n1, n2),
125
key (n4, n1, n2, n3) );
126
alter table t1 disable keys;
128
Note 1031 Table storage engine for 't1' doesn't have this option
130
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
131
t1 0 n1 1 n1 A 0 NULL NULL BTREE
132
t1 1 n1_2 1 n1 A NULL NULL NULL BTREE
133
t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE
134
t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE
135
t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE
136
t1 1 n2 1 n2 A NULL NULL NULL YES BTREE
137
t1 1 n2 2 n3 A NULL NULL NULL YES BTREE
138
t1 1 n2 3 n4 A NULL NULL NULL YES BTREE
139
t1 1 n2 4 n1 A NULL NULL NULL BTREE
140
t1 1 n3 1 n3 A NULL NULL NULL YES BTREE
141
t1 1 n3 2 n4 A NULL NULL NULL YES BTREE
142
t1 1 n3 3 n1 A NULL NULL NULL BTREE
143
t1 1 n3 4 n2 A NULL NULL NULL YES BTREE
144
t1 1 n4 1 n4 A NULL NULL NULL YES BTREE
145
t1 1 n4 2 n1 A NULL NULL NULL BTREE
146
t1 1 n4 3 n2 A NULL NULL NULL YES BTREE
147
t1 1 n4 4 n3 A NULL NULL NULL YES BTREE
148
insert into t1 values(10,RAND()*1000,RAND()*1000,RAND());
149
insert into t1 values(9,RAND()*1000,RAND()*1000,RAND());
150
insert into t1 values(8,RAND()*1000,RAND()*1000,RAND());
151
insert into t1 values(7,RAND()*1000,RAND()*1000,RAND());
152
insert into t1 values(6,RAND()*1000,RAND()*1000,RAND());
153
insert into t1 values(5,RAND()*1000,RAND()*1000,RAND());
154
insert into t1 values(4,RAND()*1000,RAND()*1000,RAND());
155
insert into t1 values(3,RAND()*1000,RAND()*1000,RAND());
156
insert into t1 values(2,RAND()*1000,RAND()*1000,RAND());
157
insert into t1 values(1,RAND()*1000,RAND()*1000,RAND());
158
alter table t1 enable keys;
160
Note 1031 Table storage engine for 't1' doesn't have this option
162
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
163
t1 0 n1 1 n1 A 10 NULL NULL BTREE
164
t1 1 n1_2 1 n1 A NULL NULL NULL BTREE
165
t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE
166
t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE
167
t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE
168
t1 1 n2 1 n2 A NULL NULL NULL YES BTREE
169
t1 1 n2 2 n3 A NULL NULL NULL YES BTREE
170
t1 1 n2 3 n4 A NULL NULL NULL YES BTREE
171
t1 1 n2 4 n1 A NULL NULL NULL BTREE
172
t1 1 n3 1 n3 A NULL NULL NULL YES BTREE
173
t1 1 n3 2 n4 A NULL NULL NULL YES BTREE
174
t1 1 n3 3 n1 A NULL NULL NULL BTREE
175
t1 1 n3 4 n2 A NULL NULL NULL YES BTREE
176
t1 1 n4 1 n4 A NULL NULL NULL YES BTREE
177
t1 1 n4 2 n1 A NULL NULL NULL BTREE
178
t1 1 n4 3 n2 A NULL NULL NULL YES BTREE
179
t1 1 n4 4 n3 A NULL NULL NULL YES BTREE
181
create table t1 (i int unsigned not null auto_increment primary key);
182
alter table t1 rename t2;
183
alter table t2 rename t1, add c char(10) comment "no comment";
184
show columns from t1;
185
Field Type Null Key Default Extra
186
i int(10) unsigned NO PRI NULL auto_increment
189
create table t1 (a int, b int);
190
insert into t1 values(1,100), (2,100), (3, 100);
191
insert into t1 values(1,99), (2,99), (3, 99);
192
insert into t1 values(1,98), (2,98), (3, 98);
193
insert into t1 values(1,97), (2,97), (3, 97);
194
insert into t1 values(1,96), (2,96), (3, 96);
195
insert into t1 values(1,95), (2,95), (3, 95);
196
insert into t1 values(1,94), (2,94), (3, 94);
197
insert into t1 values(1,93), (2,93), (3, 93);
198
insert into t1 values(1,92), (2,92), (3, 92);
199
insert into t1 values(1,91), (2,91), (3, 91);
200
insert into t1 values(1,90), (2,90), (3, 90);
201
insert into t1 values(1,89), (2,89), (3, 89);
202
insert into t1 values(1,88), (2,88), (3, 88);
203
insert into t1 values(1,87), (2,87), (3, 87);
204
insert into t1 values(1,86), (2,86), (3, 86);
205
insert into t1 values(1,85), (2,85), (3, 85);
206
insert into t1 values(1,84), (2,84), (3, 84);
207
insert into t1 values(1,83), (2,83), (3, 83);
208
insert into t1 values(1,82), (2,82), (3, 82);
209
insert into t1 values(1,81), (2,81), (3, 81);
210
insert into t1 values(1,80), (2,80), (3, 80);
211
insert into t1 values(1,79), (2,79), (3, 79);
212
insert into t1 values(1,78), (2,78), (3, 78);
213
insert into t1 values(1,77), (2,77), (3, 77);
214
insert into t1 values(1,76), (2,76), (3, 76);
215
insert into t1 values(1,75), (2,75), (3, 75);
216
insert into t1 values(1,74), (2,74), (3, 74);
217
insert into t1 values(1,73), (2,73), (3, 73);
218
insert into t1 values(1,72), (2,72), (3, 72);
219
insert into t1 values(1,71), (2,71), (3, 71);
220
insert into t1 values(1,70), (2,70), (3, 70);
221
insert into t1 values(1,69), (2,69), (3, 69);
222
insert into t1 values(1,68), (2,68), (3, 68);
223
insert into t1 values(1,67), (2,67), (3, 67);
224
insert into t1 values(1,66), (2,66), (3, 66);
225
insert into t1 values(1,65), (2,65), (3, 65);
226
insert into t1 values(1,64), (2,64), (3, 64);
227
insert into t1 values(1,63), (2,63), (3, 63);
228
insert into t1 values(1,62), (2,62), (3, 62);
229
insert into t1 values(1,61), (2,61), (3, 61);
230
insert into t1 values(1,60), (2,60), (3, 60);
231
insert into t1 values(1,59), (2,59), (3, 59);
232
insert into t1 values(1,58), (2,58), (3, 58);
233
insert into t1 values(1,57), (2,57), (3, 57);
234
insert into t1 values(1,56), (2,56), (3, 56);
235
insert into t1 values(1,55), (2,55), (3, 55);
236
insert into t1 values(1,54), (2,54), (3, 54);
237
insert into t1 values(1,53), (2,53), (3, 53);
238
insert into t1 values(1,52), (2,52), (3, 52);
239
insert into t1 values(1,51), (2,51), (3, 51);
240
insert into t1 values(1,50), (2,50), (3, 50);
241
insert into t1 values(1,49), (2,49), (3, 49);
242
insert into t1 values(1,48), (2,48), (3, 48);
243
insert into t1 values(1,47), (2,47), (3, 47);
244
insert into t1 values(1,46), (2,46), (3, 46);
245
insert into t1 values(1,45), (2,45), (3, 45);
246
insert into t1 values(1,44), (2,44), (3, 44);
247
insert into t1 values(1,43), (2,43), (3, 43);
248
insert into t1 values(1,42), (2,42), (3, 42);
249
insert into t1 values(1,41), (2,41), (3, 41);
250
insert into t1 values(1,40), (2,40), (3, 40);
251
insert into t1 values(1,39), (2,39), (3, 39);
252
insert into t1 values(1,38), (2,38), (3, 38);
253
insert into t1 values(1,37), (2,37), (3, 37);
254
insert into t1 values(1,36), (2,36), (3, 36);
255
insert into t1 values(1,35), (2,35), (3, 35);
256
insert into t1 values(1,34), (2,34), (3, 34);
257
insert into t1 values(1,33), (2,33), (3, 33);
258
insert into t1 values(1,32), (2,32), (3, 32);
259
insert into t1 values(1,31), (2,31), (3, 31);
260
insert into t1 values(1,30), (2,30), (3, 30);
261
insert into t1 values(1,29), (2,29), (3, 29);
262
insert into t1 values(1,28), (2,28), (3, 28);
263
insert into t1 values(1,27), (2,27), (3, 27);
264
insert into t1 values(1,26), (2,26), (3, 26);
265
insert into t1 values(1,25), (2,25), (3, 25);
266
insert into t1 values(1,24), (2,24), (3, 24);
267
insert into t1 values(1,23), (2,23), (3, 23);
268
insert into t1 values(1,22), (2,22), (3, 22);
269
insert into t1 values(1,21), (2,21), (3, 21);
270
insert into t1 values(1,20), (2,20), (3, 20);
271
insert into t1 values(1,19), (2,19), (3, 19);
272
insert into t1 values(1,18), (2,18), (3, 18);
273
insert into t1 values(1,17), (2,17), (3, 17);
274
insert into t1 values(1,16), (2,16), (3, 16);
275
insert into t1 values(1,15), (2,15), (3, 15);
276
insert into t1 values(1,14), (2,14), (3, 14);
277
insert into t1 values(1,13), (2,13), (3, 13);
278
insert into t1 values(1,12), (2,12), (3, 12);
279
insert into t1 values(1,11), (2,11), (3, 11);
280
insert into t1 values(1,10), (2,10), (3, 10);
281
insert into t1 values(1,9), (2,9), (3, 9);
282
insert into t1 values(1,8), (2,8), (3, 8);
283
insert into t1 values(1,7), (2,7), (3, 7);
284
insert into t1 values(1,6), (2,6), (3, 6);
285
insert into t1 values(1,5), (2,5), (3, 5);
286
insert into t1 values(1,4), (2,4), (3, 4);
287
insert into t1 values(1,3), (2,3), (3, 3);
288
insert into t1 values(1,2), (2,2), (3, 2);
289
insert into t1 values(1,1), (2,1), (3, 1);
290
alter table t1 add unique (a,b), add key (b);
292
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
293
t1 0 a 1 a A NULL NULL NULL YES BTREE
294
t1 0 a 2 b A NULL NULL NULL YES BTREE
295
t1 1 b 1 b A NULL NULL NULL YES BTREE
297
Table Op Msg_type Msg_text
298
test.t1 analyze status OK
300
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
301
t1 0 a 1 a A NULL NULL NULL YES BTREE
302
t1 0 a 2 b A NULL NULL NULL YES BTREE
303
t1 1 b 1 b A NULL NULL NULL YES BTREE
305
CREATE TABLE t1 (i int(10), index(i) );
306
ALTER TABLE t1 DISABLE KEYS;
308
Note 1031 Table storage engine for 't1' doesn't have this option
309
INSERT DELAYED INTO t1 VALUES(1),(2),(3);
310
ALTER TABLE t1 ENABLE KEYS;
312
Note 1031 Table storage engine for 't1' doesn't have this option
315
Host varchar(16) binary NOT NULL default '',
316
User varchar(16) binary NOT NULL default '',
317
PRIMARY KEY (Host,User)
319
ALTER TABLE t1 DISABLE KEYS;
320
LOCK TABLES t1 WRITE;
321
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
323
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
324
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
325
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
326
ALTER TABLE t1 ENABLE KEYS;
329
Table Op Msg_type Msg_text
330
test.t1 check status OK
333
Host varchar(16) binary NOT NULL default '',
334
User varchar(16) binary NOT NULL default '',
335
PRIMARY KEY (Host,User),
338
ALTER TABLE t1 DISABLE KEYS;
340
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
341
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
342
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
343
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
344
LOCK TABLES t1 WRITE;
345
INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
347
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
348
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
349
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
350
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
351
ALTER TABLE t1 ENABLE KEYS;
353
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
354
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
355
t1 0 PRIMARY 2 User A 2 NULL NULL BTREE
356
t1 1 Host 1 Host A 1 NULL NULL BTREE
359
Table Op Msg_type Msg_text
360
test.t1 check status OK
361
LOCK TABLES t1 WRITE;
362
ALTER TABLE t1 RENAME t2;
370
Host varchar(16) binary NOT NULL default '',
371
User varchar(16) binary NOT NULL default '',
372
PRIMARY KEY (Host,User),
375
LOCK TABLES t1 WRITE;
376
ALTER TABLE t1 DISABLE KEYS;
378
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
379
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE
380
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE
381
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled
383
create table t1 (a int);
384
alter table t1 rename to ``;
385
ERROR 42000: Incorrect table name ''
386
rename table t1 to ``;
387
ERROR 42000: Incorrect table name ''
389
drop table if exists t1, t2;
391
Note 1051 Unknown table 't1'
392
Note 1051 Unknown table 't2'
393
create table t1 ( a varchar(10) not null primary key ) engine=myisam;
394
create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1);
396
alter table t1 modify a varchar(10);
397
show create table t2;
399
t2 CREATE TABLE `t2` (
400
`a` varchar(10) NOT NULL,
402
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`)
404
alter table t1 modify a varchar(10) not null;
405
show create table t2;
407
t2 CREATE TABLE `t2` (
408
`a` varchar(10) NOT NULL,
410
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`)
411
drop table if exists t1, t2;
412
create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
413
insert into t1 (a) values(1);
414
show table status like 't1';
415
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
416
t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL
417
alter table t1 modify a int;
418
show table status like 't1';
419
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
420
t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL
422
create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
423
insert into t1 (a) values(1);
425
Warning 1364 Field 'b' doesn't have a default value
426
Warning 1364 Field 'c' doesn't have a default value
427
Warning 1364 Field 'd' doesn't have a default value
428
Warning 1364 Field 'e' doesn't have a default value
429
Warning 1364 Field 'f' doesn't have a default value
430
Warning 1364 Field 'g' doesn't have a default value
431
Warning 1364 Field 'h' doesn't have a default value
432
Warning 1364 Field 'i' doesn't have a default value
433
show table status like 't1';
434
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
435
t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL
438
create table t1 (a char(10) character set koi8r);
439
insert into t1 values ('����');
440
select a,hex(a) from t1;
443
alter table t1 change a a char(10) character set cp1251;
444
select a,hex(a) from t1;
447
alter table t1 change a a binary(4);
448
select a,hex(a) from t1;
451
alter table t1 change a a char(10) character set cp1251;
452
select a,hex(a) from t1;
455
alter table t1 change a a char(10) character set koi8r;
456
select a,hex(a) from t1;
459
alter table t1 change a a varchar(10) character set cp1251;
460
select a,hex(a) from t1;
463
alter table t1 change a a char(10) character set koi8r;
464
select a,hex(a) from t1;
467
alter table t1 change a a text character set cp1251;
468
select a,hex(a) from t1;
471
alter table t1 change a a char(10) character set koi8r;
472
select a,hex(a) from t1;
476
show create table t1;
478
t1 CREATE TABLE `t1` (
479
`a` char(10) CHARACTER SET koi8r DEFAULT NULL
480
) ENGINE=PBXT DEFAULT CHARSET=latin1
481
alter table t1 DEFAULT CHARACTER SET latin1;
482
show create table t1;
484
t1 CREATE TABLE `t1` (
485
`a` char(10) CHARACTER SET koi8r DEFAULT NULL
486
) ENGINE=PBXT DEFAULT CHARSET=latin1
487
alter table t1 CONVERT TO CHARACTER SET latin1;
488
show create table t1;
490
t1 CREATE TABLE `t1` (
491
`a` char(10) DEFAULT NULL
492
) ENGINE=PBXT DEFAULT CHARSET=latin1
493
alter table t1 DEFAULT CHARACTER SET cp1251;
494
show create table t1;
496
t1 CREATE TABLE `t1` (
497
`a` char(10) CHARACTER SET latin1 DEFAULT NULL
498
) ENGINE=PBXT DEFAULT CHARSET=cp1251
500
create table t1 (myblob longblob,mytext longtext)
501
default charset latin1 collate latin1_general_cs;
502
show create table t1;
504
t1 CREATE TABLE `t1` (
506
`mytext` longtext COLLATE latin1_general_cs
507
) ENGINE=PBXT DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
508
alter table t1 character set latin2;
509
show create table t1;
511
t1 CREATE TABLE `t1` (
513
`mytext` longtext CHARACTER SET latin1 COLLATE latin1_general_cs
514
) ENGINE=PBXT DEFAULT CHARSET=latin2
516
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
517
ALTER TABLE t1 DROP PRIMARY KEY;
518
SHOW CREATE TABLE t1;
520
t1 CREATE TABLE `t1` (
521
`a` int(11) NOT NULL,
522
`b` int(11) DEFAULT NULL,
524
) ENGINE=PBXT DEFAULT CHARSET=latin1
525
ALTER TABLE t1 DROP PRIMARY KEY;
526
ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
528
create table t1 (a int, b int, key(a));
529
insert into t1 values (1,1), (2,2);
530
alter table t1 drop key no_such_key;
531
ERROR 42000: Can't DROP 'no_such_key'; check that column/key exists
532
alter table t1 drop key a;
534
CREATE TABLE T12207(a int) ENGINE=MYISAM;
535
ALTER TABLE T12207 DISCARD TABLESPACE;
536
ERROR HY000: Table storage engine for 'T12207' doesn't have this option
538
create table t1 (a text) character set koi8r;
539
insert into t1 values (_koi8r'����');
540
select hex(a) from t1;
543
alter table t1 convert to character set cp1251;
544
select hex(a) from t1;
548
create table t1 ( a timestamp );
549
alter table t1 add unique ( a(1) );
550
ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
552
create database mysqltest;
553
create table t1 (c1 int);
554
alter table t1 rename mysqltest.t1;
556
ERROR 42S02: Unknown table 't1'
557
alter table mysqltest.t1 rename t1;
559
create table t1 (c1 int);
561
drop database mysqltest;
562
alter table test.t1 rename t1;
563
ERROR 3D000: No database selected
564
alter table test.t1 rename test.t1;
567
create table t1 (mycol int(10) not null);
568
alter table t1 alter column mycol set default 0;
570
Field Type Null Key Default Extra
573
create table t1 (v varchar(32));
574
insert into t1 values ('def'),('abc'),('hij'),('3r4f');
581
alter table t1 change v v2 varchar(32);
588
alter table t1 change v2 v varchar(64);
595
update t1 set v = 'lmn' where v = 'hij';
602
alter table t1 add i int auto_increment not null primary key first;
609
update t1 set i=5 where i=3;
616
alter table t1 change i i bigint;
623
alter table t1 add unique key (i, v);
624
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn');
628
create table t1 (t varchar(255) default null, key t (t(80)))
629
engine=myisam default charset=latin1;
630
alter table t1 change t t text;
632
CREATE TABLE t1 (s CHAR(8) BINARY);
633
INSERT INTO t1 VALUES ('test');
634
SELECT LENGTH(s) FROM t1;
637
ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
638
SELECT LENGTH(s) FROM t1;
642
CREATE TABLE t1 (s BINARY(8));
643
INSERT INTO t1 VALUES ('test');
644
SELECT LENGTH(s) FROM t1;
647
SELECT HEX(s) FROM t1;
650
ALTER TABLE t1 MODIFY s BINARY(10);
651
SELECT HEX(s) FROM t1;
654
SELECT LENGTH(s) FROM t1;
658
CREATE TABLE t1 (v VARCHAR(3), b INT);
659
INSERT INTO t1 VALUES ('abc', 5);
663
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4);
668
DROP TABLE IF EXISTS `t+1`, `t+2`;
669
CREATE TABLE `t+1` (c1 INT);
670
ALTER TABLE `t+1` RENAME `t+2`;
671
CREATE TABLE `t+1` (c1 INT);
672
ALTER TABLE `t+1` RENAME `t+2`;
673
ERROR 42S01: Table 't+2' already exists
674
DROP TABLE `t+1`, `t+2`;
675
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
676
ALTER TABLE `tt+1` RENAME `tt+2`;
677
CREATE TEMPORARY TABLE `tt+1` (c1 INT);
678
ALTER TABLE `tt+1` RENAME `tt+2`;
679
ERROR 42S01: Table 'tt+2' already exists
680
SHOW CREATE TABLE `tt+1`;
682
tt+1 CREATE TEMPORARY TABLE `tt+1` (
683
`c1` int(11) DEFAULT NULL
684
) ENGINE=PBXT DEFAULT CHARSET=latin1
685
SHOW CREATE TABLE `tt+2`;
687
tt+2 CREATE TEMPORARY TABLE `tt+2` (
688
`c1` int(11) DEFAULT NULL
689
) ENGINE=PBXT DEFAULT CHARSET=latin1
690
DROP TABLE `tt+1`, `tt+2`;
691
CREATE TABLE `#sql1` (c1 INT);
692
CREATE TABLE `@0023sql2` (c1 INT);
697
RENAME TABLE `#sql1` TO `@0023sql1`;
698
RENAME TABLE `@0023sql2` TO `#sql2`;
703
ALTER TABLE `@0023sql1` RENAME `#sql-1`;
704
ALTER TABLE `#sql2` RENAME `@0023sql-2`;
709
INSERT INTO `#sql-1` VALUES (1);
710
INSERT INTO `@0023sql-2` VALUES (2);
711
DROP TABLE `#sql-1`, `@0023sql-2`;
712
CREATE TEMPORARY TABLE `#sql1` (c1 INT);
713
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT);
716
ALTER TABLE `#sql1` RENAME `@0023sql1`;
717
ALTER TABLE `@0023sql2` RENAME `#sql2`;
720
INSERT INTO `#sql2` VALUES (1);
721
INSERT INTO `@0023sql1` VALUES (2);
722
SHOW CREATE TABLE `#sql2`;
724
#sql2 CREATE TEMPORARY TABLE `#sql2` (
725
`c1` int(11) DEFAULT NULL
726
) ENGINE=PBXT DEFAULT CHARSET=latin1
727
SHOW CREATE TABLE `@0023sql1`;
729
@0023sql1 CREATE TEMPORARY TABLE `@0023sql1` (
730
`c1` int(11) DEFAULT NULL
731
) ENGINE=PBXT DEFAULT CHARSET=latin1
732
DROP TABLE `#sql2`, `@0023sql1`;