mysql fake select

Question

My purpose is: to get multiple rows from a value list,like (1,2,3,4,5),('a','b','c','anything') and so on.

mysql> select id from accounts where id in (1,2,3,4,5,6);
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)

The above sql is surely ok,but my question is:is there a way to get the same result without

specifying a table?Because my purpose here is just to propagate rows by an id_set

another example:

mysql> select now() as  column1;
+---------------------+
| column1             |
+---------------------+
| 2009-06-01 20:59:33 |
+---------------------+
1 row in set (0.00 sec)

mysql>

This example propagated a single row result without specifying a table,

but how to propagate multiple rows from a string like (1,2,3,4,5,6)?

What rows? Rows exist in a table. Without the table there are no rows. — jmucchiello, Jun 02 '09 at 01:09

score 35 · Answer 1 · edited Jul 30 '20 at 09:09

35

Something like this should work:

SELECT 0 as id
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5

Afterwards, you can select what you need from it by giving it an alias:

SELECT *
FROM (
    SELECT 0 as id
    UNION SELECT 1
    UNION SELECT 2
    UNION SELECT 3
    UNION SELECT 4
    UNION SELECT 5
) `table1`

edited Jul 30 '20 at 09:09

Pikamander2

7,332
3
48
69

answered Jun 02 '09 at 01:13

Frank V

25,141
34
106
144

1

It feels quite loose,is there a more compact way to write this query? – omg Jun 02 '09 at 01:23
1

Why the select *? The inner query alone should work well enough. – Stefan Mai Jun 02 '09 at 01:32
Short of utilizing an integers table like Alex Martelli suggests, this is as compact as you can get in a single query. You could create a temp table and use INSERT INTO ... VALUES ((1), (2), (3), (4), (5) (6)) or something like that, then SELECT FROM that temp table. It looks a little more compact, but functionally, there's more overhead. – eksortso Jun 02 '09 at 01:38
@Stefan: You are right, the outer select * isn't really needed. That is what came to my mind so I wrote it out quickly and posted it. – Frank V Jun 02 '09 at 14:29
@Shore: I don't know what you mean by "it feels quite loose". I do feel that this is the most compact way to write something like this. You could also get in to temp tables or a table reserved just for your purposes here (a table with a single column populated with integers) but that wouldn't be as compact (in my definition of compact). – Frank V Jun 02 '09 at 14:32

score 6 · Answer 2 · answered Jun 02 '09 at 01:14

6

MySQL has a dummy table: DUAL. but using DUAL doesn't change anything (it's just for convenience), and certainly doesn't make this query work.

I'm sure there's a better way to achieve what you're trying to do. We might be able to help if you explain your problem.

answered Jun 02 '09 at 01:14

Can Berk Güder

109,922
25
130
137

1

Thanks! I needed DUAL for selecting constant values with a where clause. It's a hack to optionally insert constants. – Jay Jun 11 '13 at 18:28

score 1 · Answer 3 · answered Aug 20 '09 at 08:01

A simple and old fashioned way is to use a table which holds consecutive values.

DROP TABLE IF EXISTS `range10`;
CREATE TABLE IF NOT EXISTS `range10` (
  `id` int(11) NOT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM;
INSERT INTO `range10` (`id`) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Once installed you can write queries as shown below.

get every second row:

select * from your_data_table where id in (
 SELECT id*2 as id FROM `range10` WHERE id in(
  select id from `range10`
 )
)

get rows from 1101 to 1111:

select * from your_data_table where id in (
 SELECT id+1100 as id FROM `range10` WHERE id in(
  select id from `range10`
 )
)

So if you are in the need of greater ranges, then just increase the size of the consecutive values in table range10. Querying is simple, cost are low, no stored procedure or function needed.

Note:

You can create a table with consecutive char values, too. But varying the contents would not be so easy.

score 1 · Answer 4 · answered Jun 02 '09 at 01:15

1

This does not answer your question exactly, but I believe this will fix your actual problem..

SET @counter = 0; SELECT (@counter := @counter + 1 as counter) ... rest of your query

answered Jun 02 '09 at 01:15

Evert

93,428
18
118
189

my demo is using integer,but it's not limited to integer only,may also be string list like ('a','b','anything') – omg Jun 02 '09 at 01:33
use a temporary table i guess then =) – Evert Jun 02 '09 at 02:56
Then this temporary table may accumulate to be a huge one,I'm afraid – omg Jun 02 '09 at 03:56
Regardless it will have to reside in memory.. If you're afraid you'll run out of memory for this, you'll definitely have to assess your general approach because by the nature of your question it sounds like you have bigger problems to fix. – Evert Jun 02 '09 at 21:44

score 0 · Answer 5 · answered Aug 16 '20 at 15:54

Here a way to create custom rows directly with MySQL request SELECT :

SELECT ALL *
FROM (
    VALUES
        ROW ('1.1', '1.2', '1.3'),
        ROW ('2.1', '2.2', '2.3'),
        ROW ('3.1', '3.2', '3.3')
) AS dummy (c1, c2, c3)

Gives us a table dummy:

c1   c2   c3
-------------
1.1  1.2  1.3
2.1  2.2  2.3
3.1  3.2  3.3

score 0 · Answer 6 · answered Jun 02 '09 at 01:18

0

One technique I've found invaluable is an "integers table", which lets you easily do all kinds of neat things including this one (xaprb has written several blog posts on this technique and the closely related "mutex table" one).

answered Jun 02 '09 at 01:18

Alex Martelli

854,459
170
1,222
1,395

I don't think it's stable since we need to clear the table each time after using,which may easily lead to fault. – omg Jun 02 '09 at 01:21
@Shore, why do you need to clear your integers table after each use? If possible, you could make the table permanent and select from it as needed. – eksortso Jun 02 '09 at 01:44
The pre-requisite is that the temporary table already has an entry there.And I'm afraid one day this table will become huge. – omg Jun 02 '09 at 03:57

mysql fake select

6 Answers6

Linked