SQL中VALUES构造临时表的实用技巧与避坑指南

1. 从一个调试场景说起：为什么需要“临时造数据”

做数据库开发的人，几乎都遇到过这种尴尬：线上有个报表逻辑要验证，但测试环境里没有合适的数据；或者要排查一条 SQL 的关联逻辑对不对，但手头只有表结构，没有现成记录。以前我碰到这种情况，第一反应是INSERT INTO塞几条记录进临时表，用完再删。这个办法能用，但很啰嗦——先建表、再插数、用完还得清理，一套流程走下来，光写样板代码就够烦的。

后来我开始大量使用VALUES来直接构造临时数据集，这才发现一个很舒服的事实：大部分“临时造数据”的需求，根本不需要建表，用VALUES一句 SQL 就能搞定。它可以作为独立的查询语句直接跑，可以嵌到WITH公共表表达式里当作临时表用，也可以配合INSERT INTO一次性批量灌入真实表。这里的核心思想是：把VALUES当成一个匿名的内存表，数据即查即用，用完即走，不落盘、不建对象、不留垃圾。

适合看这篇文章的人，我大致分三类：

• 日常要做报表开发、SQL 调试的数据分析师或后端开发，想在测试时快速造数据；
• 需要写自动化脚本、做单元测试的测试工程师，想用少量代码构造各种边界数据；
• 以及那些刚接触 SQL、想知道“不建表还能怎么造数据”的新手学习者。

这篇文章我会把这些年用VALUES构造临时表的经验完整梳理一遍，包括标准语法、各数据库的兼容性细节、和临时表/CTE 的组合玩法，以及我实际踩过的一些坑。内容偏实操，看完可以直接复制去跑。

2. 先搞懂 VALUES 的本质，别把它当成“只属于 INSERT 的边角料”

2.1 从标准 SQL 角度理解 VALUES 到底是什么

很多开发者的第一反应是：VALUES不就是INSERT INTO后面跟的那一段吗？这个理解没有错，但太局限了。在 SQL 标准里，VALUES本身就是一个完整的“表构造函数”（table value constructor），它可以独立返回一张表。

拿最基础的例子来说：

SELECT * FROM (VALUES (1, 'Alice'), (2, 'Bob')) AS t(id, name);

这段 SQL 在 SQL Server、PostgreSQL、Oracle（需加FROM dual或特定写法）、MySQL 8.0+ 里，都能返回一张两行两列的表：

这里的核心点是：VALUES的每一对括号就是一行记录，括号内的每个值就是一个字段。整个VALUES子句本身具备“表”的语义，可以出现在FROM子句里能出现的绝大多数位置。理解这一点之后，你就会发现很多以前要先建临时表的场景，其实都能用一行VALUES替代。

为了让大家更直观地理解，我用一个生活化类比：VALUES就像你在纸上直接手写一张购物清单，表结构就是清单的列（商品名、数量、价格），每一对括号就是一行条目。你用完之后把纸一揉，不需要专门准备一个“购物清单文件夹”。

2.2 为什么说 VALUES 构造的是“临时表”

严格来说，VALUES构造出来的数据集，在数据库内部是一个派生表（derived table）或者内联视图。它不像物理临时表那样占用 tempdb（SQL Server）或者临时表空间，也不涉及日志记录，数据库执行完就释放。所以它天然具有“临时”的特性。

我之前在 SQL Server 里做过一次粗略对比：同样的数据量（10万行级别），用#temp物理临时表插入再查询，和用VALUES构建派生表直接关联，后者的额外开销小很多，尤其在纯内存计算时，两者的性能差距可以到几倍甚至十几倍。当然，如果你的数据量达到百万行以上，千万级别，我不建议继续用VALUES硬扛，那个场景下物理临时表或者表变量才是合理的。VALUES的最佳适用区间是“小数据量、临时计算、快速验证”，这个边界要心里有数。

2.3 一个容易被忽略的底层逻辑：行构造器的类型推断

用VALUES的时候，数据库会自动推断每一列的数据类型。这里的规则是：同一列的所有行的字面量类型会做“兼容合并”，合并的结果就是这列最终的类型。

