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
40
41
42
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
129
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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
|
CREATE SEQUENCE a1 engine=aria;
CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024;
insert into t1 values (1),(2);
CREATE SEQUENCE x1 engine=innodb;
# dump whole database
CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
SELECT SETVAL(`a1`, 1, 0);
CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`x1`, 1, 0);
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES
(1),
(2);
# dump by tables order 1
CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
SELECT SETVAL(`a1`, 1, 0);
CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`x1`, 1, 0);
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES
(1),
(2);
# dump by tables order 2
CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
SELECT SETVAL(`a1`, 1, 0);
CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`x1`, 1, 0);
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES
(1),
(2);
# dump by tables only tables
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci KEY_BLOCK_SIZE=1024;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES
(1),
(2);
# dump by tables only sequences
CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria;
SELECT SETVAL(`a1`, 1, 0);
CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
SELECT SETVAL(`x1`, 1, 0);
# end of dumps
DROP TABLE a1,t1,x1;
set default_storage_engine=InnoDB;
create sequence t1;
LOCK TABLES t1 READ;
SELECT * FROM t1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1 1 9223372036854775806 1 1 1000 0 0
unlock tables;
drop table t1;
#
# MDEV-28152 Features for sequence
#
CREATE DATABASE test1;
CREATE DATABASE test2;
USE test1;
CREATE SEQUENCE s1 as tinyint;
CREATE SEQUENCE s2 as smallint;
CREATE SEQUENCE s3 as mediumint;
CREATE SEQUENCE s4 as int;
CREATE SEQUENCE s5 as bigint;
CREATE SEQUENCE s6 as tinyint unsigned;
CREATE SEQUENCE s7 as smallint unsigned;
CREATE SEQUENCE s8 as mediumint unsigned;
CREATE SEQUENCE s9 as int unsigned;
CREATE SEQUENCE s10 as bigint unsigned;
# Dump database 1
# Restore from database 1 to database 2
SETVAL(`s1`, 1, 0)
1
SETVAL(`s10`, 1, 0)
1
SETVAL(`s2`, 1, 0)
1
SETVAL(`s3`, 1, 0)
1
SETVAL(`s4`, 1, 0)
1
SETVAL(`s5`, 1, 0)
1
SETVAL(`s6`, 1, 0)
1
SETVAL(`s7`, 1, 0)
1
SETVAL(`s8`, 1, 0)
1
SETVAL(`s9`, 1, 0)
1
USE test2;
SHOW CREATE SEQUENCE s1;
Table Create Table
s1 CREATE SEQUENCE `s1` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s2;
Table Create Table
s2 CREATE SEQUENCE `s2` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s3;
Table Create Table
s3 CREATE SEQUENCE `s3` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s4;
Table Create Table
s4 CREATE SEQUENCE `s4` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s5;
Table Create Table
s5 CREATE SEQUENCE `s5` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s6;
Table Create Table
s6 CREATE SEQUENCE `s6` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s7;
Table Create Table
s7 CREATE SEQUENCE `s7` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s8;
Table Create Table
s8 CREATE SEQUENCE `s8` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s9;
Table Create Table
s9 CREATE SEQUENCE `s9` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE SEQUENCE s10;
Table Create Table
s10 CREATE SEQUENCE `s10` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=InnoDB
DROP DATABASE test1;
DROP DATABASE test2;
## test ORACLE mode
set sql_mode=ORACLE;
CREATE DATABASE test1;
CREATE DATABASE test2;
USE test1;
CREATE SEQUENCE s1 as tinyint;
CREATE SEQUENCE s2 as smallint;
CREATE SEQUENCE s3 as mediumint;
CREATE SEQUENCE s4 as int;
CREATE SEQUENCE s5 as bigint;
CREATE SEQUENCE s6 as tinyint unsigned;
CREATE SEQUENCE s7 as smallint unsigned;
CREATE SEQUENCE s8 as mediumint unsigned;
CREATE SEQUENCE s9 as int unsigned;
CREATE SEQUENCE s10 as bigint unsigned;
# Dump database 1
# Restore from database 1 to database 2
SETVAL(`s1`, 1, 0)
1
SETVAL(`s10`, 1, 0)
1
SETVAL(`s2`, 1, 0)
1
SETVAL(`s3`, 1, 0)
1
SETVAL(`s4`, 1, 0)
1
SETVAL(`s5`, 1, 0)
1
SETVAL(`s6`, 1, 0)
1
SETVAL(`s7`, 1, 0)
1
SETVAL(`s8`, 1, 0)
1
SETVAL(`s9`, 1, 0)
1
USE test2;
SHOW CREATE SEQUENCE s1;
Table Create Table
s1 CREATE SEQUENCE "s1" start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s2;
Table Create Table
s2 CREATE SEQUENCE "s2" start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s3;
Table Create Table
s3 CREATE SEQUENCE "s3" start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s4;
Table Create Table
s4 CREATE SEQUENCE "s4" start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s5;
Table Create Table
s5 CREATE SEQUENCE "s5" start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s6;
Table Create Table
s6 CREATE SEQUENCE "s6" start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s7;
Table Create Table
s7 CREATE SEQUENCE "s7" start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s8;
Table Create Table
s8 CREATE SEQUENCE "s8" start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s9;
Table Create Table
s9 CREATE SEQUENCE "s9" start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle
SHOW CREATE SEQUENCE s10;
Table Create Table
s10 CREATE SEQUENCE "s10" start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle
DROP DATABASE test1;
DROP DATABASE test2;
set sql_mode=default;
|
