对这两天MySQL的一些总结

回归开始做Java，因为以前都是一些小项目，而且表结构都不是很复杂，所以也没什么好说的，但是现在开始专职做Java之后，现在MySQL还只是一个新手，开一个博客专门整理自己的一些问题，这样以后查看自己的blog就可以了。

1.关于left join

因为要做多表关联，但是在网上找的demo都太简单了，只能自己用命令一点一点试验。。过程很纠结。。现在有一个表和四个表的主键有关联，就根据以前同事写的一些代码，和网上的一些讲解写了一个

SELECT
	a.*, b.create_time,
	b.frequency,
	c.sourceDb as sourceDb,
	d.destDb as destDb,
	e.sourceTable as sourceTable,
	f.destTable as destTable,
	g.sourceField as sourceField,
	h.destField as destField
FROM
	a
RIGHT JOIN(
	SELECT
		um.frequency,
		um.create_time,
		um.model_id
	FROM
		um
	WHERE
		um.is_display = 1
	AND um.user_id = #{user_id}
)b ON b.model_id = a.model_id
LEFT JOIN(
	SELECT
		db.db_name AS sourceDb,
		db.db_id
	FROM
		db
)c ON c.db_id = a.source_db_id
LEFT JOIN(
	SELECT
		db.db_name AS destDb,
		db.db_id
	FROM
		db
)d ON d.db_id = a.dest_db_id
LEFT JOIN(
	SELECT
		db.table_name AS sourceTable,
		db.table_id
	FROM
		db
)e ON e.table_id = a.source_table_id
LEFT JOIN(
	SELECT
		db.table_name AS destTable,
		db.table_id
	FROM
		db
)f ON f.table_id = a.dest_table_id
LEFT JOIN(
	SELECT
		df.col_name AS sourceField,
		df.col_id
	FROM
		df
)g ON g.col_id = a.source_field_id
LEFT JOIN(
	SELECT
		df.col_name AS destField,
		df.col_id
	FROM
		df
)h ON h.col_id = a.dest_field_id

其中B表是管理A表的。。B表是用户-模型表，A表是模型表，其他的表都是关联的A表的一些字段。但是查询出来之后发现，如果B表存在脏数据，那么right join就会出现问题，就会把脏数据也显示出来。。因为right join就是说以B表的数据条目为依据查询。就很纠结，所以准备放飞思想，按照自己理解的方式写一个SQL，因为本人没有钻研过SQL，所以确实是根据经验来推测的。。。下面是修改的SQL

SELECT
	a.*, b.create_time,
	b.frequency,
	c.sourceDb,
	d.destDb,
	e.sourceTable,
	f.destTable,
	g.sourceField,
	h.destField
FROM
	 b,
	a
LEFT JOIN(
	SELECT
		db.db_name AS sourceDb,
		db.db_id
	FROM
		 db
)c ON c.db_id = a.source_db_id
LEFT JOIN(
	SELECT
		db.db_name AS destDb,
		db.db_id
	FROM
		 db
)d ON d.db_id = a.dest_db_id
LEFT JOIN(
	SELECT
		db.table_name AS sourceTable,
		db.table_id
	FROM
		 db
)e ON e.table_id = a.source_table_id
LEFT JOIN(
	SELECT
		db.table_name AS destTable,
		db.table_id
	FROM
		 db
)f ON f.table_id = a.dest_table_id
LEFT JOIN(
	SELECT
		df.col_name AS sourceField,
		df.col_id
	FROM
		 df
)g ON g.col_id = a.source_field_id
LEFT JOIN(
	SELECT
		df.col_name AS destField,
		df.col_id
	FROM
		 df
)h ON h.col_id = a.dest_field_id
WHERE
	b.is_display = 1
AND b.user_id = #{user_id}
AND a.model_id = b.model_id

这样写发现可以执行，，我就认真分析了一下，这样是先查询出来A,B然后根据查询出来的数据去关联left join 。。

2 批量更新

说道批量更新就很头疼。。因为查了很多天，，在一个文章里面竟然还有说MySQL里面可以在MySQL里面直接执行foreach update语句。。。例如

<foreach collection="list" index="index" item="item" separator=";">
update df set comment =#{item.comment}
</foreach>

这样写会报错的。。大概意思是不支持。

后台就用case when ，大概就是这样

         update df
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="source_col_type =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    when id=#{id} then #{item.sourceColType}
                </foreach>
            </trim>
        </trim> 
        where id in 
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id}
        </foreach>

解释一下，prefix的意思是说在SQL语句之前拼接这个字符，suffixOverrides的意思是去掉最后一个重复的这个字符，上面的这个在MySQL执行的语句是这样的

UPDATE df
SET source_col_type = CASE
WHEN id = '1' THEN
	'a'
WHEN id = '2' THEN
	'b'
WHEN id = '3' THEN
	'c'
END
WHERE
	id IN(1, 2, 3)

case when类似于switch

switch(id){
  case 1:
  set 'a'
  break;
case 2:
  set 'b'
  break;
case 3:
  set 'c'
  break;
}

大概就是这个意思，，但是呢实际项目中我是没办法拿到id的。。。只能根据两个条件进行了。。所以要对上面的进行变形。。

update df
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="source_col_type =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    when name=#{name} and source=#{source} then #{item.sourceColType}
                </foreach>
            </trim>
        </trim> 
        where name in 
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.name}
        </foreach> 
        and source in 

      <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
          #{item.source}
      </foreach>

如果你有多个判定条件就这样写。

注意，在SQL语句中，如果有SQL的关键字。。一定要用``这个包裹起来。。要不然报错了就。。。切记