举例来说：

SELECT * FROM (VALUES (1, '10'), (2, 20)) AS t(id, val);

第一行第二列是字符串'10'，第二行第二列是数字20。在 SQL Server 里，系统会把这列推断成int并尝试把'10'隐式转换成数字；在 PostgreSQL 里也可能做类似处理。但如果某个值没法转换，比如'abc'和20混在一起，查询就会报错。这一点我在第 6 部分会有更多实战细节。

所以我的建议是：用VALUES造数据时，尽量保持同一列的类型一致。哪怕需要不同类型，也请统一用显式类型转换，避免数据库自己猜。

3. 临时数据集的核心玩法：CTE、派生表和 INSERT INTO 三件套

3.1 用 VALUES + CTE 构建“逻辑临时表”，告别反复建表

大多数场景下，我喜欢把VALUES和公共表表达式（CTE）配合使用。CTE 本身就能像临时表一样在一条 SQL 的范围内反复被引用，而VALUES负责给 CTE 提供数据，两者结合起来会非常顺手。

WITH temp_data (order_id, customer_name, amount) AS ( SELECT * FROM (VALUES (1001, '张三', 58.50), (1002, '李四', 120.00), (1003, '王五', 35.80) ) AS td(order_id, customer_name, amount) ) SELECT customer_name, SUM(amount) AS total_amount FROM temp_data GROUP BY customer_name;

这里我建了一个叫temp_data的逻辑临时表，它只在当前这条 SQL 语句执行期间存在。后续你可以继续 JOIN 其他真实表，也可以在 CTE 里做多次计算。整个过程没有创建任何物理对象，也不需要显式DROP，非常适合做数据清洗、数据校验、报表口径验证这些场景。

有一个细节值得注意：在 SQL Server 里，WITH temp_data (order_id, ...)这种在 CTE 名称后直接带列名列表的写法是支持的；PostgreSQL 也支持。但 MySQL 8.0 里用 CTE 时，列的别名应该写在 CTE 内部：

WITH temp_data AS ( SELECT * FROM (VALUES ROW(1001, '张三', 58.50), ROW(1002, '李四', 120.00) ) AS td(order_id, customer_name, amount) ) SELECT * FROM temp_data;

两种差异不大，写的时候注意一下兼容性就好。

3.2 直接在 FROM 子句里用 VALUES 做“内联临时表”

如果只是临时关联一个小集合，不想写 CTE，也可以直接把VALUES放在FROM后面当派生表用。这一招特别适合处理“代码集”类的需求，比如根据一批指定 ID 过滤数据要保留原始顺序。

SELECT t.id, t.sort_order FROM (VALUES (101, 3), (102, 1), (103, 2) ) AS t(id, sort_order) INNER JOIN products p ON p.product_id = t.id ORDER BY t.sort_order;

这段 SQL 就实现了一个常见需求：按指定的 ID 顺序返回产品列表。原来你得写一堆CASE WHEN或者用CHARINDEX去处理自定义排序，现在用VALUES把“ID-序号”对应表造出来再 JOIN，代码可读性一下子高很多。我经常在接口的 ID 列表排序、批量状态更新这类场景里用它。

3.3 用 VALUES 给已存在的临时表批量灌数据

还有一种场景：你已经有了物理临时表（#temp或TEMPORARY TABLE），需要用VALUES往里快速插入多行。SQL Server 从 2008 开始支持多行 VALUES 插入，MySQL、PostgreSQL 更是默认支持。

CREATE TABLE #temp_products ( product_code VARCHAR(20), price DECIMAL(10,2) ); INSERT INTO #temp_products (product_code, price) VALUES ('SKU-A001', 19.99), ('SKU-A002', 29.99), ('SKU-B001', 9.99);

这个操作之前我最常犯的错就是：忘记在VALUES前加列名列表就直接INSERT INTO #temp_products VALUES...。虽然这样语法上也能过，但一旦表的列顺序改动过，数据就串位了。所以老规矩，显式列名一定要写。这也是一个习惯问题，养成好习惯能少踩很多坑。

