【MySQL】MySQL增删改查与常见陷阱（MySQL专栏启动）

2023-09-15 19:42

短信预约 -IT技能 免费直播动态提醒

📫作者简介：小明java问道之路，专注于研究 Java/ Liunx内核/ C++及汇编/计算机底层原理/源码，就职于大型金融公司后端高级工程师，擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。

📫 热衷分享，喜欢原创~ 关注我会给你带来一些不一样的认知和成长。

🏆 InfoQ签约作者、CSDN专家博主/后端领域优质创作者/内容合伙人、阿里云专家/签约博主、51CTO专家 🏆

🔥如果此文还不错的话，还请👍关注、点赞、收藏三连支持👍一下博主~

本文目录

本文导读

本文作为MySQL系列第二篇文章，详细讲解了MySQL的增删改查的语句、语义和一些我们经常在开发工作中暴露的问题，MySQL的增删改查又叫数据操作语句，本文有讲些了一些常用的数据操作语句，select语句后续将作为一篇完整的文章进行学习它的查询复杂场景语句、优化以及原理，最后通过一个生产问题介绍了mysql隐式类型的陷阱。

一、MySQL的增删改查

MySQL 中我们最常用的增删改查，对应SQL语句就是 insert 、delete、update、select，这种操作数据的语句，又叫Data Manipulation Statements（数据操作语句）。

一共是15种，分别是CALL、DELETE、DO、HANDLER、IMPORT TABLE、INSERT、LOAD DATA、LOAD XML、REPL ACE、SELECT、Subqueries、TABLE、UPDATE、VALUES、WITH。

1、insert语句

1.1 insert语句原理

insert 插入，下面给出插入数据行的通用语句，如果列表和 VALUES 列表都为空，则INSERT创建一行，每列设置为其默认值；

还可以使用 VALUES ROW() 语法的语句也可以插入多行。在这种情况下，每个值列表必须包含在ROW()（行构造函数）中，如下所示：

-- 插入语句模板INSERT INTO tbl_name () VALUES();-- 插入多行INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);INSERT INTO tbl_name (a,b,c) VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);

我们建表的时候经常会使用主键，当我们的系统执行并发落库的时候，为了避免主键冲突，经常会使用 ON DUPLICATE KEY UPDATE。

注意：ON DUPLICATE KEY UPDATE 是Mysql特有的语法，仅Mysql有效。作用：当执行insert操作时，有已经存在的记录，执行update操作。

如果使用了 ON DUPLICATE KEY UPDATE 子句，并且重复的键导致执行UPDATE，则该语句需要更新列的UPDATE权限。对于已读取但未修改的列，您只需要SELECT权限（因为无需更新，很好理解）。

INSERT INTO test ( id, NAME, age ) VALUES( 1, '张三', 13 ) ON DUPLICATE KEY UPDATE age = 13,

1.2 MySQL插入陷阱

如果未启用严格模式（严格 SQL 模式），MySQL 对任何没有显式定义默认值的列使用隐式默认值。如果启用了严格模式，如果任何列没有默认值，则会发生错误。（严格模式会在后续的文章中讲到）。

2、delete语句

2.1 delete语句原理

delete顾名思义是删除，该DELETE语句从中删除行 tbl_name并返回已删除的行数。要检查删除的行数我们一般写代码的时候使用 int 类型返回：

-- 删除语法DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name    [PARTITION (partition_name [, partition_name] ...)]    [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]-- WHERE 中的条件确定要删除哪些行，如果没有WHERE 子句则删除所有行-- 如果指定了ORDER BY子句，则按指定的顺序删除行-- LIMIT子句对可以删除的行数进行了限制-- 如果指定LOW_PRIORITY修饰符，服务器会延迟删除，DELETE直到没有其他客户端从表中读取-- QUICK是否合并索引进行删除操作，可能会导致索引中未回收的空间浪费-- IGNORE，MySQL在删除行的过程中忽略可忽略的错误

如果指定LOW_PRIORITY修饰符，服务器会延迟删除，DELETE直到没有其他客户端从表中读取。QUICK是否合并索引进行删除操作，可能会导致索引中未回收的空间浪费。IGNORE，MySQL在删除行的过程中忽略可忽略的错误。

WHERE 中的条件确定要删除哪些行，如果没有WHERE 子句则删除所有行，如果指定了ORDER BY子句，则按指定的顺序删除行，LIMIT子句对可以删除的行数进行了限制

2.2 MySQL删除陷阱

1、大批量删除

如果要从大表中删除许多行，则可能会超过InnoDB表的锁表大小。为了避免这个问题，或者仅仅为了最小化表保持锁定的时间，以下策略可能会有所帮助：

使用存储过程进行不影响业务的小批量、长时间删除，删除完毕后将存储过程从生产环境下线。

