mysql插入和更新问题记录

原创 已于 2025-11-16 23:47:12 修改 · 1.3k 阅读 · 10 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#mysql #数据库
于 2024-10-15 17:28:15 首次发布
UPDATE base_dictionary_class
SET id = 3, class_group = 4, class_name = 6
WHERE id = 3 AND class_group = 4;

IF ROW_COUNT()=0 THEN
INSERT INTO
base_dictionary_class (id, class_group, class_name)
VALUES (3, 4, 6);
END IF;

1、问题分析

  1. IF 语句的用法：

    • SQL 的 IF 语句一般是在存储过程或触发器中使用，而不能在常规的 SQL 语句中直接使用。 如果你在数据库客户端或管理工具中直接执行这段代码，会导致错误。

  2. 更新和插入的逻辑：

    • UPDATE 语句的逻辑上似乎是想要更新某一条记录，但在更新条件上使用了 id = 3 和 class_group = 4，这意味着只有在记录已经存在且符合条件时，才会进行更新。若没有符合条件的记录，才会插入新记录。

2、修正建议

如果你的目标是实现上述逻辑，可以将其放入一个存储过程，或者通过应用程序代码来处理。以下是一个如何在存储过程中实现这一逻辑的示例：

使用存储过程
DELIMITER //

CREATE PROCEDURE UpdateOrInsert()
BEGIN
    -- 更新记录
    UPDATE base_dictionary_class
    SET class_group = 4, class_name = 6
    WHERE id = 3 AND class_group = 4;

    -- 检查更新是否影响行数
    IF ROW_COUNT() = 0 THEN
        -- 插入新记录
        INSERT INTO base_dictionary_class (id, class_group, class_name)
        VALUES (3, 4, 6);
    END IF;
END //

DELIMITER ;

解释

  1. DELIMITER //: 改变语句结束符，以便可以在存储过程体中使用 ;。
  2. CREATE PROCEDURE: 创建一个名为 UpdateOrInsert 的存储过程。
  3. UPDATE: 尝试更新记录。
  4. IF ROW_COUNT() = 0: 如果没有记录被更新，则插入新记录。

调用存储过程

在创建存储过程后，你可以通过以下命令调用它：

CALL UpdateOrInsert();

直接在应用程序中处理逻辑

如果不想使用存储过程，你可以在应用程序的代码逻辑中实现类似的功能。首先执行 UPDATE，然后检查影响的行数，再决定是否执行 INSERT。

总结

在 SQL 中，IF 语句需要在存储过程或触发器中使用，不能直接用于普通的 SQL 脚本中。你可以将整个逻辑放入存储过程，或者在你的应用程序中处理这种逻辑。

在 SQL 中，IF 语句不能在常规的 SQL 语句中直接使用，它只能在存储过程、函数或触发器中使用。因此，如果你在 SQL 客户端中直接运行你提供的代码，会导致语法错误。

要使这个逻辑可以直接运行，你需要用单独的 SQL 语句处理更新和插入操作。可以通过应用程序逻辑或者在 SQL 脚本中使用条件查询来实现类似的效果。以下是如何修改你的 SQL 代码，使其能够直接运行的几种方法：

3、方法 1: 使用条件逻辑和多条语句

你可以分别执行更新和插入的操作，但在更新之后使用一个简单的 SELECT 来判断是否有行被更新。

-- 更新记录
UPDATE base_dictionary_class
SET id = 4, class_group = 4, class_name = 6
WHERE id = 5 AND class_group = 4;

-- 检查更新是否影响行数
SELECT ROW_COUNT() AS affected_rows;

-- 插入新记录（如果需要）
INSERT INTO base_dictionary_class (id, class_group, class_name)
SELECT 5, 4, 6
WHERE ROW_COUNT() = 0;

上面语句在你的 SQL 代码中，你希望检查更新是否影响了行数，并在没有影响任何行的情况下插入新记录。但是，ROW_COUNT() 只能在当前会话中使用，而在插入新记录的 INSERT 语句中，它可能不返回你预期的结果。因为每次执行 SQL 语句后，ROW_COUNT() 的结果都会更新。这里我们可以使用其他方法来实现你想要的功能。

以下是一个更新逻辑的改写示例，确保可以正确处理更新和插入：

-- 更新记录
UPDATE base_dictionary_class
SET class_name = 6
WHERE id = 2 AND class_group = 1;

