auto_increment.result 9.5 KB
Newer Older
1
drop table if exists t1;
2
drop table if exists t2;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
3
SET SQL_WARNINGS=1;
4
create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
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
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
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
39
select _rowid,t1._rowid,skey,sval from t1;
40 41 42
_rowid	_rowid	skey	sval
1	1	1	hello
2	2	2	hey
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
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
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
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;
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;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi committed
103 104
a
0
105
check table t1;
monty@tik.mysql.fi's avatar
monty@tik.mysql.fi 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
109
drop table t1;
monty@mysql.com's avatar
monty@mysql.com 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;
monty@mysql.com's avatar
monty@mysql.com committed
123
create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
monty@mysql.com's avatar
monty@mysql.com 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
monty@mysql.com's avatar
monty@mysql.com committed
133
-1
monty@mysql.com's avatar
monty@mysql.com 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 143 144 145
explain extended select last_insert_id();
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
146
Note	1003	select last_insert_id() AS `last_insert_id()`
147 148 149 150 151
insert into t1 set i = 254;
ERROR 23000: Duplicate entry '254' for key 1
select last_insert_id();
last_insert_id()
255
152
insert into t1 set i = null;
153
ERROR 23000: Duplicate entry '255' for key 1
154 155
select last_insert_id();
last_insert_id()
156
0
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;
venu@myvenu.com's avatar
venu@myvenu.com committed
165
Warnings:
166
Warning	1264	Out of range value adjusted 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 2
182 183
select last_insert_id();
last_insert_id()
184
0
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);
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
202 203
203	6
204	7
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
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;
222 223 224 225 226 227 228 229 230 231 232 233 234
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;
Warnings:
235
Warning	1263	Column was set to data type implicit default; NULL supplied for NOT NULL column 'a' at row 4
236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 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 276
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
0	6
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;
Warnings:
277
Warning	1263	Column was set to data type implicit default; NULL supplied for NOT NULL column 'a' at row 9
278 279 280 281 282 283 284 285 286 287 288 289 290
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
0	13
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 375 376 377 378 379 380
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` (
  `t1_name` varchar(255) default NULL,
  `t1_id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`t1_id`),
  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 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442
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');
ERROR 23000: Duplicate entry '1' for key 2
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;