· 8 years ago · Feb 22, 2018, 07:24 AM
1create table numbers (number int);
2insert into numbers values (3), (2), (9);
3select * from numbers;
4
5+--------+
6| number |
7+--------+
8| 3 |
9| 2 |
10| 9 |
11+--------+
12
13select * from seq_1_to_10;
14
15select * from
16(select 0 x union select 1 union select 2 union select 3 union select 4 union
17select 5 union select 6 union select 7 union select 8 union select 9) A;
18
19select (t*10+u+1) x from
20(select 0 t union select 1 union select 2 union select 3 union select 4 union
21select 5 union select 6 union select 7 union select 8 union select 9) A,
22(select 0 u union select 1 union select 2 union select 3 union select 4 union
23select 5 union select 6 union select 7 union select 8 union select 9) B
24order by x;
25
26select (h*100+t*10+u+1) x from
27(select 0 h union select 1 union select 2 union select 3 union select 4 union
28select 5 union select 6 union select 7 union select 8 union select 9) A,
29(select 0 t union select 1 union select 2 union select 3 union select 4 union
30select 5 union select 6 union select 7 union select 8 union select 9) B,
31(select 0 u union select 1 union select 2 union select 3 union select 4 union
32select 5 union select 6 union select 7 union select 8 union select 9) C
33order by x;
34
35select (th*1000+h*100+t*10+u+1) x from
36(select 0 th union select 1 union select 2 union select 3 union select 4 union
37select 5 union select 6 union select 7 union select 8 union select 9) A,
38(select 0 h union select 1 union select 2 union select 3 union select 4 union
39select 5 union select 6 union select 7 union select 8 union select 9) B,
40(select 0 t union select 1 union select 2 union select 3 union select 4 union
41select 5 union select 6 union select 7 union select 8 union select 9) C,
42(select 0 u union select 1 union select 2 union select 3 union select 4 union
43select 5 union select 6 union select 7 union select 8 union select 9) D
44order by x;
45
46use test
47drop table if exists seq10;
48create table seq10
49(x int not null auto_increment primary key);
50insert into seq10 values (),(),(),(),(),(),(),(),(),();
51select * from seq10;
52
53use test
54drop table if exists seq100;
55create table seq100
56(x int not null auto_increment primary key);
57insert into seq100 values (),(),(),(),(),(),(),(),(),();
58insert into seq100 values (),(),(),(),(),(),(),(),(),();
59insert into seq100 values (),(),(),(),(),(),(),(),(),();
60insert into seq100 values (),(),(),(),(),(),(),(),(),();
61insert into seq100 values (),(),(),(),(),(),(),(),(),();
62insert into seq100 select x + 50 from seq100;
63select * from seq100;
64
65use test
66drop table if exists seq1000;
67create table seq1000
68(x int not null auto_increment primary key);
69insert into seq1000 values ();
70set @p= -1;
71set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
72set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
73set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
74set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
75set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
76set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
77set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
78set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
79set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
80set @p=@p+1; insert into seq1000 select x+power(2,@p) from seq1000 where (x+power(2,@p)) <= 1000;
81select * from seq1000;
82
83use test
84drop table if exists seq;
85create table seq
86(x int not null auto_increment primary key);
87insert into seq values ();
88set @maxseq = 3500000;
89set @p = -1;
90set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
91set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
92set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
93set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
94set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
95set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
96set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
97set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
98set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
99set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
100set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
101set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
102set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
103set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
104set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
105set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
106set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
107set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
108set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
109set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
110set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
111set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
112set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
113set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
114set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
115set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
116set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
117set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
118set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
119set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
120set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
121set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
122set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
123set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
124set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
125set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
126set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
127set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
128set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
129set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
130set @p=@p+1; insert into seq select x+power(2,@p) from seq where (x+power(2,@p)) <= @maxseq;
131select max(x),count(x) from seq;
132
133select A.number,1-ISNULL(B.number) present from
134(select 0 number union select 1 union select 2 union select 3 union select 4 union
135select 5 union select 6 union select 7 union select 8 union select 9) A
136left join numbers B using (number);
137
138mysql> drop table numbers;
139Query OK, 0 rows affected (0.01 sec)
140
141mysql> drop table if exists numbers;
142Query OK, 0 rows affected, 1 warning (0.00 sec)
143
144mysql> create table numbers (number int);
145Query OK, 0 rows affected (0.02 sec)
146
147mysql> insert into numbers values (3), (2), (9);
148Query OK, 3 rows affected (0.01 sec)
149Records: 3 Duplicates: 0 Warnings: 0
150
151mysql> select A.number,1-ISNULL(B.number) present from
152 -> (select 0 number union select 1 union select 2 union select 3 union select 4 union
153 -> select 5 union select 6 union select 7 union select 8 union select 9) A
154 -> left join numbers B using (number);
155+--------+---------+
156| number | present |
157+--------+---------+
158| 0 | 0 |
159| 1 | 0 |
160| 2 | 1 |
161| 3 | 1 |
162| 4 | 0 |
163| 5 | 0 |
164| 6 | 0 |
165| 7 | 0 |
166| 8 | 0 |
167| 9 | 1 |
168+--------+---------+
16910 rows in set (0.00 sec)
170
171mysql>