MySQL の追加、削除、変更、およびよくある落とし穴の詳細な説明

この記事では、mysql に関する関連知識を提供します。主に、追加、削除、変更、確認、およびよくある落とし穴に関する関連コンテンツを紹介します。一緒に見てみましょう。お役に立てば幸いです。みんなに。

# 推奨学習:

mysql ビデオ チュートリアル

##1. MySQL の追加、削除、変更、クエリ

MySQL で最も一般的に使用される追加、削除、変更、およびクエリは、SQL ステートメントの挿入、削除、更新、および選択に対応しており、データを操作するこれらのステートメントはデータ操作ステートメントとも呼ばれます。

CALL、DELETE、DO、HANDLER、IMPORT TABLE、INSERT、LOAD DATA、LOAD XML、REPL ACE、SELECT、Subqueries、TABLE、UPDATE、VALUES、WITH の合計 15 種類があります。

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 でのみ有効です。 機能: 挿入操作を実行する際、既存のレコードが存在する場合は更新操作を実行します。 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. 削除ステートメント

2.1 削除ステートメントの原則delete (名前の通り) DELETE ステートメントは、tbl_name から行を削除し、削除された行の数を返します。削除された行の数を確認するには、通常、コードを記述するときに int 型の return を使用します。

-- 删除语法
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 修飾子が指定されている場合、サーバーは、他のクライアントがテーブルから読み取らなくなるまで削除を遅らせます。 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、DELETE ステートメントで複数のテーブルを指定して、WHERE 句の条件に基づいて 1 つ以上のテーブルから行を削除できますが、複数テーブルの 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 は、テーブル内の行を変更し、実際に変更された行数を返すステートメントです。単一テーブル構文の場合、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视频教程

以上がMySQL の追加、削除、変更、およびよくある落とし穴の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。