auto_increment.result 5.75 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 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:
unknown's avatar
unknown committed
146
Note	1003	select sql_no_cache 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;
unknown's avatar
unknown committed
165
Warnings:
166
Warning	1264	Data truncated; out of range 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);
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 204 205 206 207 208 209 210 211 212 213 214 215 216
203	6
204	7
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:
217
Warning	1263	Data truncated; NULL supplied to NOT NULL column 'a' at row 4
unknown's avatar
unknown committed
218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258
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:
259
Warning	1263	Data truncated; NULL supplied to NOT NULL column 'a' at row 9
unknown's avatar
unknown committed
260 261 262 263 264 265 266 267 268 269 270 271 272
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
273
drop table t1;