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

原创已于 2025-03-26 14:24:22 修改 · 2.6w 阅读

56 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #sql

于 2022-03-16 21:12:24 首次发布

mysql 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了MySQL中多表之间的常用操作，包括根据主键更新字段、`REPLACE INTO`的删除或插入功能以及`ON DUPLICATE KEY UPDATE`的更新或插入规则。通过实例展示了如何在不同场景下使用这些操作，并提到了它们在数据库同步和主从复制中的潜在问题。此外，还给出了实际的SQL示例和注意事项。

该文章已生成可运行项目，

文章目录

mysql多表之间的常用操作

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 首先尝试插入数据到表中，

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

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步骤

查看binlog是否开启： show variables like 'log_bin';
列出binlog：show binary logs; show binlog events in ‘binlog.000018’（展示细节）;
binlog文件位于mysql的data目录下
binlog文件执行器位于mysql的bin目录下
在 bin目录下，执行mysqlbinlog.exe --no-defaults --base64-output=decode-rows -v ../Data/binlog.000018 > test.txt 把binlog打印到 test.txt文件中
在test.txt文件中查看即可

replace into的应用注意事项：

插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
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>

3. on duplicate key update：更新或插入

这条语句的规则如下：

如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则仅执行update语句，反之，执行insert语句。 on duplicate key update与replace into的不同点在于：

当传入字段包含了唯一索引或者主键id，replace into 是先删除在插入，create_time与update_time会更新成当前时间
当传入字段包含了唯一索引或者主键id ，on duplicate key update 是直接更新，create_time不变，update_time会更新成当前时间
当 传入字段VALUES(value1, value2, value3, ...) 中对应字段的值和数据库一摸一样，就不会触发更新和插入，影响行数为 0 ， create_time 和 update_time 都不会发生变更！

语法如下：

INSERT INTO 
	tablename(field1,field2, field3, ...) 
	VALUES(value1, value2, value3, ...) 
	ON DUPLICATE KEY UPDATE 
	field1=values(field1),field2=values(field2), field3=values(field3), ...;

真实使用案例

写法一：单条批量INSERT语句（VALUES含多个元组）


    <insert id="replaceActualIncome" parameterType="java.util.List">
        insert into platform_actual_income (
                platform_id,
                shop_id,
                tid,
                consign_time,
                escrow_release_time,
                kj_type,
                shop_name,
                country_code,
                trade_status,
                actual_amount,
                need_check,
                currency_code)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.platformId},
            #{item.shopId},
            #{item.tid},
            #{item.consignTime},
            #{item.escrowReleaseTime},
            #{item.kjType},
            #{item.shopName},
            #{item.countryCode},
            #{item.tradeStatus},
            #{item.actualAmount},
            10,
            #{item.currencyCode})
        </foreach>
        ON DUPLICATE key update
        platform_id = values(platform_id),
        shop_id = values(shop_id),
        tid = values(tid),
        consign_time = values(consign_time),
        escrow_release_time = values(escrow_release_time),
        kj_type = values(kj_type),
        shop_name = values(shop_name),
        country_code = values(country_code),
        trade_status = values(trade_status),
        actual_amount = values(actual_amount),
        need_check = values(need_check),
        currency_code = values(currency_code)
    </insert>

