|
1
by brian
clean slate |
1 |
drop table if exists t1,t2; |
2 |
drop database if exists mysqltest; |
|
3 |
create table t1 ( |
|
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); |
|
11 |
alter table t1 |
|
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; |
|
17 |
select * from t1; |
|
18 |
col6 col1 col3 fourth col4 col4_5 col5 col7 col8 |
|
19 |
1 2 3 4 5 PENDING 0000-00-00 00:00:00 |
|
20 |
drop table t1; |
|
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; |
|
24 |
select * from t1; |
|
25 |
bandID payoutID new_col |
|
26 |
6 1 NULL |
|
27 |
3 4 NULL |
|
28 |
1 6 NULL |
|
29 |
2 6 NULL |
|
30 |
4 9 NULL |
|
31 |
5 10 NULL |
|
32 |
7 12 NULL |
|
33 |
8 12 NULL |
|
34 |
alter table t1 order by bandid,payoutid; |
|
35 |
select * from t1; |
|
36 |
bandID payoutID new_col |
|
37 |
1 6 NULL |
|
38 |
2 6 NULL |
|
39 |
3 4 NULL |
|
40 |
4 9 NULL |
|
41 |
5 10 NULL |
|
42 |
6 1 NULL |
|
43 |
7 12 NULL |
|
44 |
8 12 NULL |
|
45 |
drop table t1; |
|
46 |
CREATE TABLE t1 ( |
|
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), |
|
51 |
KEY NAME (NAME)); |
|
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 NULL # |
|
58 |
DROP TABLE t1; |
|
59 |
create table t1 (n int); |
|
60 |
insert into t1 values(9),(3),(12),(10); |
|
61 |
alter table t1 order by n; |
|
62 |
select * from t1; |
|
63 |
n
|
|
64 |
3
|
|
65 |
9
|
|
66 |
10
|
|
67 |
12
|
|
68 |
drop table t1; |
|
69 |
CREATE TABLE t1 ( |
|
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', |
|
73 |
body text NOT NULL, |
|
74 |
user_id int(11) unsigned NOT NULL default '0', |
|
75 |
status enum('new','old') NOT NULL default 'new', |
|
76 |
PRIMARY KEY (id) |
|
77 |
) ENGINE=MyISAM; |
|
78 |
ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; |
|
79 |
DROP TABLE t1; |
|
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"); |
|
82 |
LOCK TABLES t1 WRITE; |
|
83 |
ALTER TABLE t1 ADD Column new_col int not null; |
|
84 |
UNLOCK TABLES; |
|
85 |
OPTIMIZE TABLE t1; |
|
86 |
Table Op Msg_type Msg_text |
|
87 |
test.t1 optimize status OK |
|
88 |
DROP TABLE t1; |
|
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); |
|
92 |
select * from t1; |
|
93 |
i
|
|
94 |
1
|
|
95 |
2
|
|
96 |
3
|
|
97 |
4
|
|
98 |
drop table t1; |
|
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"); |
|
104 |
select * from t1; |
|
105 |
name
|
|
106 |
current
|
|
107 |
select * from mysqltest.t1; |
|
108 |
name
|
|
109 |
mysqltest
|
|
110 |
alter table t1 rename mysqltest.t1; |
|
111 |
ERROR 42S01: Table 't1' already exists |
|
112 |
select * from t1; |
|
113 |
name
|
|
114 |
current
|
|
115 |
select * from mysqltest.t1; |
|
116 |
name
|
|
117 |
mysqltest
|
|
118 |
drop table t1; |
|
119 |
drop database mysqltest; |
|
120 |
create table t1 (n1 int not null, n2 int, n3 int, n4 float, |
|
121 |
unique(n1), |
|
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; |
|
127 |
show keys from t1; |
|
128 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
129 |
t1 0 n1 1 n1 A 0 NULL NULL BTREE |
|
130 |
t1 1 n1_2 1 n1 A NULL NULL NULL BTREE disabled |
|
131 |
t1 1 n1_2 2 n2 A NULL NULL NULL YES BTREE disabled |
|
132 |
t1 1 n1_2 3 n3 A NULL NULL NULL YES BTREE disabled |
|
133 |
t1 1 n1_2 4 n4 A NULL NULL NULL YES BTREE disabled |
|
134 |
t1 1 n2 1 n2 A NULL NULL NULL YES BTREE disabled |
|
135 |
t1 1 n2 2 n3 A NULL NULL NULL YES BTREE disabled |
|
136 |
t1 1 n2 3 n4 A NULL NULL NULL YES BTREE disabled |
|
137 |
t1 1 n2 4 n1 A NULL NULL NULL BTREE disabled |
|
138 |
t1 1 n3 1 n3 A NULL NULL NULL YES BTREE disabled |
|
139 |
t1 1 n3 2 n4 A NULL NULL NULL YES BTREE disabled |
|
140 |
t1 1 n3 3 n1 A NULL NULL NULL BTREE disabled |
|
141 |
t1 1 n3 4 n2 A NULL NULL NULL YES BTREE disabled |
|
142 |
t1 1 n4 1 n4 A NULL NULL NULL YES BTREE disabled |
|
143 |
t1 1 n4 2 n1 A NULL NULL NULL BTREE disabled |
|
144 |
t1 1 n4 3 n2 A NULL NULL NULL YES BTREE disabled |
|
145 |
t1 1 n4 4 n3 A NULL NULL NULL YES BTREE disabled |
|
146 |
insert into t1 values(10,RAND()*1000,RAND()*1000,RAND()); |
|
147 |
insert into t1 values(9,RAND()*1000,RAND()*1000,RAND()); |
|
148 |
insert into t1 values(8,RAND()*1000,RAND()*1000,RAND()); |
|
149 |
insert into t1 values(7,RAND()*1000,RAND()*1000,RAND()); |
|
150 |
insert into t1 values(6,RAND()*1000,RAND()*1000,RAND()); |
|
151 |
insert into t1 values(5,RAND()*1000,RAND()*1000,RAND()); |
|
152 |
insert into t1 values(4,RAND()*1000,RAND()*1000,RAND()); |
|
153 |
insert into t1 values(3,RAND()*1000,RAND()*1000,RAND()); |
|
154 |
insert into t1 values(2,RAND()*1000,RAND()*1000,RAND()); |
|
155 |
insert into t1 values(1,RAND()*1000,RAND()*1000,RAND()); |
|
156 |
alter table t1 enable keys; |
|
157 |
show keys from t1; |
|
158 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
159 |
t1 0 n1 1 n1 A 10 NULL NULL BTREE |
|
160 |
t1 1 n1_2 1 n1 A 10 NULL NULL BTREE |
|
161 |
t1 1 n1_2 2 n2 A 10 NULL NULL YES BTREE |
|
162 |
t1 1 n1_2 3 n3 A 10 NULL NULL YES BTREE |
|
163 |
t1 1 n1_2 4 n4 A 10 NULL NULL YES BTREE |
|
164 |
t1 1 n2 1 n2 A 10 NULL NULL YES BTREE |
|
165 |
t1 1 n2 2 n3 A 10 NULL NULL YES BTREE |
|
166 |
t1 1 n2 3 n4 A 10 NULL NULL YES BTREE |
|
167 |
t1 1 n2 4 n1 A 10 NULL NULL BTREE |
|
168 |
t1 1 n3 1 n3 A 10 NULL NULL YES BTREE |
|
169 |
t1 1 n3 2 n4 A 10 NULL NULL YES BTREE |
|
170 |
t1 1 n3 3 n1 A 10 NULL NULL BTREE |
|
171 |
t1 1 n3 4 n2 A 10 NULL NULL YES BTREE |
|
172 |
t1 1 n4 1 n4 A 10 NULL NULL YES BTREE |
|
173 |
t1 1 n4 2 n1 A 10 NULL NULL BTREE |
|
174 |
t1 1 n4 3 n2 A 10 NULL NULL YES BTREE |
|
175 |
t1 1 n4 4 n3 A 10 NULL NULL YES BTREE |
|
176 |
drop table t1; |
|
177 |
create table t1 (i int unsigned not null auto_increment primary key); |
|
178 |
alter table t1 rename t2; |
|
179 |
alter table t2 rename t1, add c char(10) comment "no comment"; |
|
180 |
show columns from t1; |
|
181 |
Field Type Null Key Default Extra |
|
182 |
i int(10) unsigned NO PRI NULL auto_increment |
|
183 |
c char(10) YES NULL |
|
184 |
drop table t1; |
|
185 |
create table t1 (a int, b int); |
|
186 |
insert into t1 values(1,100), (2,100), (3, 100); |
|
187 |
insert into t1 values(1,99), (2,99), (3, 99); |
|
188 |
insert into t1 values(1,98), (2,98), (3, 98); |
|
189 |
insert into t1 values(1,97), (2,97), (3, 97); |
|
190 |
insert into t1 values(1,96), (2,96), (3, 96); |
|
191 |
insert into t1 values(1,95), (2,95), (3, 95); |
|
192 |
insert into t1 values(1,94), (2,94), (3, 94); |
|
193 |
insert into t1 values(1,93), (2,93), (3, 93); |
|
194 |
insert into t1 values(1,92), (2,92), (3, 92); |
|
195 |
insert into t1 values(1,91), (2,91), (3, 91); |
|
196 |
insert into t1 values(1,90), (2,90), (3, 90); |
|
197 |
insert into t1 values(1,89), (2,89), (3, 89); |
|
198 |
insert into t1 values(1,88), (2,88), (3, 88); |
|
199 |
insert into t1 values(1,87), (2,87), (3, 87); |
|
200 |
insert into t1 values(1,86), (2,86), (3, 86); |
|
201 |
insert into t1 values(1,85), (2,85), (3, 85); |
|
202 |
insert into t1 values(1,84), (2,84), (3, 84); |
|
203 |
insert into t1 values(1,83), (2,83), (3, 83); |
|
204 |
insert into t1 values(1,82), (2,82), (3, 82); |
|
205 |
insert into t1 values(1,81), (2,81), (3, 81); |
|
206 |
insert into t1 values(1,80), (2,80), (3, 80); |
|
207 |
insert into t1 values(1,79), (2,79), (3, 79); |
|
208 |
insert into t1 values(1,78), (2,78), (3, 78); |
|
209 |
insert into t1 values(1,77), (2,77), (3, 77); |
|
210 |
insert into t1 values(1,76), (2,76), (3, 76); |
|
211 |
insert into t1 values(1,75), (2,75), (3, 75); |
|
212 |
insert into t1 values(1,74), (2,74), (3, 74); |
|
213 |
insert into t1 values(1,73), (2,73), (3, 73); |
|
214 |
insert into t1 values(1,72), (2,72), (3, 72); |
|
215 |
insert into t1 values(1,71), (2,71), (3, 71); |
|
216 |
insert into t1 values(1,70), (2,70), (3, 70); |
|
217 |
insert into t1 values(1,69), (2,69), (3, 69); |
|
218 |
insert into t1 values(1,68), (2,68), (3, 68); |
|
219 |
insert into t1 values(1,67), (2,67), (3, 67); |
|
220 |
insert into t1 values(1,66), (2,66), (3, 66); |
|
221 |
insert into t1 values(1,65), (2,65), (3, 65); |
|
222 |
insert into t1 values(1,64), (2,64), (3, 64); |
|
223 |
insert into t1 values(1,63), (2,63), (3, 63); |
|
224 |
insert into t1 values(1,62), (2,62), (3, 62); |
|
225 |
insert into t1 values(1,61), (2,61), (3, 61); |
|
226 |
insert into t1 values(1,60), (2,60), (3, 60); |
|
227 |
insert into t1 values(1,59), (2,59), (3, 59); |
|
228 |
insert into t1 values(1,58), (2,58), (3, 58); |
|
229 |
insert into t1 values(1,57), (2,57), (3, 57); |
|
230 |
insert into t1 values(1,56), (2,56), (3, 56); |
|
231 |
insert into t1 values(1,55), (2,55), (3, 55); |
|
232 |
insert into t1 values(1,54), (2,54), (3, 54); |
|
233 |
insert into t1 values(1,53), (2,53), (3, 53); |
|
234 |
insert into t1 values(1,52), (2,52), (3, 52); |
|
235 |
insert into t1 values(1,51), (2,51), (3, 51); |
|
236 |
insert into t1 values(1,50), (2,50), (3, 50); |
|
237 |
insert into t1 values(1,49), (2,49), (3, 49); |
|
238 |
insert into t1 values(1,48), (2,48), (3, 48); |
|
239 |
insert into t1 values(1,47), (2,47), (3, 47); |
|
240 |
insert into t1 values(1,46), (2,46), (3, 46); |
|
241 |
insert into t1 values(1,45), (2,45), (3, 45); |
|
242 |
insert into t1 values(1,44), (2,44), (3, 44); |
|
243 |
insert into t1 values(1,43), (2,43), (3, 43); |
|
244 |
insert into t1 values(1,42), (2,42), (3, 42); |
|
245 |
insert into t1 values(1,41), (2,41), (3, 41); |
|
246 |
insert into t1 values(1,40), (2,40), (3, 40); |
|
247 |
insert into t1 values(1,39), (2,39), (3, 39); |
|
248 |
insert into t1 values(1,38), (2,38), (3, 38); |
|
249 |
insert into t1 values(1,37), (2,37), (3, 37); |
|
250 |
insert into t1 values(1,36), (2,36), (3, 36); |
|
251 |
insert into t1 values(1,35), (2,35), (3, 35); |
|
252 |
insert into t1 values(1,34), (2,34), (3, 34); |
|
253 |
insert into t1 values(1,33), (2,33), (3, 33); |
|
254 |
insert into t1 values(1,32), (2,32), (3, 32); |
|
255 |
insert into t1 values(1,31), (2,31), (3, 31); |
|
256 |
insert into t1 values(1,30), (2,30), (3, 30); |
|
257 |
insert into t1 values(1,29), (2,29), (3, 29); |
|
258 |
insert into t1 values(1,28), (2,28), (3, 28); |
|
259 |
insert into t1 values(1,27), (2,27), (3, 27); |
|
260 |
insert into t1 values(1,26), (2,26), (3, 26); |
|
261 |
insert into t1 values(1,25), (2,25), (3, 25); |
|
262 |
insert into t1 values(1,24), (2,24), (3, 24); |
|
263 |
insert into t1 values(1,23), (2,23), (3, 23); |
|
264 |
insert into t1 values(1,22), (2,22), (3, 22); |
|
265 |
insert into t1 values(1,21), (2,21), (3, 21); |
|
266 |
insert into t1 values(1,20), (2,20), (3, 20); |
|
267 |
insert into t1 values(1,19), (2,19), (3, 19); |
|
268 |
insert into t1 values(1,18), (2,18), (3, 18); |
|
269 |
insert into t1 values(1,17), (2,17), (3, 17); |
|
270 |
insert into t1 values(1,16), (2,16), (3, 16); |
|
271 |
insert into t1 values(1,15), (2,15), (3, 15); |
|
272 |
insert into t1 values(1,14), (2,14), (3, 14); |
|
273 |
insert into t1 values(1,13), (2,13), (3, 13); |
|
274 |
insert into t1 values(1,12), (2,12), (3, 12); |
|
275 |
insert into t1 values(1,11), (2,11), (3, 11); |
|
276 |
insert into t1 values(1,10), (2,10), (3, 10); |
|
277 |
insert into t1 values(1,9), (2,9), (3, 9); |
|
278 |
insert into t1 values(1,8), (2,8), (3, 8); |
|
279 |
insert into t1 values(1,7), (2,7), (3, 7); |
|
280 |
insert into t1 values(1,6), (2,6), (3, 6); |
|
281 |
insert into t1 values(1,5), (2,5), (3, 5); |
|
282 |
insert into t1 values(1,4), (2,4), (3, 4); |
|
283 |
insert into t1 values(1,3), (2,3), (3, 3); |
|
284 |
insert into t1 values(1,2), (2,2), (3, 2); |
|
285 |
insert into t1 values(1,1), (2,1), (3, 1); |
|
286 |
alter table t1 add unique (a,b), add key (b); |
|
287 |
show keys from t1; |
|
288 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
289 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
290 |
t1 0 a 2 b A NULL NULL NULL YES BTREE |
|
291 |
t1 1 b 1 b A 100 NULL NULL YES BTREE |
|
292 |
analyze table t1; |
|
293 |
Table Op Msg_type Msg_text |
|
294 |
test.t1 analyze status OK |
|
295 |
show keys from t1; |
|
296 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
297 |
t1 0 a 1 a A 3 NULL NULL YES BTREE |
|
298 |
t1 0 a 2 b A 300 NULL NULL YES BTREE |
|
299 |
t1 1 b 1 b A 100 NULL NULL YES BTREE |
|
300 |
drop table t1; |
|
301 |
CREATE TABLE t1 (i int(10), index(i) ); |
|
302 |
ALTER TABLE t1 DISABLE KEYS; |
|
303 |
INSERT DELAYED INTO t1 VALUES(1),(2),(3); |
|
304 |
ALTER TABLE t1 ENABLE KEYS; |
|
305 |
drop table t1; |
|
306 |
CREATE TABLE t1 ( |
|
307 |
Host varchar(16) binary NOT NULL default '', |
|
308 |
User varchar(16) binary NOT NULL default '', |
|
309 |
PRIMARY KEY (Host,User) |
|
310 |
) ENGINE=MyISAM; |
|
311 |
ALTER TABLE t1 DISABLE KEYS; |
|
312 |
LOCK TABLES t1 WRITE; |
|
313 |
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty'); |
|
314 |
SHOW INDEX FROM t1; |
|
315 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
316 |
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE |
|
317 |
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE |
|
318 |
ALTER TABLE t1 ENABLE KEYS; |
|
319 |
UNLOCK TABLES; |
|
320 |
CHECK TABLES t1; |
|
321 |
Table Op Msg_type Msg_text |
|
322 |
test.t1 check status OK |
|
323 |
DROP TABLE t1; |
|
324 |
CREATE TABLE t1 ( |
|
325 |
Host varchar(16) binary NOT NULL default '', |
|
326 |
User varchar(16) binary NOT NULL default '', |
|
327 |
PRIMARY KEY (Host,User), |
|
328 |
KEY (Host) |
|
329 |
) ENGINE=MyISAM; |
|
330 |
ALTER TABLE t1 DISABLE KEYS; |
|
331 |
SHOW INDEX FROM t1; |
|
332 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
333 |
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE |
|
334 |
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE |
|
335 |
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled |
|
336 |
LOCK TABLES t1 WRITE; |
|
337 |
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''); |
|
338 |
SHOW INDEX FROM t1; |
|
339 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
340 |
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE |
|
341 |
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE |
|
342 |
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled |
|
343 |
ALTER TABLE t1 ENABLE KEYS; |
|
344 |
SHOW INDEX FROM t1; |
|
345 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
346 |
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE |
|
347 |
t1 0 PRIMARY 2 User A 2 NULL NULL BTREE |
|
348 |
t1 1 Host 1 Host A 1 NULL NULL BTREE |
|
349 |
UNLOCK TABLES; |
|
350 |
CHECK TABLES t1; |
|
351 |
Table Op Msg_type Msg_text |
|
352 |
test.t1 check status OK |
|
353 |
LOCK TABLES t1 WRITE; |
|
354 |
ALTER TABLE t1 RENAME t2; |
|
355 |
UNLOCK TABLES; |
|
356 |
select * from t2; |
|
357 |
Host User |
|
358 |
localhost
|
|
359 |
localhost root |
|
360 |
DROP TABLE t2; |
|
361 |
CREATE TABLE t1 ( |
|
362 |
Host varchar(16) binary NOT NULL default '', |
|
363 |
User varchar(16) binary NOT NULL default '', |
|
364 |
PRIMARY KEY (Host,User), |
|
365 |
KEY (Host) |
|
366 |
) ENGINE=MyISAM; |
|
367 |
LOCK TABLES t1 WRITE; |
|
368 |
ALTER TABLE t1 DISABLE KEYS; |
|
369 |
SHOW INDEX FROM t1; |
|
370 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
371 |
t1 0 PRIMARY 1 Host A NULL NULL NULL BTREE |
|
372 |
t1 0 PRIMARY 2 User A 0 NULL NULL BTREE |
|
373 |
t1 1 Host 1 Host A NULL NULL NULL BTREE disabled |
|
374 |
DROP TABLE t1; |
|
375 |
create table t1 (a int); |
|
376 |
alter table t1 rename to ``; |
|
377 |
ERROR 42000: Incorrect table name '' |
|
378 |
rename table t1 to ``; |
|
379 |
ERROR 42000: Incorrect table name '' |
|
380 |
drop table t1; |
|
381 |
drop table if exists t1; |
|
382 |
Warnings: |
|
383 |
Note 1051 Unknown table 't1' |
|
384 |
create table t1 ( a varchar(10) not null primary key ) engine=myisam; |
|
385 |
flush tables; |
|
386 |
alter table t1 modify a varchar(10); |
|
387 |
flush tables; |
|
388 |
alter table t1 modify a varchar(10) not null; |
|
389 |
drop table if exists t1; |
|
390 |
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; |
|
391 |
insert into t1 (a) values(1); |
|
392 |
show table status like 't1'; |
|
393 |
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 |
|
394 |
t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL |
|
395 |
alter table t1 modify a int; |
|
396 |
show table status like 't1'; |
|
397 |
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 |
|
398 |
t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL |
|
399 |
drop table t1; |
|
400 |
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; |
|
401 |
insert into t1 (a) values(1); |
|
402 |
Warnings: |
|
403 |
Warning 1364 Field 'b' doesn't have a default value |
|
404 |
Warning 1364 Field 'c' doesn't have a default value |
|
405 |
Warning 1364 Field 'd' doesn't have a default value |
|
406 |
Warning 1364 Field 'e' doesn't have a default value |
|
407 |
Warning 1364 Field 'f' doesn't have a default value |
|
408 |
Warning 1364 Field 'g' doesn't have a default value |
|
409 |
Warning 1364 Field 'h' doesn't have a default value |
|
410 |
Warning 1364 Field 'i' doesn't have a default value |
|
411 |
show table status like 't1'; |
|
412 |
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 |
|
413 |
t1 MyISAM 10 Fixed 1 37 X X X X X X X X latin1_swedish_ci NULL |
|
414 |
drop table t1; |
|
415 |
set names koi8r; |
|
416 |
create table t1 (a char(10) character set koi8r); |
|
417 |
insert into t1 values ('ÔÅÓÔ'); |
|
418 |
select a,hex(a) from t1; |
|
419 |
a hex(a) |
|
420 |
ÔÅÓÔ D4C5D3D4 |
|
421 |
alter table t1 change a a char(10) character set cp1251; |
|
422 |
select a,hex(a) from t1; |
|
423 |
a hex(a) |
|
424 |
ÔÅÓÔ F2E5F1F2 |
|
425 |
alter table t1 change a a binary(4); |
|
426 |
select a,hex(a) from t1; |
|
427 |
a hex(a) |
|
428 |
òåñò F2E5F1F2 |
|
429 |
alter table t1 change a a char(10) character set cp1251; |
|
430 |
select a,hex(a) from t1; |
|
431 |
a hex(a) |
|
432 |
ÔÅÓÔ F2E5F1F2 |
|
433 |
alter table t1 change a a char(10) character set koi8r; |
|
434 |
select a,hex(a) from t1; |
|
435 |
a hex(a) |
|
436 |
ÔÅÓÔ D4C5D3D4 |
|
437 |
alter table t1 change a a varchar(10) character set cp1251; |
|
438 |
select a,hex(a) from t1; |
|
439 |
a hex(a) |
|
440 |
ÔÅÓÔ F2E5F1F2 |
|
441 |
alter table t1 change a a char(10) character set koi8r; |
|
442 |
select a,hex(a) from t1; |
|
443 |
a hex(a) |
|
444 |
ÔÅÓÔ D4C5D3D4 |
|
445 |
alter table t1 change a a text character set cp1251; |
|
446 |
select a,hex(a) from t1; |
|
447 |
a hex(a) |
|
448 |
ÔÅÓÔ F2E5F1F2 |
|
449 |
alter table t1 change a a char(10) character set koi8r; |
|
450 |
select a,hex(a) from t1; |
|
451 |
a hex(a) |
|
452 |
ÔÅÓÔ D4C5D3D4 |
|
453 |
delete from t1; |
|
454 |
show create table t1; |
|
455 |
Table Create Table |
|
456 |
t1 CREATE TABLE `t1` ( |
|
457 |
`a` char(10) CHARACTER SET koi8r DEFAULT NULL |
|
458 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
459 |
alter table t1 DEFAULT CHARACTER SET latin1; |
|
460 |
show create table t1; |
|
461 |
Table Create Table |
|
462 |
t1 CREATE TABLE `t1` ( |
|
463 |
`a` char(10) CHARACTER SET koi8r DEFAULT NULL |
|
464 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
465 |
alter table t1 CONVERT TO CHARACTER SET latin1; |
|
466 |
show create table t1; |
|
467 |
Table Create Table |
|
468 |
t1 CREATE TABLE `t1` ( |
|
469 |
`a` char(10) DEFAULT NULL |
|
470 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
471 |
alter table t1 DEFAULT CHARACTER SET cp1251; |
|
472 |
show create table t1; |
|
473 |
Table Create Table |
|
474 |
t1 CREATE TABLE `t1` ( |
|
475 |
`a` char(10) CHARACTER SET latin1 DEFAULT NULL |
|
476 |
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 |
|
477 |
drop table t1; |
|
478 |
create table t1 (myblob longblob,mytext longtext) |
|
479 |
default charset latin1 collate latin1_general_cs; |
|
480 |
show create table t1; |
|
481 |
Table Create Table |
|
482 |
t1 CREATE TABLE `t1` ( |
|
483 |
`myblob` longblob, |
|
484 |
`mytext` longtext COLLATE latin1_general_cs |
|
485 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs |
|
486 |
alter table t1 character set latin2; |
|
487 |
show create table t1; |
|
488 |
Table Create Table |
|
489 |
t1 CREATE TABLE `t1` ( |
|
490 |
`myblob` longblob, |
|
491 |
`mytext` longtext CHARACTER SET latin1 COLLATE latin1_general_cs |
|
492 |
) ENGINE=MyISAM DEFAULT CHARSET=latin2 |
|
493 |
drop table t1; |
|
494 |
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE); |
|
495 |
ALTER TABLE t1 DROP PRIMARY KEY; |
|
496 |
SHOW CREATE TABLE t1; |
|
497 |
Table Create Table |
|
498 |
t1 CREATE TABLE `t1` ( |
|
499 |
`a` int(11) NOT NULL, |
|
500 |
`b` int(11) DEFAULT NULL, |
|
501 |
UNIQUE KEY `b` (`b`) |
|
502 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
503 |
ALTER TABLE t1 DROP PRIMARY KEY; |
|
504 |
ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists |
|
505 |
DROP TABLE t1; |
|
506 |
create table t1 (a int, b int, key(a)); |
|
507 |
insert into t1 values (1,1), (2,2); |
|
508 |
alter table t1 drop key no_such_key; |
|
509 |
ERROR 42000: Can't DROP 'no_such_key'; check that column/key exists |
|
510 |
alter table t1 drop key a; |
|
511 |
drop table t1; |
|
512 |
CREATE TABLE T12207(a int) ENGINE=MYISAM; |
|
513 |
ALTER TABLE T12207 DISCARD TABLESPACE; |
|
514 |
ERROR HY000: Table storage engine for 'T12207' doesn't have this option |
|
515 |
DROP TABLE T12207; |
|
516 |
create table t1 (a text) character set koi8r; |
|
517 |
insert into t1 values (_koi8r'ÔÅÓÔ'); |
|
518 |
select hex(a) from t1; |
|
519 |
hex(a) |
|
520 |
D4C5D3D4
|
|
521 |
alter table t1 convert to character set cp1251; |
|
522 |
select hex(a) from t1; |
|
523 |
hex(a) |
|
524 |
F2E5F1F2
|
|
525 |
drop table t1; |
|
526 |
create table t1 ( a timestamp ); |
|
527 |
alter table t1 add unique ( a(1) ); |
|
528 |
ERROR HY000: Incorrect prefix 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 prefix keys |
|
529 |
drop table t1; |
|
530 |
drop table if exists t1; |
|
531 |
create table t1 (a int, key(a)); |
|
532 |
show indexes from t1; |
|
533 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
534 |
t1 1 a 1 a A NULL NULL NULL YES BTREE |
|
535 |
"this used not to disable the index"
|
|
536 |
alter table t1 modify a int, disable keys; |
|
537 |
show indexes from t1; |
|
538 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
539 |
t1 1 a 1 a A NULL NULL NULL YES BTREE disabled |
|
540 |
alter table t1 enable keys; |
|
541 |
show indexes from t1; |
|
542 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
543 |
t1 1 a 1 a A NULL NULL NULL YES BTREE |
|
544 |
alter table t1 modify a bigint, disable keys; |
|
545 |
show indexes from t1; |
|
546 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
547 |
t1 1 a 1 a A NULL NULL NULL YES BTREE disabled |
|
548 |
alter table t1 enable keys; |
|
549 |
show indexes from t1; |
|
550 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
551 |
t1 1 a 1 a A NULL NULL NULL YES BTREE |
|
552 |
alter table t1 add b char(10), disable keys; |
|
553 |
show indexes from t1; |
|
554 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
555 |
t1 1 a 1 a A NULL NULL NULL YES BTREE disabled |
|
556 |
alter table t1 add c decimal(10,2), enable keys; |
|
557 |
show indexes from t1; |
|
558 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
559 |
t1 1 a 1 a A NULL NULL NULL YES BTREE |
|
560 |
"this however did"
|
|
561 |
alter table t1 disable keys; |
|
562 |
show indexes from t1; |
|
563 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
564 |
t1 1 a 1 a A NULL NULL NULL YES BTREE disabled |
|
565 |
desc t1; |
|
566 |
Field Type Null Key Default Extra |
|
567 |
a bigint(20) YES MUL NULL |
|
568 |
b char(10) YES NULL |
|
569 |
c decimal(10,2) YES NULL |
|
570 |
alter table t1 add d decimal(15,5); |
|
571 |
"The key should still be disabled"
|
|
572 |
show indexes from t1; |
|
573 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
574 |
t1 1 a 1 a A NULL NULL NULL YES BTREE disabled |
|
575 |
drop table t1; |
|
576 |
"Now will test with one unique index"
|
|
577 |
create table t1(a int, b char(10), unique(a)); |
|
578 |
show indexes from t1; |
|
579 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
580 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
581 |
alter table t1 disable keys; |
|
582 |
show indexes from t1; |
|
583 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
584 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
585 |
alter table t1 enable keys; |
|
586 |
"If no copy on noop change, this won't touch the data file"
|
|
587 |
"Unique index, no change"
|
|
588 |
alter table t1 modify a int, disable keys; |
|
589 |
show indexes from t1; |
|
590 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
591 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
592 |
"Change the type implying data copy"
|
|
593 |
"Unique index, no change"
|
|
594 |
alter table t1 modify a bigint, disable keys; |
|
595 |
show indexes from t1; |
|
596 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
597 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
598 |
alter table t1 modify a bigint; |
|
599 |
show indexes from t1; |
|
600 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
601 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
602 |
alter table t1 modify a int; |
|
603 |
show indexes from t1; |
|
604 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
605 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
606 |
drop table t1; |
|
607 |
"Now will test with one unique and one non-unique index"
|
|
608 |
create table t1(a int, b char(10), unique(a), key(b)); |
|
609 |
show indexes from t1; |
|
610 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
611 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
612 |
t1 1 b 1 b A NULL NULL NULL YES BTREE |
|
613 |
alter table t1 disable keys; |
|
614 |
show indexes from t1; |
|
615 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
616 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
617 |
t1 1 b 1 b A NULL NULL NULL YES BTREE disabled |
|
618 |
alter table t1 enable keys; |
|
619 |
"If no copy on noop change, this won't touch the data file"
|
|
620 |
"The non-unique index will be disabled"
|
|
621 |
alter table t1 modify a int, disable keys; |
|
622 |
show indexes from t1; |
|
623 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
624 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
625 |
t1 1 b 1 b A NULL NULL NULL YES BTREE disabled |
|
626 |
alter table t1 enable keys; |
|
627 |
show indexes from t1; |
|
628 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
629 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
630 |
t1 1 b 1 b A NULL NULL NULL YES BTREE |
|
631 |
"Change the type implying data copy"
|
|
632 |
"The non-unique index will be disabled"
|
|
633 |
alter table t1 modify a bigint, disable keys; |
|
634 |
show indexes from t1; |
|
635 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
636 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
637 |
t1 1 b 1 b A NULL NULL NULL YES BTREE disabled |
|
638 |
"Change again the type, but leave the indexes as_is"
|
|
639 |
alter table t1 modify a int; |
|
640 |
show indexes from t1; |
|
641 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
642 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
643 |
t1 1 b 1 b A NULL NULL NULL YES BTREE disabled |
|
644 |
"Try the same. When data is no copied on similar tables, this is noop"
|
|
645 |
alter table t1 modify a int; |
|
646 |
show indexes from t1; |
|
647 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
648 |
t1 0 a 1 a A NULL NULL NULL YES BTREE |
|
649 |
t1 1 b 1 b A NULL NULL NULL YES BTREE disabled |
|
650 |
drop table t1; |
|
651 |
create database mysqltest; |
|
652 |
create table t1 (c1 int); |
|
653 |
alter table t1 rename mysqltest.t1; |
|
654 |
drop table t1; |
|
655 |
ERROR 42S02: Unknown table 't1' |
|
656 |
alter table mysqltest.t1 rename t1; |
|
657 |
drop table t1; |
|
658 |
create table t1 (c1 int); |
|
659 |
use mysqltest; |
|
660 |
drop database mysqltest; |
|
661 |
alter table test.t1 rename t1; |
|
662 |
ERROR 3D000: No database selected |
|
663 |
alter table test.t1 rename test.t1; |
|
664 |
use test; |
|
665 |
drop table t1; |
|
666 |
CREATE TABLE t1(a INT) ROW_FORMAT=FIXED; |
|
667 |
CREATE INDEX i1 ON t1(a); |
|
668 |
SHOW CREATE TABLE t1; |
|
669 |
Table Create Table |
|
670 |
t1 CREATE TABLE `t1` ( |
|
671 |
`a` int(11) DEFAULT NULL, |
|
672 |
KEY `i1` (`a`) |
|
673 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |
|
674 |
DROP INDEX i1 ON t1; |
|
675 |
SHOW CREATE TABLE t1; |
|
676 |
Table Create Table |
|
677 |
t1 CREATE TABLE `t1` ( |
|
678 |
`a` int(11) DEFAULT NULL |
|
679 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED |
|
680 |
DROP TABLE t1; |
|
681 |
DROP TABLE IF EXISTS bug24219; |
|
682 |
DROP TABLE IF EXISTS bug24219_2; |
|
683 |
CREATE TABLE bug24219 (a INT, INDEX(a)); |
|
684 |
SHOW INDEX FROM bug24219; |
|
685 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
686 |
bug24219 1 a 1 a A NULL NULL NULL YES BTREE |
|
687 |
ALTER TABLE bug24219 RENAME TO bug24219_2, DISABLE KEYS; |
|
688 |
SHOW INDEX FROM bug24219_2; |
|
689 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
690 |
bug24219_2 1 a 1 a A NULL NULL NULL YES BTREE disabled |
|
691 |
DROP TABLE bug24219_2; |
|
692 |
drop table if exists table_24562; |
|
693 |
create table table_24562( |
|
694 |
section int, |
|
695 |
subsection int, |
|
696 |
title varchar(50)); |
|
697 |
insert into table_24562 values |
|
698 |
(1, 0, "Introduction"), |
|
699 |
(1, 1, "Authors"), |
|
700 |
(1, 2, "Acknowledgements"), |
|
701 |
(2, 0, "Basics"), |
|
702 |
(2, 1, "Syntax"), |
|
703 |
(2, 2, "Client"), |
|
704 |
(2, 3, "Server"), |
|
705 |
(3, 0, "Intermediate"), |
|
706 |
(3, 1, "Complex queries"), |
|
707 |
(3, 2, "Stored Procedures"), |
|
708 |
(3, 3, "Stored Functions"), |
|
709 |
(4, 0, "Advanced"), |
|
710 |
(4, 1, "Replication"), |
|
711 |
(4, 2, "Load balancing"), |
|
712 |
(4, 3, "High availability"), |
|
713 |
(5, 0, "Conclusion"); |
|
714 |
select * from table_24562; |
|
715 |
section subsection title |
|
716 |
1 0 Introduction |
|
717 |
1 1 Authors |
|
718 |
1 2 Acknowledgements |
|
719 |
2 0 Basics |
|
720 |
2 1 Syntax |
|
721 |
2 2 Client |
|
722 |
2 3 Server |
|
723 |
3 0 Intermediate |
|
724 |
3 1 Complex queries |
|
725 |
3 2 Stored Procedures |
|
726 |
3 3 Stored Functions |
|
727 |
4 0 Advanced |
|
728 |
4 1 Replication |
|
729 |
4 2 Load balancing |
|
730 |
4 3 High availability |
|
731 |
5 0 Conclusion |
|
732 |
alter table table_24562 add column reviewer varchar(20), |
|
733 |
order by title; |
|
734 |
select * from table_24562; |
|
735 |
section subsection title reviewer |
|
736 |
1 2 Acknowledgements NULL |
|
737 |
4 0 Advanced NULL |
|
738 |
1 1 Authors NULL |
|
739 |
2 0 Basics NULL |
|
740 |
2 2 Client NULL |
|
741 |
3 1 Complex queries NULL |
|
742 |
5 0 Conclusion NULL |
|
743 |
4 3 High availability NULL |
|
744 |
3 0 Intermediate NULL |
|
745 |
1 0 Introduction NULL |
|
746 |
4 2 Load balancing NULL |
|
747 |
4 1 Replication NULL |
|
748 |
2 3 Server NULL |
|
749 |
3 3 Stored Functions NULL |
|
750 |
3 2 Stored Procedures NULL |
|
751 |
2 1 Syntax NULL |
|
752 |
update table_24562 set reviewer="Me" where section=2; |
|
753 |
update table_24562 set reviewer="You" where section=3; |
|
754 |
alter table table_24562 |
|
755 |
order by section ASC, subsection DESC; |
|
756 |
select * from table_24562; |
|
757 |
section subsection title reviewer |
|
758 |
1 2 Acknowledgements NULL |
|
759 |
1 1 Authors NULL |
|
760 |
1 0 Introduction NULL |
|
761 |
2 3 Server Me |
|
762 |
2 2 Client Me |
|
763 |
2 1 Syntax Me |
|
764 |
2 0 Basics Me |
|
765 |
3 3 Stored Functions You |
|
766 |
3 2 Stored Procedures You |
|
767 |
3 1 Complex queries You |
|
768 |
3 0 Intermediate You |
|
769 |
4 3 High availability NULL |
|
770 |
4 2 Load balancing NULL |
|
771 |
4 1 Replication NULL |
|
772 |
4 0 Advanced NULL |
|
773 |
5 0 Conclusion NULL |
|
774 |
alter table table_24562 |
|
775 |
order by table_24562.subsection ASC, table_24562.section DESC; |
|
776 |
select * from table_24562; |
|
777 |
section subsection title reviewer |
|
778 |
5 0 Conclusion NULL |
|
779 |
4 0 Advanced NULL |
|
780 |
3 0 Intermediate You |
|
781 |
2 0 Basics Me |
|
782 |
1 0 Introduction NULL |
|
783 |
4 1 Replication NULL |
|
784 |
3 1 Complex queries You |
|
785 |
2 1 Syntax Me |
|
786 |
1 1 Authors NULL |
|
787 |
4 2 Load balancing NULL |
|
788 |
3 2 Stored Procedures You |
|
789 |
2 2 Client Me |
|
790 |
1 2 Acknowledgements NULL |
|
791 |
4 3 High availability NULL |
|
792 |
3 3 Stored Functions You |
|
793 |
2 3 Server Me |
|
794 |
alter table table_24562 order by 12; |
|
795 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '12' at line 1 |
|
796 |
alter table table_24562 order by (section + 12); |
|
797 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(section + 12)' at line 1 |
|
798 |
alter table table_24562 order by length(title); |
|
799 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(title)' at line 1 |
|
800 |
alter table table_24562 order by (select 12 from dual); |
|
801 |
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select 12 from dual)' at line 1 |
|
802 |
alter table table_24562 order by no_such_col; |
|
803 |
ERROR 42S22: Unknown column 'no_such_col' in 'order clause' |
|
804 |
drop table table_24562; |
|
805 |
create table t1 (mycol int(10) not null); |
|
806 |
alter table t1 alter column mycol set default 0; |
|
807 |
desc t1; |
|
808 |
Field Type Null Key Default Extra |
|
809 |
mycol int(10) NO 0 |
|
810 |
drop table t1; |
|
811 |
create table t1(id int(8) primary key auto_increment) engine=heap; |
|
812 |
insert into t1 values (null); |
|
813 |
insert into t1 values (null); |
|
814 |
select * from t1; |
|
815 |
id
|
|
816 |
1
|
|
817 |
2
|
|
818 |
alter table t1 auto_increment = 50; |
|
819 |
alter table t1 engine = myisam; |
|
820 |
insert into t1 values (null); |
|
821 |
select * from t1; |
|
822 |
id
|
|
823 |
1
|
|
824 |
2
|
|
825 |
50
|
|
826 |
alter table t1 engine = heap; |
|
827 |
insert into t1 values (null); |
|
828 |
select * from t1; |
|
829 |
id
|
|
830 |
1
|
|
831 |
2
|
|
832 |
50
|
|
833 |
51
|
|
834 |
drop table t1; |
|
835 |
set @orig_sql_mode = @@sql_mode; |
|
836 |
set sql_mode="no_zero_date"; |
|
837 |
create table t1(f1 int); |
|
838 |
alter table t1 add column f2 datetime not null, add column f21 date not null; |
|
839 |
insert into t1 values(1,'2000-01-01','2000-01-01'); |
|
840 |
alter table t1 add column f3 datetime not null; |
|
841 |
ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'f3' at row 1 |
|
842 |
alter table t1 add column f3 date not null; |
|
843 |
ERROR 22007: Incorrect date value: '0000-00-00' for column 'f3' at row 1 |
|
844 |
alter table t1 add column f4 datetime not null default '2002-02-02', |
|
845 |
add column f41 date not null; |
|
846 |
ERROR 22007: Incorrect date value: '0000-00-00' for column 'f41' at row 1 |
|
847 |
alter table t1 add column f4 datetime not null default '2002-02-02', |
|
848 |
add column f41 date not null default '2002-02-02'; |
|
849 |
select * from t1; |
|
850 |
f1 f2 f21 f4 f41 |
|
851 |
1 2000-01-01 00:00:00 2000-01-01 2002-02-02 00:00:00 2002-02-02 |
|
852 |
drop table t1; |
|
853 |
set sql_mode= @orig_sql_mode; |
|
854 |
create table t1 (v varchar(32)); |
|
855 |
insert into t1 values ('def'),('abc'),('hij'),('3r4f'); |
|
856 |
select * from t1; |
|
857 |
v
|
|
858 |
def
|
|
859 |
abc
|
|
860 |
hij
|
|
861 |
3r4f |
|
862 |
alter table t1 change v v2 varchar(32); |
|
863 |
select * from t1; |
|
864 |
v2
|
|
865 |
def
|
|
866 |
abc
|
|
867 |
hij
|
|
868 |
3r4f |
|
869 |
alter table t1 change v2 v varchar(64); |
|
870 |
select * from t1; |
|
871 |
v
|
|
872 |
def
|
|
873 |
abc
|
|
874 |
hij
|
|
875 |
3r4f |
|
876 |
update t1 set v = 'lmn' where v = 'hij'; |
|
877 |
select * from t1; |
|
878 |
v
|
|
879 |
def
|
|
880 |
abc
|
|
881 |
lmn
|
|
882 |
3r4f |
|
883 |
alter table t1 add i int auto_increment not null primary key first; |
|
884 |
select * from t1; |
|
885 |
i v |
|
886 |
1 def |
|
887 |
2 abc |
|
888 |
3 lmn |
|
889 |
4 3r4f |
|
890 |
update t1 set i=5 where i=3; |
|
891 |
select * from t1; |
|
892 |
i v |
|
893 |
1 def |
|
894 |
2 abc |
|
895 |
5 lmn |
|
896 |
4 3r4f |
|
897 |
alter table t1 change i i bigint; |
|
898 |
select * from t1; |
|
899 |
i v |
|
900 |
1 def |
|
901 |
2 abc |
|
902 |
5 lmn |
|
903 |
4 3r4f |
|
904 |
alter table t1 add unique key (i, v); |
|
905 |
select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); |
|
906 |
i v |
|
907 |
4 3r4f |
|
908 |
drop table t1; |
|
909 |
create table t1 (t varchar(255) default null, key t (t(80))) |
|
910 |
engine=myisam default charset=latin1; |
|
911 |
alter table t1 change t t text; |
|
912 |
drop table t1; |
|
913 |
CREATE TABLE t1 (a varchar(500)); |
|
914 |
ALTER TABLE t1 ADD b GEOMETRY NOT NULL, ADD SPATIAL INDEX(b); |
|
915 |
SHOW CREATE TABLE t1; |
|
916 |
Table Create Table |
|
917 |
t1 CREATE TABLE `t1` ( |
|
918 |
`a` varchar(500) DEFAULT NULL, |
|
919 |
`b` geometry NOT NULL, |
|
920 |
SPATIAL KEY `b` (`b`) |
|
921 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
922 |
ALTER TABLE t1 ADD KEY(b(50)); |
|
923 |
SHOW CREATE TABLE t1; |
|
924 |
Table Create Table |
|
925 |
t1 CREATE TABLE `t1` ( |
|
926 |
`a` varchar(500) DEFAULT NULL, |
|
927 |
`b` geometry NOT NULL, |
|
928 |
SPATIAL KEY `b` (`b`), |
|
929 |
KEY `b_2` (`b`(50)) |
|
930 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
931 |
ALTER TABLE t1 ADD c POINT; |
|
932 |
SHOW CREATE TABLE t1; |
|
933 |
Table Create Table |
|
934 |
t1 CREATE TABLE `t1` ( |
|
935 |
`a` varchar(500) DEFAULT NULL, |
|
936 |
`b` geometry NOT NULL, |
|
937 |
`c` point DEFAULT NULL, |
|
938 |
SPATIAL KEY `b` (`b`), |
|
939 |
KEY `b_2` (`b`(50)) |
|
940 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
941 |
CREATE TABLE t2 (a INT, KEY (a(20))); |
|
942 |
ERROR HY000: Incorrect prefix 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 prefix keys |
|
943 |
ALTER TABLE t1 ADD d INT; |
|
944 |
ALTER TABLE t1 ADD KEY (d(20)); |
|
945 |
ERROR HY000: Incorrect prefix 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 prefix keys |
|
946 |
ALTER TABLE t1 ADD e GEOMETRY NOT NULL, ADD SPATIAL KEY (e(30)); |
|
947 |
ERROR HY000: Incorrect prefix 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 prefix keys |
|
948 |
DROP TABLE t1; |
|
949 |
CREATE TABLE t1 (s CHAR(8) BINARY); |
|
950 |
INSERT INTO t1 VALUES ('test'); |
|
951 |
SELECT LENGTH(s) FROM t1; |
|
952 |
LENGTH(s) |
|
953 |
4
|
|
954 |
ALTER TABLE t1 MODIFY s CHAR(10) BINARY; |
|
955 |
SELECT LENGTH(s) FROM t1; |
|
956 |
LENGTH(s) |
|
957 |
4
|
|
958 |
DROP TABLE t1; |
|
959 |
CREATE TABLE t1 (s BINARY(8)); |
|
960 |
INSERT INTO t1 VALUES ('test'); |
|
961 |
SELECT LENGTH(s) FROM t1; |
|
962 |
LENGTH(s) |
|
963 |
8
|
|
964 |
SELECT HEX(s) FROM t1; |
|
965 |
HEX(s) |
|
966 |
7465737400000000
|
|
967 |
ALTER TABLE t1 MODIFY s BINARY(10); |
|
968 |
SELECT HEX(s) FROM t1; |
|
969 |
HEX(s) |
|
970 |
74657374000000000000
|
|
971 |
SELECT LENGTH(s) FROM t1; |
|
972 |
LENGTH(s) |
|
973 |
10
|
|
974 |
DROP TABLE t1; |
|
975 |
CREATE TABLE t1 (v VARCHAR(3), b INT); |
|
976 |
INSERT INTO t1 VALUES ('abc', 5); |
|
977 |
SELECT * FROM t1; |
|
978 |
v b |
|
979 |
abc 5 |
|
980 |
ALTER TABLE t1 MODIFY COLUMN v VARCHAR(4); |
|
981 |
SELECT * FROM t1; |
|
982 |
v b |
|
983 |
abc 5 |
|
984 |
DROP TABLE t1; |
|
985 |
End of 5.0 tests |
|
986 |
drop table if exists t1, t2, t3; |
|
987 |
create table t1 (i int); |
|
988 |
create table t3 (j int); |
|
989 |
insert into t1 values (); |
|
990 |
insert into t3 values (); |
|
991 |
lock table t1 write, t3 read; |
|
992 |
alter table t1 modify i int default 1; |
|
993 |
insert into t1 values (); |
|
994 |
select * from t1; |
|
995 |
i
|
|
996 |
NULL
|
|
997 |
1
|
|
998 |
alter table t1 change i c char(10) default "Two"; |
|
999 |
insert into t1 values (); |
|
1000 |
select * from t1; |
|
1001 |
c
|
|
1002 |
NULL
|
|
1003 |
1
|
|
1004 |
Two
|
|
1005 |
alter table t1 modify c char(10) default "Three", rename to t2; |
|
1006 |
select * from t1; |
|
1007 |
ERROR HY000: Table 't1' was not locked with LOCK TABLES |
|
1008 |
select * from t2; |
|
1009 |
ERROR HY000: Table 't2' was not locked with LOCK TABLES |
|
1010 |
select * from t3; |
|
1011 |
j
|
|
1012 |
NULL
|
|
1013 |
unlock tables; |
|
1014 |
insert into t2 values (); |
|
1015 |
select * from t2; |
|
1016 |
c
|
|
1017 |
NULL
|
|
1018 |
1
|
|
1019 |
Two
|
|
1020 |
Three
|
|
1021 |
lock table t2 write, t3 read; |
|
1022 |
alter table t2 change c vc varchar(100) default "Four", rename to t1; |
|
1023 |
select * from t1; |
|
1024 |
ERROR HY000: Table 't1' was not locked with LOCK TABLES |
|
1025 |
select * from t2; |
|
1026 |
ERROR HY000: Table 't2' was not locked with LOCK TABLES |
|
1027 |
select * from t3; |
|
1028 |
j
|
|
1029 |
NULL
|
|
1030 |
unlock tables; |
|
1031 |
insert into t1 values (); |
|
1032 |
select * from t1; |
|
1033 |
vc
|
|
1034 |
NULL
|
|
1035 |
1
|
|
1036 |
Two
|
|
1037 |
Three
|
|
1038 |
Four
|
|
1039 |
drop tables t1, t3; |
|
1040 |
DROP TABLE IF EXISTS `t+1`, `t+2`; |
|
1041 |
CREATE TABLE `t+1` (c1 INT); |
|
1042 |
ALTER TABLE `t+1` RENAME `t+2`; |
|
1043 |
CREATE TABLE `t+1` (c1 INT); |
|
1044 |
ALTER TABLE `t+1` RENAME `t+2`; |
|
1045 |
ERROR 42S01: Table 't+2' already exists |
|
1046 |
DROP TABLE `t+1`, `t+2`; |
|
1047 |
CREATE TEMPORARY TABLE `tt+1` (c1 INT); |
|
1048 |
ALTER TABLE `tt+1` RENAME `tt+2`; |
|
1049 |
CREATE TEMPORARY TABLE `tt+1` (c1 INT); |
|
1050 |
ALTER TABLE `tt+1` RENAME `tt+2`; |
|
1051 |
ERROR 42S01: Table 'tt+2' already exists |
|
1052 |
SHOW CREATE TABLE `tt+1`; |
|
1053 |
Table Create Table |
|
1054 |
tt+1 CREATE TEMPORARY TABLE `tt+1` ( |
|
1055 |
`c1` int(11) DEFAULT NULL |
|
1056 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
1057 |
SHOW CREATE TABLE `tt+2`; |
|
1058 |
Table Create Table |
|
1059 |
tt+2 CREATE TEMPORARY TABLE `tt+2` ( |
|
1060 |
`c1` int(11) DEFAULT NULL |
|
1061 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
1062 |
DROP TABLE `tt+1`, `tt+2`; |
|
1063 |
CREATE TABLE `#sql1` (c1 INT); |
|
1064 |
CREATE TABLE `@0023sql2` (c1 INT); |
|
1065 |
SHOW TABLES; |
|
1066 |
Tables_in_test
|
|
1067 |
#sql1
|
|
1068 |
@0023sql2 |
|
1069 |
RENAME TABLE `#sql1` TO `@0023sql1`; |
|
1070 |
RENAME TABLE `@0023sql2` TO `#sql2`; |
|
1071 |
SHOW TABLES; |
|
1072 |
Tables_in_test
|
|
1073 |
#sql2
|
|
1074 |
@0023sql1 |
|
1075 |
ALTER TABLE `@0023sql1` RENAME `#sql-1`; |
|
1076 |
ALTER TABLE `#sql2` RENAME `@0023sql-2`; |
|
1077 |
SHOW TABLES; |
|
1078 |
Tables_in_test
|
|
1079 |
#sql-1
|
|
1080 |
@0023sql-2 |
|
1081 |
INSERT INTO `#sql-1` VALUES (1); |
|
1082 |
INSERT INTO `@0023sql-2` VALUES (2); |
|
1083 |
DROP TABLE `#sql-1`, `@0023sql-2`; |
|
1084 |
CREATE TEMPORARY TABLE `#sql1` (c1 INT); |
|
1085 |
CREATE TEMPORARY TABLE `@0023sql2` (c1 INT); |
|
1086 |
SHOW TABLES; |
|
1087 |
Tables_in_test
|
|
1088 |
ALTER TABLE `#sql1` RENAME `@0023sql1`; |
|
1089 |
ALTER TABLE `@0023sql2` RENAME `#sql2`; |
|
1090 |
SHOW TABLES; |
|
1091 |
Tables_in_test
|
|
1092 |
INSERT INTO `#sql2` VALUES (1); |
|
1093 |
INSERT INTO `@0023sql1` VALUES (2); |
|
1094 |
SHOW CREATE TABLE `#sql2`; |
|
1095 |
Table Create Table |
|
1096 |
#sql2 CREATE TEMPORARY TABLE `#sql2` (
|
|
1097 |
`c1` int(11) DEFAULT NULL |
|
1098 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
1099 |
SHOW CREATE TABLE `@0023sql1`; |
|
1100 |
Table Create Table |
|
1101 |
@0023sql1 CREATE TEMPORARY TABLE `@0023sql1` ( |
|
1102 |
`c1` int(11) DEFAULT NULL |
|
1103 |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
|
1104 |
DROP TABLE `#sql2`, `@0023sql1`; |
|
1105 |
DROP TABLE IF EXISTS t1; |
|
1106 |
DROP TABLE IF EXISTS t2; |
|
1107 |
CREATE TABLE t1 ( |
|
1108 |
int_field INTEGER UNSIGNED NOT NULL, |
|
1109 |
char_field CHAR(10), |
|
1110 |
INDEX(`int_field`) |
|
1111 |
);
|
|
1112 |
DESCRIBE t1; |
|
1113 |
Field Type Null Key Default Extra |
|
1114 |
int_field int(10) unsigned NO MUL NULL |
|
1115 |
char_field char(10) YES NULL |
|
1116 |
SHOW INDEXES FROM t1; |
|
1117 |
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment |
|
1118 |
t1 1 int_field 1 int_field A NULL NULL NULL BTREE |
|
1119 |
INSERT INTO t1 VALUES (1, "edno"), (1, "edno"), (2, "dve"), (3, "tri"), (5, "pet"); |
|
1120 |
"Non-copy data change - new frm, but old data and index files"
|
|
1121 |
ALTER TABLE t1 |
|
1122 |
CHANGE int_field unsigned_int_field INTEGER UNSIGNED NOT NULL, |
|
1123 |
RENAME t2; |
|
1124 |
SELECT * FROM t1 ORDER BY int_field; |
|
1125 |
ERROR 42S02: Table 'test.t1' doesn't exist |
|
1126 |
SELECT * FROM t2 ORDER BY unsigned_int_field; |
|
1127 |
unsigned_int_field char_field |
|
1128 |
1 edno |
|
1129 |
1 edno |
|
1130 |
2 dve |
|
1131 |
3 tri |
|
1132 |
5 pet |
|
1133 |
DESCRIBE t2; |
|
1134 |
Field Type Null Key Default Extra |
|
1135 |
unsigned_int_field int(10) unsigned NO MUL NULL |
|
1136 |
char_field char(10) YES NULL |
|
1137 |
DESCRIBE t2; |
|
1138 |
Field Type Null Key Default Extra |
|
1139 |
unsigned_int_field int(10) unsigned NO MUL NULL |
|
1140 |
char_field char(10) YES NULL |
|
1141 |
ALTER TABLE t2 MODIFY unsigned_int_field BIGINT UNSIGNED NOT NULL; |
|
1142 |
DESCRIBE t2; |
|
1143 |
Field Type Null Key Default Extra |
|
1144 |
unsigned_int_field bigint(20) unsigned NO MUL NULL |
|
1145 |
char_field char(10) YES NULL |
|
1146 |
DROP TABLE t2; |
|
1147 |
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); |
|
1148 |
INSERT INTO t1 VALUES (1, 2, NULL); |
|
1149 |
SELECT * FROM t1; |
|
1150 |
f1 f2 f3 |
|
1151 |
1 2 NULL |
|
1152 |
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f1; |
|
1153 |
SELECT * FROM t1; |
|
1154 |
f1 f3 f2 |
|
1155 |
1 NULL 2 |
|
1156 |
ALTER TABLE t1 MODIFY COLUMN f3 INT AFTER f2; |
|
1157 |
SELECT * FROM t1; |
|
1158 |
f1 f2 f3 |
|
1159 |
1 2 NULL |
|
1160 |
DROP TABLE t1; |
|
1161 |
create table t1 (c char(10) default "Two"); |
|
1162 |
lock table t1 write; |
|
1163 |
insert into t1 values (); |
|
1164 |
alter table t1 modify c char(10) default "Three"; |
|
1165 |
unlock tables; |
|
1166 |
select * from t1; |
|
1167 |
c
|
|
1168 |
Two
|
|
1169 |
check table t1; |
|
1170 |
Table Op Msg_type Msg_text |
|
1171 |
test.t1 check status OK |
|
1172 |
drop table t1; |