mysql - 无法将 int 列表作为变量传递

Question

我正在使用 DBeaber 6.0.3 并尝试进行此查询

declare @samplesList table (id int);
insert @samplesList(id) values(1121),(2121),(3121); 

SELECT *
FROM samples
where samplesId in (select id from @samplesList) 

但是我收到语法错误我究竟做错了什么？

Answer 1

如果您使用 MySQL v8，您可以通过这种方式重现您的示例:

with samplesList as (
  select 1121 as id union all
  select 2121 union all
  select 3121 
)
SELECT *
FROM samples
where samplesId in (select id from samplesList)

或者在任何版本的 MySQL 中:

create temporary table samplesList (id int);
insert into samplesList(id) values(1121),(2121),(3121);

SELECT *
FROM samples
where samplesId in (select id from samplesList)