1
drop table if exists t1;
2
drop table if exists t2;
4
create TEMPORARY table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
5
insert into t1 values (1,1),(NULL,3),(NULL,4);
6
delete from t1 where a=4;
7
insert into t1 values (NULL,5),(NULL,6);
14
delete from t1 where a=6;
15
show table status like "t1";
16
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
17
# test t1 TEMPORARY MyISAM # # # # #
18
replace t1 values (3,1);
19
ALTER TABLE t1 add c int;
20
replace t1 values (3,3,3);
21
insert into t1 values (NULL,7,7);
22
update t1 set a=8,b=b+1,c=c+1 where a=7;
23
insert into t1 values (NULL,9,9);
33
skey int NOT NULL auto_increment PRIMARY KEY,
36
insert into t1 values (NULL, "hello");
37
insert into t1 values (NULL, "hey");
42
select _rowid,t1._rowid,skey,sval from t1;
43
_rowid _rowid skey sval
47
create table t1 (a int not null primary key auto_increment);
48
insert into t1 values (0);
54
Table Op Msg_type Msg_text
55
test.t1 check status OK
57
create table t1 (a int not null auto_increment primary key);
58
insert into t1 values (NULL);
59
insert into t1 values (-1);
60
select last_insert_id();
63
insert into t1 values (NULL);
70
create temporary table t1 (a int not null auto_increment primary key) /*!40102 engine=MEMORY */;
71
insert into t1 values (NULL);
72
insert into t1 values (-1);
73
select last_insert_id();
76
insert into t1 values (NULL);
83
create table t1 (i int not null auto_increment, key (i));
84
insert into t1 set i = 254;
85
insert into t1 set i = null;
86
select last_insert_id();
89
insert into t1 set i = null;
90
select last_insert_id();
94
create table t1 (i int not null auto_increment primary key, b int, unique (b));
95
insert into t1 values (NULL, 10);
96
select last_insert_id();
99
insert into t1 values (NULL, 15);
100
select last_insert_id();
103
insert into t1 values (NULL, 10);
104
Got one of the listed errors
105
select last_insert_id();
109
create table t1(a int auto_increment,b int null,primary key(a));
110
insert into t1(a,b)values(NULL,1);
111
insert into t1(a,b)values(200,2);
112
insert into t1(a,b)values(0,3);
113
insert into t1(b)values(4);
114
insert into t1(b)values(5);
115
insert into t1(b)values(6);
116
insert into t1(b)values(7);
117
select * from t1 order by b;
126
alter table t1 modify b int;
130
select * from t1 order by b;
139
create table t2 (a int primary key);
140
insert t2 values (1),(2);
141
alter table t2 drop primary key, add b int auto_increment primary key;
147
delete from t1 where a=0;
156
show create table t1;
158
t1 CREATE TABLE `t1` (
159
`a` INT NOT NULL AUTO_INCREMENT,
160
`b` INT DEFAULT NULL,
161
PRIMARY KEY (`a`) USING BTREE
162
) ENGINE=InnoDB COLLATE = utf8_general_ci
163
update t1 set a=0 where b=5;
164
select * from t1 order by b;
172
delete from t1 where a=0;
173
update t1 set a=NULL where b=6;
174
ERROR 23000: Column 'a' cannot be null
175
update t1 set a=300 where b=7;
176
insert into t1(a,b)values(NULL,8);
177
insert into t1(a,b)values(400,9);
178
insert into t1(a,b)values(0,10);
179
insert into t1(b)values(11);
180
insert into t1(b)values(12);
181
insert into t1(b)values(13);
182
insert into t1(b)values(14);
183
select * from t1 order by b;
197
delete from t1 where a=0;
198
update t1 set a=0 where b=12;
199
select * from t1 order by b;
212
delete from t1 where a=0;
213
update t1 set a=NULL where b=13;
214
ERROR 23000: Column 'a' cannot be null
215
update t1 set a=500 where b=14;
216
select * from t1 order by b;
229
create table t1 (a bigint, b int auto_increment primary key);
230
insert into t1 (a) values (1), (2), (3), (NULL), (NULL);
231
alter table t1 drop column b, modify a bigint not null auto_increment primary key;
240
create table t1 (a bigint, b int auto_increment primary key);
241
insert into t1 (a) values (1), (2), (3), (0), (0);
242
alter table t1 drop column b, modify a bigint not null auto_increment primary key;
243
Got one of the listed errors
252
create table t1 (a bigint, b int auto_increment primary key);
253
insert into t1 (a) values (0), (1), (2), (3);
254
alter table t1 drop column b, modify a bigint not null auto_increment primary key;
262
create table t1 (a int auto_increment primary key , b int null);
263
insert into t1 values (0,1),(1,2),(2,3);
269
alter table t1 modify b varchar(255);
270
insert into t1 values (0,4);
271
Got one of the listed errors
278
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b VARBINARY(1000), PRIMARY KEY (a,b(10)));
279
INSERT INTO t1 (b) VALUES ('aaaa');
281
Table Op Msg_type Msg_text
282
test.t1 check status OK
283
INSERT INTO t1 (b) VALUES ('');
285
Table Op Msg_type Msg_text
286
test.t1 check status OK
287
INSERT INTO t1 (b) VALUES ('bbbb');
289
Table Op Msg_type Msg_text
290
test.t1 check status OK
291
DROP TABLE IF EXISTS t1;
293
t1_name VARCHAR(255) DEFAULT NULL,
294
t1_id INT not null AUTO_INCREMENT,
297
) AUTO_INCREMENT = 1000;
299
Warning 1071 Specified key was too long; max key length is 767 bytes
300
INSERT INTO t1 (t1_name) VALUES('MySQL');
301
INSERT INTO t1 (t1_name) VALUES('MySQL');
302
INSERT INTO t1 (t1_name) VALUES('MySQL');
308
SHOW CREATE TABLE `t1`;
310
t1 CREATE TABLE `t1` (
311
`t1_name` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL,
312
`t1_id` INT NOT NULL AUTO_INCREMENT,
313
PRIMARY KEY (`t1_id`) USING BTREE,
314
KEY `t1_name` (`t1_name`(191)) USING BTREE
315
) ENGINE=InnoDB COLLATE = utf8_general_ci AUTO_INCREMENT=1000
317
create table t1(a int not null auto_increment primary key);
318
create table t2(a int not null auto_increment primary key, t1a int);
319
insert into t1 values(NULL);
320
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
321
insert into t1 values (NULL);
322
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
323
(NULL, LAST_INSERT_ID());
324
insert into t1 values (NULL);
325
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
326
(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
340
CREATE TABLE t1 (t1 INT PRIMARY KEY, t2 INT);
341
INSERT INTO t1 VALUES(0, 0);
342
INSERT INTO t1 VALUES(1, 1);
343
ALTER TABLE t1 CHANGE t1 t1 INT auto_increment;
344
INSERT INTO t1 VALUES(0,0);
345
Got one of the listed errors