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)?

Answer 1

Something like this should work:

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

Answer 2

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.

Answer 3

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

Answer 4

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).

Answer 5

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.