4. 格式不同，别掉坑：主流数据库 VALUES 语法差异速查

很多人觉得自己会写 SQL Server 的VALUES，到了 PostgreSQL 或者 MySQL 就懵了。原因是不同数据库对“行构造器”的语法支持并不完全一样。以下是我根据自己多数据库开发经验整理的差异点。

4.1 SQL Server：简单直接，还可以用 VALUES 做 UPDATE 数据源

SQL Server 里最标准的写法就是每一行括号加逗号隔开：

SELECT * FROM (VALUES (1, 'A'), (2, 'B')) AS t(id, val);

不需要在每行前写ROW关键字，也没那么多装饰。SQL Server 还支持VALUES用在MERGE语句的USING子句里，比如你要根据一个临时数据集去匹配更新目标表：

MERGE INTO products AS target USING (VALUES (101, '新版产品A', 25.00), (102, '新版产品B', 35.00) ) AS source(product_id, product_name, price) ON target.product_id = source.product_id WHEN MATCHED THEN UPDATE SET product_name = source.product_name, price = source.price;

这个技巧特别适合批量刷数据、做一次性数据订正。比起一条一条写UPDATE，用VALUES构造数据集再MERGE，效率和正确率都高不少。

4.2 PostgreSQL：需要 ROW 关键字，且 VALUES 自带类型转换能力

PostgreSQL 的写法稍微严一点，推荐用ROW关键字显式构造行：

SELECT * FROM (VALUES ROW(1, 'Alice'), ROW(2, 'Bob') ) AS t(id, name);

不带ROW也支持，但带上更清晰。PostgreSQL 的VALUES列表有一个特性：如果同一列出现不同类型，它会用UNION的类型推断规则去寻找“公共类型”。比如VALUES (1, '2024-01-01'), (2, '2024-01-02')，第二列会被自动识别为date类型，这一点比 SQL Server 要聪明一些。

4.3 MySQL：8.0 才完整支持 VALUES 表构造器，老版本要绕路

MySQL 在 8.0 里终于支持了独立的VALUES构造表：

SELECT * FROM (VALUES ROW(1, 'Alice'), ROW(2, 'Bob') ) AS t(id, name);

但如果你还在维护 5.7 及更老的库，用不了这种写法。老版本的替代方案是用多个SELECT ... UNION ALL来模拟，虽然丑一点，但效果一样：

SELECT 1 AS id, 'Alice' AS name UNION ALL SELECT 2, 'Bob';

4.4 Oracle：从 23c 开始支持，老版本用 FROM dual 拼接

Oracle 对VALUES表构造器的支持比 MySQL 还晚，23c 才开始原生支持较好。老版本里最常用的是SELECT ... FROM dual加UNION ALL：

SELECT 1 AS id, 'Alice' AS name FROM dual UNION ALL SELECT 2, 'Bob' FROM dual;

Oracle 也有一种SYS.ODCIVARCHAR2LIST或SYS.ODCINUMBERLIST构造集合的写法，配合TABLE()函数也能当表用，但可读性和通用性都比较差。如果你在 Oracle 环境里非要用VALUES风格，建议升级到 23c+，或者在旧版本老老实实用UNION ALL。

我把主要差异整理成了下面的速查表，方便大家查阅：

5. VALUES 的进阶应用场景，我用它解决过的几个实际问题

5.1 用 VALUES 生成连续数字序列，替代递归 CTE 的笨办法

有时候我们想要一张“1 到 1000 的连续数字表”，用于统计连续日期、补齐缺失行或做排列组合。很多人的第一反应是写递归 CTE，但用VALUES加上交叉连接（CROSS JOIN）也可以非常高效地生成：

WITH nums AS ( SELECT * FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n) ) SELECT a.n + b.n * 10 + c.n * 100 AS num FROM nums a CROSS JOIN nums b CROSS JOIN nums c ORDER BY num;

这段代码生成了 0 到 999 的 1000 个数字，利用了三组数字的笛卡尔积。整个过程没有递归、没有循环，执行效率很高。有了这张数字表，你可以去补日期序列、做分页模拟、按数字拆字符串等等。我自己做日历报表的时候，经常用这个思路先构造一个完整的日期序列，再左连接业务表，这样就能保证报表上每一天都有记录，不会出现日期断层。

