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

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 2

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`