Mysql获得在表中描述的所有记录，即使表中不存在这些记录

Consider I have a table like the following:

假设我有这样一张桌子:

my_table

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+

And say I have a query like this

假设我有一个这样的查询

select * from my_table where id in (1,2,3,4,5)

Is it possible to get output like the following,by changing the query.

是否可以通过更改查询获得如下输出。

+---------------+
| id  |  name   |
+---------------+
| 1   |  ABC    |
+---------------+
| 2   |  XYZ    |
+---------------+
| 3   |  PQR    |
+---------------+
| 4   |  LMN    |
+---------------+
| 5   |  NULL   |
+---------------+

I tried using self JOIN and other conditions and also google'd a lot,but didn't find a solution.

我尝试了使用self JOIN和其他条件，也尝试了很多谷歌，但是没有找到一个解决方案。

Can anyone suggest a solution?

有人能提出一个解决方案吗?

3 个解决方案

#1

Unfortunately, mysql doesn't have a built in function that generates a series as many other databases do). There are (at least) two ways of doing it:

不幸的是，mysql并没有像许多其他数据库那样内置生成系列的函数)。(至少)有两种方法:

Hard code the desired values as a subquery, then left join to your table:

将所需的值硬编码为子查询，然后将连接保留到表中:

select x.id, t.name
from (select 1 id
  union select 2
  union select 3
  union select 4
  union select 5) x
left join my_table t on t.id = x.id

But this is tedious and hard to code and maintain.

但是，这是冗长乏味的，很难编写和维护。

Or (as I have done before) create a table (once) and populate with natural numbers (once) to use as a proxy series generator:

或者(如我之前所做的)创建一个表(一次)并使用自然数字填充(一次)作为代理序列生成器:

create table numbers (num int);
insert into numbers values (1), (2), (3), ... etc

then:

然后:

select n.num id, t.name
from numbers n
left join my_table t on t.id = n.num
where n.num in (1,2,3,4,5)

Once set up and populated with lots of numbers, this approach is very handy.

一旦设置并填充了大量的数字，这种方法非常方便。

You can create a similar table populated with dates, used in a similar way, which is very handy for producing figures for every date in a range when not all dates have data.

您可以创建一个类似的表，以类似的方式填充日期，这对于在不所有日期都有数据的范围内为每个日期的数据生成数据非常方便。

#2

You can create a number serie without having to create an extra table or without writing conditions for each values that needed to be searched. You can use a variable rownum, initialize with value 0 & increase it by 1 to easily create a serie by using ‘limit’. I used the INFORMATION_SCHEMA.COLUMNStable so you can create a big serie (you can use any bigger table that you have in your DB or any table large enough for your needs).

您可以创建一个数字系列，而无需创建额外的表或不为需要搜索的每个值编写条件。您可以使用一个变量rownum，初始化值为0，并将其增加1，通过使用“limit”轻松创建一个意甲。我使用了INFORMATION_SCHEMA。这样你就可以创建一个大的意甲(你可以使用你DB中的任何大的表，或者任何足够大的表来满足你的需要)。

SQL Fiddle

SQL小提琴

MySQL 5.6.6 m9 Schema Setup:

MySQL 5.6.6 m9模式设置:

CREATE TABLE my_table
    (`id` int, `name` varchar(3))
;

INSERT INTO my_table
    (`id`, `name`)
VALUES
    (1, 'ABC'),
    (2, 'XYZ'),
    (3, 'PQR'),
    (4, 'LMN')
;

Query 1:

查询1:

select rownum id, name 
from (
select @rownum:=@rownum+1 as rownum
from INFORMATION_SCHEMA.COLUMNS,(SELECT @rownum:=0) r limit 5) as num
left outer join my_table on id = rownum

Results:

结果:

| ID |   NAME |
|----|--------|
|  1 |    ABC |
|  2 |    XYZ |
|  3 |    PQR |
|  4 |    LMN |
|  5 | (null) |

#3

you can use clause IN, as you did and as you can see here http://www.tutorialspoint.com/mysql/mysql-in-clause.htm

您可以在http://www.tutorialspoint.com/mysql/mysql-in-clause.htm中使用子句

I have performed a test by myself and it returned the name as expected from 1 to 4, But when I put the one id that not exist mysql query will return nothing for this id, because it not exist in database.

我自己做了一个测试，它按照预期从1到4返回了名称，但是当我放入一个不存在的id时，mysql查询将不会为这个id返回任何内容，因为它在数据库中不存在。

SELECT * FROM employee_tbl WHERE id IN ( 1,2,3,4,5,6,7,8);

1   John    250
2   Ram     220
3   Jack    170
4   Jack    100
5   Jill    220
6   Zara    300
7   Zara    360

If you realy want this table, I think you could try some stored procedure to format the output table. Here you can learn more about it. http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html and here 'IF' in 'SELECT' statement - choose output value based on column values

如果您确实需要这个表，我认为您可以尝试一些存储过程来格式化输出表。在这里你可以了解更多。http://dev.sqmyl.com/doc/connector -net/en/connector-net-tutorials-stored-procedure dures.html和这里的“IF”在“SELECT”语句中——根据列值选择输出值

#1