2_1、mysql基础--DML-CSDN博客

本文链接：https://blog.csdn.net/maoxuemin/article/details/123516033

一、DML

DML（Data Manipulation Language）指数据操作语言，用来对数据表中的数据记录进行增/删/改操作，不包括数据查询

//插入数据语法
insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

//删除数据语法
delete from 表名 [where 条件];

//修改数据语法
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

//清空数据语法
truncate table  表名 或者 truncate 表名

二、批量插入数据

2.1 批量插入数据示例

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

//批量插入
INSERT INTO book(name,price)
VALUES
('a',1),
('b',2),
('c',3);

2.2 批量插入的数据量大小限制

1、mysql中每次批量插入或更新的数据量（查询、删除不受影响），会受max_allowed_packet 参数限制，默认是4M(4194304字节)。

//mysql中查看max_allowed_packet 值的语句
show variables like '%max_allowed_packet%';

2、当需要批量插入大量数据时，如何解决呢？

常见的方法是：将待插入的数据list，分成多个list，分批插入。

//
    /**
     * mybatis-plus中批量插入（分批插入）的源码
     *
     * @param entityList 待插入数据列表
     * @param batchSize 每次分批的list大小
     * @return ignore
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean saveBatch(Collection<T> entityList, int batchSize) {
        String sqlStatement = sqlStatement(SqlMethod.INSERT_ONE);
        try (SqlSession batchSqlSession = sqlSessionBatch()) {
            int i = 0;
            for (T anEntityList : entityList) {
                batchSqlSession.insert(sqlStatement, anEntityList);
                //此处将批量数据，分成多个批次
                if (i >= 1 && i % batchSize == 0) {
                    batchSqlSession.flushStatements();
                }
                i++;
            }
            //将小批次的批量数据插入
            batchSqlSession.flushStatements();
        }
        return true;
    }

2.3 批量插入时数据重复处理

在 mysql 中，当存在主键冲突或任意一个唯一键冲突的情况下，根据插入策略不同，一般有以下三种方法避免插入失败：

insert ignore into：若没有则插入，若存在则忽略
insert into … on duplicate key update：若没有则正常插入，若存在则更新
replace into：若没有则正常插入，若存在则先删除后插入

注意：

1、replace into 遇到已存在的记录，会先删除掉表中原有的记录后，再插入新的记录，如果该表的和其它表有关联，那么可能会导致关联数据丢失，所以一般不用replace into 方式

2、三种方法在数据重复未插入时，未插入数据也会造成AUTO_INCREMENT自增（AUTO_INCREMENT不连续问题），且这种不连续不会同步更新到 slave 的 AUTO_INCREMENT,当 master 被 kill，且 slave 升级为 master 时，就会出现主键冲突问题。【所以一般不用自增约束，采用分布式id来生成各表主键】

3、mybatis-plus目前没支持如上3中处理方式封装，可通过xml文件手写对应的sql或者扩展框架

2.3.1 批量插入示例

（1）新建数据表与初始化数据

CREATE TABLE `user_card` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `uid` int(10) DEFAULT '0' COMMENT '用户ID',
  `dep_id` int(10) DEFAULT '0' COMMENT '部门ID',
  `name` varchar(255) DEFAULT '' COMMENT '姓名',
  `money` decimal(10,2) DEFAULT '0.00' COMMENT '余额',
	`company_id` int(10) DEFAULT '0' COMMENT '公司ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid_did` (`uid`,`dep_id`),   
  UNIQUE KEY `uid_cid` (`uid`,`company_id`)
);


INSERT INTO user_card(uid,dep_id,name,money,company_id)
VALUES
(10000,10,'a',11,100),
(20000,20,'b',22,200),
(30000,30,'c',33,300);

（2）批量插入示例

//insert ignore into用法
INSERT IGNORE INTO user_card(uid,dep_id,name,money,company_id)
VALUES
(10000,10,'a',11,100),
(40000,40,'d',44,400);



//insert into ... on duplicate key update用法
//update money=values(money)只会修改money字段的值，其他字段的值不变
INSERT  INTO user_card(uid,dep_id,money,company_id)
VALUES
(10000,10,11.11,111),
(40000,40,44,400)
on duplicate key update money=values(money);



//insert into ... on duplicate key update用法
//update 中使用values关键字的才会更新，不使用values关键字会不出现在update中的字段值不会更新
INSERT  INTO user_card(uid,dep_id,money,company_id)
VALUES
(10000,10,11.11,111),
(40000,40,44,400)
on duplicate key update dep_id=dep_id,money=values(money);     //dep_id与其他字段值不会更新

2.3.2 AUTO_INCREMENT不连续原因–自增锁模式

insert语句常常涉及自增列的加锁过程，会涉及到AUTO-INC Locks加锁过程。因此我们了解下自增锁及模式。

AUTO-INC锁是一种特殊的表级锁，innodb_autoinc_lock_mode有3种配置模式：0、1、2，分别对应”传统模式”, “连续模式”（默认值）, “交错模式”：

mysql> show variables like  'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+

传统模式：涉及auto-increment列的插入语句加的表级AUTO-INC锁，只有插入执行结束后才会释放锁。
连续模式：事先确定插入行数(包括单行和多行插入)，分配连续的确定的auto-increment值。这种模式下，事务回滚，auto-increment值不会回滚，换句话说，自增列内容会不连续。（当行数不能确定时仍加表锁）
交错模式：同一时刻多条SQL语句产生交错的auto-increment值。