5.2 在存储过程里用 VALUES 构造参数集合，避免拼装 SQL

有些存储过程需要接收一个“ID 数组”，但存储过程的参数不好直接传列表。以前很多人选择把 ID 列表拼成一个逗号分隔的字符串，然后在存储过程里用字符串拆分函数处理，不仅慢，而且容易出错。后来我习惯让调用方直接把数据以VALUES的形式放到 SQL 里，当成派生表来传参：

CREATE PROCEDURE usp_GetProductInfo AS BEGIN SELECT ... FROM products p INNER JOIN (VALUES (1001, 5), (1002, 3), (1003, 8) ) AS param(product_id, quantity) ON p.product_id = param.product_id; END;

这里的param表就充当了“输入参数集合”的角色。如果你的 ORM 框架支持构造这种 SQL，那么很多批量操作都会变得非常简单。

5.3 配合字符串拆分函数，生成“动态 IN 列表”

有时候我们传入的是'1,5,9,20'这种字符串，直接 IN 肯定不行。结合VALUES和递归或拆分函数，可以做出一套比较优雅的拆解方案（这里以 SQL Server 的STRING_SPLIT为例）：

DECLARE @ids VARCHAR(100) = '1,5,9,20'; SELECT value AS id FROM STRING_SPLIT(@ids, ',') WHERE TRY_CAST(value AS INT) IS NOT NULL;

如果你用的是更老的版本，没有STRING_SPLIT，也可以用VALUES构造一个“十位数表”来配合SUBSTRING拆分。思路本质一样：先有一张数字序列表，再对字符串按位置切片。我在做一个老的 2008R2 项目时，就是靠这个思路解决了“字符串 ID 列表如何关联查询”的问题。

5.4 数据清洗时用 VALUES 建映射表，一步完成翻译转换

做数据清洗时经常遇到编码值和显示值不一致的情况，比如数据库里存的是0/1，要显示成男/女；或者状态字段存的N/P/F，要翻译成中文。常规做法是左连接一张字典表，但字典表要维护。如果只是临时做一次清洗，完全可以用VALUES现场建映射：

SELECT raw.user_code, raw.status_code, mapping.status_name FROM user_raw_data raw LEFT JOIN (VALUES ('N', '新建'), ('P', '进行中'), ('F', '已完成') ) AS mapping(status_code, status_name) ON raw.status_code = mapping.status_code;

这招在处理一次性数据迁移、临时报表、接口联调时特别香。不用事先建字典表，也不用提交数据库变更脚本，一条 SQL 自己就闭环了。

6. 我实际踩过的 VALUES 相关的几个坑

6.1 列数不一致直接报错

最常见的问题是行的列数不一致。比如：

SELECT * FROM (VALUES (1, 'A'), (2) ) AS t(id, val);

第二行只有一列，数据库会直接报“列数不匹配”的错误。这个问题在新手身上出现得特别多，而且有时候肉眼扫很难发现。我的经验是：写完 VALUES 列表之后，数一下每行的括号内有几个值，尤其是复制粘贴改数据的时候，特别容易删多或删少。（我自己有几次就是从 10 行数据里删掉中间一行，结果把一列顺手删了，查了半天才反应过来。）

6.2 列类型冲突与隐式转换陷阱

前面提到过类型推断的问题。再举一个我实际遇到的例子：有次我把一个参数NULL放进了VALUES，结果 SQL Server 推断这一列为int，后来想 CAST 成varchar时报错。正确做法是在NULL旁边用显式类型转换：

SELECT * FROM (VALUES (1, CAST(NULL AS VARCHAR(20))), (2, 'Alice') ) AS t(id, name);

把NULL单独放在VALUES里时，数据库不知道它是字符串、数字还是日期，所以最好用CAST或CONVERT明确指定类型。这一点在“向临时表插入数据后继续 JOIN”时尤其重要，不然等到运行到后面才发现类型对不上，排查成本就高了。

