MySQL $用法详解：shell变量展开与命令行排错

“mysql $的用法”这个搜索词我第一次看到的时候也愣了一下——MySQL 的命令行提示符确实是mysql但要说到$其实大多数时候它压根不属于 MySQL而是属于你面前那个 shell。“Mysql一些基础用法”和$撞在一起说明搜这个问题的人大概率卡在了两个场景之一要么是在终端里敲命令被$的变量展开搞得莫名其妙要么是听说 MySQL 里有变量结果发现写出来不生效。这两种困惑背后其实是同一件事你得先搞清楚哪一层是 shell、哪一层是客户端、哪一层才是 MySQL 服务端。这篇东西我把这几层拆开讲清楚然后顺着把 MySQL 的日常工作流走一遍连库、建表、增删改查、索引、变量、脚本化、批量导入导出最后给一张报错对照表。刚接手数据库的后端开发、做数据处理的同学、以及需要在服务器上跑脚本的运维都能直接抄作业。文中所有参数和写法我都按常见实践给出来了机器上跑一遍就能验证不用猜。1. 先把“$”这件事讲清楚1.1 终端里的 $ 只是提示符不是 MySQL 的语法打开终端看到的$是 shell 的普通用户提示符root 用户通常是#它只是告诉你“可以输入命令了”本身没有任何语法含义。真正让$产生作用的是变量展开在 bash、zsh 这类 shell 里$后面跟一个名字shell 会在把命令交给程序之前先把这个位置替换成对应变量的值。这里有个关键的时间顺序问题变量展开发生在 MySQL 客户端启动之前。你敲的是这样一个命令mysql -u root -p$DB_PASS -e select * from $TABLE_NAMEshell 先做替换假设DB_PASS是abc123、TABLE_NAME是users那么实际执行的程序调用就变成了mysql -u root -pabc123 -e select * from users。MySQL 客户端从头到尾没见过$这个东西。所以“mysql $的用法”这个问题准确说法应该是“在 shell 里调用 mysql 时$怎么用”。理解了这一层很多莫名其妙的现象就通了。比如你在服务器上敲了一条命令本地测好好的换个环境就报语法错误八成是某个变量在那个环境里是空的$被替换成了空气SQL 语句拼接出来就残缺了。这类问题不看展开后的结果光盯着 SQL 看一辈子也找不到。1.2 MySQL 里的变量符号是 和 而不是 $MySQL 服务端和客户端语法里没有$这个变量符号。它的用户变量用单个系统变量用两个。这个和 shell 的$完全是两套体系互相不认识。很多人写法上混淆是因为见过存储过程或者某些配置里出现$比如一些 ORM 框架的 SQL 模板、某些数据同步工具的参数占位。那是应用层的东西不是 MySQL 原生语法。原生 MySQL 里你写SELECT $x只会得到ERROR 1064 (42000): You have an error in your SQL syntax因为解析器看到$不知道你要干什么。顺带把几个容易混的符号一次性摆清楚符号所属层用途$shell变量引用、命令替换$(...)、提示符nameMySQL用户变量会话级无需声明nameMySQL系统变量默认读会话级可加GLOBAL?应用驱动预处理语句占位符防注入反引号MySQL包裹标识符库名、表名、字段名这里要特别提醒?占位符是驱动层的东西PHP 的 PDO、Java 的 PreparedStatement、Python 的各类驱动都支持但你在 mysql 命令行里手写?是不行的命令行不认。有人把代码里的 SQL 复制到命令行调试看到?报错就觉得是驱动有 bug其实就是层级搞混了。注意命令行里调试带占位符的 SQL要先把?换成真实值再执行别指望客户端帮你 bind。1.3 预处理语句里的 ? 与驱动层的占位符既然提到了就把这块补完整。预处理的思路是把“语句结构”和“数据值”分两次发给服务端先发一条带?的模板让服务端编译好执行计划再把参数值单独发过去填充。好处有两个一是参数永远被当成值处理不会被拼进语句结构从根上堵住注入二是同样的模板重复执行时省掉解析开销。用 Python 的pymysql举例写法是这样的cursor.execute( select id, name from users where age %s and city %s, (18, 某城市) )注意这里的占位符是%s不是?不同驱动的占位符不统一。MySQL Connector/Python 用%smysql2Node.js用?JDBC 用?。换驱动的时候占位符要跟着改这是迁移里很容易漏的一处。还有一个常见误区占位符只能替换值不能替换表名、字段名、order by后面的排序方式。你写select * from ?是不行的服务端会把它当成语法错误。这类动态部分只能靠白名单校验把允许的表名、字段名枚举出来做匹配绝对不能直接拼用户输入。这条是硬规矩我在代码评审里见过太多次“用户传啥就拼啥”的写法出问题是迟早的事。2. 连接 MySQL 的几种姿势与参数取舍2.1 命令行登录的完整参数拆解最常用的登录形式是mysql -h 主机 -P 端口 -u 用户 -p。逐个说-h主机名或 IP不填默认localhost。填localhost和填127.0.0.1在 Linux 上行为可能不同localhost走 Unix socket127.0.0.1走 TCP。这个差别在排查连接问题时非常关键。-P端口注意是大写 P小写-p是密码提示。写反了会得到一串看不懂的报错新手很容易在这里卡半天。-u用户名。-p后面不要跟空格再跟密码正确写法是-p直接回车然后在提示里输入这样密码不会进命令历史如果非要写在一起是-p密码中间没空格。最后一个位置参数是默认库名mysql -u root -p mydb登录后直接进入mydb省得再敲一次use。再加上几个实战里高频的选项-e SQL执行完直接退出适合脚本-N去掉输出表头-B用制表符分隔而不是画表格边框方便管道处理--default-character-setutf8mb4保证中文不乱码。一条我经常用来检查环境的命令mysql -h 127.0.0.1 -P 3306 -u appuser -p -N -B \ -e select version(), character_set_server, max_connections一次拿到版本、服务端字符集、最大连接数三个信息排障时很有用。2.2 把密码从命令行里拿掉命令行写密码有三个风险进 shell 历史、被ps看到、写进脚本后被提交到仓库。前两个是真问题第三个是事故来源。常见的替代方案有几种各有取舍。第一种是~/.my.cnf配置文件权限必须设成600[client] userappuser password你的密码 host127.0.0.1 port3306 default-character-setutf8mb4放在[client]段下所有 MySQL 客户端工具含mysqldump都会读不用重复传参。用chmod 600 ~/.my.cnf收紧权限否则 MySQL 会直接忽略这个文件并给个警告。第二种是--defaults-extra-file/路径/配置适合一个机器上要连多个实例的场景每个实例一份配置用哪个传哪个。这个选项必须放在命令的第一个参数位置放后面不生效这是个很隐蔽的坑。第三种是用环境变量MYSQL_PWD能用但我不太推荐因为环境变量在同机其他进程里可能可见安全性并不比命令行好多少。临时调试可以长期跑脚本还是配置文件稳妥。注意别把带密码的配置文件放进代码仓库哪怕是“内部仓库”。加进.gitignore用部署流程去下发。2.3 连接不上时按这个顺序查连接失败别慌按固定顺序排查能省很多时间。第一步先确认服务端有没有起来在服务器上systemctl status mysql或mysqld看发行版看进程状态ss -lntp | grep 3306看端口有没有监听。端口没监听后面都不用查。第二步区分是网络不通还是认证失败。报ERROR 2003 (HY000): Cant connect to MySQL server on ... (111)是连不上可能是服务没起、监听地址不对、或者中间有网络策略挡着。报ERROR 1045 (28000): Access denied for user是连上了但认证没过问题在账号密码或授权。第三步如果是 1045先确认账号的 host 部分。MySQL 的账号是用户名主机的组合appuserlocalhost和appuser%是两个不同的账号。从远程连进来匹配的是%或者具体 IP 那条。很多人本地能连、远程连不上就是只建了localhost的账号。还有一种情况是ERROR 1698 (28000): Access denied for user rootlocalhost这在较新的发行版里很常见原因是 root 账号用了 socket 认证插件而不是密码认证。改成密码认证或者用系统 root 通过 socket 登录再改配置别硬试密码。具体做法是登录进去执行ALTER USER rootlocalhost IDENTIFIED WITH mysql_native_password BY 新密码;然后FLUSH PRIVILEGES;。3. 库、表、字段基础操作里的隐藏细节3.1 建库建表的字符集与排序规则建库的时候把字符集显式写出来别依赖默认值CREATE DATABASE app_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;为什么一定要utf8mb4而不是utf8因为 MySQL 里的utf8实际是utf8mb3最多 3 字节存不下 emoji 和部分生僻字插入时报ERROR 1366 (HY000): Incorrect string value。这个坑踩一次就够记一辈子——用户昵称里带个表情写入直接失败还得回来改表。新库一律utf8mb4没有例外。排序规则collation决定比较和排序的规则。utf8mb4_general_ci是不区分大小写的通用规则utf8mb4_bin区分大小写按字节比8.0 之后还有utf8mb4_0900_ai_ci。选哪个影响的是where name Tom能不能匹配到tom。业务上要区分大小写的字段比如邀请码、token单独给字段指定_bin排序规则别为了一个字段把整张表改掉。建表时同理表和字段都可以单独指定字符集但没必要层层指定跟着库走就行。要检查现状用SHOW VARIABLES LIKE character_set%; SHOW CREATE TABLE users\G这两条能看到服务端默认值和你实际建出来的表结构对比一下就知道有没有漏。3.2 增删改查的正确写法基础四条语句本身不难难的是写出“不会在半年后咬你一口”的版本。先说查询SELECT *在调试时可以写进代码里就是隐患字段顺序变了、加了新字段、加了大数据类型都会让上层代码出问题。明确列出需要的列这个习惯要养。条件更新和删除先写WHERE并跑一遍SELECT验证影响范围这是保命动作-- 先看会动到哪些行 SELECT id, status FROM orders WHERE status pending AND created_at 2024-01-01; -- 确认无误再改 UPDATE orders SET status closed WHERE status pending AND created_at 2024-01-01 LIMIT 1000;UPDATE和DELETE后面加LIMIT是防一次性锁太多行。大表上直接全量更新事务会撑得很大主从延迟跟着涨线上表现就是“忽然卡住了”。分批做每批几千行中间歇一下稳得多。插入有几种写法值得区分。单条INSERT INTO ... VALUES (...)最简单批量插入用一条语句带多个值组比循环单条插入快一个量级因为省掉了每条的往返和事务开销。如果要处理主键冲突INSERT ... ON DUPLICATE KEY UPDATE能做 upsert但要注意它会消耗自增 ID长期高频用会让 ID 涨得飞快、不连续在意这个的话改用INSERT IGNORE加后续更新。3.3 索引不是越多越好索引的本质是用额外的写入成本和存储空间换读取时的定位速度。每建一个索引插入、更新、删除都要多维护一棵 B 树写入量大的表索引一多吞吐掉得很明显。我见过一张订单表上建了十几个单列索引结果写入压力上来之后整体变慢清理掉一半反而快了。联合索引有个“最左前缀”原则(a, b, c)这个索引能用于a、(a,b)、(a,b,c)这三种查询模式单独查b或c用不上。所以字段顺序不是随便排的要把区分度高、查询频率高的放左边。判断一条 SQL 有没有用上索引看EXPLAINEXPLAIN SELECT id, name FROM users WHERE city 某城市 AND age 18;看几个关键列type最好是ref、range或const出现ALL就是全表扫key是实际用的索引名为NULL说明没走索引rows是估算扫描行数越小越好Extra里出现Using filesort是额外排序、Using temporary是用了临时表这两个在大结果集上都要留意。还有一种情况容易被误判字段上套了函数。WHERE DATE(created_at) 2024-01-01这种写法即使created_at上有索引也用不上因为服务端得先算函数值再比。改成区间写法WHERE created_at 2024-01-01 AND created_at 2024-01-02索引就能发挥作用。这个改法改动量小、收益大我在好几个项目里都做过批量清理。4. 变量体系用户变量、系统变量与作用域4.1 用户变量会话级的临时储物柜用户变量用打头不需要声明类型赋值即创建生命周期跟着当前连接走连接断了就没了。SET today CURDATE(); SET min_amount 100; SELECT id, amount FROM orders WHERE created_at today AND amount min_amount;它最实用的场景是在同一条语句里跨行传递状态。比如按销量排个名次SET rn : 0; SELECT rn : rn 1 AS rank_no, p.name, p.sales FROM products p ORDER BY p.sales DESC;这个写法在旧版本里很常见但没有保证的顺序语义ORDER BY和赋值求值的先后关系依赖执行计划换个版本可能结果就飘了。MySQL 8.0 之后直接用窗口函数SELECT ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank_no, name, sales FROM products;干净、确定、推荐。手上还有老版本要用变量写法的务必在测试环境验证结果顺序符合预期别想当然。用户变量还有两个需要注意的点。一是它不进事务回滚不受影响二是在同一条SELECT里既读又写同一个变量行为不一定符合直觉。我个人的原则是能用窗口函数或子查询解决的就不用用户变量。4.2 系统变量与 SET 的两种作用域系统变量控制服务端行为用访问。不带前缀时默认读会话级加了GLOBAL读全局SHOW VARIABLES LIKE max_connections; SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; SELECT session.sql_mode, global.sql_mode;SET也分两种作用域这个区别直接决定“改完为什么不生效”写法影响范围生效时机重启后SET SESSION x v当前连接立即不保留SET GLOBAL x v新连接对已有连接无效丢失SET PERSIST x v新连接对已有连接无效保留配置文件my.cnf全部重启后保留最典型的踩坑用SET GLOBAL max_connections 500;改完当前窗口再查max_connections还是旧值——因为查的是会话变量。要看新的得查global.max_connections或者重连一次。SET GLOBAL在重启后会丢要持久化得写配置文件或用 8.0 的SET PERSIST。后者会把设置写到一个专门的配置文件里重启后自动加载比重启前手改my.cnf省事但注意它和手改的配置可能冲突排查“配置为什么不生效”时要想到这一层。4.3 用变量做序列号与累计求和除了排名累计求和也是变量常见的用法。假设要算每天的销售额和累计值SET cum : 0; SELECT d.sale_date, d.amount, cum : cum d.amount AS cumulative FROM daily_sales d ORDER BY d.sale_date;同样8.0 里有更稳的写法SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative FROM daily_sales;窗口函数的好处是语义明确、不依赖执行顺序而且能和GROUP BY、子查询自由组合。我现在的习惯是看到代码里有SET x : 0开头的统计逻辑只要版本允许就顺手改成窗口函数。改动不大但后面接手的人少踩很多坑。还有一个容易忽略的点用户变量在GROUP BY里赋值的结果只有在ONLY_FULL_GROUP_BY关闭或者你按功能依赖分组时才可预测。这个sql_mode在 5.7 之后默认开启行为变化会影响老代码。查一下SELECT sql_mode;如果里面有ONLY_FULL_GROUP_BY那些“随便取一行”的写法会直接报错。这不是 bug是规范收紧了该改的 SQL 得改。5. 把 SQL 塞进 shell 脚本$ 真正会咬人的地方5.1 引号选择决定变量谁来展开写脚本调 MySQL最容易出事的不是 SQL 本身是引号。规则记三条单引号里的内容原样保留双引号里$和反引号会被展开反引号在 shell 里是命令替换。看这个例子TBLusers # 双引号$TBL 被替换成 users mysql -e select count(*) from $TBL # 单引号$TBL 原样传进去MySQL 报语法错误 mysql -e select count(*) from $TBL单引号那条会报ERROR 1064因为 MySQL 收到了字面的$TBL。这不是 MySQL 不认$是 shell 根本没帮你换。更阴的是变量名边界问题。$TBL_suffix会被理解成变量TBL_suffix而不是TBL加_suffix结果是空值。要加花括号明确边界${TBL}_suffix。这个坑在拼表名、拼库名的时候特别常见尤其是按日期分表的场景。再一个SQL 字符串里如果本身带$比如金额符号在双引号里会被 shell 当成变量开头# 危险$100 里的 $1 会被当成位置参数 mysql -e insert into logs(msg) values($100)$1在脚本里是第一个命令行参数没传就是空最后插进去的是00。金额、模板变量、正则表达式里带$的情况都得注意要么用单引号包 SQL、要么把$转义成\$。我一般写脚本时 SQL 部分统一用单引号需要插变量的地方单独拿出来拼一眼能看出哪里在展开。注意SQL 里用反引号包表名user在 shell 双引号里会被当成命令替换执行报command not found。要么用单引号要么就别给表名加反引号表名不是保留字时不需要。5.2 批量执行与 here-doc多条 SQL 一起跑heredoc 比一堆-e清爽。关键在开始标记加不加引号TBLorders # 不加引号$TBL 会被展开 mysql -u appuser -p$DB_PASS mydb EOF select count(*) from $TBL where status pending; update $TBL set status closed where id 1000; EOF # 加引号全部原样传入适合纯静态 SQL mysql -u appuser -p$DB_PASS mydb EOF select count(*) from orders where status pending; EOF注意第二种写法里EOF带单引号整段内容不做任何展开。做数据修复脚本时我倾向用这个能保证 SQL 一个字符都不变。需要插变量的场景用第一种但要把变量在脚本开头统一校验空值直接退出: ${TBL:?表名未设置}这行在变量为空时会直接报错退出比跑一半出错强。脚本里还要加set -euo pipefail让命令失败时立刻停别带着错误状态往下跑。MySQL 客户端默认遇到 SQL 错误是继续执行后续语句的要在脚本层面控制可以用--force的反面默认不加--force遇到错误就退出并返回非零码配合set -e就能中断流程。5.3 导出导入的实用组合mysqldump和mysql的输入输出重定向是最常用的一对# 导出单表结构和数据 mysqldump -u appuser -p$DB_PASS --single-transaction --quick mydb orders orders.sql # 导入 mysql -u appuser -p$DB_PASS mydb orders.sql参数解释一下--single-transaction让 InnoDB 表在一个一致性快照里导出不锁表线上做备份基本都要加--quick是逐行取数据不一次性载入内存大表必备。这两个参数配合使用是标准做法。只要结构不要数据用--no-data反过来用--no-create-info。跨库迁移时--databases会把CREATE DATABASE一起带上。大文件导入有几个提速点临时关掉唯一性检查和自动提交导完再开回来——SET autocommit 0; SET unique_checks 0; SET foreign_key_checks 0; -- 导入过程 COMMIT; SET unique_checks 1; SET foreign_key_checks 1;关外键检查这条要谨慎导入期间如果有人并发写可能产生脏数据。只在明确的维护窗口里做。还有一个常被忽略的参数max_allowed_packet。导入单个大字段或者大INSERT时报ERROR 1153 (08S01): Got a packet bigger than max_allowed_packet bytes就是这个限制。客户端和服务端都要调客户端用--max-allowed-packet256M服务端用SET GLOBAL max_allowed_packet 268435456;。两边都要够只改一边照样报错。6. 常见问题速查与排查思路6.1 报错信息对照表把高频报错整理成表遇到时先对号入座能省掉大量猜测时间。报错常见原因处理方向ERROR 1045 (28000)账号密码错或账号 host 不匹配确认用户主机组合远程连接要建%账号ERROR 2003 (HY000) (111)服务未启动、端口未监听、网络不通查进程、查端口、查监听地址ERROR 1049 (42000)库不存在检查库名拼写确认use正确ERROR 1064 (42000)语法错误最常见是 shell 变量没展开把命令echo出来看展开结果ERROR 1366 (HY000)字符集不兼容写入了 4 字节字符库表连接统一改utf8mb4ERROR 1153 (08S01)数据包超限两边调大max_allowed_packetERROR 1213 (40001)死锁看SHOW ENGINE INNODB STATUS的 LATEST DETECTED DEADLOCKLock wait timeout exceeded行锁等待超时查长事务information_schema.innodb_trxToo many connections连接数打满查连接来源排查连接池泄漏Lock wait timeout这条我想多说一句。它和死锁不一样死锁是服务端主动回滚一方Lock wait timeout是傻等超时。排查时先看information_schema.innodb_trx里哪个事务跑得最久再看它卡在什么语句上。十次里有八次是应用层事务开太久——开了事务中间去调了个慢接口锁就攥着不放。解决办法是缩小事务范围别把网络调用塞进事务里。6.2 几个只有踩过才知道的细节第一localhost和127.0.0.1不是一回事。Linux 上localhost走 Unix socket127.0.0.1走 TCP。socket 路径不对、权限不够-h localhost会失败但-h 127.0.0.1能连上。反过来为了走 socket 绕过网络层就得显式写-h localhost。这个区别在容器环境里尤其明显容器里往往没有 socket只能用 IP。第二密码里有$会让命令行认证莫名失败。密码是ab$cd在双引号里$cd被当变量展开成空实际传进去的是ab。用单引号包住-pab$cd或者干脆用配置文件。这类问题排查起来最费劲因为报错是 1045你会以为是密码记错了其实是密码被改了。第三交互式客户端的\G比;好用。SHOW CREATE TABLE users\G竖排输出字段多的时候比横排清楚得多。\s看当前连接状态包含版本、字符集、当前库、连接 ID排查环境问题很方便。第四事务隔离级别影响你看到的数据。默认是REPEATABLE READ同一事务内多次查询看到的是同一个快照。想在事务里看到别的事务刚提交的数据得改隔离级别或者提交重开。这个行为不是 bug是设计如此但如果你在写对账逻辑没意识到这点会得出错误结论。用SELECT transaction_isolation;确认当前级别。第五count(*)和count(字段)语义不同。前者数行数后者只数该字段非 NULL 的行。表里有 NULL 值时两者结果不一样统计时想清楚要哪个。另外大表上精确count(*)很慢需要频繁统计的场景应该维护计数表或用近似值。第六DELETE不会立刻释放磁盘空间。InnoDB 只是把行标记删除空间留在表空间里复用。删了大量数据后文件大小不变是正常的要真正回收得OPTIMIZE TABLE或者重建表而这个操作会锁表、耗时长线上别随便跑。第七utf8mb4下索引长度限制。建索引时如果字段是varchar(255)且字符集是utf8mb4索引长度按 4 字节算就是 1020 字节超过某些引擎或版本的索引前缀限制会报错。解决办法是给索引起前缀长度比如INDEX idx_name (name(64))或者用TEXT字段时强制指定前缀。这个报错信息不太直观容易卡住。6.3 脚本化时的几个硬规矩跑批量脚本前我给自己定了几条不能破的规矩分享出来。先SELECT后UPDATE。任何写操作先用同样条件查一遍看行数和内容对不对。行数超出预期就停下来重新确认条件别抱着“应该没问题”的心态按回车。大表操作分批。一次动几千行循环推进每批之间留间隔。判断标准是单次事务的锁持有时间控制在秒级以内。有人图快一次改几十万行结果主从延迟、连接堆积恢复起来比慢慢跑麻烦十倍。脚本必须幂等。同样的脚本跑两遍结果应该一样。做不到幂等的在脚本里加个标记位或者先检查状态避免重跑时重复扣款、重复发货这类事故。这条在数据修复场景里是底线。操作前留退路。改之前把要动的行导出备份哪怕只是SELECT ... INTO OUTFILE存一份。真出问题时有个对照能少掉很多头发。别在业务高峰期跑。这条听着像废话但真正执行起来经常被“就差这一下”说服。批量任务该排到低峰该限速限速。最后说个我自己的体会。刚接触 MySQL 那阵子我也搜过类似“mysql $的用法”这种问题当时以为是个语法点后来才明白这类困惑的根源是分层没想清楚shell 是一层客户端是一层服务端又是一层每一层有自己的符号体系和执行时机。把这三层的边界划清大部分“玄学问题”都会变成可解释的现象。遇到报错先问自己一句“这是哪一层报的”问题基本就拆掉一半了。至于具体的函数和参数查文档就有反倒是这个分层思维得靠一次次踩坑才能长出来。