1. 先搞清楚SQL自定义变量到底解决什么问题写了这么多年 SQL我见过太多脚本是这么写的一个报表查询时间范围写死在WHERE create_time 2024-01-01 AND create_time 2024-02-01里同一个日期在脚本里出现七八次。等到要改成三个月区间得一行一行找、一行一行改改漏一处前后数据就对不上。SQL 自定义变量MySQL 里叫用户变量SQL Server 里叫局部变量Oracle 里叫绑定变量就是专门治这类毛病的。它的本质很简单给一个值起个名字存在会话或批处理的内存里后面反复引用这个名字而不是反复写那串字面量。它的价值不在语法本身而在三件事上一是可维护改一处生效全局二是可复用同一个中间结果在多个子查询里传递三是可传参让一段 SQL 从一次性脚本变成能反复调用的工具。适合谁看写报表的、做数据清洗的、维护存储过程的、天天跟 ETL 打交道的人都会用到。哪怕你只是偶尔写几条查询学会用变量也能让脚本干净一大截。不过这里有个前提得先摆在桌面上不同数据库对变量的定义差得很远有的甚至根本没有会话级用户变量。所以别看到别人写x就照抄先确认你手上这套引擎到底支持哪一类。下面我按分家族—上实操—落玩法—谈性能—排故障的顺序展开中间穿插我自己踩过的坑。2. 别把几种变量混成一锅变量家族分类梳理很多人第一次接触变量就是在网上抄了一段SET rownum : 0的行号模拟脚本跑通了于是以为变量就是这么用的。等到某天写存储过程把rownum塞进DECLARE里立刻报语法错误——因为这两个根本不是一个东西。所以第一步必须把分类理清楚。2.1 MySQL 的三类变量作用域和生命周期完全不同MySQL 里至少有三种容易被混为一谈的东西用户自定义变量User-Defined Variables带前缀比如total、rn。会话级当前连接内到处可见连接断开就没了。不需要声明直接赋值即创建类型由赋的值决定。这是最灵活也最容易出问题的一类。局部变量Local Variables不带用DECLARE声明。只能在BEGIN ... END块内使用也就是存储过程、函数、触发器里。必须先声明后使用而且声明必须写在块的开头位置不能夹在中间。系统变量System Variablesversion、autocommit、session.sql_mode这些。又分全局GLOBAL和会话SESSION两级用SET GLOBAL或SET SESSION修改。全局改动影响所有新连接会话改动只影响当前连接。这三者的关键差异在作用域和类型约束上。用户变量灵活但没类型容易踩隐式转换局部变量有明确类型写起来啰嗦但安全系统变量是配置项改它等于改数据库行为需要相应权限动之前得想清楚。提示MySQL 8.0 的官方文档已经把用户自定义变量标记为未来版本可能移除建议新代码优先考虑窗口函数或应用层传参。老脚本还能跑但别在新项目里当主力方案用。2.2 SQL Server 的变量批处理内有效GO 之后就消失SQL Server 用的是DECLARE var TYPE这套写法语法上更接近编程语言必须声明类型。它的作用域规则很硬变量的生命期是一个批处理Batch。你在一个批处理里声明了dt一旦遇到GO再往下就找不到了必须重新声明。另一个新手常踩的坑是动态 SQL。用EXEC(sql)或sp_executesql执行拼接出来的字符串时那个字符串是独立的作用域外面声明的变量在里面看不见。想传值进去只能通过sp_executesql的参数列表或者把值拼进字符串里。这一点跟 MySQL 的PREPARE ... USING var思路一致都是跨作用域必须显式传参。2.3 Oracle 与 PostgreSQL 的对应写法Oracle 的绑定变量:var主要用于客户端与数据库之间的参数传递PL/SQL 块里则是标准的DECLARE ... BEGIN ... END。在 SQL*Plus 这类工具里还有一套替代变量var、var那是客户端层面做文本替换跟数据库里的变量不是一回事容易被误解。PostgreSQL 更干脆核心 SQL 里没有会话级用户变量。PL/pgSQL 里当然有DECLARE客户端工具 psql 里可以用\set做文本替换另外从 9.2 起支持自定义参数SET myapp.batch_size 500通过current_setting(myapp.batch_size)读取这是一种官方认可的配置型变量用法。数据库会话级用户变量局部变量系统/配置变量典型写法MySQL支持xDECLARE仅块内global.x/session.xSET x : 1;SQL Server无批处理级变量DECLARE x无同名概念靠配置项DECLARE x INT 1;Oracle绑定变量:xPL/SQLDECLAREALTER SESSION:x/v_xPostgreSQL无靠自定义参数PL/pgSQLDECLARESET x ySET myapp.x 1;这张表建议存下来换数据库写脚本时对照一眼能省掉大量为什么语法不对的纠结时间。3. 从声明到赋值一步步把变量用起来搞清楚分类之后就能动手了。这一节我按数据库分别给完整可跑的代码顺便把赋值环节最容易出错的地方点出来。赋值看着简单实际上用 SET 还是 SELECT赋不到值会怎样类型不对会怎样这三个问题能解释一大半的诡异现象。3.1 MySQLSET 与 SELECT 的分工及:的必要性MySQL 用户变量赋值有两条路-- 方式一SET最直观 SET start_date : 2024-01-01; SET end_date : 2024-02-01; SET min_amount 100; -- SET 里用 也可以 -- 方式二SELECT适合从表里取值 SELECT MAX(id) INTO max_id FROM orders; SELECT row_cnt : COUNT(*) FROM users WHERE status 1;有个细节必须强调在SELECT语句里给变量赋值必须用:不能用。因为SELECT里的是等值比较运算符SELECT x 1的意思是判断 x 是否等于 1返回 0 或 1而不是赋值。这个坑我见过太多人在调试时被绕进去——明明写了赋值结果变量一直是 NULL输出里却多了一列 0/1。另一个实用技巧是一次赋多个值SET a : 1, b : hello, c : NOW(); SELECT a, b, c;从表里批量取值到多个变量也可以用一条SELECT ... INTOSELECT MIN(amount), MAX(amount), AVG(amount) INTO min_amt, max_amt, avg_amt FROM orders WHERE create_time start_date;这条语句要求返回恰好一行因为聚合函数保证了这一点所以安全。但如果去掉聚合、碰上多行MySQL 会直接报错这反而是好事——报错总比悄悄取错值强。3.2 SQL Server无行匹配时变量保留原值这是个大坑SQL Server 的赋值同样有SET和SELECT两种但行为差异很微妙值得单独拎出来讲DECLARE max_id INT 0; -- SET 配合子查询无行时结果为 NULL多行时报错 SET max_id (SELECT MAX(id) FROM orders WHERE status 99); -- SELECT 直接赋值无行时保持原值多行时取最后一行 SELECT max_id id FROM orders WHERE status 1;看出问题了吗如果用SELECT v col FROM t这种写法查询结果为空时v不会变成 NULL而是保留赋值的原值。假如你写了DECLARE max_id INT 1000然后从一张空表里 SELECT 赋值max_id依然是 1000脚本会带着一个上次的脏值继续跑最后结果莫名其妙。这个行为几乎没人会主动告诉你但排查线上数据异常时它是常客。多行的情况更隐蔽SELECT v col FROM t碰上多行不报错取值顺序在没有ORDER BY的情况下是不确定的。你想取最大值实际可能拿到任意一行两次执行结果还不一样。所以我的建议很直接需要取一行明确的值用SET v (SELECT ...)配合聚合函数或TOP 1 ... ORDER BY。多行会报错等于给你一道安全网。明确需要遍历多行逐行处理用游标CURSOR不要用 SELECT 赋值的隐式行为。3.3 类型与隐式转换那个身份证变成科学计数法的经典事故变量不声明类型MySQL 用户变量或者类型不匹配SQL Server、Oracle都会触发隐式转换。这里有个流传很广的案例从数据库导出身份证号、订单号、银行卡号这类长数字串落到文件里变成1.10105E17这种科学计数法原始值全丢了。根因通常有两层。第一层在数据库侧字段本来就是数值类型NUMBER、BIGINT或者导出时被当成数值处理。第二层在变量侧如果你在 PL/SQL 或 T-SQL 里声明了DECLARE id NUMBER然后往里塞 18 位身份证号数值类型就会用科学计数法显示精度还可能被截断。正确的处理方式是把这类看起来像数字但本质是字符串的东西全程按字符串处理-- Oracle显式转字符并加前导符防止下游工具误判成数字 SELECT || TO_CHAR(id_card) || AS id_card_str FROM persons; -- MySQL用 CAST 或直接保持字符类型 SELECT CAST(id_card AS CHAR) AS id_card_str FROM persons; -- SQL Server变量直接声明为字符类型 DECLARE id_card VARCHAR(20); SET id_card (SELECT id_card FROM persons WHERE id 1);注意身份证号、手机号、银行卡号、订单号一律用字符类型存储和传递从表结构到变量声明到导出格式三处都要一致。任何一环用了数值类型都可能在那一步丢精度。用 Excel 打开 CSV 时还要把该列预设为文本格式否则前功尽弃。3.4 变量作用域的边界测试写脚本前的必做动作在正式写业务脚本前我习惯先花两分钟做一次作用域验证确认我的变量在需要的位置还活着。MySQL 里可以这样测SET probe : alive; SELECT probe; -- 会话内可见 PREPARE s FROM SELECT ?; EXECUTE s USING probe; -- PREPARE 只认用户变量 DEALLOCATE PREPARE s;SQL Server 里则要特别注意GODECLARE probe VARCHAR(10) alive; PRINT probe; -- 正常输出 GO PRINT probe; -- 报错必须声明标量变量 probe这两段小测试花不了多少时间但能在你写了几百行脚本之后避免为什么这里变量是空的深夜排查。4. 变量在真实业务里的四种高频玩法语法弄明白只是及格线真正让变量产生价值的是把它放进具体场景。下面这四种玩法是我在报表、清洗、批量维护任务里用得最多的每种都附上可直接改用例的代码。4.1 动态 SQL 拼接灵活与风险只隔一层白名单当表名、列名、排序字段需要运行时决定时参数化查询帮不上忙——因为占位符只能传值不能传标识符。这时只能拼接字符串但拼接绝不等于放任。MySQL 的做法是PREPARE / EXECUTE / DEALLOCATESET sql : CONCAT(SELECT COUNT(*) FROM , table_name, WHERE id ?); SET min_id : 1000; PREPARE stmt FROM sql; EXECUTE stmt USING min_id; DEALLOCATE PREPARE stmt;注意PREPARE的?占位符只能用用户变量min_id传值这也是用户变量一个不可替代的用途。表名table_name来自外部时必须做白名单校验比如-- 只允许在预设的几张表里选别的直接拒绝 SET table_name : IF(table_name IN (orders, users, logs), table_name, NULL);SQL Server 侧优先用sp_executesql而不是EXECDECLARE sql NVARCHAR(MAX) NSELECT COUNT(*) FROM dbo.Orders WHERE Id p1; EXEC sp_executesql sql, Np1 INT, p1 1000;原因是sp_executesql把参数作为真正的参数传进去生成的执行计划可以复用而EXEC(...)每次拼出来的字符串内容都不同值被拼进了文本计划缓存命中率极低高频调用的场景下 CPU 会被编译计划吃掉一大块。4.2 用变量模拟行号和累计值老写法与新写法的取舍这是变量最出圈的玩法。在没有窗口函数的年代MySQL 里做行号、分组排名、累计求和全靠用户变量-- 行号 SET rn : 0; SELECT rn : rn 1 AS rn, name, score FROM users ORDER BY score DESC; -- 累计求和 SET running : 0; SELECT dt, amount, running : running amount AS running_total FROM orders ORDER BY dt;实测下来这种写法在 MySQL 5.x 上基本稳但到了 8.0 会偶发错乱。原因是优化器可能把带ORDER BY的派生表合并进外层导致排序和赋值的先后顺序跟你预期的不一样行号最大值都对不上。我遇到过一次排名列出现了跳跃数字排查半天才发现是执行顺序问题。规避方法有两个。一是把排序压到子查询里并阻止合并SELECT rn : rn 1 AS rn, t.* FROM (SELECT id, name, score FROM users ORDER BY score DESC LIMIT 18446744073709551615) t;那个超大的LIMIT是业内常用的阻止派生表合并技巧看着古怪但有效。二是彻底换思路用窗口函数——这也是 8.0 之后我推荐的做法SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, name, score FROM users; SELECT dt, amount, SUM(amount) OVER (ORDER BY dt) AS running_total FROM orders;窗口函数由优化器统一保证语义不用赌执行顺序可读性也更好。唯一的门槛是版本要求MySQL 8.0、SQL Server 2012 及以上、PostgreSQL 全系都支持。老库没法升级时再回到变量方案但一定记得加那种防护性写法。有个分组取最新的需求也很典型用窗口函数写出来是这样SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY create_time DESC) AS rn FROM scores t ) x WHERE rn 1;换成变量方案会长很多而且排序依赖更微妙。我的建议是能用窗口函数就不要用变量模拟变量留给确实没有别的办法的场景。4.3 把魔法数字抽成配置型变量脚本里散落着WHERE status 3 AND type IN (1,2,5) AND create_time 2024-01-01这种字面量是维护性最大的敌人。抽成变量后改动只要一处SET biz_status : 3; SET biz_types : 1,2,5; SET start_date : 2024-01-01; SET end_date : DATE_ADD(start_date, INTERVAL 1 MONTH); SELECT COUNT(*) FROM orders WHERE status biz_status AND create_time start_date AND create_time end_date;这里有个细节值得说biz_types是字符串1,2,5不能直接写type IN (biz_types)那样 MySQL 会把它当成一个整体去比较等于type IN (1,2,5)结果永远是空。想用字符串列表得靠FIND_IN_SETWHERE FIND_IN_SET(type, biz_types) 0或者干脆用PREPARE把列表拼进 SQL 文本。SQL Server 里则推荐用表变量或STRING_SPLIT2016 及以上来接收列表比字符串切分靠得住。用end_date派生而不是硬写还有一个隐藏好处下个月跑同样的脚本把开始日期一改周期自动跟着变。这就是用计算代替复制的基本功。4.4 批处理与分批维护别让一条 SQL 堵住整张表做数据清理时一条DELETE打几千万行会把日志撑爆、锁住整张表。稳妥做法是分批来而分批的批大小自然落在变量上。SQL Server 可以这样写DECLARE batch INT 2000; WHILE 1 1 BEGIN DELETE TOP (batch) FROM dbo.AccessLog WHERE CreateTime DATEADD(DAY, -90, GETDATE()); IF ROWCOUNT 0 BREAK; WAITFOR DELAY 00:00:00.200; ENDTOP (batch)这种带括号的写法是 SQL Server 2005 之后支持的可以直接吃变量。中间那句WAITFOR DELAY看着多余但在主从同步或高并发环境下能有效缓解压力我用它救过好几次。MySQL 侧对应的是LIMIT在存储过程里也可以接受变量CREATE PROCEDURE clean_logs(IN p_batch INT, IN p_days INT) BEGIN DECLARE affected INT DEFAULT 1; WHILE affected 0 DO DELETE FROM access_log WHERE create_time DATE_SUB(NOW(), INTERVAL p_days DAY) LIMIT p_batch; SET affected ROW_COUNT(); DO SLEEP(0.2); END WHILE; END;注意这里p_batch是存储过程的局部参数不是用户变量ROW_COUNT()返回上一条语句影响的行数用它做循环判断比再查一次COUNT(*)高效得多。这种局部变量 参数的组合是存储过程比裸脚本更适合做运维任务的核心原因。5. 性能视角变量不是免费的午餐用变量写脚本顺手之后很容易产生一种错觉——参数化一定比字面量快。这个结论只在特定条件下成立很多时候恰恰相反。5.1 参数嗅探与变量反而估不准的悖论SQL Server 里有一个非常著名的现象叫参数嗅探Parameter Sniffing存储过程第一次执行时优化器会拿这次传入的实际参数值去估算行数直接生成执行计划之后这个计划被缓存复用。如果数据分布极不均匀比如某天订单量是平日的 50 倍第一次恰好用了那天的值编译之后所有常规日期都会沿用这个为超大结果集优化的计划可能走全表扫描慢得离谱。有意思的是如果你在存储过程里把参数赋值给局部变量再用变量写查询优化器在编译时并不知道变量的值只能按统计信息的平均密度估算。这规避了参数嗅探但也可能得到一个哪儿都不太准的估算——这就是业内常说的用变量治嗅探有时管用有时更糟。排查这类问题实用手段有这么几种用OPTION (RECOMPILE)让语句每次重新编译、用OPTIMIZE FOR指定一个典型值、或者把复杂的存储过程拆开。到底选哪种得看你的数据分布是稳定偏斜还是波动剧烈。5.2 变量不会让索引失效但万能查询会网上有说法是用变量查询会走不了索引这个表述不准确。真正的问题出在下面这种写法上-- 不推荐的一个 SQL 应付所有筛选条件 SELECT * FROM orders WHERE (status IS NULL OR status status) AND (start_date IS NULL OR create_time start_date);为了让status为空时也能返回全部数据你加了OR条件。优化器面对这种表达式无法推导出一个稳定的索引查找区间只能退化成扫描再逐行过滤。数据量一大慢得肉眼可见而且这种万能查询在报表系统里到处都是。替代方案是分支处理按参数是否为空走不同语句IF status IS NULL SELECT * FROM orders WHERE create_time start_date; ELSE SELECT * FROM orders WHERE create_time start_date AND status status;代码长一点但每条分支都能拿到合适的索引计划。SQL Server 里还有OPTION (RECOMPILE)配合动态 SQL 的写法也能解决但可读性更差我一般留给确定的热点语句。排查慢 SQL 时我的习惯动作是先用工具看执行计划DBeaver 里对着语句按执行计划按钮或者用 SQL Server 的SET SHOWPLAN_ALL ON重点看两处——有没有出现扫描代替查找、估算行数与实际行数差了几个数量级。后者几乎总是指向统计信息陈旧或者参数估算失准。5.3 会话变量在连接池里的串味风险这一条更偏工程实践但杀伤力不小。MySQL 的SET SESSION只影响当前连接听起来很干净问题是连接池会复用连接。你在这个请求里把sql_mode或某个超时参数改了请求结束没还原下一个请求从池里拿到同一条连接就继承了你的改动。表现是偶发的诡异行为——同样的代码大部分时候正常偶尔报错或结果不同。我的建议是会话级参数改动必须在同一连接上还原或者干脆别在应用代码里改真正需要持久化的配置写进配置文件不要靠运行时的SET SESSION兜着。6. 常见问题与排查速查表下面这些是我在实际工作里反复遇到的情况按现象、原因、处理方式整理成表碰到问题可以先扫一眼。6.1 报错与异常速查现象可能原因处理方式变量一直是 NULL在 SELECT 里用了而不是:改成x : 值变量值上次的还在SQL Server 用 SELECT 赋值且无行匹配保留原值先显式初始化为 NULL或改用SET v (SELECT ...)多行赋值结果不稳定SELECT v col FROM t多行、无 ORDER BY加ORDER BY并TOP 1或改用游标存储过程里DECLARE报语法错DECLARE 没有放在 BEGIN 块开头把声明挪到块最前面动态 SQL 里取不到外部变量拼接字符串是独立作用域用sp_executesql参数列表或PREPARE ... USING传值GO之后变量失效SQL Server 变量作用域限于批处理每个批处理内重新 DECLARE行号出现跳号MySQL 8.0 派生表合并打乱赋值顺序用窗口函数或加超大 LIMIT 阻止合并长数字变成科学计数法类型被当成数值处理或导出工具按数值解析全程用字符类型必要时加前导符IN (list)查不出数据字符串列表被当成单个值用FIND_IN_SET或用表变量/STRING_SPLIT存储过程偶尔极慢参数嗅探导致计划不适合当前参数OPTION (RECOMPILE)、OPTIMIZE FOR或改写偶发行为异常、重启就好连接池复用了被污染会话参数的连接会话参数用完还原或不用运行时 SET6.2 三条我自己一直在用的排查思路第一条先把变量打出来看。别急着怀疑逻辑SELECT x打一下往往一眼就看出是 NULL 还是取错行。这一步花三秒能省半小时。第二条把变量换成字面量对比。如果换成写死的值结果就对了问题百分百在赋值环节或类型转换上而不是查询逻辑。第三条看执行计划里估算与实际行数的比值。差一两个数量级说明统计信息或参数估算有问题估算准但依然慢问题多半在索引设计和 IO 上。6.3 几个能直接省事的实操心得关于命名我坚持用户变量统一加业务前缀比如v_start_date、v_biz_status。不带前缀的x、a在长脚本里三天后自己都看不懂。系统变量和用户变量也不要混着命名和差一个字符肉眼扫过去容易看漏。关于注释给变量赋值的那一行旁边一定要写清楚这个值是干什么的、单位是什么。SET v_offset : 28800;这种不写注释谁能想到是时区偏移秒数我在接手别人的脚本时最花时间的从来不是 SQL 有多复杂而是搞不懂每个魔法值代表什么。关于版本涉及变量的写法在不同版本行为差别很大。特别是 MySQL 5.7 升 8.0 的项目行号模拟这块一定要回归测试SQL Server 2016 之前的STRING_SPLIT不可用字符串列表处理得换方案。上线前在目标版本上跑一遍比事后救火便宜太多。关于动态 SQL我的原则是表名、列名走白名单值走参数化两者绝不能混。拼接字符串的时候如果那个字符串里有任何一段来自用户输入或者外部配置就必须先校验再拼这跟变量本身没关系纯粹是纪律问题。最后分享一个小习惯凡是准备长期复用的脚本我会在开头集中放一段参数区所有变量在这里一次性声明和赋值中间业务逻辑部分只引用不赋值。这样做的好处是一眼就能看清这个脚本可调的旋钮有哪几个交接给别人时对方改开头那几行就够了不用通读全文。这个习惯坚持了几年脚本的可维护性上了一个台阶也少了很多改漏一处的返工。变量这东西语法半小时就能学会真正拉开差距的是怎么组织它、放在哪儿、怎么命名——这些细节决定了半年后你还愿不愿意打开这个文件。 SQL自定义变量实战：跨库作用域、赋值陷阱与性能优化
1. 先搞清楚SQL自定义变量到底解决什么问题写了这么多年 SQL我见过太多脚本是这么写的一个报表查询时间范围写死在WHERE create_time 2024-01-01 AND create_time 2024-02-01里同一个日期在脚本里出现七八次。等到要改成三个月区间得一行一行找、一行一行改改漏一处前后数据就对不上。SQL 自定义变量MySQL 里叫用户变量SQL Server 里叫局部变量Oracle 里叫绑定变量就是专门治这类毛病的。它的本质很简单给一个值起个名字存在会话或批处理的内存里后面反复引用这个名字而不是反复写那串字面量。它的价值不在语法本身而在三件事上一是可维护改一处生效全局二是可复用同一个中间结果在多个子查询里传递三是可传参让一段 SQL 从一次性脚本变成能反复调用的工具。适合谁看写报表的、做数据清洗的、维护存储过程的、天天跟 ETL 打交道的人都会用到。哪怕你只是偶尔写几条查询学会用变量也能让脚本干净一大截。不过这里有个前提得先摆在桌面上不同数据库对变量的定义差得很远有的甚至根本没有会话级用户变量。所以别看到别人写x就照抄先确认你手上这套引擎到底支持哪一类。下面我按分家族—上实操—落玩法—谈性能—排故障的顺序展开中间穿插我自己踩过的坑。2. 别把几种变量混成一锅变量家族分类梳理很多人第一次接触变量就是在网上抄了一段SET rownum : 0的行号模拟脚本跑通了于是以为变量就是这么用的。等到某天写存储过程把rownum塞进DECLARE里立刻报语法错误——因为这两个根本不是一个东西。所以第一步必须把分类理清楚。2.1 MySQL 的三类变量作用域和生命周期完全不同MySQL 里至少有三种容易被混为一谈的东西用户自定义变量User-Defined Variables带前缀比如total、rn。会话级当前连接内到处可见连接断开就没了。不需要声明直接赋值即创建类型由赋的值决定。这是最灵活也最容易出问题的一类。局部变量Local Variables不带用DECLARE声明。只能在BEGIN ... END块内使用也就是存储过程、函数、触发器里。必须先声明后使用而且声明必须写在块的开头位置不能夹在中间。系统变量System Variablesversion、autocommit、session.sql_mode这些。又分全局GLOBAL和会话SESSION两级用SET GLOBAL或SET SESSION修改。全局改动影响所有新连接会话改动只影响当前连接。这三者的关键差异在作用域和类型约束上。用户变量灵活但没类型容易踩隐式转换局部变量有明确类型写起来啰嗦但安全系统变量是配置项改它等于改数据库行为需要相应权限动之前得想清楚。提示MySQL 8.0 的官方文档已经把用户自定义变量标记为未来版本可能移除建议新代码优先考虑窗口函数或应用层传参。老脚本还能跑但别在新项目里当主力方案用。2.2 SQL Server 的变量批处理内有效GO 之后就消失SQL Server 用的是DECLARE var TYPE这套写法语法上更接近编程语言必须声明类型。它的作用域规则很硬变量的生命期是一个批处理Batch。你在一个批处理里声明了dt一旦遇到GO再往下就找不到了必须重新声明。另一个新手常踩的坑是动态 SQL。用EXEC(sql)或sp_executesql执行拼接出来的字符串时那个字符串是独立的作用域外面声明的变量在里面看不见。想传值进去只能通过sp_executesql的参数列表或者把值拼进字符串里。这一点跟 MySQL 的PREPARE ... USING var思路一致都是跨作用域必须显式传参。2.3 Oracle 与 PostgreSQL 的对应写法Oracle 的绑定变量:var主要用于客户端与数据库之间的参数传递PL/SQL 块里则是标准的DECLARE ... BEGIN ... END。在 SQL*Plus 这类工具里还有一套替代变量var、var那是客户端层面做文本替换跟数据库里的变量不是一回事容易被误解。PostgreSQL 更干脆核心 SQL 里没有会话级用户变量。PL/pgSQL 里当然有DECLARE客户端工具 psql 里可以用\set做文本替换另外从 9.2 起支持自定义参数SET myapp.batch_size 500通过current_setting(myapp.batch_size)读取这是一种官方认可的配置型变量用法。数据库会话级用户变量局部变量系统/配置变量典型写法MySQL支持xDECLARE仅块内global.x/session.xSET x : 1;SQL Server无批处理级变量DECLARE x无同名概念靠配置项DECLARE x INT 1;Oracle绑定变量:xPL/SQLDECLAREALTER SESSION:x/v_xPostgreSQL无靠自定义参数PL/pgSQLDECLARESET x ySET myapp.x 1;这张表建议存下来换数据库写脚本时对照一眼能省掉大量为什么语法不对的纠结时间。3. 从声明到赋值一步步把变量用起来搞清楚分类之后就能动手了。这一节我按数据库分别给完整可跑的代码顺便把赋值环节最容易出错的地方点出来。赋值看着简单实际上用 SET 还是 SELECT赋不到值会怎样类型不对会怎样这三个问题能解释一大半的诡异现象。3.1 MySQLSET 与 SELECT 的分工及:的必要性MySQL 用户变量赋值有两条路-- 方式一SET最直观 SET start_date : 2024-01-01; SET end_date : 2024-02-01; SET min_amount 100; -- SET 里用 也可以 -- 方式二SELECT适合从表里取值 SELECT MAX(id) INTO max_id FROM orders; SELECT row_cnt : COUNT(*) FROM users WHERE status 1;有个细节必须强调在SELECT语句里给变量赋值必须用:不能用。因为SELECT里的是等值比较运算符SELECT x 1的意思是判断 x 是否等于 1返回 0 或 1而不是赋值。这个坑我见过太多人在调试时被绕进去——明明写了赋值结果变量一直是 NULL输出里却多了一列 0/1。另一个实用技巧是一次赋多个值SET a : 1, b : hello, c : NOW(); SELECT a, b, c;从表里批量取值到多个变量也可以用一条SELECT ... INTOSELECT MIN(amount), MAX(amount), AVG(amount) INTO min_amt, max_amt, avg_amt FROM orders WHERE create_time start_date;这条语句要求返回恰好一行因为聚合函数保证了这一点所以安全。但如果去掉聚合、碰上多行MySQL 会直接报错这反而是好事——报错总比悄悄取错值强。3.2 SQL Server无行匹配时变量保留原值这是个大坑SQL Server 的赋值同样有SET和SELECT两种但行为差异很微妙值得单独拎出来讲DECLARE max_id INT 0; -- SET 配合子查询无行时结果为 NULL多行时报错 SET max_id (SELECT MAX(id) FROM orders WHERE status 99); -- SELECT 直接赋值无行时保持原值多行时取最后一行 SELECT max_id id FROM orders WHERE status 1;看出问题了吗如果用SELECT v col FROM t这种写法查询结果为空时v不会变成 NULL而是保留赋值的原值。假如你写了DECLARE max_id INT 1000然后从一张空表里 SELECT 赋值max_id依然是 1000脚本会带着一个上次的脏值继续跑最后结果莫名其妙。这个行为几乎没人会主动告诉你但排查线上数据异常时它是常客。多行的情况更隐蔽SELECT v col FROM t碰上多行不报错取值顺序在没有ORDER BY的情况下是不确定的。你想取最大值实际可能拿到任意一行两次执行结果还不一样。所以我的建议很直接需要取一行明确的值用SET v (SELECT ...)配合聚合函数或TOP 1 ... ORDER BY。多行会报错等于给你一道安全网。明确需要遍历多行逐行处理用游标CURSOR不要用 SELECT 赋值的隐式行为。3.3 类型与隐式转换那个身份证变成科学计数法的经典事故变量不声明类型MySQL 用户变量或者类型不匹配SQL Server、Oracle都会触发隐式转换。这里有个流传很广的案例从数据库导出身份证号、订单号、银行卡号这类长数字串落到文件里变成1.10105E17这种科学计数法原始值全丢了。根因通常有两层。第一层在数据库侧字段本来就是数值类型NUMBER、BIGINT或者导出时被当成数值处理。第二层在变量侧如果你在 PL/SQL 或 T-SQL 里声明了DECLARE id NUMBER然后往里塞 18 位身份证号数值类型就会用科学计数法显示精度还可能被截断。正确的处理方式是把这类看起来像数字但本质是字符串的东西全程按字符串处理-- Oracle显式转字符并加前导符防止下游工具误判成数字 SELECT || TO_CHAR(id_card) || AS id_card_str FROM persons; -- MySQL用 CAST 或直接保持字符类型 SELECT CAST(id_card AS CHAR) AS id_card_str FROM persons; -- SQL Server变量直接声明为字符类型 DECLARE id_card VARCHAR(20); SET id_card (SELECT id_card FROM persons WHERE id 1);注意身份证号、手机号、银行卡号、订单号一律用字符类型存储和传递从表结构到变量声明到导出格式三处都要一致。任何一环用了数值类型都可能在那一步丢精度。用 Excel 打开 CSV 时还要把该列预设为文本格式否则前功尽弃。3.4 变量作用域的边界测试写脚本前的必做动作在正式写业务脚本前我习惯先花两分钟做一次作用域验证确认我的变量在需要的位置还活着。MySQL 里可以这样测SET probe : alive; SELECT probe; -- 会话内可见 PREPARE s FROM SELECT ?; EXECUTE s USING probe; -- PREPARE 只认用户变量 DEALLOCATE PREPARE s;SQL Server 里则要特别注意GODECLARE probe VARCHAR(10) alive; PRINT probe; -- 正常输出 GO PRINT probe; -- 报错必须声明标量变量 probe这两段小测试花不了多少时间但能在你写了几百行脚本之后避免为什么这里变量是空的深夜排查。4. 变量在真实业务里的四种高频玩法语法弄明白只是及格线真正让变量产生价值的是把它放进具体场景。下面这四种玩法是我在报表、清洗、批量维护任务里用得最多的每种都附上可直接改用例的代码。4.1 动态 SQL 拼接灵活与风险只隔一层白名单当表名、列名、排序字段需要运行时决定时参数化查询帮不上忙——因为占位符只能传值不能传标识符。这时只能拼接字符串但拼接绝不等于放任。MySQL 的做法是PREPARE / EXECUTE / DEALLOCATESET sql : CONCAT(SELECT COUNT(*) FROM , table_name, WHERE id ?); SET min_id : 1000; PREPARE stmt FROM sql; EXECUTE stmt USING min_id; DEALLOCATE PREPARE stmt;注意PREPARE的?占位符只能用用户变量min_id传值这也是用户变量一个不可替代的用途。表名table_name来自外部时必须做白名单校验比如-- 只允许在预设的几张表里选别的直接拒绝 SET table_name : IF(table_name IN (orders, users, logs), table_name, NULL);SQL Server 侧优先用sp_executesql而不是EXECDECLARE sql NVARCHAR(MAX) NSELECT COUNT(*) FROM dbo.Orders WHERE Id p1; EXEC sp_executesql sql, Np1 INT, p1 1000;原因是sp_executesql把参数作为真正的参数传进去生成的执行计划可以复用而EXEC(...)每次拼出来的字符串内容都不同值被拼进了文本计划缓存命中率极低高频调用的场景下 CPU 会被编译计划吃掉一大块。4.2 用变量模拟行号和累计值老写法与新写法的取舍这是变量最出圈的玩法。在没有窗口函数的年代MySQL 里做行号、分组排名、累计求和全靠用户变量-- 行号 SET rn : 0; SELECT rn : rn 1 AS rn, name, score FROM users ORDER BY score DESC; -- 累计求和 SET running : 0; SELECT dt, amount, running : running amount AS running_total FROM orders ORDER BY dt;实测下来这种写法在 MySQL 5.x 上基本稳但到了 8.0 会偶发错乱。原因是优化器可能把带ORDER BY的派生表合并进外层导致排序和赋值的先后顺序跟你预期的不一样行号最大值都对不上。我遇到过一次排名列出现了跳跃数字排查半天才发现是执行顺序问题。规避方法有两个。一是把排序压到子查询里并阻止合并SELECT rn : rn 1 AS rn, t.* FROM (SELECT id, name, score FROM users ORDER BY score DESC LIMIT 18446744073709551615) t;那个超大的LIMIT是业内常用的阻止派生表合并技巧看着古怪但有效。二是彻底换思路用窗口函数——这也是 8.0 之后我推荐的做法SELECT ROW_NUMBER() OVER (ORDER BY score DESC) AS rn, name, score FROM users; SELECT dt, amount, SUM(amount) OVER (ORDER BY dt) AS running_total FROM orders;窗口函数由优化器统一保证语义不用赌执行顺序可读性也更好。唯一的门槛是版本要求MySQL 8.0、SQL Server 2012 及以上、PostgreSQL 全系都支持。老库没法升级时再回到变量方案但一定记得加那种防护性写法。有个分组取最新的需求也很典型用窗口函数写出来是这样SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY create_time DESC) AS rn FROM scores t ) x WHERE rn 1;换成变量方案会长很多而且排序依赖更微妙。我的建议是能用窗口函数就不要用变量模拟变量留给确实没有别的办法的场景。4.3 把魔法数字抽成配置型变量脚本里散落着WHERE status 3 AND type IN (1,2,5) AND create_time 2024-01-01这种字面量是维护性最大的敌人。抽成变量后改动只要一处SET biz_status : 3; SET biz_types : 1,2,5; SET start_date : 2024-01-01; SET end_date : DATE_ADD(start_date, INTERVAL 1 MONTH); SELECT COUNT(*) FROM orders WHERE status biz_status AND create_time start_date AND create_time end_date;这里有个细节值得说biz_types是字符串1,2,5不能直接写type IN (biz_types)那样 MySQL 会把它当成一个整体去比较等于type IN (1,2,5)结果永远是空。想用字符串列表得靠FIND_IN_SETWHERE FIND_IN_SET(type, biz_types) 0或者干脆用PREPARE把列表拼进 SQL 文本。SQL Server 里则推荐用表变量或STRING_SPLIT2016 及以上来接收列表比字符串切分靠得住。用end_date派生而不是硬写还有一个隐藏好处下个月跑同样的脚本把开始日期一改周期自动跟着变。这就是用计算代替复制的基本功。4.4 批处理与分批维护别让一条 SQL 堵住整张表做数据清理时一条DELETE打几千万行会把日志撑爆、锁住整张表。稳妥做法是分批来而分批的批大小自然落在变量上。SQL Server 可以这样写DECLARE batch INT 2000; WHILE 1 1 BEGIN DELETE TOP (batch) FROM dbo.AccessLog WHERE CreateTime DATEADD(DAY, -90, GETDATE()); IF ROWCOUNT 0 BREAK; WAITFOR DELAY 00:00:00.200; ENDTOP (batch)这种带括号的写法是 SQL Server 2005 之后支持的可以直接吃变量。中间那句WAITFOR DELAY看着多余但在主从同步或高并发环境下能有效缓解压力我用它救过好几次。MySQL 侧对应的是LIMIT在存储过程里也可以接受变量CREATE PROCEDURE clean_logs(IN p_batch INT, IN p_days INT) BEGIN DECLARE affected INT DEFAULT 1; WHILE affected 0 DO DELETE FROM access_log WHERE create_time DATE_SUB(NOW(), INTERVAL p_days DAY) LIMIT p_batch; SET affected ROW_COUNT(); DO SLEEP(0.2); END WHILE; END;注意这里p_batch是存储过程的局部参数不是用户变量ROW_COUNT()返回上一条语句影响的行数用它做循环判断比再查一次COUNT(*)高效得多。这种局部变量 参数的组合是存储过程比裸脚本更适合做运维任务的核心原因。5. 性能视角变量不是免费的午餐用变量写脚本顺手之后很容易产生一种错觉——参数化一定比字面量快。这个结论只在特定条件下成立很多时候恰恰相反。5.1 参数嗅探与变量反而估不准的悖论SQL Server 里有一个非常著名的现象叫参数嗅探Parameter Sniffing存储过程第一次执行时优化器会拿这次传入的实际参数值去估算行数直接生成执行计划之后这个计划被缓存复用。如果数据分布极不均匀比如某天订单量是平日的 50 倍第一次恰好用了那天的值编译之后所有常规日期都会沿用这个为超大结果集优化的计划可能走全表扫描慢得离谱。有意思的是如果你在存储过程里把参数赋值给局部变量再用变量写查询优化器在编译时并不知道变量的值只能按统计信息的平均密度估算。这规避了参数嗅探但也可能得到一个哪儿都不太准的估算——这就是业内常说的用变量治嗅探有时管用有时更糟。排查这类问题实用手段有这么几种用OPTION (RECOMPILE)让语句每次重新编译、用OPTIMIZE FOR指定一个典型值、或者把复杂的存储过程拆开。到底选哪种得看你的数据分布是稳定偏斜还是波动剧烈。5.2 变量不会让索引失效但万能查询会网上有说法是用变量查询会走不了索引这个表述不准确。真正的问题出在下面这种写法上-- 不推荐的一个 SQL 应付所有筛选条件 SELECT * FROM orders WHERE (status IS NULL OR status status) AND (start_date IS NULL OR create_time start_date);为了让status为空时也能返回全部数据你加了OR条件。优化器面对这种表达式无法推导出一个稳定的索引查找区间只能退化成扫描再逐行过滤。数据量一大慢得肉眼可见而且这种万能查询在报表系统里到处都是。替代方案是分支处理按参数是否为空走不同语句IF status IS NULL SELECT * FROM orders WHERE create_time start_date; ELSE SELECT * FROM orders WHERE create_time start_date AND status status;代码长一点但每条分支都能拿到合适的索引计划。SQL Server 里还有OPTION (RECOMPILE)配合动态 SQL 的写法也能解决但可读性更差我一般留给确定的热点语句。排查慢 SQL 时我的习惯动作是先用工具看执行计划DBeaver 里对着语句按执行计划按钮或者用 SQL Server 的SET SHOWPLAN_ALL ON重点看两处——有没有出现扫描代替查找、估算行数与实际行数差了几个数量级。后者几乎总是指向统计信息陈旧或者参数估算失准。5.3 会话变量在连接池里的串味风险这一条更偏工程实践但杀伤力不小。MySQL 的SET SESSION只影响当前连接听起来很干净问题是连接池会复用连接。你在这个请求里把sql_mode或某个超时参数改了请求结束没还原下一个请求从池里拿到同一条连接就继承了你的改动。表现是偶发的诡异行为——同样的代码大部分时候正常偶尔报错或结果不同。我的建议是会话级参数改动必须在同一连接上还原或者干脆别在应用代码里改真正需要持久化的配置写进配置文件不要靠运行时的SET SESSION兜着。6. 常见问题与排查速查表下面这些是我在实际工作里反复遇到的情况按现象、原因、处理方式整理成表碰到问题可以先扫一眼。6.1 报错与异常速查现象可能原因处理方式变量一直是 NULL在 SELECT 里用了而不是:改成x : 值变量值上次的还在SQL Server 用 SELECT 赋值且无行匹配保留原值先显式初始化为 NULL或改用SET v (SELECT ...)多行赋值结果不稳定SELECT v col FROM t多行、无 ORDER BY加ORDER BY并TOP 1或改用游标存储过程里DECLARE报语法错DECLARE 没有放在 BEGIN 块开头把声明挪到块最前面动态 SQL 里取不到外部变量拼接字符串是独立作用域用sp_executesql参数列表或PREPARE ... USING传值GO之后变量失效SQL Server 变量作用域限于批处理每个批处理内重新 DECLARE行号出现跳号MySQL 8.0 派生表合并打乱赋值顺序用窗口函数或加超大 LIMIT 阻止合并长数字变成科学计数法类型被当成数值处理或导出工具按数值解析全程用字符类型必要时加前导符IN (list)查不出数据字符串列表被当成单个值用FIND_IN_SET或用表变量/STRING_SPLIT存储过程偶尔极慢参数嗅探导致计划不适合当前参数OPTION (RECOMPILE)、OPTIMIZE FOR或改写偶发行为异常、重启就好连接池复用了被污染会话参数的连接会话参数用完还原或不用运行时 SET6.2 三条我自己一直在用的排查思路第一条先把变量打出来看。别急着怀疑逻辑SELECT x打一下往往一眼就看出是 NULL 还是取错行。这一步花三秒能省半小时。第二条把变量换成字面量对比。如果换成写死的值结果就对了问题百分百在赋值环节或类型转换上而不是查询逻辑。第三条看执行计划里估算与实际行数的比值。差一两个数量级说明统计信息或参数估算有问题估算准但依然慢问题多半在索引设计和 IO 上。6.3 几个能直接省事的实操心得关于命名我坚持用户变量统一加业务前缀比如v_start_date、v_biz_status。不带前缀的x、a在长脚本里三天后自己都看不懂。系统变量和用户变量也不要混着命名和差一个字符肉眼扫过去容易看漏。关于注释给变量赋值的那一行旁边一定要写清楚这个值是干什么的、单位是什么。SET v_offset : 28800;这种不写注释谁能想到是时区偏移秒数我在接手别人的脚本时最花时间的从来不是 SQL 有多复杂而是搞不懂每个魔法值代表什么。关于版本涉及变量的写法在不同版本行为差别很大。特别是 MySQL 5.7 升 8.0 的项目行号模拟这块一定要回归测试SQL Server 2016 之前的STRING_SPLIT不可用字符串列表处理得换方案。上线前在目标版本上跑一遍比事后救火便宜太多。关于动态 SQL我的原则是表名、列名走白名单值走参数化两者绝不能混。拼接字符串的时候如果那个字符串里有任何一段来自用户输入或者外部配置就必须先校验再拼这跟变量本身没关系纯粹是纪律问题。最后分享一个小习惯凡是准备长期复用的脚本我会在开头集中放一段参数区所有变量在这里一次性声明和赋值中间业务逻辑部分只引用不赋值。这样做的好处是一眼就能看清这个脚本可调的旋钮有哪几个交接给别人时对方改开头那几行就够了不用通读全文。这个习惯坚持了几年脚本的可维护性上了一个台阶也少了很多改漏一处的返工。变量这东西语法半小时就能学会真正拉开差距的是怎么组织它、放在哪儿、怎么命名——这些细节决定了半年后你还愿不愿意打开这个文件。