mysql把一个表的字段赋值到另一张表，多表之间常用的操作

文章目录

• mysql多表之间的常用操作
• • 1. 根据主键，把一个表的字段赋值到另一张表
  • 2. replace into：把一张表的数据新增或更新到另一张表

mysql多表之间的常用操作

1. 根据主键，把一个表的字段赋值到另一张表

user表

userId

username

password

sex

addr

phone

1

张珊

123

男

北京市

1562356586

2

李思

456

女

北京市

1562354256

score表

scoreId

userId

username

score

course

phone

1

1

80

语文

2

1

85

数学

需求：根据userId 把user表的 username 和phone字段填充到score表中

update score,user
set score.username = user.username , score.phone = user.phone
where score.userId = user.userId

或者

update score join user on score.userId=user.userId
set score.username = user.username , score.phone = user.phone 

（生产案例）

update shopee_finances_escrow,ods_api_trade 
set shopee_finances_escrow.trade_status = ods_api_trade.trade_status
where shopee_finances_escrow.id = ods_api_trade.rec_id
and shopee_finances_escrow.shop_id = 28

2. replace into：把一张表的数据新增或更新到另一张表

replace是insert的增强版

replace into 首先尝试插入数据到表中，

1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。
2. 否则，直接插入新数据

replace into的三种使用： 点击查看详情！

• replace into … values …
• replace into … select (valueA, valueB, …) from table
• replace into tbl_name set colA=valueA, …

replace into 在binlog的表现形式：注意如果有重复数据时，binlog会记录成update的形式

• 1、replace into tbl_name(col_name, …) values(…)
  无重复数据时插入replace into t1 values(1,“liu”,28);
  
  有重复数据时插入replace into t1 values(1,“yun”,29);
  
  2.replace into tbl_name(col_name, …) select …
  无重复数据时插入replace into t1 select * from t2
  
  有重复数据时插入replace into t1 select * from t2
  

windows查看binlog步骤

1. 查看binlog是否开启： show variables like 'log_bin';
   

2. 列出binlog：show binary logs; show binlog events in ‘binlog.000018’（展示细节）;
   

3. binlog文件位于mysql的data目录下
   

4. binlog文件执行器位于mysql的bin目录下
   

5. 在 bin目录下，执行mysqlbinlog.exe --no-defaults --base64-output=decode-rows -v ../Data/binlog.000018 > test.txt 把binlog打印到 test.txt文件中

6. 在test.txt文件中查看即可
   

replace into的应用注意事项：

1. 插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
2. replace操作在自增主键的情况下，遇到唯一键冲突时执行的是delete+insert,但是在记录binlog时,却记录成了update操作,update操作不会涉及到auto_increment的修改。备库应用了binlog之后，备库的表的auto_increment属性不变。如果主备库发生主从切换，备库变为原来的主库，写新的主库则有风险发生主键冲突 点击查看id自增时，使用replace into备机可能存在的问题!
   点击查看id自增时，使用replace into备机可能存在的问题!

示例：

    <insert id="replaceIntoOrderItemData">
        REPLACE INTO shopee_aggregation_order_items (
            id,
            platform_id,
            shop_id,
            tid,
            item_id,
            item_name,
            item_sku
        ) 
        SELECT
	        id,
	        platform_id,
	        shop_id,
	        tid,
	        item_id,
	        item_name,
	        item_sku
        FROM
            shopee_finances_escrow_items
        WHERE 1=1
        <if test="shopId != null and shopId != '' ">
            and shop_id = #{shopId}
        </if>
        <if test="tidList != null and tidList.size > 0">
            and tid in
            <foreach collection="tidList" index="index" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>

	</insert>