写法二：多条INSERT语句（分号分隔）

 <foreach collection="list" item="item" separator=";">
            INSERT INTO temu_sku_stat
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="item.shopId!=null">
                    shop_id,
                </if>
                <if test="item.shopName!='' and item.shopName!=''">
                    shop_name,
                </if>
                <if test="item.statDate !=null">
                    stat_date,
                </if>
                <if test="item.apiSpecId!=null and item.apiSpecId!=''">
                    api_spec_id,
                </if>
                <if test="item.apiSpecNo!=null and item.apiSpecNo!=''">
                    api_spec_no,
                </if>
                <if test="item.apiSpecName!=null and item.apiSpecName!=''">
                    api_spec_name,
                </if>
                <if test="item.apiGoodsId!=null and item.apiGoodsId!=''">
                    api_goods_id,
                </if>
                <if test="item.apiGoodsName!=null and item.apiGoodsName!=''">
                    api_goods_name,
                </if>
                <if test="item.matchSpecId!=null and item.matchSpecId!=''">
                    match_spec_id,
                </if>
                <if test="item.matchSpecNo!=null and item.matchSpecNo!=''">
                    match_spec_no,
                </if>
                <if test="item.matchSpecName!=null and item.matchSpecName!=''">
                    match_spec_name,
                </if>
                <if test="item.deliverQty !=null">
                    deliver_qty,
                </if>
                <if test="item.realReceiveQty !=null">
                    real_receive_qty,
                </if>
                <if test="item.returnQty !=null">
                    return_qty,
                </if>
                <if test="item.salesQty!=null">
                    sales_qty,
                </if>
                <if test="item.incomeAmount!=null">
                    income_amount,
                </if>
                <if test="item.reserveAmount!=null">
                    reserve_amount,
                </if>
                <if test="item.releaseAmount!=null">
                    release_amount,
                </if>
                <if test="item.issueAmount!=null">
                    issue_amount,
                </if>
                <if test="item.returnLogisticsFee!=null">
                    return_logistics_fee,
                </if>
                <if test="item.preLogisticsFee!=null">
                    pre_logistics_fee,
                </if>
                <if test="item.skuFee !=null">
                    sku_fee,
                </if>
                <if test="item.currencyCode!=null and item.currencyCode!=''">
                    currency_code,
                </if>
                <if test="item.skuDimension!=null and item.skuDimension!=''">
                    sku_dimension,
                </if>
                <if test="item.created!=null">
                    created,
                </if>
                <if test="item.updated!=null">
                    updated,
                </if>
                <if test="item.fixCost!=null">
                    fix_cost,
                </if>
                <if test="item.outCost!=null">
                    out_cost,
                </if>
                <if test="item.strategyCost!=null">
                    strategy_cost,
                </if>
                <if test="item.apiGoodsNo!=null and item.apiGoodsNo!=null">
                    api_goods_no,
                </if>
                <if test="item.goodsDeveloper!=null and item.goodsDeveloper!=''">
                    goods_developer,
                </if>
                <if test="item.goodsDeveloperId!=null">
                    goods_developer_id,
                </if>
                <if test="item.redundanceStatDate!=null">
                    redundance_stat_date,
                </if>
                <if test="item.afterSalesCompensation!=null">
                    after_sales_compensation,
                </if>

                <if test="item.delayedShipping!=null">
                    delayed_shipping,
                </if>
                <if test="item.outOfStockUponPrepareOrderArrival!=null">
                    out_of_stock_upon_prepare_order_arrival,
                </if>
                <if test="item.qualityIssuesWithJitGoods!=null">
                    quality_issues_with_jit_goods,
                </if>
                <!--<if test="item.frenchEprEnvironmentalFee!=null">
                    french_epr_environmental_fee=VALUES(french_epr_environmental_fee),
                </if>-->
                <if test="item.postSalesResponsibleParty!=null">
                    post_sales_responsible_party,
                </if>
            </trim>
            <trim prefix="VALUES (" suffixOverrides="," suffix=")">
                <if test="item.shopId!=null">
                    #{item.shopId},
                </if>
                <if test="item.shopName!='' and item.shopName!=''">
                    #{item.shopName},
                </if>
                <if test="item.statDate !=null">
                    #{item.statDate},
                </if>
                <if test="item.apiSpecId!=null and item.apiSpecId!=''">
                    #{item.apiSpecId},
                </if>
                <if test="item.apiSpecNo!=null and item.apiSpecNo!=''">
                    #{item.apiSpecNo},
                </if>
                <if test="item.apiSpecName!=null and item.apiSpecName!=''">
                    #{item.apiSpecName},
                </if>
                <if test="item.apiGoodsId!=null and item.apiGoodsId!=''">
                    #{item.apiGoodsId},
                </if>
                <if test="item.apiGoodsName!=null and item.apiGoodsName!=''">
                    #{item.apiGoodsName},
                </if>
                <if test="item.matchSpecId!=null and item.matchSpecId!=''">
                    #{item.matchSpecId},
                </if>
                <if test="item.matchSpecNo!=null and item.matchSpecNo!=''">
                    #{item.matchSpecNo},
                </if>
                <if test="item.matchSpecName!=null and item.matchSpecName!=''">
                    #{item.matchSpecName},
                </if>
                <if test="item.deliverQty !=null">
                    #{item.deliverQty},
                </if>
                <if test="item.realReceiveQty !=null">
                    #{item.realReceiveQty},
                </if>
                <if test="item.returnQty !=null">
                    #{item.returnQty},
                </if>
                <if test="item.salesQty!=null">
                    #{item.salesQty},
                </if>
                <if test="item.incomeAmount!=null">
                    #{item.incomeAmount},
                </if>
                <if test="item.reserveAmount!=null">
                    #{item.reserveAmount},
                </if>
                <if test="item.releaseAmount!=null">
                    #{item.releaseAmount},
                </if>
                <if test="item.issueAmount!=null">
                    #{item.issueAmount},
                </if>
                <if test="item.returnLogisticsFee!=null">
                    #{item.returnLogisticsFee},
                </if>
                <if test="item.preLogisticsFee!=null">
                    #{item.preLogisticsFee},
                </if>

                <if test="item.skuFee !=null">
                    #{item.skuFee},
                </if>
                <if test="item.currencyCode!=null and item.currencyCode!=''">
                    #{item.currencyCode},
                </if>
                <if test="item.skuDimension!=null and item.skuDimension!=''">
                    #{item.skuDimension},
                </if>
                <if test="item.created!=null">
                    #{item.created},
                </if>
                <if test="item.updated!=null">
                    #{item.updated},
                </if>
                <if test="item.fixCost!=null">
                    #{item.fixCost},
                </if>
                <if test="item.outCost!=null">
                    #{item.outCost},
                </if>
                <if test="item.strategyCost!=null">
                    #{item.strategyCost},
                </if>
                <if test="item.apiGoodsNo!=null and item.apiGoodsNo!=null">
                    #{item.apiGoodsNo},
                </if>
                <if test="item.goodsDeveloper!=null and item.goodsDeveloper!=''">
                    #{item.goodsDeveloper},
                </if>
                <if test="item.goodsDeveloperId!=null">
                    #{item.goodsDeveloperId},
                </if>
                <if test="item.redundanceStatDate!=null">
                    #{item.redundanceStatDate},
                </if>
                <if test="item.afterSalesCompensation!=null">
                    #{item.afterSalesCompensation},
                </if>

                <if test="item.delayedShipping!=null">
                    #{item.delayedShipping},
                </if>
                <if test="item.outOfStockUponPrepareOrderArrival!=null">
                    #{item.outOfStockUponPrepareOrderArrival},
                </if>
                <if test="item.qualityIssuesWithJitGoods!=null">
                    #{item.qualityIssuesWithJitGoods},
                </if>
                <!--<if test="item.frenchEprEnvironmentalFee!=null">
                    french_epr_environmental_fee=VALUES(french_epr_environmental_fee),
                </if>-->
                <if test="item.postSalesResponsibleParty!=null">
                    #{item.postSalesResponsibleParty},
                </if>

            </trim>
            ON DUPLICATE KEY UPDATE
            <trim suffixOverrides=",">
                <if test="item.shopId!=null">
                    shop_id =VALUES(shop_id),
                </if>
                <if test="item.shopName!='' and item.shopName!=''">
                    shop_id =VALUES(shop_id),
                </if>
                <if test="item.statDate !=null">
                    stat_date =VALUES(stat_date),
                </if>
                <if test="item.apiSpecId!=null and item.apiSpecId!=''">
                    api_spec_id=VALUES(api_spec_id),
                </if>
                <if test="item.apiSpecNo!=null and item.apiSpecNo!=''">
                    api_spec_no=VALUES(api_spec_no),
                </if>
                <if test="item.apiSpecName!=null and item.apiSpecName!=''">
                    api_spec_name=VALUES(api_spec_name),
                </if>
                <if test="item.apiGoodsId!=null and item.apiGoodsId!=''">
                    api_goods_id=VALUES(api_goods_id),
                </if>
                <if test="item.apiGoodsName!=null and item.apiGoodsName!=''">
                    api_goods_name=VALUES(api_goods_name),
                </if>
                <if test="item.matchSpecId!=null and item.matchSpecId!=''">
                    match_spec_id=VALUES(match_spec_id),
                </if>
                <if test="item.matchSpecNo!=null and item.matchSpecNo!=''">
                    match_spec_no=VALUES(match_spec_no),
                </if>
                <if test="item.matchSpecName!=null and item.matchSpecName!=''">
                    match_spec_name=VALUES(match_spec_name),
                </if>
                <if test="item.deliverQty !=null">
                    deliver_qty =VALUES(deliver_qty),
                </if>
                <if test="item.realReceiveQty !=null">
                    real_receive_qty =VALUES(real_receive_qty),
                </if>
                <if test="item.returnQty !=null">
                    return_qty =VALUES(return_qty),
                </if>
                <if test="item.salesQty!=null">
                    sales_qty =VALUES(sales_qty),
                </if>
                <if test="item.incomeAmount!=null">
                    income_amount =VALUES(income_amount),
                </if>
                <if test="item.reserveAmount!=null">
                    reserve_amount =VALUES(reserve_amount),
                </if>
                <if test="item.releaseAmount!=null">
                    release_amount =VALUES(release_amount),
                </if>
                <if test="item.issueAmount!=null">
                    issue_amount =VALUES(issue_amount),
                </if>
                <if test="item.returnLogisticsFee!=null">
                    return_logistics_fee =VALUES(return_logistics_fee),
                </if>
                <if test="item.preLogisticsFee!=null">
                    pre_logistics_fee =VALUES(pre_logistics_fee),
                </if>

                <if test="item.skuFee !=null">
                    sku_fee =VALUES(sku_fee),
                </if>
                <if test="item.currencyCode!=null and item.currencyCode!=''">
                    currency_code =VALUES(currency_code),
                </if>
                <if test="item.skuDimension!=null and item.skuDimension!=''">
                    sku_dimension=VALUES(sku_dimension),
                </if>

                <if test="item.updated">
                    updated =VALUES(updated),
                </if>
                <if test="item.fixCost!=null">
                    fix_cost=VALUES(fix_cost),
                </if>
                <if test="item.outCost!=null">
                    out_cost=VALUES(out_cost),
                </if>
                <if test="item.strategyCost!=null">
                    strategy_cost=VALUES(strategy_cost),
                </if>
                <if test="item.apiGoodsNo!=null and item.apiGoodsNo!=''">
                    api_goods_no=VALUES(api_goods_no),
                </if>
                <if test="item.goodsDeveloper!=null and item.goodsDeveloper!=''">
                    goods_developer=VALUES(goods_developer),
                </if>
                <if test="item.goodsDeveloperId!=null">
                    goods_developer_id=VALUES(goods_developer_id),
                </if>
                <if test="item.redundanceStatDate!=null">
                    redundance_stat_date=VALUES(redundance_stat_date),
                </if>
                <if test="item.afterSalesCompensation!=null">
                    after_sales_compensation=VALUES(after_sales_compensation),
                </if>

                <if test="item.delayedShipping!=null">
                    delayed_shipping=VALUES(delayed_shipping),
                </if>
                <if test="item.outOfStockUponPrepareOrderArrival!=null">
                    out_of_stock_upon_prepare_order_arrival=VALUES(out_of_stock_upon_prepare_order_arrival),
                </if>
                <if test="item.qualityIssuesWithJitGoods!=null">
                    quality_issues_with_jit_goods=VALUES(quality_issues_with_jit_goods),
                </if>
               <!-- <if test="item.frenchEprEnvironmentalFee!=null">
                    french_epr_environmental_fee=VALUES(french_epr_environmental_fee),
                </if>-->
                <if test="item.postSalesResponsibleParty!=null">
                    post_sales_responsible_party=VALUES(post_sales_responsible_party),
                </if>
            </trim>
        </foreach>

本文章已经生成可运行项目