一、批量修改等

​ 数据库连接地址后记得加上**&allowMultiQueries=true**不然会报错

批量插入

<insert id="batchInsertTbProject">
        insert into
        tb_project(
        id,
        name,
        type,
        easy_id,
        parent_id
        )
        values
        <foreach collection="batchInsertTbProjectList" item="insertTbProject" separator="," close=";">
            (
            #{insertTbProject.id},
            #{insertTbProject.name},
            #{insertTbProject.type},
            #{insertTbProject.easyId},
            #{insertTbProject.parentId}
            )
        </foreach>
    </insert>

批量修改

<update id="batchUpdateTbProject">
        <foreach collection="batchUpdateTbProjectList" item="batchUpdateTbProject" index="index" open="" close=""
                 separator=";">
            update tb_project
            <set>
                <if test="batchUpdateTbProject.name != null">
                    name = #{batchUpdateTbProject.name},
                </if>
                <if test="batchUpdateTbProject.type != null">
                    type = #{batchUpdateTbProject.type},
                </if>
                <if test="batchUpdateTbProject.easyId != null">
                    easy_id = #{batchUpdateTbProject.easyId},
                </if>
                <if test="batchUpdateTbProject.parentId != null">
                    parent_id = #{batchUpdateTbProject.parentId},
                </if>
            </set>
            where id = #{batchUpdateTbProject.id}
        </foreach>
    </update>

批量删除

<delete id="batchDeleteTbSyncBuild" parameterType="java.util.List">
        DELETE FROM tb_sync_build WHERE build_id IN
        <foreach item="item" collection="batchDeleteTbSyncBuildList" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

<delete id="deleteBatchRelation">
        DELETE FROM pms_attr_attrgroup_relation WHERE
        <foreach collection="relationEntities" item="item" separator=" OR ">
            (attr_id=#{item.attrId} AND attr_group_id=#{item.attrGroupId})
        </foreach>
    </delete>

二、MyBatis分页查询

public Msg getHourseName(TbSynHouseQuery query) {
        String projectId = query.getProjectId();
        QueryprojectdataPo queryprojectdataPo = 			         queryProjectDateReadMapper.queryById(projectId);
        if (queryprojectdataPo ==null) {
            return Msg.error().message("projectId不存在！！");
        }
        projectId = queryprojectdataPo.getEasyid();
        query.setProjectId(projectId);

        // 分页，传入页码以及每页大小
        PageHelper.startPage(query.getPage(),query.getSize());
    	// 房屋信息
        List<TbSynHouseVo> hoursePoList = tbSyncHourseReadMapper.queryHouses(query); 

        // 使用PageInfo包装查询后的结果
         com.github.pagehelper.PageInfo page = new com.github.pagehelper.PageInfo(hoursePoList);

        List<TbSynHouseVo> houses = page.getList();

        Map<String,Object> map = new HashMap<>();
        long total = page.getTotal();// 总记录数
        int pageNum = page.getPageNum();// 当前页
        int pageSize = page.getPageSize();// 每页大小
        int currentSize = page.getSize();// 当前页大小
        map.put("total",total);
        map.put("pageNum",pageNum);
        map.put("pageSize",pageSize);
        map.put("currentSize",currentSize);
        map.put("houses",houses);

        return Msg.ok().add(map);

    }

注意： // 分页，传入页码以及每页大小 PageHelper.startPage(query.getPage(),query.getSize()); 执行完这句后后面需要紧跟查询语句否则分页不会起效

三、SQL知识点

①SQL删除重复数据

-- 查询出所有重复数据的唯一id（不包括最小的id）
SELECT id,name
FROM stu s
WHERE (s.name) in (SELECT name FROM stu GROUP BY name HAVING count(*) > 1)
			AND id not in (SELECT min(id) FROM stu GROUP BY name HAVING count(*) > 1);
			
-- 删除重复数据
DELETE
FROM stu
WHERE 
		1=1
		AND name in (SELECT * FROM (SELECT name FROM stu GROUP BY name HAVING count(*) > 1) as s1)
		AND id not in (SELECT * FROM (SELECT min(id) id FROM stu GROUP BY name HAVING count(*) > 1) as s2);
		
-- 优化版
SELECT id,name
FROM stu s
WHERE	
		EXISTS
		(SELECT s1.id FROM stu s1 JOIN (SELECT min(id) id,name FROM stu GROUP BY name HAVING count(*)>1) as s2
		WHERE s1.name = s2.name AND s1.id <> s2.id AND s.id = s1.id)
		
DELETE s
FROM stu s
WHERE	
	EXISTS(SELECT s1.id FROM stu s1 JOIN (SELECT min(id) id,name FROM stu GROUP BY name HAVING count(*)>1) as s2
		WHERE s1.name = s2.name AND s1.id <> s2.id and s.id = s1.id)	

DELETE 
FROM stu
WHERE	
	id in (SELECT * FROM (SELECT s1.id FROM stu s1 JOIN (SELECT min(id) id,name FROM stu GROUP BY name HAVING count(*)>1) as s2
		WHERE s1.name = s2.name AND s1.id <> s2.id) a)
		

②MySQL提示1040 TooMany Connections

##当前修改只在当前进程有效重启mysql服务后失效。如需永久修改这需要修改配置文件
#重启mysql服务器
1.service mysqld restart
2.mysql -u root -p 回车输入密码进入mysql
#查看最大连接数
3.show variables like "max_connections";
#修改最大连接数
4.set GLOBAL max_connections=1000; 
5.退出mysql服务，exit