Explication détaillée de l'ajout, de la suppression, de la modification de MySQL et des pièges courants

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement le contenu pertinent sur l'ajout, la suppression, la modification et les pièges courants. Examinons-le ensemble, j'espère qu'il sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

1. Requête d'ajout, de suppression et de modification MySQL

Notre requête d'ajout, de suppression et de modification la plus couramment utilisée dans MySQL, les instructions SQL correspondantes sont insérer, supprimer, update, select, ce type Les instructions qui manipulent des données sont également appelées instructions de manipulation de données.

Il existe 15 types au total, à savoir CALL, DELETE, DO, HANDLER, IMPORT TABLE, INSERT, LOAD DATA, LOAD XML, REPL ACE, SELECT, Subrequeries, TABLE, UPDATE, VALUES, WITH.

1. Insérer une instruction

1.1 Principe de l'instruction d'insertion

insert Insert, ce qui suit est une instruction générale pour insérer des lignes de données Si la liste et la liste VALUES sont toutes deux vides, INSERT. crée une ligne. , chaque colonne est définie sur sa valeur par défaut ;

Vous pouvez également utiliser l'instruction de syntaxe VALUES ROW() pour insérer plusieurs lignes. Dans ce cas, chaque liste de valeurs doit être incluse dans ROW() (constructeur de lignes), comme indiqué ci-dessous :

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

Nous utilisons souvent des clés primaires lors de la construction de tables et lorsque notre système effectue une suppression simultanée de la base de données, afin d'éviter la clé primaire. conflits, ON DUPLICATE KEY UPDATE est souvent utilisé.

Remarque : ON DUPLICATE KEY UPDATE est une syntaxe spécifique à Mysql et n'est valable que pour Mysql. Fonction : Lors de l'opération d'insertion, s'il existe un enregistrement existant, effectuez l'opération de mise à jour.

Si la clause ON DUPLICATE KEY UPDATE est utilisée et que la clé en double entraîne l'exécution d'une UPDATE, l'instruction nécessite l'autorisation UPDATE pour mettre à jour la colonne. Pour les colonnes qui ont été lues mais non modifiées, vous n'avez besoin que de l'autorisation SELECT (car aucune mise à jour n'est requise, ce qui est facile à comprendre).

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

1.2 MySQL Insertion Trap

Si le mode strict (mode SQL strict) n'est pas activé, MySQL utilise des valeurs par défaut implicites pour toute colonne qui n'a pas de valeur par défaut explicitement définie. Si le mode strict est activé, une erreur se produira si une colonne n'a pas de valeur par défaut. (Le mode strict sera abordé dans les articles suivants).

2. Instruction de suppression

2.1 Principe de l'instruction de suppression

delete, comme son nom l'indique, supprime la ligne de tbl_name et renvoie le nombre de lignes supprimées. Pour vérifier le nombre de lignes supprimées, nous utilisons généralement le retour de type int lors de l'écriture du 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在删除行的过程中忽略可忽略的错误

Si le modificateur LOW_PRIORITY est spécifié, le serveur retardera la suppression jusqu'à ce qu'aucun autre client ne lise la table. Le fait que QUICK fusionne les index pour les opérations de suppression peut entraîner une perte d'espace non récupéré dans l'index. IGNORE, MySQL ignore les erreurs ignorables lors de la suppression de lignes.

Les conditions dans WHERE déterminent quelles lignes doivent être supprimées. S'il n'y a pas de clause WHERE, toutes les lignes sont supprimées. Si la clause ORDER BY est spécifiée, les lignes sont supprimées dans l'ordre spécifié. qui peut être supprimé.

2.2 Pièges de suppression MySQL

1. Suppression par lots volumineux

Si vous souhaitez supprimer plusieurs lignes d'une grande table, la taille de la table de verrouillage de la table InnoDB peut être dépassée. Pour éviter ce problème, ou simplement pour minimiser la durée pendant laquelle la table reste verrouillée, les stratégies suivantes peuvent être utiles :

1. Utilisez des procédures stockées pour effectuer des suppressions par petits lots et à long terme qui n'affectent pas l'entreprise. terminé, supprimez les procédures stockées de L'environnement de production est hors ligne.

2. Sélectionnez les lignes qui ne sont pas supprimées et synchronisez la table vide avec la même structure que la table d'origine : INSERT INTO t_copy SELECT * FROM t WHERE ... ;

3. Utilisez RENAMETABLE pour déplacer atomiquement la table d'origine et Renommer. la copie au nom d'origine : RENAME TABLE t TO t_old, t_copy TO t 

2 Suppression de plusieurs tables

1 Selon les conditions de la clause WHERE, plusieurs tables peuvent être spécifiées dans l'instruction DELETE pour. supprimez les données d'une ou supprimez les lignes de plusieurs tables, mais ORDER BY ou LIMIT ne peuvent pas être utilisés dans DELETE multi-tables.

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

3. Principe de l'instruction update

UPDATE est une instruction qui modifie les lignes d'un tableau et renvoie le nombre réel de lignes modifiées. Pour vérifier le nombre de lignes supprimées, nous utilisons généralement le retour de type int lors de l'écriture du code. Pour la syntaxe de table unique, UPDATE L'instruction met à jour une colonne d'une ligne existante dans la table nommée avec une nouvelle valeur.

SET La colonne à modifier et la valeur qui doit être donnée, chaque valeur peut être donnée sous forme d'expression ou de mot-clé DEFAULT pour définir explicitement la colonne à sa valeur par défaut.

WHERE spécifie une condition qui identifie les lignes à mettre à jour. Sans clause WHERE, toutes les lignes seront mises à jour. Si une clause ORDER BY est spécifiée, les lignes seront mises à jour dans l'ordre spécifié. La clause LIMIT limite le nombre de lignes pouvant être mises à jour.

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!