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