mysql通过字符串计算hashcode更新到原表和多表关联优化

mysql通过字符串计算hashcode更新到原表和多表关联优化

一. 需求描述
现有表a，要求通过每条数据中的三个字段计算hashcode值更新到原表做为shopid.
二. 第一种做法
最好的方式是通过spark sql计算出来更新，但是spark sql不能update，要想update要设计到改源码。
所以就退而求其次选择用JDBC更新，缺点是慢。
把主要代码贴一下：

result.foreachPartition(
      it => {
        var url = "jdbc:mysql://localhost/test?characterEncoding=utf8"
        val conn = DriverManager.getConnection(url, "root", "1234")
        val pstat = conn.prepareStatement("update t_test set shop_id=? where id=?")
        for (obj <- it) {
          pstat.setString(1, obj._2)
          pstat.setInt(2, obj._1)
          pstat.addBatch
        }
        try {
          pstat.executeBatch
        } finally {
          pstat.close
          conn.close
        }
      }
    )

三.第二种方法是用到spark和MySQL结合。这种虽然看着麻烦，但是超级快！！！
思路如下：
1.首先用spark读出表的信息，计算hashcode，得到id和hashcode。（id用作更新表时的判断条件）

 val conf = new SparkConf()
      .setMaster("local[*]")
      .setAppName("用户标签")
    val sc = new SparkContext(conf)
    val sqlC = new SQLContext(sc)
    val url = "jdbc:mysql://localhost:3306/test"
    val tableName = "t_test"

    // 设置连接用户&密码
    val prop = new java.util.Properties
    prop.setProperty("user", "root")
    prop.setProperty("password", "123")
    prop.setProperty("driver", "com.mysql.jdbc.Driver")
    val json: DataFrame = sqlC.read.jdbc(url, tableName, prop)
    val data = json.rdd.map(row => {
      val id = row.getAs[Int]("id")
      val update_count = row.getAs[Int]("update_count")
      val client_name = row.getAs[String]("client_name")
      val latitude = row.getAs[String]("latitude")
      val longitude = row.getAs[String]("longitude")
      val call_center = row.getAs[String]("call_center")
      (client_name,latitude,longitude,call_center,id,update_count)
    })
    val hashString: RDD[UpdateShopId] = data.map(x=>{
      val hashString = x._1 + x._2 + x._3 + x._4
      val shop_id: Int = hashString.hashCode
      UpdateShopId(x._5,shop_id)
    })

2.把计算好的（id,hashcode）导出到本地为.csv文件。

	//	注意要导入隐式转换，不然不能注册表
	import sqlC.implicits._

    val mapDF: DataFrame = hashString.toDF()

    mapDF.createOrReplaceTempView("hash")

    val resultDF: DataFrame = sqlC.sql("select * from hash")

    resultDF.write.mode("Append").csv("C:\\Users\\Desktop\\test\\a")

3.用navicat把.csv文件导入到数据库中。

4.通过sql完成最后一步，更新！

UPDATE t_test a,
 update_shop_id b
SET a.shop_id = b.shop_id
WHERE
	a.id = b.id

注：在MySQL两张表关联更新的时候，如果表的数据多，会非常非常慢，这里进行一些优化
1、不要直接去关联表名查询，在关联表外加个select查询。

UPDATE t_test a,
( select * from update_shop_id) b
SET a.shop_id = b.shop_id
WHERE
	a.id = b.id

2、主表有条件先select一遍主表，关联表有条件最好放最后where后。

四. 最后一种，在MySQL中创建函数，直接调用函数就行更新

DELIMITER $$

CREATE
    PROCEDURE `test`.`HashValue`(IN str CHAR(32))
    BEGIN
	
	SET @pos = 1;
	SET @hashValue = 0;
	SET @szHex = HEX(str);
	SET @size = LENGTH(@szHex);
	WHILE @pos<@size+1 DO
		SET @cCh = SUBSTRING(@szHex,@pos,2);
		SET @nCh =CAST(ASCII(UNHEX(@cCh)) AS UNSIGNED);
		SET @hashValue = @hashValue + @nCh;
		SET @pos = @pos + 2;
	END WHILE;

	SELECT @hashValue;
    END$$

DELIMITER ;