Detaillierte Erläuterung des Hinzufügens, Löschens, Änderns von MySQL und häufiger Fallstricke

Dieser Artikel bringt Ihnen relevantes Wissen über MySQL, das hauptsächlich die relevanten Inhalte zum Hinzufügen, Löschen, Ändern und häufigen Fallstricken vorstellt. Ich hoffe, es wird für alle hilfreich sein.

Empfohlenes Lernen: MySQL-Video-Tutorial

1. MySQL-Abfrage zum Hinzufügen, Löschen und Ändern

Unsere am häufigsten verwendete Abfrage zum Hinzufügen, Löschen und Ändern in MySQL. Die entsprechenden SQL-Anweisungen sind Einfügen, Löschen, aktualisieren, auswählen, diese Art Anweisungen, die Daten manipulieren, werden auch Datenmanipulationsanweisungen genannt.

Es gibt insgesamt 15 Typen, nämlich CALL, DELETE, DO, HANDLER, IMPORT TABLE, INSERT, LOAD DATA, LOAD XML, REPL ACE, SELECT, Subqueries, TABLE, UPDATE, VALUES, WITH.

1. Insert-Anweisung

1.1 Prinzip der Insert-Anweisung

Insert: Wenn sowohl die Liste als auch die VALUES-Liste leer sind, verwenden Sie INSERT erstellt eine Zeile, jede Spalte wird auf ihren Standardwert gesetzt.

Sie können auch die VALUES ROW()-Syntaxanweisung verwenden, um mehrere Zeilen einzufügen. In diesem Fall muss jede Werteliste in ROW() (Zeilenkonstruktor) enthalten sein, wie unten gezeigt:

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

Wir verwenden häufig Primärschlüssel beim Erstellen von Tabellen und wenn unser System gleichzeitiges Datenbanklöschen durchführt, um Primärschlüssel zu vermeiden Bei Konflikten wird häufig ON DUPLICATE KEY UPDATE verwendet.

Hinweis: ON DUPLICATE KEY UPDATE ist eine MySQL-spezifische Syntax und gilt nur für MySQL. Funktion: Wenn beim Ausführen des Einfügevorgangs ein Datensatz vorhanden ist, führen Sie den Aktualisierungsvorgang aus.

Wenn die ON DUPLICATE KEY UPDATE-Klausel verwendet wird und der doppelte Schlüssel die Ausführung eines UPDATE bewirkt, erfordert die Anweisung die UPDATE-Berechtigung, um die Spalte zu aktualisieren. Für gelesene, aber nicht geänderte Spalten benötigen Sie nur die SELECT-Berechtigung (da keine Aktualisierung erforderlich ist, was leicht zu verstehen ist).

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

1.2 MySQL Insertion Trap

Wenn der strikte Modus (strikter SQL-Modus) nicht aktiviert ist, verwendet MySQL implizite Standardwerte für jede Spalte, die keinen explizit definierten Standardwert hat. Wenn der strikte Modus aktiviert ist, tritt ein Fehler auf, wenn eine Spalte keinen Standardwert hat. (Der strenge Modus wird in den folgenden Artikeln besprochen).

2. Löschanweisung

2.1 Prinzip der Löschanweisung

delete löscht, wie der Name schon sagt, die Zeile aus tbl_name und gibt die Anzahl der gelöschten Zeilen zurück. Um die Anzahl der gelöschten Zeilen zu überprüfen, verwenden wir beim Schreiben von Code normalerweise die Rückgabe vom Typ 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在删除行的过程中忽略可忽略的错误

Wenn der Modifikator LOW_PRIORITY angegeben ist, verzögert der Server das Löschen, bis kein anderer Client mehr aus der Tabelle liest. Ob QUICK Indizes für Löschvorgänge zusammenführt, kann zu einer Verschwendung von nicht freigegebenem Speicherplatz im Index führen. IGNORE, MySQL ignoriert vernachlässigbare Fehler beim Löschen von Zeilen.

Die Bedingungen in WHERE bestimmen, welche Zeilen gelöscht werden sollen. Wenn die ORDER BY-Klausel angegeben ist, wird die Anzahl der Zeilen begrenzt die gelöscht werden können.

2.2 MySQL-Löschfallen

1. Massenlöschung

Wenn Sie viele Zeilen aus einer großen Tabelle löschen möchten, kann die Sperrtabellengröße der InnoDB-Tabelle überschritten werden. Um dieses Problem zu vermeiden oder einfach die Zeit zu minimieren, die die Tabelle gesperrt bleibt, können die folgenden Strategien hilfreich sein:

1. Verwenden Sie gespeicherte Prozeduren, um kleine, langfristige Löschvorgänge durchzuführen, die sich nicht auf das Geschäft auswirken Wenn der Vorgang abgeschlossen ist, entfernen Sie die gespeicherten Prozeduren aus Die Produktionsumgebung ist offline.

2. Wählen Sie Zeilen aus, die nicht gelöscht werden, und synchronisieren Sie die leere Tabelle mit derselben Struktur wie die Originaltabelle: INSERT INTO t_copy SELECT * FROM t WHERE ... ;

3 Verwenden Sie RENAMETABLE, um die Originaltabelle atomar zu verschieben und umzubenennen die Kopie auf den ursprünglichen Namen: RENAME TABLE t TO t_old, t_copy TO t; Löschen Sie Daten aus einer oder löschen Sie Zeilen in mehreren Tabellen, aber ORDER BY oder LIMIT können nicht in DELETE für mehrere Tabellen verwendet werden.

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

3. Prinzip der Update-Anweisung

UPDATE ist eine Anweisung, die Zeilen in einer Tabelle ändert und die tatsächliche Anzahl der geänderten Zeilen zurückgibt. Um die Anzahl der gelöschten Zeilen zu überprüfen, verwenden wir beim Schreiben von Code normalerweise den Typ „int“. Bei der Syntax einer einzelnen Tabelle aktualisiert die UPDATE-Anweisung eine Spalte einer vorhandenen Zeile in der benannten Tabelle mit einem neuen Wert.

SET Die zu ändernde Spalte und der Wert, der angegeben werden soll. Jeder Wert kann als Ausdruck oder Schlüsselwort DEFAULT angegeben werden, um die Spalte explizit auf ihren Standardwert zu setzen. WHERE gibt eine Bedingung an, die angibt, welche Zeilen aktualisiert werden sollen. Ohne eine WHERE-Klausel werden alle Zeilen aktualisiert. Wenn eine ORDER BY-Klausel angegeben ist, werden die Zeilen in der angegebenen Reihenfolge aktualisiert. Die LIMIT-Klausel begrenzt die Anzahl der Zeilen, die aktualisiert werden können.

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

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des Hinzufügens, Löschens, Änderns von MySQL und häufiger Fallstricke. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!