-- 检查更新是否影响行数
SET @affected_rows = ROW_COUNT();

-- 插入新记录（如果没有更新）
INSERT INTO base_dictionary_class (id, class_group, class_name)
SELECT 2, 1, 6
WHERE @affected_rows = 0;

关键改动说明：

  1. 设置变量：在检查受影响行数时，将 ROW_COUNT() 的结果存储在一个用户定义的变量中，例如 @affected_rows。这样，在插入语句中可以使用这个变量进行判断，而不会受到 ROW_COUNT() 的影响。

  2. 更新语句：在更新语句中，只更新 class_name。因为你已经在 WHERE 子句中指定了 id 和 class_group，这条更新语句会准确更新对应的记录。

  3. 插入语句：在插入新记录的 INSERT 语句中，使用 @affected_rows = 0 作为条件。这确保只有在更新未影响行时才会插入新记录。

注意事项：

  • 确保 base_dictionary_class 表中允许有重复的 id 值（如 id = 2），否则插入操作可能会因为唯一性约束而失败。如果 id 字段是主键，应该考虑使用不同的 id 值进行插入。
  • 如果在执行此 SQL 代码的环境中不支持用户变量（如某些 ORM 框架），你可以通过编程逻辑来分步执行更新和插入操作。

另外的解决方案：

如果你可以使用存储过程或事务，可以将这些逻辑封装起来，这样可以更清晰地管理状态和操作。是错的，修改为

4、方法 2: 使用 INSERT ... ON DUPLICATE KEY UPDATE

如果你的表有唯一索引（例如，id 列），你可以使用 INSERT ... ON DUPLICATE KEY UPDATE 来合并这两个操作。

INSERT INTO base_dictionary_class (id, class_group, class_name)
VALUES (5, 4, 6)
ON DUPLICATE KEY UPDATE
    class_group = 4, class_name = 6;

这种方法的优点是无论是插入还是更新，它都可以在一个语句中完成。注意，在使用此方法之前，确保 id 列是唯一的主键或有唯一索引。

5、方法 3: 使用存储过程（如前所述）

如果你希望保留 IF 语句，可以将其放入存储过程中。

DELIMITER //

CREATE PROCEDURE UpdateOrInsert()
BEGIN
    -- 更新记录
    UPDATE base_dictionary_class
    SET id = 4, class_group = 4, class_name = 6
    WHERE id = 5 AND class_group = 4;

    -- 检查更新是否影响行数
    IF ROW_COUNT() = 0 THEN
        -- 插入新记录
        INSERT INTO base_dictionary_class (id, class_group, class_name)
        VALUES (5, 4, 6);
    END IF;
END //

DELIMITER ;

-- 调用存储过程
CALL UpdateOrInsert();

选择方法

  • 如果你只是想快速执行更新和插入，方法 1 或 方法 2 是最简洁的。
  • 如果你的数据库逻辑更复杂，或者你希望重用这段代码，那么使用 方法 3（存储过程）可能是更好的选择。

请根据你的需求和数据库结构选择适合的方法。

6、方法 4: python连接mysql完成插入和更新版本1

from sqlalchemy import create_engine,text
from sqlalchemy.orm import sessionmaker

# 建立数据库连接，用于入ip服务器database数据库下表格中
def engine_conn():
    # 建立数据库连接，用于入ip服务器database数据库
    url = urllib.parse.quote_plus('xxx@xx')
    engine = create_engine(f"mysql+pymysql://{user}:{url}@{ip}:{port}/{database}?charset=utf8")
    return engine

def get_column_names(table_name):
    engine = engine_conn()
    query = f"SELECT * FROM {table_name} LIMIT 0"
    df = pd.read_sql_query(query, engine)
    column_names = df.columns.tolist()
    # 注：这里还可能通过information_shema系统数据库获取
    '''
    python执行cursor.fetchall()将返回元组列表[(,),(,),...]
    SELECT COLUMN_NAME
	FROM information_schema.COLUMNS
	WHERE TABLE_SCHEMA = 'your_database_name'
	  AND TABLE_NAME = 'your_table_name';
	'''
    return column_names

