rpl_auto_increment.result 4.42 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
insert into t1 values (NULL,1),(NULL,2),(NULL,3);
select * from t1;
a	b
12	1
22	2
32	3
select * from t1;
a	b
12	1
22	2
32	3
drop table t1;
create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam;
insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4);
delete from t1 where b=4;
insert into t1 values (NULL,5),(NULL,6);
select * from t1;
a	b
1	1
2	2
3	3
22	5
32	6
select * from t1;
a	b
1	1
2	2
3	3
22	5
32	6
drop table t1;
set @@session.auto_increment_increment=100, @@session.auto_increment_offset=10;
40
show variables like "%auto_inc%";
41
Variable_name	Value
42
auto_increment_increment	100
43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
auto_increment_offset	10
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
insert into t1 values (NULL),(5),(NULL);
insert into t1 values (250),(NULL);
select * from t1;
a
5
10
110
250
310
insert into t1 values (1000);
set @@insert_id=400;
insert into t1 values(NULL),(NULL);
select * from t1;
a
5
10
110
250
310
400
410
1000
select * from t1;
a
5
10
110
250
310
400
410
1000
drop table t1;
create table t1 (a int not null auto_increment, primary key (a)) engine=innodb;
insert into t1 values (NULL),(5),(NULL);
insert into t1 values (250),(NULL);
select * from t1;
a
5
10
110
250
310
insert into t1 values (1000);
set @@insert_id=400;
insert into t1 values(NULL),(NULL);
select * from t1;
a
5
10
110
250
310
400
410
1000
select * from t1;
a
5
10
110
250
310
400
410
1000
drop table t1;
set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
insert into t1 values (NULL),(5),(NULL),(NULL);
insert into t1 values (500),(NULL),(502),(NULL),(NULL);
select * from t1;
a
1
5
6
7
500
501
502
503
504
set @@insert_id=600;
insert into t1 values(600),(NULL),(NULL);
129
ERROR 23000: Duplicate entry '600' for key 'PRIMARY'
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185
set @@insert_id=600;
insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
select * from t1;
a
1
5
6
7
500
501
502
503
504
600
610
611
select * from t1;
a
1
5
6
7
500
501
502
503
504
600
610
611
drop table t1;
set @@session.auto_increment_increment=10, @@session.auto_increment_offset=1;
create table t1 (a int not null auto_increment, primary key (a)) engine=myisam;
insert into t1 values(2),(12),(22),(32),(42);
insert into t1 values (NULL),(NULL);
insert into t1 values (3),(NULL),(NULL);
select * from t1;
a
1
3
11
21
31
select * from t1;
a
1
2
3
11
12
21
22
31
32
42
drop table t1;
186 187 188 189 190 191 192
create table t1 (a tinyint not null auto_increment primary key) engine=myisam;
insert into t1 values(103);
set auto_increment_increment=11;
set auto_increment_offset=4;
insert into t1 values(null);
insert into t1 values(null);
insert into t1 values(null);
193
ERROR 23000: Duplicate entry '125' for key 'PRIMARY'
194 195 196 197 198 199 200 201 202 203 204
select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a;
a	mod(a-@@auto_increment_offset,@@auto_increment_increment)
103	0
114	0
125	0
create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam;
set auto_increment_increment=10;
set auto_increment_offset=1;
set insert_id=1000;
insert into t2 values(null);
Warnings:
205
Warning	1264	Out of range value for column 'a' at row 1
206 207 208 209 210 211 212 213
select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a;
a	mod(a-@@auto_increment_offset,@@auto_increment_increment)
251	0
create table t3 like t1;
set auto_increment_increment=1000;
set auto_increment_offset=700;
insert into t3 values(null);
Warnings:
214
Warning	1264	Out of range value for column 'a' at row 1
215 216 217 218 219 220 221 222 223 224 225 226 227 228 229
select * from t3 order by a;
a
127
select * from t1 order by a;
a
103
114
125
select * from t2 order by a;
a
251
select * from t3 order by a;
a
127
drop table t1,t2,t3;
230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
set auto_increment_increment=1;
set auto_increment_offset=1;
CREATE TABLE t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb;
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
drop table t1;