MySQL 추가, 삭제, 수정 및 일반적인 함정에 대한 자세한 설명

이 글은 mysql에 대한 관련 지식을 제공하며, 추가, 삭제, 수정 및 일반적인 함정에 대한 관련 내용을 주로 소개합니다. 모두에게 도움이 되기를 바랍니다.

추천 학습: mysql 비디오 튜토리얼

1. MySQL 추가, 삭제, 수정 쿼리

MySQL에서 가장 일반적으로 사용되는 추가, 삭제, 수정 쿼리에 해당하는 SQL 문은 insert, delete, 업데이트, 선택, 이런 종류의 데이터를 조작하는 명령문을 데이터 조작 명령문이라고도 합니다.

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 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가 자주 사용됩니다.

Note: 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 삽입 트랩

엄격 모드(strict SQL 모드)가 활성화되지 않은 경우 MySQL은 명시적으로 정의된 기본값이 없는 모든 열에 대해 암시적 기본값을 사용합니다. 엄격 모드가 활성화된 경우 기본값이 없는 열이 있으면 오류가 발생합니다. (엄격 모드는 다음 글에서 다루겠습니다)

2. 삭제문

2.1 삭제문의 원리

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 수정자가 지정되면 서버는 다른 클라이언트가 테이블에서 읽지 않을 때까지 삭제를 지연합니다. 삭제 작업을 위해 QUICK가 인덱스를 병합하면 인덱스에서 회수되지 않은 공간이 낭비될 수 있습니다. IGNORE, MySQL은 행 삭제 중 무시할 수 있는 오류를 무시합니다.

WHERE 조건에 따라 삭제할 행이 결정됩니다. WHERE 절이 없으면 모든 행이 삭제됩니다. ORDER BY 절이 지정되면 행이 지정된 순서대로 삭제됩니다.

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; 다중 테이블 삭제

1 WHERE 절의 조건에 따라 DELETE 문에 여러 테이블을 지정할 수 있습니다. 하나의 테이블에서 데이터를 삭제하거나 여러 테이블의 행을 삭제하지만 ORDER BY 또는 LIMIT는 다중 테이블 DELETE에 사용할 수 없습니다.

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형 return을 사용합니다. 단일 테이블 구문의 경우 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 중국어 웹사이트의 기타 관련 기사를 참조하세요!