def update_and_insert_hrs(table,df_data,engine):
    Session = sessionmaker(bind=engine)
    session = Session()
    total_rows = len(df_data)
    for index, row in tqdm(df_data.iterrows(), total=total_rows, unit='row', desc='Processing'):
        sql_insert = f'''
                    INSERT INTO {table} ({", ".join(df_data.columns)})
                    SELECT {":" + ", :".join(df_data.columns)}
                    WHERE NOT EXISTS 
                    (SELECT column1, column2, column3  FROM {table} 
                    WHERE column1 = :column1 AND column2 = :column2 AND column3 = :column3
                    )
                '''

        dic_params = {col: getattr(row, col) for col in df_data.columns}
        session.execute(text(sql_insert), dic_params)

        sql_update = f'''
                    UPDATE {table}
                    SET  {", ".join([f"{column} = :{column}"
                                     for column in df_data.columns if column != 'column1' and column != 'column2' and column != 'column3'])}
                    WHERE column1 = :column1 AND column2 = :column2 AND column3 = :column3
                    '''
        session.execute(text(sql_update), dic_params)

    session.commit()  # 将所有数据的插入和更新操作一起提交
    session.close()  # 关闭session

此方法存在插入后又更新一次的情况，但对整体任务完成没有影响。

7、方法 5: python连接mysql完成插入和更新版本2

import pymysql

ip='xxx'
port='xxx'
user='xxx'
passwd='xxx'
db='xxx'

def get_conn():
    conn = pymysql.connect(host={ip},
                           port={port},
                           user={user},
                           passwd={passwd},
                           db={db},
                           charset="utf8")
    return conn

def update_and_insert_1(table, column1, column2, column3):
    conn = get_conn()
    cursor = conn.cursor()
    column3 = column3[:-1]
    update_sql = f"""
                    UPDATE {table} 
                    SET column2={column2}, column3='{column3}' 
                    WHERE column1='{column1}';
                  """
    insert_sql = f"""
                    INSERT INTO {table} (column1, column2, column3)
                    SELECT '{column1}', {column2}, '{column3}'
                    WHERE NOT EXISTS (SELECT 1  FROM {table} WHERE column1 = '{column1}' AND column2={column2} AND column3='{column3}');
                  """
    # 执行更新操作
    cursor.execute(update_sql)
    # 注意：这里执行更新操作后，没有执行conn.commit()提交事物，可能会出错，可加上去尝试

    # 检查是否更新了行（这里不直接检查 ROW_COUNT，而是通过再次查询来避免竞态条件）
    cursor.execute("SELECT COUNT(*) FROM {table} WHERE column1 = %s AND photo_class is null",
                   (column1))
    count = cursor.fetchone()[0]

    # 如果没有更新行，则执行插入操作
    if count == 0:
        cursor.execute(insert_sql)
        conn.commit()  # 提交事务（如果需要的话）
    conn.close()
    return None
    
def update_and_insert_2(table, column_order, dic_result):
    """Get the photo category."""
    conn = get_conn()
    cursor = conn.cursor()
    # 遍历字典
    for key, value_ls in dic_result.items():
        # 将列表转换为逗号分隔的字符串（注意：这里假设列表中的元素不包含逗号或特殊字符）
        column2 = ', '.join(map(str, value_ls))

        # 准备 SQL 语句（使用参数化查询来避免 SQL 注入）
        update_sql = """  
        UPDATE {table}  
        SET column2 = %s  
        WHERE column_order = %s AND column1 = %s;  
        """
        insert_sql = """  
        INSERT INTO {table} (column_order, column1, column2)  
        SELECT %s, %s, %s  
        WHERE NOT EXISTS (  
            SELECT 1  
            FROM {table}  
            WHERE column_order = %s AND column1 = %s  
        );  
        """

        # 执行更新操作
        cursor.execute(update_sql, (column2, column_order, key))
        # conn.commit()  # 提交事务（如果需要的话）,此处需特别注意，需要注释回去

        # 检查是否更新了行（这里不直接检查 ROW_COUNT，而是通过再次查询来避免竞态条件）
        cursor.execute("SELECT COUNT(*) FROM {table} WHERE column_order = %s AND column1 = %s",
                       (column_order, key))
        count = cursor.fetchone()[0]

        # 如果没有更新行，则执行插入操作
        if count == 0:
            cursor.execute(insert_sql, (column_order, key, column2, column_order, key))
            conn.commit()  # 提交事务（如果需要的话）
    conn.close()
    return None

8、方法 6: 在mysql部分关键字段设置成unique可避免重复

try:
	cursor.execute(insert)
