auto_increment.result 10.3 KB
Newer Older
unknown's avatar
unknown committed
1
drop table if exists t1;
2
drop table if exists t2;
unknown's avatar
unknown committed
3
SET SQL_WARNINGS=1;
unknown's avatar
unknown committed
4
create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
unknown's avatar
unknown committed
5 6 7 8
insert into t1 values (1,1),(NULL,3),(NULL,4);
delete from t1 where a=4;
insert into t1 values (NULL,5),(NULL,6);
select * from t1;
9 10 11 12 13
a	b
1	1
3	3
5	5
6	6
unknown's avatar
unknown committed
14 15 16 17 18 19 20 21
delete from t1 where a=6;
replace t1 values (3,1);
ALTER TABLE t1 add c int;
replace t1 values (3,3,3);
insert into t1 values (NULL,7,7);
update t1 set a=8,b=b+1,c=c+1 where a=7;
insert into t1 values (NULL,9,9);
select * from t1;
22 23 24 25 26 27
a	b	c
1	1	NULL
3	3	3
5	5	NULL
8	8	8
9	9	9
unknown's avatar
unknown committed
28 29 30 31 32 33 34 35
drop table t1;
create table t1 (
skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
sval char(20)
);
insert into t1 values (NULL, "hello");
insert into t1 values (NULL, "hey");
select * from t1;
36 37 38
skey	sval
1	hello
2	hey
unknown's avatar
unknown committed
39
select _rowid,t1._rowid,skey,sval from t1;
40 41 42
_rowid	_rowid	skey	sval
1	1	1	hello
2	2	2	hey
unknown's avatar
unknown committed
43 44 45 46 47 48 49 50 51
drop table t1;
create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
insert into t1 values ("a",1),("b",2),("a",2),("c",1);
insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
insert into t1 (a) values ("a"),("b"),("c"),("d");
insert into t1 (a) values ('k'),('d');
insert into t1 (a) values ("a");
insert into t1 values ("d",last_insert_id());
select * from t1;
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
a	b
a	1
a	2
a	3
a	4
a	5
b	2
b	3
b	4
c	1
c	2
c	3
d	1
d	2
d	5
e	1
k	1
unknown's avatar
unknown committed
69 70 71 72
drop table t1;
create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ordid), index(ord,ordid));
insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
select * from t1;
73 74 75
ordid	ord
1	sdj
2	sdj
unknown's avatar
unknown committed
76 77 78 79
drop table t1;
create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid));
insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
select * from t1;
80 81 82 83 84 85 86 87
ordid	ord
1	abc
2	abc
3	abc
1	sdj
2	sdj
3	sdj
1	zzz
unknown's avatar
unknown committed
88
drop table t1;
89 90 91 92 93 94 95 96 97 98
create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid,  id));
create table t2 (sid char(20), id int(2));
insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
insert into t1 select * from t2;
select * from t1;
sid	id
skr	1
skr	2
test	1
drop table t1,t2;
unknown's avatar
unknown committed
99 100 101 102
create table t1 (a int not null primary key auto_increment);
insert into t1 values (0);
update t1 set a=0;
select * from t1;
unknown's avatar
unknown committed
103 104
a
0
unknown's avatar
unknown committed
105
check table t1;
unknown's avatar
unknown committed
106 107 108
Table	Op	Msg_type	Msg_text
test.t1	check	warning	Found row where the auto_increment column has the value 0
test.t1	check	status	OK
unknown's avatar
unknown committed
109
drop table t1;
unknown's avatar
unknown committed
110 111 112 113 114 115 116 117 118 119 120 121 122
create table t1 (a int not null auto_increment primary key);
insert into t1 values (NULL);
insert into t1 values (-1);
select last_insert_id();
last_insert_id()
1
insert into t1 values (NULL);
select * from t1;
a
-1
1
2
drop table t1;
unknown's avatar
unknown committed
123
create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
unknown's avatar
unknown committed
124 125 126 127 128 129 130 131 132
insert into t1 values (NULL);
insert into t1 values (-1);
select last_insert_id();
last_insert_id()
1
insert into t1 values (NULL);
select * from t1;
a
1
unknown's avatar
unknown committed
133
-1
unknown's avatar
unknown committed
134 135
2
drop table t1;
136 137 138 139 140 141
create table t1 (i tinyint unsigned not null auto_increment primary key);
insert into t1 set i = 254;
insert into t1 set i = null;
select last_insert_id();
last_insert_id()
255
142
explain extended select last_insert_id();
143 144
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
145
Warnings:
146
Note	1003	select last_insert_id() AS `last_insert_id()`
147
insert into t1 set i = 254;
148
ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
149 150 151
select last_insert_id();
last_insert_id()
255
152
insert into t1 set i = null;
153
ERROR 23000: Duplicate entry '255' for key 'PRIMARY'
154 155
select last_insert_id();
last_insert_id()
156
255
157 158 159 160 161 162 163 164
drop table t1;
create table t1 (i tinyint unsigned not null auto_increment, key (i));
insert into t1 set i = 254;
insert into t1 set i = null;
select last_insert_id();
last_insert_id()
255
insert into t1 set i = null;
unknown's avatar
unknown committed
165
Warnings:
166
Warning	1264	Out of range value for column 'i' at row 1
167 168 169 170 171 172 173 174 175 176 177 178 179 180
select last_insert_id();
last_insert_id()
255
drop table t1;
create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
insert into t1 values (NULL, 10);
select last_insert_id();
last_insert_id()
1
insert into t1 values (NULL, 15);
select last_insert_id();
last_insert_id()
2
insert into t1 values (NULL, 10);
181
ERROR 23000: Duplicate entry '10' for key 'b'
182 183
select last_insert_id();
last_insert_id()
184
2
185
drop table t1;
186 187 188 189 190 191
create table t1(a int auto_increment,b int null,primary key(a));
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
insert into t1(a,b)values(NULL,1);
insert into t1(a,b)values(200,2);
insert into t1(a,b)values(0,3);
insert into t1(b)values(4);
unknown's avatar
unknown committed
192 193 194 195
insert into t1(b)values(5);
insert into t1(b)values(6);
insert into t1(b)values(7);
select * from t1 order by b;
196 197 198 199 200 201
a	b
1	1
200	2
0	3
201	4
202	5
unknown's avatar
unknown committed
202 203
203	6
204	7
unknown's avatar
unknown committed
204 205 206 207 208
alter table t1 modify b mediumint;
select * from t1 order by b;
a	b
1	1
200	2
209
0	3
unknown's avatar
unknown committed
210 211 212 213
201	4
202	5
203	6
204	7
214 215 216 217 218 219 220 221
create table t2 (a int);
insert t2 values (1),(2);
alter table t2 add b int auto_increment primary key;
select * from t2;
a	b
1	1
2	2
drop table t2;
unknown's avatar
unknown committed
222 223 224 225 226 227 228 229 230 231 232 233
delete from t1 where a=0;
update t1 set a=0 where b=5;
select * from t1 order by b;
a	b
1	1
200	2
201	4
0	5
203	6
204	7
delete from t1 where a=0;
update t1 set a=NULL where b=6;
234 235
Warnings:
Warning	1048	Column 'a' cannot be null
unknown's avatar
unknown committed
236 237 238 239 240 241 242 243 244 245 246 247 248 249
update t1 set a=300 where b=7;
SET SQL_MODE='';
insert into t1(a,b)values(NULL,8);
insert into t1(a,b)values(400,9);
insert into t1(a,b)values(0,10);
insert into t1(b)values(11);
insert into t1(b)values(12);
insert into t1(b)values(13);
insert into t1(b)values(14);
select * from t1 order by b;
a	b
1	1
200	2
201	4
250
0	6
unknown's avatar
unknown committed
251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275
300	7
301	8
400	9
401	10
402	11
403	12
404	13
405	14
delete from t1 where a=0;
update t1 set a=0 where b=12;
select * from t1 order by b;
a	b
1	1
200	2
201	4
300	7
301	8
400	9
401	10
402	11
0	12
404	13
405	14
delete from t1 where a=0;
update t1 set a=NULL where b=13;
276 277
Warnings:
Warning	1048	Column 'a' cannot be null
unknown's avatar
unknown committed
278 279 280 281 282 283 284 285 286 287 288
update t1 set a=500 where b=14;
select * from t1 order by b;
a	b
1	1
200	2
201	4
300	7
301	8
400	9
401	10
402	11
289
0	13
unknown's avatar
unknown committed
290
500	14
291
drop table t1;
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343
create table t1 (a bigint);
insert into t1 values (1), (2), (3), (NULL), (NULL);
alter table t1 modify a bigint not null auto_increment primary key;
select * from t1;
a
1
2
3
4
5
drop table t1;
create table t1 (a bigint);
insert into t1 values (1), (2), (3), (0), (0);
alter table t1 modify a bigint not null auto_increment primary key;
select * from t1;
a
1
2
3
4
5
drop table t1;
create table t1 (a bigint);
insert into t1 values (0), (1), (2), (3);
set sql_mode=NO_AUTO_VALUE_ON_ZERO;
alter table t1 modify a bigint not null auto_increment primary key;
set sql_mode= '';
select * from t1;
a
0
1
2
3
drop table t1;
create table t1 (a int auto_increment primary key , b int null);
set sql_mode=NO_AUTO_VALUE_ON_ZERO;
insert into t1 values (0,1),(1,2),(2,3);
select * from t1;
a	b
0	1
1	2
2	3
set sql_mode= '';
alter table t1 modify b varchar(255);
insert into t1 values (0,4);
select * from t1;
a	b
0	1
1	2
2	3
3	4
drop table t1;
344 345 346 347 348 349 350 351 352 353 354 355 356 357
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
INSERT INTO t1 (b) VALUES ('aaaa');
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1 (b) VALUES ('');
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
INSERT INTO t1 (b) VALUES ('bbbb');
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE IF EXISTS t1;
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374
CREATE TABLE `t1` (
t1_name VARCHAR(255) DEFAULT NULL,
t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
KEY (t1_name),
PRIMARY KEY (t1_id)
) AUTO_INCREMENT = 1000;
INSERT INTO t1 (t1_name) VALUES('MySQL');
INSERT INTO t1 (t1_name) VALUES('MySQL');
INSERT INTO t1 (t1_name) VALUES('MySQL');
SELECT * from t1;
t1_name	t1_id
MySQL	1000
MySQL	1001
MySQL	1002
SHOW CREATE TABLE `t1`;
Table	Create Table
t1	CREATE TABLE `t1` (
unknown's avatar
unknown committed
375 376 377
  `t1_name` varchar(255) DEFAULT NULL,
  `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`t1_id`),
378 379 380
  KEY `t1_name` (`t1_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1
DROP TABLE `t1`;
381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
create table t1(a int not null auto_increment primary key);
create table t2(a int not null auto_increment primary key, t1a int);
insert into t1 values(NULL);
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
insert into t1 values (NULL);
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID());
insert into t1 values (NULL);
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
select * from t2;
a	t1a
1	1
2	1
3	2
4	2
5	2
6	3
7	3
8	3
9	3
drop table t1, t2;
403
End of 4.1 tests
404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));
insert into t1 (b) values (1);
replace into t1 (b) values (2), (1), (3);
select * from t1;
a	b
3	1
2	2
4	3
truncate table t1;
insert into t1 (b) values (1);
replace into t1 (b) values (2);
replace into t1 (b) values (1);
replace into t1 (b) values (3);
select * from t1;
a	b
3	1
2	2
4	3
drop table t1;
create table t1 (rowid int not null auto_increment, val int not null,primary
key (rowid), unique(val));
replace into t1 (val) values ('1'),('2');
replace into t1 (val) values ('1'),('2');
insert into t1 (val) values ('1'),('2');
428
ERROR 23000: Duplicate entry '1' for key 'val'
429 430 431 432 433 434 435 436 437 438 439 440 441 442
select * from t1;
rowid	val
3	1
4	2
drop table t1;
create table t1 (a int not null auto_increment primary key, val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);
select * from t1;
a	val
2	1
3	1
drop table t1;
443 444 445 446
CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
INSERT INTO t1 VALUES(0, 0);
INSERT INTO t1 VALUES(1, 1);
ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
unknown's avatar
unknown committed
447
ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
448
DROP TABLE t1;
449 450 451 452 453 454 455 456
create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
insert into t1 values(null,1,1,now());
insert into t1 values(null,0,0,null);
replace into t1 values(null,1,0,null);
select last_insert_id();
last_insert_id()
3
drop table t1;
457 458 459 460 461 462 463 464
create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
insert into t1 values(null,1,1,1,now());
insert into t1 values(null,0,0,0,null);
replace into t1 values(null,1,0,2,null);
select last_insert_id();
last_insert_id()
3
drop table t1;