选择不删除的行，同步与原表结构相同的空表中：INSERT INTO t_copy SELECT * FROM t WHERE ... ;

用于 RENAMETABLE 以原子方式将原始表移开并将副本重命名为原始名称：RENAME TABLE t TO t_old, t_copy TO t;

2、多表删除

根据WHERE子句中的条件，可以在DELETE语句中指定多个表以从一个或多个表中删除行，但是不能在多表DELETE中使用ORDER BY或LIMIT。

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3    WHERE t1.id=t2.id AND t2.id=t3.id;

3、update语句原理

UPDATE是修改表中行的语句，返回实际更改的行数，要检查删除的行数我们一般写代码的时候使用 int 类型返回，对于单表语法，UPDATE语句使用新值更新命名表中现有行的列。

SET 要修改的列以及应该给出的值，每个值都可以作为表达式或关键字DEFAULT给出，以将列显式设置为其默认值。

WHERE 指定标识要更新哪些行的条件。如果没有WHERE子句，将更新所有行。如果指定了ORDER BY子句，则将按指定的顺序更新行。LIMIT子句限制了可以更新的行数。

-- 更新单表语法UPDATE [LOW_PRIORITY] [IGNORE] table_reference    SET assignment_list    [WHERE where_condition]    [ORDER BY ...]    [LIMIT row_count]-- 使用LOW_PRIORITY修饰符，UPDATE延迟执行，直到没有其他客户端从表中读取-- 使用IGNORE修饰符，即使更新期间发生错误，更新语句也不会中止UPDATE item_id, discounted SET items_info WHERE id = "";

4、select

SELECT用于检索从一个或多个表中选择的行，并且可以包括UNION操作和子查询。从MySQL 8.0.31开始，还支持INTERSECT和EXCEPT操作。后面笔者会单独拿出一篇文章讲解子查询、左连接、查询优化、查询原理等等。

后面更新后会附上连接

二、15种MySQL数据操作语句

类似于增删改查的语句我们在第一节已经学习，本小节主要讲解 CALL、DO、HANDLER、IMPORT TABLE、LOAD DATA、LOAD XML、REPL ACE、Subqueries、TABLE、VALUES、WITH，这11个语句的使用，后续会详细的进行详细分析，关注本专栏。

1、REPLACE语句

REPLACE的工作方式与INSERT完全相同，只是如果表中的一个旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前会删除旧行。在MySQL 8.0中已不支持DELAYED。

2、CALL语句

CALL语句调用先前使用CREATE procedure定义的存储过程。当过程返回时，客户端程序还可以获得例程内执行的最终语句所影响的行数。

3、TABLE语句

TABLE是MySQL 8.0.19中引入的DML语句，返回命名表的行和列。

4、WITH语句

WITH每个子子句提供一个子查询，该子查询生成一个结果集，并将名称与子查询相关联。

WITH  cte1 AS (SELECT a, b FROM table1),  cte2 AS (SELECT c, d FROM table2)SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;

三、MySQL查询陷阱

两个值进行查询，运算或者比较，首先要求数据类型必须一致。如果发现两个数据类型不一致时就会发生隐式类型转换。

问题描述：

分享一个笔者同事曾经发生的产线问题：在一次MySQL查询中，某字段为 varchar 字符串类型，传入参数值为 long 数字类型，发现查询的结果和预期的不一致。

select * from 表 where odr_id = "";select * from 表 where odr_id = long;

但是由于测试环境的数据量较少，并没有发现，只到上了生产环境，在进行大数据查询时，由于数据库的odr_id是 varchar 类型，查询条件是 long类型，所有每条查询出来的数据都会进行隐式类型转换的比较，直接导致long sql，处理办法是紧急版本上线。

隐式类型转换原理：

如果一个或两个参数均为NULL，则比较的结果为NULL，除了相等比较运算符。对于NULL NULL，结果为true；如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较；如果两个参数都是整数，则将它们作为整数进行比较。

如果不与数字比较，则将十六进制值视为二进制字符串；如果参数之一是 timestamp 或 datatime column，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳；如果参数之一是十进制值，则比较取决于另一个参数。

如果另一个参数是十进制或整数值，则将参数作为十进制值进行比较（这里如果生产环境是varchar后果将是灾难级的）；

如果另一个参数是浮点值，则将参数作为浮点值进行比较。；在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较。

通过隐式类型转换可以得出上述示例的结果：当查询中有数字时那么会将字符串转化成数字进行比较。所以当你的列为字符串时那么需要将列中字符串进行类型格式转换而进行字符格式转换之后则与索引不一致；当你的列为数字时查询等式为字符串时只是把查询的常量转成数字并不影响列的类型所以依然可以使用索引并没有破坏索引的类型。