except:
	cursor.execute(update)

try:
	df.to_sql()
except:
	continue()
MySQL数据库中实现记录的插入和更新操作有多种方式。在本文中，我们将讨论其中的几种常见实现方式，并提供相应的源代码示例。
HackGJN的博客
09-23 255
如果你只需要插入一条新记录或更新现有记录的某些列，可以使用第一种方法。该方法首先尝试插入一条新记录，如果唯一键冲突，则从子查询中选择指定的列值进行更新。该语句首先尝试插入一条新记录，如果存在唯一键冲突，则删除旧记录并插入新记录。以上是几种常见的MySQL数据库中实现记录插入和更新的方法。替换为目标表的实际名称，并提供要插入或更新的列名和相应的值。替换为目标表的实际名称，并提供要插入或更新的列名和相应的值。替换为目标表的实际名称，并提供要插入或更新的列名和相应的值。替换为你要检查的唯一键的值。
MySQL修炼手册7：数据修改基础：INSERT、UPDATE、DELETE语句详解
theskylife的博客
01-13 4496
在掌握了MySQL数据库的基础之后，学习如何对数据进行有效的修改是至关重要的。本篇博客旨在提供一个深入的指南，涵盖了数据修改的三大基础操作：插入（INSERT）、更新（UPDATE）、删除（DELETE）
MySQL插入并更新
chengyuan的博客
06-21 808
Mysql方式。
mysql插入并更新（on duplicate key update）
lw18751836671的专栏
03-24 8541
Mysql插入数据，如果存在此数据，那么就更新这条数据，否则插入数据。 使用 on duplicate key update。 准备工作 建表 create table user_info( id int auto_increment primary key, name varchar(20), user_code varchar(20) unique not null, age int ); 建立了一个表，这个表有主键（id），有唯一键（user_code）。 插入数据 insert int
Mybatis语法（四）mysql插入或更新
w_t_y_y的博客
05-31 8217
现有user表，userId为用户id，做为数据表user的主键： 由于userId不可以重复，而这里userId直接作为主键。为防止并发操作，插入语句可以这样设计：不存在userId则插入，否则更新： <!--修改或更新--> <insert id="insertOrUpdate" parameterType="com.iflytek.edu.zx.activ...
Django 使用 MySQL 存储时间中遇到的问题(在数据库中记录插入时间、更新时间、删除时间)
热门推荐
技术是条不归路
08-20 1万+
一、MySQL 的时间存储格式 首先，把 MySQL 的时间类型做一下解释。在 MySQL 中，表示时间值的DATE和时间类型为 DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的 MySQL 不能表示的值时使用“零”值。TIMESTAMP 类型有专有的自动更新特性。         1. DATE，日期。支持的范围为
php gmtime存mysql_Django 使用 MySQL 存储时间中遇到的问题(在数据库中记录插入时间、更新时间、删除时间)...
weixin_39531183的博客
01-19 180
首先，把 MySQL 的时间类型做一下解释。在 mysql 中，表示时间值的DATE和时间类型为 DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的 MySQL 不能表示的值时使用“零”值。TIMESTAMP 类型有专有的自动更新特性。1. DATE，日期。支持的范围为'1000-01-01'到'9999-12-31'。M...
MySQL ON DUPLICATE KEY UPDATE影响行数
CoderJia的学习之路
07-16 1076
经常使用 `ON DUPLICATE KEY UPDATE` 来插入数据或者更新已存在的记录，使用到 MySQL 一个 `INSERT ... ON DUPLICATE KEY UPDATE` 的语法，明明只更新了两条记录，返回的影响行数竟然是 4
mysql 插入 已有跳过_Mysql插入数据：不存在则插入，存在则跳过或更新
weixin_39673704的博客
02-07 2297
一般情况下我们会写两条sql去操作select 查询当前插入的数据是否已经存在如果不存在就插入新的记录，如果存在就提示用户该记录已经存在在mysql里面可以这么操作，如果记录存在就跳过或者更新，不存在就插入！IGNORE这个是根据插入表的主键和唯一索引去匹配的，如果当前插入的记录存在则跳过不插入。示例：INSERT IGNORE INTO `user` (‘phone‘) VALUES (‘188...
mysql数据库 一条语句实现记录存在则更新，不存在则插入
whatday的专栏
02-24 1933
问题产生 博主相信很多人在开发的时候难免会出现需要先判断某个记录是否存在，存在则更新，不存在则插入新记录的情况（如在填写报名表的时候有用户需要修改信息等）。通常的做法是先使用 DELETE 语句先删除一下再使用 INSERT INTO 语句插入，这样做显然效率会比较低。 解决办法 MySQL 为我们提供了一个新的途径可以方便地实现仅执行一条 SQL 语句达成这个操，那就是 REPLACE INT...
MySQL插入、更新和删除
旭日的博客
01-12 2235
在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表（请参阅后面的小节），然后再重建它，而不是简单地用创建表语句覆盖它。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。但是，不一定要求列名匹配。
mysql插入更新insert into
牧竹子
07-06 1309
1.如果表中不存在主键记录，replace和insertupdate都与insert是一样的特点。2.如 果表中存在主键记录，replace相当于执行delete 和 insert两条操作，而insertupdate的相当于执行if exist do update else do insert操作。因此，如果replace填充的字段不全，则会导致未被更新的字段都会修改为默认值，并且如果有自增id的话，自增id会变化为最新的 值（这样如果是以自增id为标志的话可能导致记录丢失）；
MySQL--插入、更新与删除数据
恐龙让Lee的博客
08-02 1787
插入、更新与删除数据：插入数据：、为表的所有字段插入数据、为表的指定字段插入数据、同时插入多条记录、将查询结果插入到表中、更新数据、删除数据、为表增加计算列
MySQL-插入、更新与删除数据
真正的大师永远怀着一颗学徒的心
09-11 6078
MySQL-插入、更新与删除数据
MySQL插入或更新数据技巧
weixin_35308656的博客
12-18 655
在MySQL中，插入或更新数据时有一些常用的技巧可以帮助你更高效、准确地操作数据库。
php更新mysql大量数据_MySQL快速插入/更新大量记录
weixin_39561168的博客
02-19 313
最近的项目中需要将几个dump文件(文本格式、1~2G)的记录导入到mysql数据库中，由于数据量比较大(几百万、上千万条记录)，有插最近的项目中需要将几个dump文件(文本格式、1~2G)的记录导入到mysql数据库中，由于数据量比较大(几百万、上千万条记录)，有插入记录，也有更新记录的，导致插入\更新速度比较慢。一开始，将单条sql语句交给mysql执行，测试下来，最快一次也要一个半小时。于是...
mysql 插入或更新
eli的博客
11-04 5716
插入数据时，我们经常会遇到这样的情况：1、首先判断数据是否存在；2、如果不存在，则插入；3、如果存在，则更新。 在SQLServer中可以这样处理： if not exists (select 1 from t where id = 1)? insert into t(id, update_time) values(1, getdate()) else update t set updat...
python中单个和批量增加更新的mysql（没有则插入，有则更新）
weixin_30897079的博客
05-06 336
建表语句： DROP TABLE IF EXISTS `stock_discover`; CREATE TABLE `stock_discover` ( `code` char(6) NOT NULL, `index` int(11) unsigned NOT NULL DEFAULT '0', `name` varchar(20) NOT NULL, `excha...
Mysql 插入记录时检查记录是否已经存在，存在则更新，不存在则插入记录SQL
08-23 463
Mysql 插入记录时检查记录是否已经存在，存在则更新，不存在则插入记录SQL 我们在开发数据库相关的逻辑过程中， 经常检查表中是否已经存在这样的一条记录， 如果存在则更新或者不做操作， 如果没有存在记录，则需要插入一条新的记录。 这样的逻辑固然可以通过两条sql语句完成。 SELECT COUNT(*) FROM x...
告别重复数据烦恼！MySQL ON DUPLICATE KEY UPDATE 优雅解决存在更新/不存在插入难题
最新发布
从基础到源码解析的学习记录
10-09 4123
MySQL中的ON DUPLICATE KEY UPDATE语法提供了一种高效的"存在则更新，不存在则插入"解决方案。该语法在插入数据与主键或唯一索引冲突时，自动转为执行UPDATE操作。基本语法为在INSERT语句后添加ON DUPLICATE KEY UPDATE子句，并通过VALUES()函数引用原插入值。典型应用场景包括计数器更新、配置项维护和购物车管理等。高级用法支持条件更新、多表关联和批量操作优化。相比REPLACE INTO（先删后插）和INSERT IGNORE（忽略冲突
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值