MySQL运算符详解：从基础到高级应用

1. MySQL运算符基础概念解析MySQL运算符是数据库操作中最基础也最核心的组成部分它们就像数据库世界的标点符号决定了数据如何被处理、计算和比较。作为从业15年的数据库工程师我发现很多性能问题和逻辑错误其实都源于对运算符特性的误解。运算符主要分为四大类算术运算符处理数值计算、-、*、/、%比较运算符进行条件判断、、、、、逻辑运算符组合多个条件AND、OR、NOT特殊运算符处理特定场景BETWEEN、IN、LIKE等注意MySQL中所有运算符都有明确的优先级顺序当混合使用时乘除会先于加减NOT先于ANDAND先于OR。建议复杂表达式始终使用括号明确优先级。2. 算术运算符实战详解2.1 基础算术运算加减乘除看似简单但在MySQL中有几个关键特性SELECT 10 / 3; -- 结果为3.3333自动转为DECIMAL SELECT 10 DIV 3; -- 结果为3整除运算 SELECT 10 % 3; -- 结果为1取模运算2.2 浮点数精度陷阱这是实际项目中最容易踩的坑CREATE TABLE accounts ( balance DECIMAL(10,2) ); INSERT INTO accounts VALUES (0.1), (0.2); SELECT SUM(balance) FROM accounts; -- 正确结果为0.30DECIMAL精确计算 -- 若使用FLOAT类型结果可能是0.300000000000000042.3 溢出处理机制MySQL对数值溢出有特殊处理规则默认情况下超过字段范围的数值会被截断为最接近的合法值开启严格模式时(SQL_MODESTRICT_ALL_TABLES)会直接报错SET SESSION sql_mode STRICT_ALL_TABLES; INSERT INTO tiny_int_table VALUES (128); -- 报错Out of range3. 比较运算符深度剖析3.1 等号运算符的隐藏特性SELECT 0 abc; -- 结果为1true SELECT 10 10; -- 结果为1类型自动转换 SELECT NULL NULL; -- 结果为NULL不是13.2 安全等于运算符()专门处理NULL比较的特殊运算符SELECT NULL NULL; -- 结果为1true SELECT 1 1; -- 结果为1类型转换后相等3.3 BETWEEN的闭区间特性SELECT 10 BETWEEN 5 AND 10; -- 结果为1包含边界 -- 等效于 SELECT 10 5 AND 10 10;4. 逻辑运算符高级技巧4.1 短路求值机制MySQL会优化逻辑运算的执行顺序SELECT * FROM users WHERE 10 AND expensive_function(); -- expensive_function()不会被执行4.2 NULL值的逻辑运算三值逻辑是MySQL的难点SELECT NULL AND 1; -- 结果为NULL SELECT NULL OR 1; -- 结果为1 SELECT NOT NULL; -- 结果为NULL4.3 运算符优先级实战一个复杂案例演示SELECT * FROM products WHERE category electronics OR price 1000 AND stock 0; -- 实际执行顺序是price1000 AND stock0 先计算 -- 应该写成 WHERE (category electronics OR price 1000) AND stock 0;5. 特殊运算符应用场景5.1 IN运算符的优化建议-- 不好的写法 SELECT * FROM users WHERE id IN (SELECT user_id FROM blacklist); -- 好的写法MySQL 8.0 SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id u.id);5.2 LIKE的通配符优化-- 无法使用索引 SELECT * FROM products WHERE name LIKE %apple%; -- 可以使用前缀索引 SELECT * FROM products WHERE name LIKE apple%;5.3 REGEXP正则表达式强大的模式匹配工具SELECT MySQL REGEXP ^[A-Z][a-z]$; -- 结果为1 -- 匹配邮箱格式 SELECT email REGEXP ^[A-Z0-9._%-][A-Z0-9.-]\\.[A-Z]{2,4}$;6. 运算符性能优化实践6.1 避免隐式类型转换-- 坏的实践导致索引失效 SELECT * FROM users WHERE id 123; -- 好的实践 SELECT * FROM users WHERE id 123;6.2 使用覆盖索引优化OR-- 原始低效查询 SELECT * FROM orders WHERE status shipped OR total_amount 1000; -- 优化方案 SELECT * FROM orders WHERE status shipped UNION ALL SELECT * FROM orders WHERE total_amount 1000 AND status ! shipped;6.3 批量操作代替循环-- 低效做法应用程序中循环执行 UPDATE products SET stock stock - 1 WHERE id 1; UPDATE products SET stock stock - 1 WHERE id 2; -- 高效做法 UPDATE products SET stock stock - 1 WHERE id IN (1, 2);7. 运算符在高级特性中的应用7.1 存储过程中的运算符DELIMITER // CREATE PROCEDURE adjust_salary(IN emp_id INT) BEGIN UPDATE employees SET salary salary * CASE WHEN performance 90 THEN 1.2 WHEN performance 70 THEN 1.1 ELSE 1.05 END WHERE id emp_id; END // DELIMITER ;7.2 触发器中的运算符逻辑CREATE TRIGGER before_order_insert BEFORE INSERT ON orders FOR EACH ROW BEGIN IF NEW.amount 0 THEN SET NEW.amount 0; END IF; SET NEW.total_amount NEW.amount * (1 - NEW.discount); END;7.3 窗口函数中的运算符SELECT product_id, sales, sales - LAG(sales) OVER (ORDER BY month) AS growth FROM product_stats;8. 常见误区与调试技巧8.1 NULL值处理的黄金法则任何与NULL的比较运算返回NULLNULL参与算术运算结果也是NULL使用IS NULL/IS NOT NULL专门判断NULL聚合函数(SUM,AVG等)自动忽略NULL8.2 浮点数比较的正确方式-- 错误方式 SELECT * FROM measurements WHERE value 0.3; -- 正确方式 SELECT * FROM measurements WHERE ABS(value - 0.3) 0.0001;8.3 运算符优先级自查表当不确定运算顺序时可以使用这个快速验证方法SELECT 1 2 * 3; -- 结果为7乘优先 SELECT (1 2) * 3; -- 结果为9我在实际项目中总结的运算符调试技巧是对于复杂表达式先用SELECT单独测试运算符结果再整合到主查询中。曾经有一个生产环境bug花了3小时排查最后发现是因为没有理解NOT IN对NULL值的处理方式。记住当NOT IN子查询可能包含NULL时整个表达式会返回NULL而非TRUE/FALSE。