6.3 关键字冲突和别名缺失

很多数据库要求派生表必须有别名。我在 PostgreSQL 里曾经写过：

SELECT * FROM (VALUES (1, 2));

直接报错，必须给派生表加别名：

SELECT * FROM (VALUES (1, 2)) AS t(a, b);

另外，如果列名用了数据库的保留字，比如order、group、key，记得用反引号（MySQL）或双引号（标准 SQL）包裹。SQL Server 用方括号[order]，PostgreSQL 用双引号"order"。这个坑我在 MySQL 里踩过好几次，因为key太常见了，不注意就会变成语法错误。

6.4 太长的 VALUES 列表会导致 SQL 难以维护

尽管VALUES很好用，但你别把它撑成一个几百行的巨型列表。我见过有人在代码里写了一个 2000 行数据的 VALUES 集合，结果阅读、调试、排错都极其痛苦。遇到这种情况，建议采用临时表 + 批量插入，或者从 Excel 生成 INSERT 脚本。VALUES适合几十行以内的小数据集合，一旦超过这个量级，我就开始考虑物理临时表了。这个度，每个人可以结合自己的习惯，但别让一段 SQL 膨胀成了“数据文件”。

6.5 不同数据库对 NULL 排序的差异影响

如果VALUES造出来的临时表里有 NULL，在后面做排序或分组的时候，不同数据库的行为不一样。SQL Server 中 NULL 默认排在前面，PostgreSQL 默认排在最后，MySQL 中 NULL 也排在前面。这有时会打乱你的预期结果。如果是做报表，最好明确用ORDER BY ... NULLS LAST或COALESCE把 NULL 转成默认值，避免环境不同导致结果不同。

7. 常见问题与排查技巧实录

我在技术群里经常看到有人问 VALUES 相关的问题，挑几个典型的，把排查思路也列出来，做成一个速查表，方便大家以后直接照着查。

这里再单独说一个很隐蔽的坑：在 SQL Server 里，用过大的数字直接放入VALUES，可能会被推断为numeric或decimal，导致精度变化。比如(100000000000000000000)这种超长数字，直接放进 VALUES，再和其他bigint列做 JOIN 时，类型不匹配会引发隐式转换，可能导致索引失效。解决办法还是老办法：显式CAST成目标类型。

另外，如果发现VALUES构造的表在查询计划里出现了“Table Scan”或“Constant Scan”，这是正常的，数据量小时无需担心。如果数据量大导致查询变慢，那是VALUES用错了场景，要果断换成临时表。

8. 实践中的选择判断：什么时候用 VALUES，什么时候老实建临时表

用VALUES构造临时数据有很多优势，但它不是万能的。我给自己定了几条原则，供大家参考：

其一，数据量在几百行以内的、临时验证性质的，优先用VALUES。不需要 DDL，不需要考虑事务和日志，SQL 跑完就完事。这也是它最舒服的场景。

其二，数据量在上千到几万行、而且后续会多次引用，老老实实建临时表。物理临时表有统计信息，可以被索引，可以被多个批次访问，性能和稳定性都更好。

其三，数据从别的表查出来，而不是手写的，那直接用SELECT ... INTO或者CREATE TABLE AS，别把数据复制粘贴成VALUES，那样又慢又容易出错。

其四，需要跨多条 SQL 语句共享数据集时，用临时表或表变量，别指望VALUES能跨语句存活。VALUES的“临时性”仅限于它所在的那一条 SQL 语句内部。

这套判断逻辑，说白了就是看数据量和生命周期。生命周期短、量小，就VALUES；生命周期长、量大，就临时表。没有绝对的哪个更好，只有合不合适。

最后分享一个我自己的使用习惯：我经常用 VALUES 构建一个“期望结果表”，然后把真实查询结果跟它对比，来做快速数据校验。比如算一个汇总口径，我先把用手工算好的答案写成 VALUES 表，再EXCEPT一下真实结果，两者不一致立刻能看出来。这个技巧帮我节省了大量核对时间，也算是对VALUES的一个独创用法吧。希望这些内容对你也有实实在在的帮助。