共计 4380 个字符，预计需要花费 11 分钟才能阅读完成。
本文丸趣 TV 小编为大家详细介绍“MySQL 增删改查方法与常见陷阱有哪些”，内容详细，步骤清晰，细节处理妥当，希望这篇“MySQL 增删改查方法与常见陷阱有哪些”文章能帮助大家解决疑惑，下面跟着丸趣 TV 小编的思路慢慢深入，一起来学习新知识吧。
一、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 表的锁表大小。为了避免这个问题，或者仅仅为了最小化表保持锁定的时间，以下策略可能会有所帮助：
1、使用存储过程进行不影响业务的小批量、长时间删除，删除完毕后将存储过程从生产环境下线。
2、选择不删除的行，同步与原表结构相同的空表中：INSERT INTO t_copy SELECT * FROM t WHERE … ;
3、用于 RENAMETABLE 以原子方式将原始表移开并将副本重命名为原始名称：RENAME TABLE t TO t_old, t_copy TO t;
2、多表删除
1、根据 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 后果将是灾难级的）；
如果另一个参数是浮点值，则将参数作为浮点值进行比较。；在所有其他情况下，将参数作为浮点数（实数）进行比较。例如，将字符串和数字操作数进行比较，将其作为浮点数的比较。
通过隐式类型转换可以得出上述示例的结果：当查询中有数字时那么会将字符串转化成数字进行比较。所以当你的列为字符串时那么需要将列中字符串进行类型格式转换而进行字符格式转换之后则与索引不一致；当你的列为数字时查询等式为字符串时只是把查询的常量转成数字并不影响列的类型所以依然可以使用索引并没有破坏索引的类型。
读到这里，这篇“MySQL 增删改查方法与常见陷阱有哪些”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注丸趣 TV 行业资讯频道。
