Detailed explanation of MySQL addition, deletion, modification and common pitfalls

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about adding, deleting, modifying and checking and common pitfalls. Let’s take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

##1. MySQL add, delete, modify and query

The most commonly used additions, deletions, modifications, and queries in MySQL correspond to the SQL statements insert, delete, update, and select. These statements for operating data are also called Data Manipulation Statements.

There are 15 types in total, namely CALL, DELETE, DO, HANDLER, IMPORT TABLE, INSERT, LOAD DATA, LOAD XML, REPL ACE, SELECT, Subqueries, TABLE, UPDATE, VALUES, WITH.

1. Insert statement

1.1 Principle of insert statement

insert Insert, the general statement for inserting data rows is given below. If the list and VALUES list are both empty, INSERT creates a row and each column is set to its default value;

can also be used The VALUES ROW() syntax statement can also insert multiple rows. In this case, each value list must be included in ROW() (row constructor), as shown below:

-- 插入语句模板
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);

We often use primary keys when building tables. When our system performs concurrency When dropping the database, in order to avoid primary key conflicts, ON DUPLICATE KEY UPDATE is often used.

Note: ON DUPLICATE KEY UPDATE is a syntax unique to Mysql and is only valid for Mysql. Function: When performing the insert operation, if there is an existing record, perform the update operation.

If the ON DUPLICATE KEY UPDATE clause is used and the duplicate key causes an UPDATE to be performed, the statement requires UPDATE permission to update the column. For columns that have been read but not modified, you only need SELECT permission (because no update is required, which is easy to understand).

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

1.2 MySQL insertion trap

If strict mode (strict SQL mode) is not enabled, MySQL uses implicit default values ​​for any columns that do not have an explicitly defined default value . If strict mode is enabled, an error will occur if any column does not have a default value. (Strict mode will be discussed in subsequent articles).

2. Delete statement

2.1 Principle of delete statement

delete, as the name suggests, means to delete. DELETE statement deletes the row from tbl_name and returns the number of deleted rows. To check the number of deleted rows, we usually use int type return when writing code:

-- 删除语法
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在删除行的过程中忽略可忽略的错误

If the LOW_PRIORITY modifier is specified, the server will delay deletion until no other client reads from the table. Whether QUICK merges indexes for deletion operations may result in a waste of unreclaimed space in the index. IGNORE, MySQL ignores ignorable errors during deletion of rows.

The conditions in WHERE determine which rows are to be deleted. If there is no WHERE clause, all rows are deleted. If the ORDER BY clause is specified, rows are deleted in the specified order. The LIMIT clause determines the rows that can be deleted. The number is limited

2.2 MySQL deletion trap

1. Mass deletion

If To delete many rows from a large table, the lock table size of the InnoDB table may be exceeded. To avoid this problem, or simply to minimize the time the table remains locked, the following strategies may be helpful:

1. Use stored procedures to perform small batch, long-term deletions that do not affect the business. After the deletion is completed, Take the stored procedure offline from the production environment.

2. Select rows that are not deleted and synchronize them into an empty table with the same structure as the original table: INSERT INTO t_copy SELECT * FROM t WHERE ... ;

3. Used for RENAMETABLE with atoms Method to move the original table and rename the copy to the original name: RENAME TABLE t TO t_old, t_copy TO t;

2, multiple table deletion

1, You can specify multiple tables in a DELETE statement to delete rows from one or more tables based on conditions in the WHERE clause, but you cannot use ORDER BY or LIMIT in a multi-table DELETE.

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

3. Principle of update statement

UPDATE is a statement that modifies rows in a table and returns the actual number of changed rows. To check the number of deleted rows, we usually write code When using an int type return, for single-table syntax, the UPDATE statement updates a column of an existing row in the named table with a new value.

SET The column to be modified and the value that should be given. Each value can be given as an expression or the keyword DEFAULT to explicitly set the column to its default value.

WHERE Specifies a condition that identifies which rows are to be updated. Without a WHERE clause, all rows will be updated. If an ORDER BY clause is specified, rows will be updated in the order specified. The LIMIT clause limits the number of rows that can be updated.

-- 更新单表语法
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视频教程

The above is the detailed content of Detailed explanation of MySQL addition, deletion, modification and common pitfalls. For more information, please follow other related articles on the PHP Chinese website!