第 5 章 DML (Data Manipulation Language)

最新推荐文章于 2025-09-06 09:33:29 发布

weixin_33782386

最新推荐文章于 2025-09-06 09:33:29 发布

阅读量67

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 java

原文链接：https://yq.aliyun.com/articles/337564

本文详细介绍了MySQL中INSERT语句的各种用法，包括INSERT INTO...SELECT用于从一个表复制数据到另一个表，INSERT IGNORE用于避免因重复数据而产生的错误，以及INSERT...ON DUPLICATE KEY UPDATE用于更新已存在记录的方法。

5.1. INSERT

5.1.1. INSERT INTO ... SELECT 5.1.2. INSERT IGNORE 5.1.3. INSERT...ON DUPLICATE KEY UPDATE

5.2. REPLACE 5.3. DELETE

5.3.1. 删除重复数据

SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency

5.1. INSERT

5.1.1. INSERT INTO ... SELECT

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `members_mobile_insert` BEFORE INSERT ON `members_mobile` FOR EACH ROW BEGIN
	insert into members_location(id,province,city) select NEW.id,mobile_location.province,mobile_location.city from  mobile_location where mobile_location.id = md5(LEFT(NEW.number, 7));
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

5.1.2. INSERT IGNORE

INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

insert ignore into table(name)  select  name from table2

5.1.3. INSERT...ON DUPLICATE KEY UPDATE

			
create table foo (id serial primary key, u int, unique key (u));

insert into foo (u) values (10);
insert into foo (u) values (10) on duplicate key update u = 20;

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

			
DROP TRIGGER IF EXISTS `cms`.`jc_content_BEFORE_DELETE`;

DELIMITER $$
USE `cms`$$
CREATE DEFINER=`5kwords`@`%` TRIGGER `jc_content_BEFORE_DELETE` BEFORE DELETE ON `jc_content`
FOR EACH ROW BEGIN

    insert into `cms`.elasticsearch_trash(id) values(OLD.content_id) on duplicate key update ctime = now();
    insert into `cms`.trash(id,`type`, site_id) values(OLD.content_id, "delete", OLD.site_id) on duplicate key update `type`="delete", ctime = now();

END$$
DELIMITER ;

原文出处：Netkiller 系列手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。